SQLite

Creating the app

We'll start by creating an Electron-Vite boilerplate app:

npm create @quick-start/electron@latest

Need to install the following packages:
@quick-start/create-electron@1.0.23
Ok to proceed? (y)
✔ Project name: … sqlite
✔ Select a framework: › react
✔ Add TypeScript? … [No] / Yes
✔ Add Electron updater plugin? … [No] / Yes
✔ Enable Electron download mirror proxy? … [No] / Yes

We'll then add the node-zigar module:

cd sqlite
npm install
npm install node-zigar

After that we'll install zig-sqlite, a Zig package that provides a wrapper around the SQLite C API. As there is currently no central repository for Zig packages, you'll need to obtain zig-sqlite from the source. First, go to the project's Github page. Click on the SHA of the last commit:

Then click the "Browse files" button:

Then click the "Code" button, right-click on "Download ZIP", and select "Copy link address":

Go back to the terminal, create the sub-directory zig, and cd to it:

mkdir zig
cd zig

Create an empty build.zig:

touch build.zig

Enter "zig fetch --save " then paste the copied URL and press ENTER:

zig fetch --save https://github.com/vrischmann/zig-sqlite/archive/91e5fedd15c5ea3cb42ccceefb3d0f4bb9bad68f.zip

That'll fetch the package and create a build.zig.zon listing it as a dependency. We'll then fill in build.zig:

const std = @import("std");
const cfg = @import("./build-cfg.zig");

pub fn build(b: *std.Build) void {
    const target = b.standardTargetOptions(.{});
    const optimize = b.standardOptimizeOption(.{});
    const lib = b.addSharedLibrary(.{
        .name = cfg.module_name,
        .root_source_file = .{ .cwd_relative = cfg.stub_path },
        .target = target,
        .optimize = optimize,
    });
    const sqlite = b.dependency("sqlite", .{
        .target = target,
        .optimize = optimize,
    });
    const imports = .{
        .{ .name = "sqlite", .module = sqlite.module("sqlite") },
    };
    const mod = b.createModule(.{
        .root_source_file = .{ .cwd_relative = cfg.module_path },
        .imports = &imports,
    });
    mod.addIncludePath(.{ .cwd_relative = cfg.module_dir });
    lib.root_module.addImport("module", mod);
    if (cfg.use_libc) {
        lib.linkLibC();
    }
    const wf = switch (@hasDecl(std.Build, "addUpdateSourceFiles")) {
        true => b.addUpdateSourceFiles(),
        false => b.addWriteFiles(),
    };
    wf.addCopyFileToSource(lib.getEmittedBin(), cfg.output_path);
    wf.step.dependOn(&lib.step);
    b.getInstallStep().dependOn(&wf.step);
}

For this example we're going to the sample database provided by sqlitetutorial.net:

Download chinook.zip and unzip the file into the sqlite directory.

In your text editor, create sqlite.zig in the zig sub-directory. Add the following code:

const std = @import("std");
const sqlite = @import("sqlite");

var gpa = std.heap.GeneralPurposeAllocator(.{}){};

const SqliteOpaquePtr = *align(@alignOf(sqlite.Db)) opaque {};

pub fn openDb(path: [:0]const u8) !SqliteOpaquePtr {
    const allocator = gpa.allocator();
    const db_ptr = try allocator.create(sqlite.Db);
    errdefer allocator.destroy(db_ptr);
    db_ptr.* = try sqlite.Db.init(.{
        .mode = .{ .File = path },
        .open_flags = .{},
        .threading_mode = .MultiThread,
    });
    return @ptrCast(db_ptr);
}

pub fn closeDb(db_op: SqliteOpaquePtr) void {
    const db_ptr: *sqlite.Db = @ptrCast(db_op);
    db_ptr.deinit();
    const allocator = gpa.allocator();
    allocator.destroy(db_ptr);
}

openDb() allocates a memory from the heap to hold the Db struct from zig-sqlite. It then calls Db.init() to open the database file specified by path. We return the pointer to Db as an opaque pointer so we aren't leaking zig-sqlite's implementation details to the JavaScript side.

closeDb() takes a opaque pointer and casts it back into a pointer to Db. It calls Db.deinit() then frees the struct.

After, creating the zig file, open index.js in src/main. Scroll to the bottom and add the following lines:

// In this file you can include the rest of your app"s specific main process
// code. You can also put them in separate files and require them here.
require ('node-zigar/cjs');
const { openDb, closeDb } = require('../../zig/sqlite.zig');

const path = resolve(__dirname, '../../chinook.db');
const db = openDb(path);
closeDb(db);

Start Electron-Vite in dev mode:

npm run dev

You should see a message indicating the sqlite module is being built. After a while the Electron window will open. That means our functions work. We don't see anything because all our code does is open and close the database file. Let us create a function that query the database.

Our function is going to run the following query:

SELECT a.AlbumId, a.Title, b.ArtistId, b.Name AS Artist
FROM albums a
INNER JOIN artists b ON a.ArtistId = b.ArtistId
WHERE a.Title LIKE '%' || ? || '%'

It has one bound variable, the search string. It's sandwiched between two % to indicate that it can occur anywhere within Title.

We add the following struct to sqlite.zig, which is used to store the columns returned by the query:

pub const Album = struct {
    AlbumId: u32,
    Title: []const u8,
    ArtistId: u32,
    Artist: []const u8,
};

Then the following function:

pub fn printAlbums(db_op: SqliteOpaquePtr, title: []const u8) !void {
    const db_ptr: *sqlite.Db = @ptrCast(db_op);
    const query =
        \\SELECT a.AlbumId, a.Title, b.ArtistId, b.Name AS Artist
        \\FROM albums a
        \\INNER JOIN artists b ON a.ArtistId = b.ArtistId
        \\WHERE a.Title LIKE '%' || ? || '%'
    ;
    // prepare sql query
    var stmt = try db_ptr.prepare(query);
    defer stmt.deinit();
    const allocator = gpa.allocator();
    // create arena allocator
    var arena = std.heap.ArenaAllocator.init(allocator);
    defer arena.deinit();
    // create iterator
    var iterator = try stmt.iteratorAlloc(Album, arena.allocator(), .{title});
    while (try iterator.nextAlloc(arena.allocator(), .{})) |row| {
        std.debug.print("{d} {s}\n", .{ row.AlbumId, row.Title });
    }
}

The code above should be largely self-explanatory. We prepare a statement, obtain an iterator, then loop through it.

In index.js, we call this function:

const db = openDb(path);
printAlbums(db, 'best');
closeDb(db);

When you restart Electron-Vite, you should see the following:

dev server running for the electron renderer process at:

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose

start electron app...

13 The Best Of Billy Cobham
20 The Best Of Buddy Guy - The Millenium Collection
47 The Best of Ed Motta
61 Knocking at Your Back Door: The Best Of Deep Purple in the 80's
83 My Way: The Best Of Frank Sinatra [Disc 1]
146 Seek And Shall Find: More Of The Best (1963-1981)
147 The Best Of Men At Work
190 The Best Of R.E.M.: The IRS Years
213 Pure Cult: The Best Of The Cult (For Rockers, Ravers, Lovers & Sinners) [UK]
221 My Generation - The Very Best Of The Who
238 The Best Of 1980-1990
241 UB40 The Best Of - Volume Two [UK]
243 The Best Of Van Halen, Vol. I
257 20th Century Masters - The Millennium Collection: The Best of Scorpions
268 The Best of Beethoven

So our function is working. It's not particularly useful though. Instead of dumping the database rows, we want it to return them to our JavaScript code. Perhaps we can just return the iterator? The answer is no. While Zigar supports iterator starting from version 0.13.1, on return both the statement and the arena allocator would get deinit'ed, rendering the iterator invalid.

What we need to do is create a wrapper iterator that hangs onto stmt and arena until iterator is done:

fn Iterator(comptime T: type, query: []const u8) type {
    const IteratorData = struct {
        stmt: sqlite.StatementType(.{}, query),
        iterator: sqlite.Iterator(T),
        arena: std.heap.ArenaAllocator,
    };
    const IteratorDataOpaquePtr = *align(@alignOf(IteratorData)) opaque {};
    return struct {
        data_ptr: IteratorDataOpaquePtr,

        fn init(db_op: SqliteOpaquePtr, params: anytype) !@This() {
            // allocate data for fields used by iterator
            const allocator = gpa.allocator();
            const data_ptr = try allocator.create(IteratorData);
            errdefer allocator.destroy(data_ptr);
            // prepare sql query
            const db_ptr: *sqlite.Db = @ptrCast(db_op);
            data_ptr.stmt = try db_ptr.prepare(query);
            errdefer data_ptr.stmt.deinit();
            // create arena allocator
            data_ptr.arena = std.heap.ArenaAllocator.init(allocator);
            errdefer data_ptr.arena.deinit();
            // create iterator
            data_ptr.iterator = try data_ptr.stmt.iteratorAlloc(T, data_ptr.arena.allocator(), params);
            return .{ .data_ptr = @ptrCast(data_ptr) };
        }

        fn deinit(self: *@This()) void {
            const data_ptr: *IteratorData = @ptrCast(self.data_ptr);
            data_ptr.stmt.deinit();
            data_ptr.arena.deinit();
            const allocator = gpa.allocator();
            errdefer allocator.destroy(data_ptr);
        }

        pub fn next(self: *@This(), allocator: std.mem.Allocator) !?T {
            errdefer self.deinit();
            const data_ptr: *IteratorData = @ptrCast(self.data_ptr);
            // return row if there's one, otherwise deinit the iterator
            if (try data_ptr.iterator.nextAlloc(allocator, .{})) |row| {
                return row;
            } else {
                self.deinit();
                return null;
            }
        }
    };
}

Now it's just a matter of defining a specific iterator for our query and write a function that initiatizes and returns it:

const FindAlbumsIterator = Iterator(Album,
    \\SELECT a.AlbumId, a.Title, b.ArtistId, b.Name AS Artist
    \\FROM albums a
    \\INNER JOIN artists b ON a.ArtistId = b.ArtistId
    \\WHERE a.Title LIKE '%' || ? || '%'
);

pub fn findAlbums(db_op: SqliteOpaquePtr, title: []const u8) !FindAlbumsIterator {
    return try FindAlbumsIterator.init(db_op, .{title});
}

In index.js we replace the call to printAlbums() with the following:

for(const album of findAlbums(db, 'best')) {
  console.log(`${album.AlbumId} ${album.Title.string}`);
}

And you restart Electron, you should see the same output as before.

Now let us write a second function, one that retrieves the tracks in an album. Most of the hard work is done already. We just need to define a struct and an iterator for the query:

pub const Track = struct {
    TrackId: u32,
    Name: []const u8,
    Milliseconds: u32,
    GenreId: u32,
    Genre: []const u8,
};

const GetTracksIterator = Iterator(Track,
    \\SELECT a.TrackId, a.Name, a.Milliseconds, b.GenreId, b.Name as Genre
    \\FROM tracks a
    \\INNER JOIN genres b ON a.GenreId = b.GenreId
    \\WHERE a.AlbumId = ?
    \\ORDER BY a.TrackId
);

pub fn getTracks(db_op: SqliteOpaquePtr, album_id: u32) !GetTracksIterator {
    return try GetTracksIterator.init(db_op, .{album_id});
}

We replace the previous code in index.js with what follows:

for (const track of getTracks(db, 147)) {
  console.log(`${track.Name.string}`);
}

And here's the new output:

dev server running for the electron renderer process at:

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose

start electron app...

Down Under
Overkill
Be Good Johnny
Everything I Need
Down by the Sea
Who Can It Be Now?
It's a Mistake
Dr. Heckyll & Mr. Jive
Shakes and Ladders
No Sign of Yesterday

Now that we have backend functions, it's time to create the UI for our app.