Schema & Repository

The std/db/schema and std/db/repository modules provide a high-level, type-safe ORM layer built on top of the Database core and Query Builder. Define your schemas with a fluent callback API, then use repositories for CRUD operations.

Overview

The schema/repository pattern in Chuks follows three steps:

1. Define a dataType — a pure data model with no database logic
2. Define a Schema — map the data type to a table using db.define<T>()
3. Create a Repository — extend Repository<T> for type-safe CRUD

In a real project, schemas are defined without a connection (pure definitions), and the connection is wired up once in your entry point via repo.useConnection(conn) — which also auto-syncs the table.

// ── src/db/entities/userEntity.chuks ──
import { db } from "std/db"
import { Schema, SchemaBuilder } from "std/db/schema"

dataType User { id: int; name: string; email: string; age: int; createdAt: string }

export const UserSchema: Schema = db.define<User>("users", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
    schema.string("email").unique().notNull()
    schema.int("age").min(0).max(150)
    schema.timestamp("createdAt").defaultTo("now")
})

// ── src/db/repository/userRepo.chuks ──
import { Repository } from "std/db/repository"
import { UserSchema } from "../entities/userEntity.chuks"

export class UserRepo extends Repository<User> {
    constructor() { super(UserSchema) }

    async findByEmail(email: string): Task<any> {
        return await this.where("email", email).first()
    }
}

// ── src/main.chuks ──
import { db, DbDriver } from "std/db"
import { UserRepo } from "./db/repository/userRepo.chuks"

async function main(): Task<any> {
    var conn: any = db.open(DbDriver.Postgres, databaseUrl)
    var userRepo = new UserRepo()
    await userRepo.useConnection(conn) // sets connection + auto-syncs table
    // Ready to use
    await userRepo.create({"name": "Alice", "email": "alice@test.com", "age": 30})
    return null
}

Import

import { db, DbDriver } from "std/db"
import { Schema, SchemaBuilder, ColumnBuilder, Relationship } from "std/db/schema"
import { Repository } from "std/db/repository"

---

Schema Definition

db.define<T>(tableName: string, callback: (schema: SchemaBuilder) => void): Schema

Define a table schema using a fluent callback API. The callback receives a SchemaBuilder and returns a Schema object. This is the recommended form — define schemas as pure declarations, then wire up the connection via repo.useConnection(conn) (which auto-syncs).

const ProductSchema: Schema = db.define<Product>("products", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
    schema.float("price").notNull()
    schema.int("stock").defaultTo(0)
    schema.text("description").nullable()
    schema.timestamp("createdAt").defaultTo("now")
})

db.define<T>(conn: any, tableName: string, callback: (schema: SchemaBuilder) => void): Schema

Alternative form that accepts a connection handle. Useful for quick scripts and inline usage. When a connection is provided and autoSyncTableToDB is not false, the table is automatically created.

// Quick script — define + sync in one step
var conn: any = db.open(DbDriver.Sqlite, ":memory:")
const ProductSchema: Schema = db.define<Product>(conn, "products", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
})

SchemaBuilder Methods

The SchemaBuilder passed to the callback provides column factory methods. Each returns a ColumnBuilder for fluent constraint configuration.

Core Types

Method	SQL Type (SQLite)	Description
schema.pk(name)	INTEGER	Primary key column (auto NOT NULL)
schema.string(name)	TEXT	Text/string column
schema.int(name)	INTEGER	Integer column
schema.float(name)	REAL	Floating-point column
schema.bool(name)	INTEGER	Boolean (stored as 0/1)
schema.text(name)	TEXT	Long text column
schema.timestamp(name)	TEXT	Timestamp column
schema.blob(name)	BLOB	Binary data column

Extended Types

Method	SQLite	PostgreSQL	MySQL	MSSQL	Description
schema.uuid(name)	TEXT	UUID	CHAR(36)	UNIQUEIDENTIFIER	UUID column
schema.json(name)	TEXT	JSONB	JSON	NVARCHAR(MAX)	JSON data column
schema.date(name)	TEXT	DATE	DATE	DATE	Date-only column
schema.datetime(name)	TEXT	TIMESTAMP	DATETIME	DATETIME2	Date + time column
schema.decimal(name)	REAL	DECIMAL	DECIMAL	DECIMAL	Fixed-precision number
schema.bigInt(name)	INTEGER	BIGINT	BIGINT	BIGINT	Large integer column
schema.enum(name)	TEXT + CHECK	TEXT + CHECK	ENUM(...)	NVARCHAR + CHECK	Enum column (use .values())

schema.uuid("externalId")
schema.json("metadata")
schema.date("releaseDate")
schema.datetime("createdAt").defaultTo("now")
schema.decimal("price").precision(10, 2).notNull()
schema.bigInt("viewCount").defaultTo(0)
schema.enum("status").values(["active", "inactive", "draft"]).notNull().defaultTo("active")

ColumnBuilder Constraints

Chain constraint methods on any column builder. All return this for fluent chaining.

Method	Description
.primaryKey()	Mark column as PRIMARY KEY (also sets NOT NULL)
.auto()	Auto-increment (AUTOINCREMENT)
.unique()	Add UNIQUE constraint
.notNull()	Add NOT NULL constraint
.nullable()	Allow NULL values (default for non-PK columns)
.defaultTo(val)	Set default value. Use "now" for CURRENT_TIMESTAMP
.min(val)	Add minimum CHECK constraint
.max(val)	Add maximum CHECK constraint
.check(expr)	Add custom CHECK expression
.references(ref)	Add foreign key reference (e.g. "users(id)")
.cascade()	Shorthand for ON DELETE CASCADE ON UPDATE CASCADE
.onDelete(action)	Set ON DELETE action ("CASCADE", "SET NULL", "RESTRICT", "NO ACTION")
.onUpdate(action)	Set ON UPDATE action ("CASCADE", "SET NULL", "RESTRICT", "NO ACTION")
.index()	Mark column as indexed
.length(len)	Set max length (e.g. VARCHAR(255))
.precision(p, s)	Set decimal precision and scale
.values(vals)	Set allowed values for enum columns

Column Examples

schema.pk("id").auto()                        // id INTEGER PRIMARY KEY AUTOINCREMENT
schema.string("email").unique().notNull()      // email TEXT NOT NULL UNIQUE
schema.int("age").min(0).max(150)              // age INTEGER CHECK (age >= 0 AND age <= 150)
schema.timestamp("createdAt").defaultTo("now")   // createdAt TEXT DEFAULT CURRENT_TIMESTAMP
schema.float("balance").defaultTo(0.0)           // balance REAL DEFAULT 0
schema.string("role").check("role IN ('admin','user','guest')")

// Foreign key with cascade
schema.int("authorId").references("users(id)").cascade()
// → REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE

// Foreign key with specific actions
schema.int("categoryId").references("categories(id)").onDelete("SET NULL").onUpdate("CASCADE")

// Enum with allowed values
schema.enum("status").values(["active", "inactive", "draft"]).notNull().defaultTo("active")

// Decimal with precision
schema.decimal("price").precision(10, 2).notNull()

// String with max length
schema.string("name").length(255).notNull()

Relationships

Define relationships between schemas using the SchemaBuilder relationship methods. Each returns a Relationship object for optional fluent configuration.

schema.hasOne(name, relatedTable): Relationship

Define a one-to-one relationship. The foreign key lives on the related table.

// User has one Profile (profiles table has userId column)
schema.hasOne("profile", "profiles").foreignKey("userId")

schema.hasMany(name, relatedTable): Relationship

Define a one-to-many relationship. The foreign key lives on the related table.

// User has many Posts (posts table has authorId column)
schema.hasMany("posts", "posts").foreignKey("authorId")

schema.belongsTo(name, relatedTable): Relationship

Define an inverse (many-to-one) relationship. The foreign key lives on this table.

// Post belongs to User (posts table has authorId column)
schema.belongsTo("author", "users").foreignKey("authorId")

schema.manyToMany(name, relatedTable): Relationship

Define a many-to-many relationship via a pivot (junction) table.

// User has many Roles via user_roles pivot table
schema.manyToMany("roles", "roles")
    .pivotTable("user_roles")
    .pivotLocalKey("userId")
    .pivotForeignKey("roleId")

Relationship Fluent Methods

Method	Description
.foreignKey(fk)	Set the foreign key column name
.localKey(lk)	Set the local key column (default: "id")
.pivotTable(pt)	Set the junction table for manyToMany
.pivotLocalKey(plk)	Set the pivot table’s local key column
.pivotForeignKey(pfk)	Set the pivot table’s foreign key column

Full Schema with Relationships

dataType User { id: int; name: string; email: string }
dataType Post { id: int; title: string; authorId: int }
dataType Role { id: int; name: string }

const UserSchema: Schema = db.define<User>("users", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
    schema.string("email").unique().notNull()

    schema.hasMany("posts", "posts").foreignKey("authorId")
    schema.hasOne("profile", "profiles").foreignKey("userId")
    schema.manyToMany("roles", "roles")
        .pivotTable("user_roles")
        .pivotLocalKey("userId")
        .pivotForeignKey("roleId")
})

const PostSchema: Schema = db.define<Post>("posts", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("title").notNull()
    schema.int("authorId").notNull().references("users(id)").cascade()

    schema.belongsTo("author", "users").foreignKey("authorId")
})

Accessing Relationship Metadata

var rels: []any = UserSchema.getRelationships()
println(length(rels)) // 3

var postsRel: any = UserSchema.getRelationship("posts")
println(postsRel.getType())         // hasMany
println(postsRel.getRelatedTable())  // posts
println(postsRel.getForeignKey())    // authorId

---

Schema Object

The Schema returned by db.define<T>() provides metadata, relationship access, and DDL generation.

schema.getTableName(): string

Returns the table name.

schema.getColumns(): []any

Returns the array of ColumnBuilder objects.

schema.getRelationships(): []any

Returns the array of Relationship objects defined on this schema.

schema.getRelationship(name: string): any

Find a relationship by name. Returns the Relationship or null.

schema.getPrimaryKey(): any

Returns the name of the primary key column, or null if none defined.

schema.getConnection(): any

Returns the database connection handle associated with this schema (set via db.define<T>(conn, ...) or repo.useConnection(conn)), or null if no connection has been set.

schema.setConnection(conn: any): Schema

Set the database connection on this schema. Called automatically by repo.useConnection(conn). Returns this for chaining.

schema.toCreateSQL(): string

Generate a CREATE TABLE IF NOT EXISTS statement using SQLite syntax (the default).

println(UserSchema.toCreateSQL())
// CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,
//   name TEXT NOT NULL, email TEXT NOT NULL UNIQUE,
//   age INTEGER CHECK (age >= 0 AND age <= 150),
//   createdAt TEXT DEFAULT CURRENT_TIMESTAMP)

schema.toCreateSQLForDriver(driver: string): string

Generate a CREATE TABLE statement using driver-specific DDL. The driver string can be "sqlite", "postgres", "mysql", or "mssql". Each driver produces the correct type mappings, auto-increment syntax, and constraint formats.

// PostgreSQL DDL
println(ProductSchema.toCreateSQLForDriver("postgres"))
// CREATE TABLE IF NOT EXISTS products (
//   id SERIAL PRIMARY KEY,
//   price DECIMAL(10,2) NOT NULL,
//   metadata JSONB,
//   status TEXT CHECK (status IN ('active','inactive','draft')) NOT NULL DEFAULT 'active',
//   externalId UUID, ...)

// MySQL DDL
println(ProductSchema.toCreateSQLForDriver("mysql"))
// CREATE TABLE IF NOT EXISTS products (
//   id INT AUTO_INCREMENT PRIMARY KEY,
//   price DECIMAL(10,2) NOT NULL,
//   metadata JSON,
//   status ENUM('active','inactive','draft') NOT NULL DEFAULT 'active', ...)

// MSSQL DDL
println(ProductSchema.toCreateSQLForDriver("mssql"))
// CREATE TABLE products (
//   id INT IDENTITY(1,1) PRIMARY KEY,
//   price DECIMAL(10,2) NOT NULL,
//   metadata NVARCHAR(MAX),
//   status NVARCHAR(255) CHECK (status IN ('active','inactive','draft')), ...)

schema.toDropSQL(): string

Generate a DROP TABLE IF EXISTS statement.

println(UserSchema.toDropSQL())
// DROP TABLE IF EXISTS users

async schema.sync(conn: any): Task<any>

Execute CREATE TABLE IF NOT EXISTS on the given connection.

async schema.drop(conn: any): Task<any>

Execute DROP TABLE IF EXISTS on the given connection.

---

Repository

Creating a Repository

Extend Repository<T> and pass the schema to super():

class UserRepo extends Repository<User> {
    constructor() {
        super(UserSchema)
    }
}

var repo: any = new UserRepo()

Connection

async repo.useConnection(conn: any): Task<any>

Set the database connection for this repository and its schema. Automatically syncs the table to the database (CREATE TABLE IF NOT EXISTS) unless autoSyncTableToDB is set to false on the connection. Must be called before any database operations.

var conn: any = db.open(DbDriver.Postgres, databaseUrl)
await repo.useConnection(conn) // connects + auto-syncs table

To disable auto-sync:

var conn: any = db.open(DbDriver.Sqlite, ":memory:", { "autoSyncTableToDB": false })
await repo.useConnection(conn) // connects only, no auto-sync
await repo.sync()              // manual sync when ready

Schema Operations

async repo.sync(): Task<any>

Manually create the table if it doesn’t exist. Not needed if useConnection already auto-synced.

await repo.sync()

async repo.drop(): Task<any>

Drop the table.

await repo.drop()

CRUD Operations

async repo.create(data: any): Task<any>

Insert a new row. Pass a map of column names to values. Returns { rowsAffected, lastInsertId }.

var result: any = await repo.create({
    "name": "Alice",
    "email": "alice@example.com",
    "age": 30
})
println(result["lastInsertId"]) // 1

async repo.findById(id: any): Task<any>

Find a single row by primary key. Returns the row map or null.

var user: any = await repo.findById(1)
println(user["name"]) // Alice

async repo.all(): Task<any>

Fetch all matching rows (combine with .where(), .orderBy(), etc.).

var users: any = await repo.orderBy("name", "ASC").all()

async repo.first(): Task<any>

Fetch the first matching row, or null.

var user: any = await repo.where("email", "alice@example.com").first()

async repo.count(): Task<any>

Count matching rows.

var total: any = await repo.count()
var adults: any = await repo.whereOp("age", ">=", 18).count()

async repo.update(data: any): Task<any>

Update matching rows. Combine with .where() to target specific rows.

await repo.where("id", 1).update({"age": 31})

async repo.updateById(id: any, data: any): Task<any>

Update a single row by primary key.

await repo.updateById(1, {"name": "Alice Smith"})

async repo.delete(): Task<any>

Delete matching rows.

await repo.where("id", 1).delete()

async repo.deleteById(id: any): Task<any>

Delete a single row by primary key.

await repo.deleteById(1)

Query Chaining

Repository methods support fluent chaining for building queries:

.where(column: string, value: any): any

Add a WHERE column = value condition (AND).

.whereOp(column: string, op: string, value: any): any

Add a WHERE column op value condition with a custom operator (AND).

.orWhere(column: string, value: any): any

Add a WHERE column = value condition (OR).

.orWhereOp(column: string, op: string, value: any): any

Add a WHERE column op value condition (OR).

.whereNull(column: string): any

Add WHERE column IS NULL.

.whereNotNull(column: string): any

Add WHERE column IS NOT NULL.

.select(columns: []any): any

Specify columns to select. Omit for SELECT *.

.orderBy(column: string, direction?: string): any

Add ORDER BY. Direction defaults to "ASC".

.limit(n: int): any

Limit the number of rows returned.

.offset(n: int): any

Skip rows (for pagination).

.groupBy(columns: []any): any

Group results by columns.

// Complex query
var results: any = await repo
    .whereOp("age", ">=", 18)
    .whereNotNull("email")
    .orderBy("name", "ASC")
    .limit(10)
    .offset(20)
    .all()

// Aggregation
var stats: any = await repo
    .select(["status", "COUNT(*) as total"])
    .groupBy(["status"])
    .all()

Eager Loading: .with(relationName: string): any

Eagerly load a named relationship defined in the schema. Results include the related data under the relationship name key. Supports hasOne, hasMany, belongsTo, and manyToMany.

// Load posts for each user
var users: any = await userRepo.with("posts").all()
println(users[0]["posts"])    // array of related posts

// Load multiple relations
var user: any = await userRepo
    .with("posts")
    .with("profile")
    .with("roles")
    .first()

println(user["posts"])        // []Post
println(user["profile"])      // Profile map or null
println(user["roles"])        // []Role

// Works with findById too
var u: any = await userRepo.with("posts").findById(1)
println(length(u["posts"]))   // number of posts

// belongsTo loads the parent
var posts: any = await postRepo.with("author").all()
println(posts[0]["author"]["name"]) // author's name

Debug: .toSql(): any

Returns the generated SQL and parameters without executing the query. Useful for debugging.

var debug: any = repo.where("email", "alice@example.com").toSql()
println(debug["sql"])    // SELECT * FROM users WHERE email = ?
println(debug["params"]) // [alice@example.com]

Custom Repository Methods

Add domain-specific query methods to your repository subclass. Remember to await calls to other async methods:

class UserRepo extends Repository<User> {
    constructor() { super(UserSchema) }

    async findByEmail(email: string): Task<any> {
        return await this.where("email", email).first()
    }

    async findAdults(): Task<any> {
        return await this.whereOp("age", ">=", 18).orderBy("age", "ASC").all()
    }

    async searchByName(query: string): Task<any> {
        return await this.whereOp("name", "LIKE", "%" + query + "%").all()
    }

    async countByStatus(status: string): Task<any> {
        return await this.where("status", status).count()
    }
}

Async Return Rule

When an async method returns the result of another async method, always use return await:

// ✅ Correct — unwraps inner Task
async findByEmail(email: string): Task<any> {
    return await this.where("email", email).first()
}

// ❌ Wrong — returns Task<Task<any>> (double-wrapped)
async findByEmail(email: string): Task<any> {
    return this.where("email", email).first()
}

---

Complete Example

Quick Script (single file)

For quick scripts and prototyping, use the inline form with db.define<T>(conn, ...) — everything in one file:

import { db, DbDriver } from "std/db"
import { Schema, SchemaBuilder } from "std/db/schema"
import { Repository } from "std/db/repository"

dataType Product {
    id: int;
    name: string;
    price: float;
    stock: int;
    createdAt: string
}

var conn: any = db.open(DbDriver.Sqlite, ":memory:")

const ProductSchema: Schema = db.define<Product>(conn, "products", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
    schema.float("price").notNull()
    schema.int("stock").defaultTo(0)
    schema.timestamp("createdAt").defaultTo("now")
})

class ProductRepo extends Repository<Product> {
    constructor() { super(ProductSchema) }
}

async function main(): Task<any> {
    var repo: any = new ProductRepo()
    await repo.useConnection(conn)

    await repo.create({"name": "Widget", "price": 9.99, "stock": 100})
    var all: any = await repo.all()
    println(all)

    await repo.drop()
    db.close(conn)
    return null
}

await main()

Real Project (multi-file, recommended)

For real projects with multiple entities, define schemas as pure declarations in separate files. The connection is established once and passed via useConnection(conn):

// ── src/db/entities/productEntity.chuks ──
import { db } from "std/db"
import { Schema, SchemaBuilder } from "std/db/schema"

export dataType Product {
    id: int;
    name: string;
    price: float;
    stock: int;
    createdAt: string
}

export const ProductSchema: Schema = db.define<Product>("products", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
    schema.float("price").notNull()
    schema.int("stock").defaultTo(0)
    schema.timestamp("createdAt").defaultTo("now")
})

// ── src/db/repository/productRepo.chuks ──
import { Repository } from "std/db/repository"
import { Product, ProductSchema } from "../entities/productEntity.chuks"

export class ProductRepo extends Repository<Product> {
    constructor() { super(ProductSchema) }

    async findByName(name: string): Task<any> {
        return await this.where("name", name).first()
    }

    async findInStock(): Task<any> {
        return await this.whereOp("stock", ">", 0).orderBy("name", "ASC").all()
    }

    async lowStock(threshold: int): Task<any> {
        return await this.whereOp("stock", "<=", threshold)
            .whereOp("stock", ">", 0)
            .orderBy("stock", "ASC")
            .all()
    }
}

// ── src/main.chuks ──
import { db, DbDriver } from "std/db"
import { dotenv } from "std/dotenv"
import { ProductRepo } from "./db/repository/productRepo.chuks"
import { OrderRepo } from "./db/repository/orderRepo.chuks"
import { UserRepo } from "./db/repository/userRepo.chuks"

async function main(): Task<any> {
    dotenv.load()
    var conn: any = db.open(DbDriver.Postgres, dotenv.get("DATABASE_URL"))

    // Wire up all repositories — one line each, auto-syncs tables
    var productRepo = new ProductRepo()
    await productRepo.useConnection(conn)

    var orderRepo = new OrderRepo()
    await orderRepo.useConnection(conn)

    var userRepo = new UserRepo()
    await userRepo.useConnection(conn)

    println("All schemas synced")

    // Insert products
    await productRepo.create({"name": "Widget", "price": 9.99, "stock": 100})
    await productRepo.create({"name": "Gadget", "price": 24.99, "stock": 50})
    await productRepo.create({"name": "Doohickey", "price": 4.99, "stock": 5})

    // Query
    var inStock: any = await productRepo.findInStock()
    for (var i: int = 0; i < length(inStock); i++) {
        println(inStock[i]["name"] + " — $" + string(inStock[i]["price"]))
    }

    // Update stock
    await productRepo.updateById(1, {"stock": 98})

    // Find low stock
    var low: any = await productRepo.lowStock(10)
    println("Low stock items: " + string(length(low)))

    // Count
    var total: any = await productRepo.count()
    println("Total products: " + string(total))

    db.close(conn)
    return null
}

await main()