Dig ORM – A Type-safe SQL Query Builder for Zig

Overview

Dig ORM is a type-safe SQL query builder library for Zig programming language. It provides an intuitive API for building SQL queries similar to Drizzle ORM, with support for PostgreSQL and MySQL.

Key Features

Type-safe query building: Build SQL queries with compile-time type checking.
Fluent API: Chain methods to build queries intuitively with chainable query builders.
Multi-database support: PostgreSQL and MySQL with full driver implementations.
Schema definition: Define tables and columns with a simple API.
Migration system: SQL-based database schema versioning with up/down migrations.
Transaction support: Built-in transaction management (BEGIN/COMMIT/ROLLBACK).
JOIN support: Full support for INNER, LEFT, RIGHT, and FULL OUTER JOINs.
Result parsing: Automatic type conversion from database results.
C library bindings: Complete bindings for libpq and libmysqlclient.

Requirements

Zig: 0.15.2 or later.
PostgreSQL: libpq development libraries (if using PostgreSQL).
MySQL: libmysqlclient development libraries (if using MySQL).
Docker & Docker Compose (optional): For development environment.

Getting Started

This quickstart shows how to set up Dig ORM as a dependency and execute SQL queries.

1. Add Dig ORM as a dependency

Fetch Horizon using Zig's package manager:

zig fetch --save-exact=dig https://github.com/HARMONICOM/dig/archive/refs/tags/v0.1.2.tar.gz

2. Configure `build.zig`

Import the Dig module and enable the database drivers you need:

const dig = b.dependency("dig", .{
    .target = target,
    .optimize = optimize,
    // Enable the database drivers you need (both disabled by default)
    .postgresql = true,  // Enable if using PostgreSQL
    .mysql = true,       // Enable if using MySQL
});

exe.root_module.addImport("dig", dig.module("dig"));
b.installArtifact(exe);

// Install migration tool (automatically built by Dig)
const migrate_artifact = dig.artifact("migrate");
b.installArtifact(migrate_artifact);

3. Install database libraries

Install the required libraries for your database:

# PostgreSQL (Debian/Ubuntu)
sudo apt-get install libpq-dev

# MySQL (Debian/Ubuntu)
sudo apt-get install default-libmysqlclient-dev

# macOS (Homebrew)
brew install postgresql
brew install mysql-client

4. Connect and query

Here is a minimal example using Dig ORM with the recommended chainable query builder:

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

pub fn main() !void {
    var gpa = std.heap.GeneralPurposeAllocator(.{}){};
    defer _ = gpa.deinit();
    const allocator = gpa.allocator();

    // Connect to database
    const config = dig.types.ConnectionConfig{
        .database_type = .postgresql,
        .host = "localhost",
        .port = 5432,
        .database = "mydb",
        .username = "user",
        .password = "pass",
    };

    var conn = try dig.db.connect(allocator, config);
    defer conn.disconnect();

    // Chainable query builder - build and execute in one chain
    var result = try conn.table("users")
        .select(&.{"id", "name", "email"})
        .where("age", ">", .{.integer = 18})
        .orderBy("name", .asc)
        .limit(10)
        .get();
    defer result.deinit();

    for (result.rows) |row| {
        const id = row.get("id").?.integer;
        const name = row.get("name").?.text;
        std.debug.print("User: {d}, {s}\n", .{ id, name });
    }
}

Query Builders

Dig ORM provides type-safe query builders for SELECT, INSERT, UPDATE, and DELETE operations. You can use chainable query builders (recommended) or traditional query builders.

Chainable Query Builder (Recommended)

Build and execute queries directly from the database connection:

SELECT

// Build and execute in one chain
var result = try conn.table("users")
    .select(&.{"id", "name", "email"})
    .where("age", ">", .{.integer = 18})
    .orderBy("name", .asc)
    .limit(10)
    .get();
defer result.deinit();

INSERT

try conn.table("users")
    .addValue("name", .{.text = "John Doe"})
    .addValue("email", .{.text = "john@example.com"})
    .addValue("age", .{.integer = 30})
    .execute();

UPDATE

try conn.table("users")
    .set("age", .{.integer = 31})
    .where("id", "=", .{.integer = 1})
    .execute();

DELETE

try conn.table("users")
    .delete()
    .where("id", "=", .{.integer = 1})
    .execute();

JOIN Queries

// INNER JOIN
var result = try conn.table("users")
    .select(&.{"users.id", "users.name", "posts.title"})
    .join("posts", "users.id", "posts.user_id")
    .where("users.active", "=", .{.boolean = true})
    .get();
defer result.deinit();

// LEFT JOIN
var result2 = try conn.table("users")
    .select(&.{"users.id", "users.name", "posts.title"})
    .leftJoin("posts", "users.id", "posts.user_id")
    .get();
defer result2.deinit();

Traditional Query Builders

Generate SQL separately and execute (still supported):

SELECT Queries

var query = try dig.query.Select.init(allocator, "users");
defer query.deinit();

const sql = try (try query
    .select(&[_][]const u8{ "id", "name", "email" })
    .where("age", ">", .{ .integer = 18 }))
    .orderBy("name", .asc)
    .limit(10)
    .toSql(.postgresql);
defer allocator.free(sql);

var result = try conn.query(sql);
defer result.deinit();

INSERT Queries

var query = try dig.query.Insert.init(allocator, "users");
defer query.deinit();

const sql = try (try (try query
    .addValue("name", .{ .text = "John Doe" }))
    .addValue("email", .{ .text = "john@example.com" }))
    .addValue("age", .{ .integer = 30 }))
    .toSql(.postgresql);
defer allocator.free(sql);

try conn.execute(sql);

UPDATE Queries

var query = try dig.query.Update.init(allocator, "users");
defer query.deinit();

const sql = try (try (try query
    .set("age", .{ .integer = 31 }))
    .where("id", "=", .{ .integer = 1 }))
    .toSql(.postgresql);
defer allocator.free(sql);

try conn.execute(sql);

DELETE Queries

var query = try dig.query.Delete.init(allocator, "users");
defer query.deinit();

const sql = try (try query
    .where("id", "=", .{ .integer = 1 }))
    .toSql(.postgresql);
defer allocator.free(sql);

try conn.execute(sql);

For more details, see the Query Builders Documentation.

Schema Definition

Define database schemas with tables and columns using Dig's schema API. The schema can be compiled to CREATE TABLE SQL statements for PostgreSQL and MySQL.

Defining Tables

var table = dig.schema.Table.init(allocator, "users");
defer table.deinit();

try table.addColumn(.{
    .name = "id",
    .type = .bigint,
    .primary_key = true,
    .auto_increment = true,
});

try table.addColumn(.{
    .name = "name",
    .type = .varchar,
    .length = 255,
    .nullable = false,
});

try table.addColumn(.{
    .name = "email",
    .type = .varchar,
    .length = 255,
    .nullable = false,
    .unique = true,
});

try table.addColumn(.{
    .name = "created_at",
    .type = .timestamp,
    .nullable = false,
});

const create_sql = try table.toCreateTableSql(.postgresql, allocator);
defer allocator.free(create_sql);

Supported Column Types

integer - INTEGER
bigint - BIGINT
text - TEXT
varchar - VARCHAR (with optional length)
boolean - BOOLEAN
float - FLOAT
double - DOUBLE
timestamp - TIMESTAMP
blob - BLOB
json - JSON/JSONB (PostgreSQL uses JSONB, MySQL uses JSON)

Migrations

Dig provides a SQL-based migration system for database schema management. Migrations are stored as SQL files with up/down sections.

Migration Files

Create migration files in a migrations/ directory:

File: migrations/20251122_create_users_table.sql

-- Migration: Create users table

-- up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

-- down
DROP TABLE IF EXISTS users;

Standalone Migration Tool

Dig automatically provides a standalone migration CLI tool that runs independently from your main application.

Run migrations:

# Run migrations
DB_TYPE=postgresql DB_DATABASE=mydb DB_USERNAME=user DB_PASSWORD=pass \
  ./zig-out/bin/migrate up

# Check status
DB_TYPE=postgresql DB_DATABASE=mydb DB_USERNAME=user DB_PASSWORD=pass \
  ./zig-out/bin/migrate status

# Rollback
DB_TYPE=postgresql DB_DATABASE=mydb DB_USERNAME=user DB_PASSWORD=pass \
  ./zig-out/bin/migrate down

Transactions

Dig supports transaction management for both PostgreSQL and MySQL. Use transactions to ensure data consistency across multiple operations.

Using Transactions

try db.beginTransaction();
errdefer db.rollback(); // Rollback on error

try db.execute("INSERT INTO users (name) VALUES ('Alice')");
try db.execute("UPDATE users SET active = true WHERE name = 'Alice'");

try db.commit();

Database Support

Dig ORM provides full support for PostgreSQL and MySQL through complete C library bindings and driver implementations.

PostgreSQL

Status: ✅ Fully implemented
Requirements: libpq development libraries (libpq-dev)
Enable: .postgresql = true in build.zig
Features: Connection management, query execution, transactions, type conversion, JSONB support

MySQL

Status: ✅ Fully implemented
Requirements: libmysqlclient development libraries (default-libmysqlclient-dev)
Enable: .mysql = true in build.zig
Features: Connection management, query execution, transactions, type conversion, JSON support

Conditional Compilation

Database drivers are disabled by default. You must explicitly enable the drivers you need when adding Dig as a dependency:

const dig = b.dependency("dig", .{
    .target = target,
    .optimize = optimize,
    .postgresql = true,  // Explicitly enable PostgreSQL
    .mysql = true,       // Explicitly enable MySQL
});

API Reference

This section summarizes the main APIs in Dig ORM. For complete documentation, refer to the project's documentation files.

Connection

dig.db.connect(allocator, config) - Create a database connection
db.disconnect() - Close the connection
db.table(name) - Start a chainable query builder
db.execute(query) - Execute a SQL query
db.query(query) - Execute a query and return results
db.beginTransaction() - Start a transaction
db.commit() - Commit a transaction
db.rollback() - Rollback a transaction

Query Builders

Chainable Query Builder (via db.table()):

table(name) - Start a chainable query builder
select(columns) - Set columns to select
join/leftJoin/rightJoin/fullJoin - Add JOIN clauses
where(column, operator, value) - Add WHERE clause
orderBy(column, direction) - Add ORDER BY
limit(count) - Add LIMIT
offset(count) - Add OFFSET
get() - Execute SELECT and return results
first() - Execute SELECT and return first result
addValue(column, value) - Add value for INSERT
set(column, value) - Set column for UPDATE
delete() - Start DELETE query
execute() - Execute INSERT/UPDATE/DELETE

Traditional Query Builders:

Select: init, select, where, orderBy, limit, offset, toSql
Insert: init, addValue, setValues, toSql
Update: init, set, setMultiple, where, toSql
Delete: init, where, toSql

Schema

Table.init(allocator, name) - Create a table definition
Table.addColumn(column) - Add a column to the table
Table.toCreateTableSql(db_type, allocator) - Generate CREATE TABLE SQL

Migrations

Manager.init(db, allocator) - Create a migration manager
Manager.loadFromDirectory(dir) - Load migrations from directory
Manager.migrate(migrations) - Run pending migrations
Manager.rollback(migrations) - Rollback last batch
Manager.reset(migrations) - Rollback all migrations
Manager.status(migrations) - Show migration status

Project Structure

The Dig ORM repository is organized as follows:

dig/
├── src/
│   ├── dig.zig                    # Module entry point
│   ├── migrate.zig                # Migration CLI tool
│   └── dig/                       # Module files directory
│       ├── connection.zig         # Connection abstraction
│       ├── db.zig                 # Database interface
│       ├── query.zig              # Query builders (Select, Insert, Update, Delete)
│       ├── queryBuilder.zig      # Chainable query builder
│       ├── schema.zig             # Schema definitions
│       ├── migration.zig          # Migration system
│       ├── types.zig              # Type definitions
│       ├── errors.zig             # Error definitions
│       ├── drivers/               # Database drivers
│       │   ├── postgresql.zig     # PostgreSQL driver
│       │   └── mysql.zig          # MySQL driver
│       └── libs/                  # C library bindings
│           ├── libpq.zig          # PostgreSQL C API
│           └── libmysql.zig       # MySQL C API
├── tests/                         # Test files
└── documents/                     # User documentation
    ├── README.md                  # Documentation index
    ├── overview.md                # Project overview
    ├── getting-started.md         # Installation and setup
    ├── schema.md                  # Schema definition guide
    ├── query-builders.md          # Query builders guide
    ├── migrations.md              # Migration system guide
    ├── database-drivers.md        # Database driver details
    ├── api-reference.md           # API reference
    └── architecture.md            # Architecture (for contributors)

Build type-safe SQL queries with Dig ORM.

Overview

Key Features

Requirements

Getting Started

1. Add Dig ORM as a dependency

2. Configure build.zig

3. Install database libraries

4. Connect and query

Query Builders

Chainable Query Builder (Recommended)

SELECT

INSERT

UPDATE

DELETE

JOIN Queries

Traditional Query Builders

SELECT Queries

INSERT Queries

UPDATE Queries

DELETE Queries

Schema Definition

Defining Tables

Supported Column Types

Migrations

Migration Files

Standalone Migration Tool

Transactions

Using Transactions

Database Support

PostgreSQL

MySQL

Conditional Compilation

API Reference

Connection

Query Builders

Schema

Migrations

Project Structure

2. Configure `build.zig`