Database API Reference

Complete API reference for @curisjs/db package.

Source Code

All source code is available on GitHub

Model

Class: Model

Source View on GitHub

Base class for all database models.

Static Properties

tableName

Type: string

The database table name.

Example:

class User extends Model {
  static tableName = 'users';
}

timestamps

Type: boolean

Enable automatic timestamps (default: true).

Example:

class User extends Model {
  static tableName = 'users';
  static timestamps = true; // Adds createdAt, updatedAt
}

softDelete

Type: boolean

Enable soft deletes (default: false).

Example:

class User extends Model {
  static tableName = 'users';
  static softDelete = true; // Adds deletedAt
}

primaryKey

Type: string

Primary key column name (default: 'id').

Example:

class User extends Model {
  static tableName = 'users';
  static primaryKey = 'userId';
}

---

Static Methods

Query Methods

findById(id)

Find a record by primary key.

Signature:

static findById<T extends Model>(id: number | string): Promise<T | null>

Parameters:

• id: Primary key value

Returns: Promise resolving to model instance or null

Example:

const user = await User.findById(1);
if (user) {
  console.log(user.name);
}

findOne(conditions)

Find a single record matching conditions.

Signature:

static findOne<T extends Model>(conditions: Partial<T>): Promise<T | null>

Example:

const user = await User.findOne({ email: 'user@example.com' });

findMany(options?)

Find multiple records.

Signature:

static findMany<T extends Model>(options?: FindOptions): Promise<T[]>

Options:

• where: Query conditions
• limit: Maximum records
• offset: Skip records
• orderBy: Sort order
• select: Columns to select

Example:

const users = await User.findMany({
  where: { active: true },
  limit: 10,
  offset: 0,
  orderBy: { createdAt: 'desc' }
});

findAll()

Find all records.

Signature:

static findAll<T extends Model>(): Promise<T[]>

Example:

const users = await User.findAll();

count(conditions?)

Count records matching conditions.

Signature:

static count(conditions?: Record<string, any>): Promise<number>

Example:

const total = await User.count();
const activeCount = await User.count({ active: true });

exists(conditions)

Check if record exists.

Signature:

static exists(conditions: Record<string, any>): Promise<boolean>

Example:

const exists = await User.exists({ email: 'user@example.com' });

CRUD Methods

create(data)

Create a new record.

Signature:

static create<T extends Model>(data: Partial<T>): Promise<T>

Example:

const user = await User.create({
  name: 'John Doe',
  email: 'john@example.com'
});

createMany(data)

Create multiple records.

Signature:

static createMany<T extends Model>(data: Partial<T>[]): Promise<T[]>

Example:

const users = await User.createMany([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' }
]);

updateById(id, data)

Update a record by ID.

Signature:

static updateById<T extends Model>(
  id: number | string, 
  data: Partial<T>
): Promise<T | null>

Example:

const user = await User.updateById(1, { name: 'Updated Name' });

updateMany(conditions, data)

Update multiple records.

Signature:

static updateMany(
  conditions: Record<string, any>,
  data: Record<string, any>
): Promise<number>

Returns: Number of updated records

Example:

const count = await User.updateMany(
  { active: false },
  { status: 'inactive' }
);

deleteById(id)

Delete a record by ID.

Signature:

static deleteById(id: number | string): Promise<boolean>

Returns: True if deleted, false if not found

Example:

const deleted = await User.deleteById(1);

deleteMany(conditions)

Delete multiple records.

Signature:

static deleteMany(conditions: Record<string, any>): Promise<number>

Returns: Number of deleted records

Example:

const count = await User.deleteMany({ active: false });

Relationship Methods

hasMany(model, foreignKey?, localKey?)

Define a one-to-many relationship.

Signature:

static hasMany<T extends Model>(
  model: typeof Model,
  foreignKey?: string,
  localKey?: string
): Relationship<T[]>

Example:

class User extends Model {
  static tableName = 'users';
  
  posts() {
    return this.hasMany(Post, 'userId', 'id');
  }
}

belongsTo(model, foreignKey?, ownerKey?)

Define an inverse one-to-many relationship.

Signature:

belongsTo<T extends Model>(
  model: typeof Model,
  foreignKey?: string,
  ownerKey?: string
): Relationship<T>

Example:

class Post extends Model {
  static tableName = 'posts';
  
  user() {
    return this.belongsTo(User, 'userId', 'id');
  }
}

hasOne(model, foreignKey?, localKey?)

Define a one-to-one relationship.

Signature:

static hasOne<T extends Model>(
  model: typeof Model,
  foreignKey?: string,
  localKey?: string
): Relationship<T>

belongsToMany(model, pivotTable, foreignKey?, relatedKey?)

Define a many-to-many relationship.

Signature:

static belongsToMany<T extends Model>(
  model: typeof Model,
  pivotTable: string,
  foreignKey?: string,
  relatedKey?: string
): Relationship<T[]>

Example:

class User extends Model {
  roles() {
    return this.belongsToMany(Role, 'user_roles', 'userId', 'roleId');
  }
}

---

Instance Methods

save()

Save changes to the database.

Signature:

save(): Promise<this>

Example:

const user = await User.findById(1);
user.name = 'Updated Name';
await user.save();

update(data)

Update instance properties and save.

Signature:

update(data: Partial<this>): Promise<this>

Example:

const user = await User.findById(1);
await user.update({ name: 'New Name', email: 'new@example.com' });

delete()

Delete the record.

Signature:

delete(): Promise<boolean>

Example:

const user = await User.findById(1);
await user.delete();

restore()

Restore a soft-deleted record.

Signature:

restore(): Promise<this>

Example:

const user = await User.findById(1);
await user.restore();

fresh()

Reload the model from database.

Signature:

fresh(): Promise<this>

Example:

const user = await User.findById(1);
// ... modifications by other processes
const refreshed = await user.fresh();

toJSON()

Convert model to plain object.

Signature:

toJSON(): Record<string, any>

---

QueryBuilder

Class: QueryBuilder

Source View on GitHub

Fluent query builder.

Methods

table(tableName)

Set the table for the query.

Signature:

table(tableName: string): QueryBuilder

Example:

import { db } from '@curisjs/db';

const users = await db.table('users').get();

select(...columns)

Select specific columns.

Signature:

select(...columns: string[]): QueryBuilder

Example:

const users = await db.table('users')
  .select('id', 'name', 'email')
  .get();

where(column, operator?, value?)

Add a WHERE clause.

Signature:

where(
  column: string | Record<string, any>,
  operator?: string,
  value?: any
): QueryBuilder

Example:

// Simple equality
await db.table('users').where('active', true).get();

// With operator
await db.table('users').where('age', '>', 18).get();

// Object syntax
await db.table('users').where({ active: true, role: 'admin' }).get();

whereIn(column, values)

WHERE IN clause.

Signature:

whereIn(column: string, values: any[]): QueryBuilder

Example:

await db.table('users').whereIn('id', [1, 2, 3]).get();

whereNull(column)

WHERE NULL clause.

Signature:

whereNull(column: string): QueryBuilder

whereNotNull(column)

WHERE NOT NULL clause.

Signature:

whereNotNull(column: string): QueryBuilder

whereBetween(column, range)

WHERE BETWEEN clause.

Signature:

whereBetween(column: string, range: [any, any]): QueryBuilder

Example:

await db.table('users').whereBetween('age', [18, 65]).get();

orWhere(column, operator?, value?)

Add an OR WHERE clause.

Signature:

orWhere(column: string, operator?: string, value?: any): QueryBuilder

orderBy(column, direction?)

Add ORDER BY clause.

Signature:

orderBy(column: string, direction?: 'asc' | 'desc'): QueryBuilder

Example:

await db.table('users').orderBy('createdAt', 'desc').get();

limit(count)

Limit number of results.

Signature:

limit(count: number): QueryBuilder

offset(count)

Skip records.

Signature:

offset(count: number): QueryBuilder

join(table, first, operator?, second?)

Add a JOIN clause.

Signature:

join(
  table: string,
  first: string,
  operator?: string,
  second?: string
): QueryBuilder

Example:

await db.table('users')
  .join('posts', 'users.id', '=', 'posts.userId')
  .get();

leftJoin(table, first, operator?, second?)

Add a LEFT JOIN clause.

Signature:

leftJoin(table: string, first: string, operator?: string, second?: string): QueryBuilder

groupBy(...columns)

Add GROUP BY clause.

Signature:

groupBy(...columns: string[]): QueryBuilder

having(column, operator, value)

Add HAVING clause.

Signature:

having(column: string, operator: string, value: any): QueryBuilder

get()

Execute query and get results.

Signature:

get<T = any>(): Promise<T[]>

first()

Get first result.

Signature:

first<T = any>(): Promise<T | null>

find(id)

Find by primary key.

Signature:

find<T = any>(id: number | string): Promise<T | null>

count(column?)

Count records.

Signature:

count(column?: string): Promise<number>

sum(column)

Sum column values.

Signature:

sum(column: string): Promise<number>

avg(column)

Average of column values.

Signature:

avg(column: string): Promise<number>

min(column)

Minimum value.

Signature:

min(column: string): Promise<number>

max(column)

Maximum value.

Signature:

max(column: string): Promise<number>

insert(data)

Insert record(s).

Signature:

insert(data: Record<string, any> | Record<string, any>[]): Promise<number[]>

Returns: Array of inserted IDs

Example:

const [id] = await db.table('users').insert({
  name: 'John',
  email: 'john@example.com'
});

update(data)

Update records.

Signature:

update(data: Record<string, any>): Promise<number>

Returns: Number of updated records

delete()

Delete records.

Signature:

delete(): Promise<number>

Returns: Number of deleted records

raw(sql, bindings?)

Execute raw SQL.

Signature:

raw(sql: string, bindings?: any[]): Promise<any>

---

Connection

Source View on GitHub

createConnection(config)

Create a database connection.

Signature:

createConnection(config: ConnectionConfig): Connection

Config:

interface ConnectionConfig {
  client: 'better-sqlite3' | 'pg' | 'mysql2';
  connection: {
    filename?: string;      // SQLite
    host?: string;          // PostgreSQL/MySQL
    port?: number;          // PostgreSQL/MySQL
    user?: string;          // PostgreSQL/MySQL
    password?: string;      // PostgreSQL/MySQL
    database?: string;      // PostgreSQL/MySQL
  };
  pool?: {
    min?: number;
    max?: number;
  };
}

Example:

import { createConnection } from '@curisjs/db';

// SQLite
const db = createConnection({
  client: 'better-sqlite3',
  connection: {
    filename: './database.sqlite'
  }
});

// PostgreSQL
const db = createConnection({
  client: 'pg',
  connection: {
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'password',
    database: 'myapp'
  }
});

---

Transactions

Source View on GitHub

transaction(callback)

Execute queries in a transaction.

Signature:

transaction<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>

Example:

import { db } from '@curisjs/db';

await db.transaction(async (trx) => {
  await trx.table('users').insert({ name: 'John' });
  await trx.table('posts').insert({ userId: 1, title: 'Hello' });
});

Transaction Object Methods

commit()

Commit the transaction.

Signature:

commit(): Promise<void>

rollback()

Rollback the transaction.

Signature:

rollback(): Promise<void>

savepoint(name)

Create a savepoint.

Signature:

savepoint(name: string): Promise<void>

rollbackTo(name)

Rollback to a savepoint.

Signature:

rollbackTo(name: string): Promise<void>

---

Schema Builder

Source View on GitHub

schema.createTable(tableName, callback)

Create a new table.

Signature:

schema.createTable(
  tableName: string,
  callback: (table: TableBuilder) => void
): Promise<void>

Example:

import { db } from '@curisjs/db';

await db.schema.createTable('users', (table) => {
  table.increments('id').primary();
  table.string('name').notNullable();
  table.string('email').notNullable().unique();
  table.timestamps(true, true);
});

TableBuilder Methods

Column Types

// Integers
table.increments(name)      // Auto-increment integer
table.integer(name)          // Integer
table.bigInteger(name)       // Big integer
table.tinyint(name)          // Tiny integer

// Decimals
table.decimal(name, precision?, scale?)
table.float(name, precision?, scale?)
table.double(name, precision?, scale?)

// Strings
table.string(name, length?)  // VARCHAR
table.text(name, type?)      // TEXT
table.uuid(name)             // UUID

// Dates
table.date(name)             // DATE
table.datetime(name)         // DATETIME
table.timestamp(name)        // TIMESTAMP
table.timestamps(useTimestamps?, defaultToNow?)  // created_at, updated_at

// Boolean
table.boolean(name)          // BOOLEAN

// JSON
table.json(name)             // JSON
table.jsonb(name)            // JSONB (PostgreSQL)

// Binary
table.binary(name)           // BLOB

Column Modifiers

column.notNullable()         // NOT NULL
column.nullable()            // Allow NULL
column.defaultTo(value)      // DEFAULT value
column.unique()              // UNIQUE constraint
column.primary()             // PRIMARY KEY
column.unsigned()            // UNSIGNED (MySQL)
column.index(indexName?)     // Create index
column.comment(text)         // Column comment

Table Methods

table.foreign(column)
  .references(column)
  .inTable(table)
  .onDelete(action)
  .onUpdate(action)

table.index(columns, indexName?, indexType?)
table.unique(columns, indexName?)
table.dropColumn(name)
table.renameColumn(from, to)

---

Migrations

migrate.latest()

Run all pending migrations.

Signature:

migrate.latest(): Promise<void>

migrate.rollback()

Rollback last batch of migrations.

Signature:

migrate.rollback(): Promise<void>

migrate.up()

Run next migration.

Signature:

migrate.up(): Promise<void>

migrate.down()

Rollback last migration.

Signature:

migrate.down(): Promise<void>

---

Types

Source View on GitHub

FindOptions

Options for finding records.

interface FindOptions {
  where?: Record<string, any>;
  select?: string[];
  limit?: number;
  offset?: number;
  orderBy?: Record<string, 'asc' | 'desc'>;
  include?: string[];
}

Relationship<T>

Relationship definition.

interface Relationship<T> {
  get(): Promise<T>;
  load(): Promise<T>;
}

---

See Also

• Models Guide
• Query Builder Guide
• Relations Guide
• Transactions Guide
• Migrations Guide