A lightweight SQLite wrapper and schema manager.

While one should not encounter any difficulty, when calling the C interface of SQLite from Zig, it may become somewhat tedious: Firstly, all return values need to be checked for error codes; And secondly, one would continuously need to coerce between C- and Zig language types.

The sqlitez package provides a wrapper for common sqlite functions that automatically coerce between language types, and return errors when a function call fails.

Additionally, the sqlitez package provides a convenient mechanism for maintaining a schema with upgrade scripts.

Copyright

LICENSE

MIT with non-AI clauses: I am not really sure how a generative AI could comply with the copyright notice requirement of the standard MIT license, so I would expect that ethically sourced AI models would have some contractual agreements with the content creators whose work they sourced ¯\()/¯. However, since there might be an element of ambiguity, I added some explicit clauses to the MIT license to make my expectations clear.

Getting started

Add the dependency to your project

1. From your project folder, run the following command to add the sqlitez entry in your build.zig.zon file.

zig fetch --save https://codeberg.org/Swindlers-Inc/sqlitez/archive/0.1.3.tar.gz

1. In your build.zig file, add sqlitez as a dependency for your executable or library. Example:

const sqlitez_dep = b.dependency("sqlitez", .{
.target = target,
.optimize = optimize,
});
const sqlitez = sqlitez_dep.module("sqlitez");

const exe = b.addExecutable(.{
...
.root_module = b.createModule(.{
...
.imports = &.{
.{ .name = "sqlitez", .module = sqlitez },
},
}),
});

1. In your build.zig file, link your executable with sqlite3 and libC. Example:

exe.linkSystemLibrary("sqlite3");
exe.linkLibC();

1. In your Zig code, import sqlitez:

const sqlitez = @import("sqlitez");

If you get stuck see the example project.

Opening a database connection

Equivalent to the sqlite3_open function as described in the SQLite docs.

To open a database connection, one simply needs to call the init function of the Connection struct. The function parameter is the UTF8 encoded file path for the database file, or :memory: for an in memory database.

It is the responsibility of the caller to close the connection once it is no longer needed. To close the database, simply call the deinit function.

var connection = try sqlitez.Connection.init(file_path);
defer connection.deinit();
// Do some work

Executing SQL statements

Equivalent to the sqlite3_exec function as described in the SQLite docs.

To run SQL statements, simply call the exec function on a database connection. The parameter is the UTF8 encoded SQL, semicolons can be used to separate multiple SQL statements.

There is also a execWithCallback function, that allows callers to supply a callback function and user-data for the underlying sqlite3_exec call. This package does not wrap the call back, and the caller is responsible for the appropriate handling of the C language types.

try connection.exec(
\\ CREATE TABLE products (
\\      id          INTEGER PRIMARY KEY,
\\      name        TEXT NOT NULL,
\\      description TEXT NOT NULL,
\\      price       BIGINT
\\ );
);

Prepared statement

Prepared statements (also known as parameterised statements) is a database feature that pre-compile SQL, separates data from the SQL, and stores the query result. Prepared statements improves efficiency, as they can be used repeatedly without re-compiling; and prepared statements also reduce the opportunities for common injection attacks.

Compiling a statement

Equivalent to the sqlite3_prepare_v2 function as described in the SQLite docs.

To compile a prepared statement, one can call the prepare function on a database connection. The parameter is the UTF8 encoded SQL. Inside the SQL, one may use named parameters, e.g. :name.

It is the responsibility of the caller to finalise the prepared statement once it is no longer needed. To finalise the statement, simply call the deinit function.

var stmt = try connection.prepare(
\\  SELECT *
\\  FROM products
\\  WHERE name = :name
);
defer stmt.deinit();
// Do some work

Binding parameters

Equivalent to the sqlite3_bind_* functions as described in the SQLite docs.

To bind parameters to a prepared statement, one may use the following functions:

• bindBlob to bind an array of bytes, where the caller retains ownership of the memory.
• bindDouble to bind a 64 bit float value;
• bindInt to bind a 32 bit signed integer value;
• bindInt64 to bind a 64 bit singed integer value;
• bindNull to bind a null value; and
• bindText to bind UTF8 encoded text, where the caller retains ownership of the memory.

Blob and text values are binded as static, and need to remain valid until either the statement is finalised, or the parameter is bound to something else.

try stmt.bindText(1, name);

Parameter indices start their count at 1, but there is a convenience function to lookup the index of named parameters.

The bindParameterIndex function is equivalent to the sqlite3_bind_parameter_index function as described in the SQLite docs.

try stmt.bindText(try stmt.bindParameterIndex(":name"), name);

Evaluating a statement

Equivalent to the sqlite3_step function as described in the SQLite docs.

After preparing a statement, one must call the step function one or more times to evaluate the statement. The function returns .RowAvailable if there is a new row of data ready to be processed by the caller. And a return value of .Done means that the statement has finished executing and there is no data to be processed by the caller.

while (try stmt.step() == .RowAvailable) {
// Do some work
}

Result values

Equivalent to the sqlite3_column_* functions as described in the SQLite docs.

If there is a row of data ready for processing, one may access the data with the following functions:

• columnBlob for an array of bytes, where null column values resolve to null.
• columnDouble for 64 bit float values, where null values resolve to zero;
• columnInt for 32 bit signed integer values, where null values resolve to zero;
• columnInt64 for 64 bit signed integer values, where null values resolve to zero; and
• columnText for UTF8 encoded text, where null column values resolve to null.

Since null values for numeric columns would resolve to zero, one may use the columnIsNull function to check if the column value is null.

if (try stmt.columnText(1)) |name| {
// Do some work
}

Pointer return values is only valid until a type conversion occurs, or until step, reset, or finalize is called. Use the following functions to return a copy of the column values, where the caller is responsible for freeing the memory:

• columnBlobAlloc for an array of bytes, where null column values resolve to null.
• columnTextAlloc for UTF8 encoded text, where null column values resolve to null.

if (try stmt.columnTextAlloc(1, allocator)) |name| {
defer allocator.free(name);
// Do some work
}

Recycling prepared statements

Prepared statements remain valid until either the statement is finalised, or the associated database connection is closed. One may therefore reuse prepared statements by restoring them to their initial states.

The reset is equivalent to the sqlite3_reset function as described in the SQLite docs. One can use this function to reset the prepared statement back to its initial state.

Resetting a prepared statement does not clear the parameter bindings.

The clearBindings is equivalent to the sqlite3_clear_bindings function as described in the SQLite docs. One can use this function to reset all host parameters.

try stmt.reset();
try stmt.clearBindings();

Transactions

Equivalent to transactions as described in the SQLite docs.

By default, SQLite will autocommit each statement, which may be undesirable if a sequence of operations should form an atomic unit. For these situations one would use transactions, where the BEGIN, END, and ROLLBACK statements are used to start, end, or abort the transactions.

The sqlitez package provides a convenient Transaction struct, that can be used instead of issuing the SQL transaction control statements.

const transaction = try sqlitez.Transaction.begin(connection);
errdefer transaction.rollback() catch @panic("Rollback failed!");
// do some work
try transaction.commit();

Errors and debugging

The sqlitez package functions will return errors if the underlying sqlite3_* functions return error codes. A lot of these failures would be irrecoverable during runtime (e.g. a mistake in the SQL for a prepared statement), and one would need to implement test cases to verify that caller workflows would succeed during typical usage.

The sqlitez package functions use the .sqlitez log scope to print error messages. And most error messages are logged at debug level.

The errorMessage function on a connection, or statement, is equivalent to the sqlite3_errmsg function as described in the SQLite docs. The errorMessage function returns the UTF8 encoded error message of the previous SQLite interface function call, or null if there is no error message. The memory is managed internally, and may be overwritten or deallocated on subsequent SQLite interface function calls.

In debug mode, some sqlitez package functions may call @panic when an irrecoverable failure occurs. While in release mode, those same functions would simply return an error. This is to help ensure that irrecoverable errors are detected and fixed during development, while allowing applications to gracefully shutdown once released.

Schema maintenance

Over the lifetime of an application, schema management can become a troublesome endeavor.

It would be extraordinary, if one’s first attempt at a database schema for a given application, can service all the needs of that application into perpetuity. It is more likely that the database schema will change with newer versions of the application, as new features are added and new requirements arise.

And once an application has users, those users would need mechanisms to port their data from older versions of an application to newer versions of the same application. Ideally, those mechanisms should be non intrusive on the user workflows.

A common way to maintain the database schema is with a sequence of upgrade scripts, every time there is a change to the schema a new upgrade script is added to the sequence. When the application opens a connection to a database for the first time after startup, the application will first run the necessary upgrade scripts to align the database schema to the application. This does necessitate some mechanism to keep track of which upgrade scripts already ran, and which upgrade scripts still need to run.

The sqlitez package provides a convenient SchemaMaintainer struct that maintains the database schema from a user supplied sequence of upgrade scripts. Internally, the package will create a table to keep track of which upgrade scripts already ran. So when the maintain function is called only the newer upgrade scripts run.

The validate function compares the list of upgrade scripts to the upgrade scripts that previously ran, and returns an error if there is a mismatch. This would help guard against accidentally modifying the database schema of a different application.

Once that database schema was upgraded, older versions of the application may no longer be able to use that database. One may use the isOnLatestSchema function to check if the database is on the latest schema, and to offer the user an option before commencing with the schema upgrade.

fn maintainSchema(
connection: sqlitez.Connection,
comptime upgrade_scripts: []const [:0]const u8,
) !void {
const SchemaMaintainer = sqlitez.defineSchemaMaintainer(upgrade_scripts);
const maintainer = try SchemaMaintainer.init(connection);
try maintainer.validate();
try maintainer.upgrade();
}

Development

Setup

This project has the following dependencies:

• SQLite
• Zig version 0.15.1
• Optionally, zls for viewing and editting the code.

On Arch Linux, simply run scripts/setup-arch.sh from the project folder to install the project dependencies.

Build

From the project folder run zig build to build the project.

Test

From the project folder run either of the following commands:

• zig build test
• zig build test --summary all

Resources

Non AI licenses

• non-ai-licenses

SQLite

• Getting started

• C-language Interface Specification for SQLite

• List Of Functions

Zig

• Zig Language Reference 0.15.1

• Zig Build System