Schema

Schemas map Zig structs to database tables at compile time with zero runtime overhead. The Schema.define function analyzes your struct fields and generates column lists, INSERT placeholders, and CREATE TABLE SQL for both SQLite and PostgreSQL.

Defining a schema

Every schema is a regular Zig struct with a pub const Meta declaration produced by Schema.define:

const zzz_db = @import("zzz_db");

pub const User = struct {
    id: i64,
    name: []const u8,
    email: []const u8,
    inserted_at: i64 = 0,
    updated_at: i64 = 0,

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "users",
        .primary_key = "id",
        .timestamps = true,
    });
};

The Meta type is generated entirely at compile time and contains all the metadata the repository and query builder need.

Schema options

The second argument to Schema.define is a SchemaOpts struct:

Option	Type	Default	Description
table	[:0]const u8	Required	The database table name
primary_key	[]const u8	"id"	Name of the primary key field
timestamps	bool	true	Whether inserted_at/updated_at are timestamps
associations	[]const AssociationDef	&.{}	Association definitions (has_many, belongs_to, etc)
virtual_fields	[]const []const u8	&.{}	Fields excluded from INSERT (computed/virtual)

Field type mapping

zzz_db automatically maps Zig types to SQL column types at compile time:

Zig type	SQLite type	PostgreSQL type
i16, u16, i32, u32	INTEGER	INTEGER
i64, u64	BIGINT	BIGINT
f32, f64	REAL	DOUBLE PRECISION
bool	BOOLEAN	BOOLEAN
[]const u8	TEXT	TEXT
?[]const u8	TEXT	TEXT
Other pointer types	BLOB	BYTEA

Optional types (?T) are mapped to the same SQL type as T and are treated as nullable columns.

Primary keys

The field named by primary_key (default "id") is treated specially:

• It is excluded from INSERT column lists (auto-generated by the database)
• For integer PKs, SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT and PostgreSQL uses BIGSERIAL PRIMARY KEY
• Non-integer PKs use a standard PRIMARY KEY constraint

// Integer primary key (auto-increment)
pub const Post = struct {
    id: i64,
    title: []const u8,

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "posts",
        .primary_key = "id",
        .timestamps = false,
    });
};

// Custom primary key name
pub const Country = struct {
    code: []const u8,
    name: []const u8,

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "countries",
        .primary_key = "code",
        .timestamps = false,
    });
};

Timestamps

When timestamps is set to true, fields named inserted_at and updated_at are marked as timestamp fields in the schema metadata. You must declare these fields on your struct:

pub const Article = struct {
    id: i64,
    title: []const u8,
    body: []const u8,
    inserted_at: i64 = 0,
    updated_at: i64 = 0,

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "articles",
        .timestamps = true,
    });
};

Timestamp values are stored as i64 (Unix epoch). Default values of 0 ensure they are populated by your application logic on insert and update.

Virtual fields

Virtual fields are included in SELECT queries but excluded from INSERT statements. This is useful for computed columns, aggregates, or fields populated by database triggers:

pub const Item = struct {
    id: i64,
    name: []const u8,
    full_name: []const u8 = "",

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "items",
        .primary_key = "id",
        .timestamps = false,
        .virtual_fields = &.{"full_name"},
    });
};

In the example above, full_name appears in SELECT column lists but not in INSERT statements:

// Meta.columns     -> "id, name, full_name"
// Meta.insert_columns -> "name"

Generated metadata

The Meta type produced by Schema.define exposes the following comptime constants:

Constant	Type	Description
Table	[:0]const u8	Table name
PrimaryKey	[]const u8	Primary key field name
Timestamps	bool	Whether timestamps are enabled
Struct	type	The original struct type
field_count	usize	Total number of fields
field_info	[N]FieldInfo	Per-field metadata (name, sql_type, nullable, is_pk, is_timestamp)
field_names	[N][]const u8	Array of field name strings
columns	[]const u8	Comma-separated column list for SELECT
insert_columns	[]const u8	Comma-separated columns for INSERT (excludes PK and virtual)
insert_placeholders	[]const u8	SQLite placeholders (?, ?, ?)
insert_placeholders_pg	[]const u8	PostgreSQL placeholders ($1, $2, $3)
create_table	[:0]const u8	CREATE TABLE IF NOT EXISTS SQL (SQLite)
create_table_pg	[:0]const u8	CREATE TABLE IF NOT EXISTS SQL (PostgreSQL)
associations	[]const AssociationDef	Association metadata
virtual_fields	[]const []const u8	Virtual field names

The createTable function dispatches by dialect:

const sql = User.Meta.createTable(.sqlite);   // SQLite DDL
const sql_pg = User.Meta.createTable(.postgres); // PostgreSQL DDL

Generated SQL examples

Given this schema:

pub const User = struct {
    id: i64,
    name: []const u8,
    email: []const u8,
    inserted_at: i64 = 0,
    updated_at: i64 = 0,

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "users",
        .primary_key = "id",
        .timestamps = true,
    });
};

SQLite

-- User.Meta.create_table
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT,
  inserted_at BIGINT,
  updated_at BIGINT
)

-- User.Meta.columns
-- "id, name, email, inserted_at, updated_at"

-- User.Meta.insert_columns
-- "name, email, inserted_at, updated_at"

-- User.Meta.insert_placeholders
-- "?, ?, ?, ?"

PostgreSQL

-- User.Meta.create_table_pg
CREATE TABLE IF NOT EXISTS users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT,
  email TEXT,
  inserted_at BIGINT,
  updated_at BIGINT
)

-- User.Meta.insert_placeholders_pg
-- "$1, $2, $3, $4"

Associations

Schemas can declare associations for use with repo.preload():

pub const User = struct {
    id: i64,
    name: []const u8,
    email: []const u8,
    inserted_at: i64 = 0,
    updated_at: i64 = 0,

    pub const Meta = zzz_db.Schema.define(@This(), .{
        .table = "users",
        .primary_key = "id",
        .timestamps = true,
        .associations = &.{
            .{
                .name = "posts",
                .assoc_type = .has_many,
                .related_table = "posts",
                .foreign_key = "user_id",
            },
            .{
                .name = "profile",
                .assoc_type = .has_one,
                .related_table = "profiles",
                .foreign_key = "user_id",
            },
        },
    });
};

Available association types: has_many, has_one, belongs_to, many_to_many.

For many_to_many, additionally specify join_table, join_fk, and join_assoc_fk.

Table naming conventions

zzz_db does not enforce naming conventions — you explicitly set the table name in SchemaOpts. Common conventions:

• Use lowercase plural names for tables: users, posts, order_items
• Use snake_case for column names, matching Zig field names
• Use inserted_at and updated_at for timestamp fields (required by the timestamps feature)

Accessing schema metadata

Use Schema.meta(T) to retrieve the Meta type from any schema struct:

const M = zzz_db.Schema.meta(User);
// M.Table == "users"
// M.PrimaryKey == "id"
// M.field_count == 5

This is useful in generic functions that operate on any schema type.

Next steps

• Repository — use schemas with the CRUD API
• Query Builder — build queries against schema tables
• Associations — preload related records
• Migrations — generate CREATE TABLE from schema definitions