Skip to main content

Quik Framework :: Database

The Database package provides Quik's model, repository, query-builder, and migration primitives. It supports typed models, relation loading, soft deletes, pagination helpers, and migration or seed execution.

Features​

  • Model-based persistence with QModel.
  • Repository pattern with QRepository.
  • Query composition with QWhereBuilder, Conditions, and Clauses.
  • Relation loading with nested relation definitions.
  • Pagination and search helpers with QPaginatedParameters.
  • Automatic soft-delete filtering: all find queries exclude soft-deleted rows by default; pass withDeleted: true in QFindOptions to opt out.
  • Migrations and seeds through registered CLI commands.

Installation​

pnpm add @quik/database

Configuration​

Key environment variables:

  • DATABASE_CONFIGURATION with mysql, postgres, or sqlite.
  • DATABASE_MYSQL_HOSTNAME, DATABASE_MYSQL_PORT, DATABASE_MYSQL_USERNAME, DATABASE_MYSQL_PASSWORD, DATABASE_MYSQL_DATABASE.
  • DATABASE_POSTGRES_HOSTNAME, DATABASE_POSTGRES_PORT, DATABASE_POSTGRES_USERNAME, DATABASE_POSTGRES_PASSWORD, DATABASE_POSTGRES_DATABASE, DATABASE_POSTGRES_SCHEMA.
  • DATABASE_SQLITE_FILENAME.
  • DATABASE_LOGGING, DATABASE_DEBUGGING.

Migration-related config keys:

  • db.migrations.enabled.
  • db.migrations.table.

See the package defaults for the source of truth:

Usage​

Define a Model​

import { DateTime } from 'luxon';

import { Column, Model, QModel } from '@quik/database';

@Model()
export class User extends QModel {
  @Column.PrimaryGenerated()
  public id: number;

  @Column.String({ length: 500 })
  public name: string;

  @Column.String({ length: 500 })
  public email: string;

  @Column.CreatedAt()
  public createdAt: DateTime;
}

Define a Repository​

import { QRepository } from '@quik/database';

import { User } from './models/User.ts';

export class UserRepository extends QRepository<User> {
  public constructor() {
    super(User);
  }
}

Register or Inject a Repository​

You can instantiate repositories directly, but the package also exposes RepositoryStore and @GetRepository(...) for lazy resolution.

import { GetRepository, RepositoryStore } from '@quik/database';

import { UserRepository } from './repositories/UserRepository.ts';

RepositoryStore.register(UserRepository);
RepositoryStore.has(UserRepository); // true
RepositoryStore.all(); // [UserRepository]

class UsersService {
  @GetRepository(UserRepository)
  public repository!: UserRepository;
}

Query with a Repository​

import { QWhereBuilder } from '@quik/database';
import { Entity, Fields, QEntity } from '@quik/entity';

const repository = new UserRepository();

const users = await repository.get({
  where: QWhereBuilder.New()
    .Equal('status', 'active')
    .And(QWhereBuilder.New().NotNull('email')),
  sort: [
    { field: 'createdAt', direction: 'desc' }
  ],
  relations: [
    'profile',
    { posts: [ 'author', 'tags' ] }
  ]
});

const user = await repository.findById(1, {
  relations: [ 'profile', { posts: [ 'tags' ] } ]
});

const dailyRequests = await repository.countBy<{
  date: string;
  status: string;
  count: number;
}>({
  where: QWhereBuilder.New().NotNull('createdAt'),
  select: [
    { field: 'createdAt', as: 'date', transform: 'date' },
    'status'
  ],
  count: { field: 'id' },
  sort: [
    { field: 'date', direction: 'asc' },
    { field: 'status', direction: 'asc' }
  ]
});

@Entity('RequestCountRow')
class RequestCountRow extends QEntity {
  @Fields.String()
  public date: string;

  @Fields.String()
  public status: string;

  @Fields.Integer()
  public count: number;
}

const entityRows = await repository.countBy(RequestCountRow, {
  select: [
    { field: 'createdAt', as: 'date', transform: 'date' },
    'status'
  ],
  count: { field: 'id' }
});

Paginate with QPaginatedParameters​

QRepository.paged() accepts a QPaginatedParameters entity, not a plain object.

import {
  QPaginatedParameters,
  QSearchOperator
} from '@quik/database';
import { EntityStore } from '@quik/entity';

const parameters = EntityStore.create(QPaginatedParameters, {
  page: 1,
  size: 10,
  sortBy: [
    { field: 'name', direction: 'asc' }
  ],
  searchBy: [
    { field: 'status', operator: QSearchOperator.EQ, value: 'active' },
    { field: 'email', operator: QSearchOperator.LIKE, value: '%@example.com' }
  ],
  strict: true
});

const pagedUsers = await repository.paged(parameters, {
  relations: [ 'profile' ]
});

QSearchField also supports an optional table property when you need to target a joined table or relation alias such as posts_RelPost.title.

Use Query Helpers​

QWhereBuilder is the main public API for filtering. Conditions and Clauses are lower-level helpers when you want plain condition objects or prebuilt builders.

import { Clauses, Conditions, QWhereBuilder } from '@quik/database';

const builder = QWhereBuilder.New()
  .Equal('status', 'active')
  .Or(
    QWhereBuilder.New().And(
      Conditions.GreaterThan('score', 10),
      Conditions.NotNull('nickname')
    )
  );

const archived = Clauses.Not('status', 'archived');

.And(...)/.Or(...) accept Conditions.* objects directly — you don't need to wrap each one in QWhereBuilder.New(). Nested groups (like an OR sub-clause inside an AND chain) can be built inline with a callback instead of a separate QWhereBuilder.New() call, so a filter combining several plain conditions and one nested group fits in a single .And(...):

const builder = QWhereBuilder.New()
  .Equal('ProjectTeam.projectId', projectId)
  .And(
    Conditions.Equal('ProjectTeamMember.userId', userId),
    Conditions.LessThanOrEqual('ProjectTeamMember.startDate', date),
    (group) => group.Or(
      Conditions.IsNull('ProjectTeamMember.endDate'),
      Conditions.GreaterThanOrEqual('ProjectTeamMember.endDate', date)
    )
  );
// WHERE ProjectTeam.projectId = ? AND ProjectTeamMember.userId = ? AND ProjectTeamMember.startDate <= ?
//   AND (ProjectTeamMember.endDate IS NULL OR ProjectTeamMember.endDate >= ?)

When you know the model type and want field-name autocomplete for direct model fields, use the typed helper view:

import type { User } from './models/User.ts';
import { Clauses, Conditions } from '@quik/database';

const where = Clauses.Typed<User>().Equal('email', 'demo@example.com');
const condition = Conditions.Typed<User>().GreaterThan('id', 10);

Use the typed helpers for direct model fields. Use the untyped Clauses or Conditions helpers when you need dotted relation paths or relation aliases such as profile.email or posts_RelPost.title.

Join Queries with a Custom Result Shape​

QRepository.joinQuery(resultEntity) is for queries that join tables not declared as @Relations on the model (get/find/paged only join declared relations) and/or return a shape other than the model itself. Field names passed to .where() should be fully qualified as table.column when the query spans more than one table.

import { Entity, Fields, QEntity } from '@quik/entity';
import { Conditions, QRepository, QWhereBuilder } from '@quik/database';

@Entity('ActiveMembershipRow')
class ActiveMembershipRow extends QEntity {
  @Fields.Integer()
  public id: number;

  @Fields.Integer()
  public userId: number;
}

class ProjectTeamMemberRepository extends QRepository<ProjectTeamMember> {
  async findActiveMembership(projectId: number, userId: number, date: string) {
    return this.joinQuery(ActiveMembershipRow)
      .join('ProjectTeam', 'ProjectTeam.id', 'ProjectTeamMember.projectTeamId')
      .where(QWhereBuilder.New()
        .Equal('ProjectTeam.projectId', projectId)
        .And(
          Conditions.Equal('ProjectTeamMember.userId', userId),
          Conditions.LessThanOrEqual('ProjectTeamMember.startDate', date),
          (group) => group.Or(
            Conditions.IsNull('ProjectTeamMember.endDate'),
            Conditions.GreaterThanOrEqual('ProjectTeamMember.endDate', date)
          )
        ))
      .select('ProjectTeamMember.*')
      .first(); // or .get() for all matching rows
  }
}

resultEntity can be any plain @Entity()-decorated class — it doesn't need to be a @Model()/table-backed model. Rows are mapped with the same mechanism countBy(entity, ...) uses for its custom-shaped results (EntityStore.create(entity, row, true, { applyDefaults: false })), so unrecognized/absent fields don't trigger default-filling or validation errors. .first()/.get() are terminal.

Join types: .join()/.innerJoin() (alias), .leftJoin(), .rightJoin(), .fullOuterJoin(). Each accepts either (table, first, second), (table, first, operator, second), or (table, callback) for a multi-condition ON clause via Knex's own join callback:

.join('ProjectTeam', (on) => {
  on.on('ProjectTeam.id', 'ProjectTeamMember.projectTeamId').andOn('ProjectTeam.archived', knex.raw('false'));
})

Typed field names: field name strings passed to .where()/.having()/.select()/.groupBy()/.orderBy() are plain, unchecked strings by default. Field<TModel>(table, field) builds a table.field string with the field name checked against TModel's actual fields at compile time (the table name itself is not verified — pass the same string you used in .join()):

import { Field } from '@quik/database';

.where(QWhereBuilder.New().Equal(Field<ProjectTeam>('ProjectTeam', 'projectId'), projectId))

Select and aliasing: .select() accepts a mix of plain table.column/table.* strings, { field, as } pairs (quoted as a single identifier — safe for plain columns, not for aggregate expressions), and Knex.Raw for aggregates:

.select(
  'ProjectTeamMember.projectTeamId',
  this.knex.raw('COUNT(??) as ??', ['ProjectTeamMember.id', 'memberCount'])
)

Aggregates: .groupBy(...columns) and .having(whereBuilder) (filtering on SELECT aliases, e.g. memberCount above — unlike .where(), .having() does not auto-qualify unprefixed field names with the base table, since aliases aren't columns of any one table).

Use Relation Helpers​

RelationsUtils.fromList() converts flat relation paths into the nested structure used by repository queries.

import { RelationsUtils } from '@quik/database';

const relations = RelationsUtils.fromList([
  'profile',
  'posts.author',
  'posts.tags'
]);

const users = await repository.get({ relations });

Define Relations​

Relations are defined with decorators. For many-to-many relations, you can override the link table name with link.

import { ManyToMany } from '@quik/database';

@ManyToMany({ entity: () => Tag, column: 'tagId', link: 'UserTagLink' })
public tags: Tag[];

API Reference​

Core Types​

  • QModel: Base class for database models.
  • QRepository<TModel, TKey>: Base class for repositories.
  • QQueryBuilder: Low-level SQL builder used by repository methods.
  • QWhereBuilder: Fluent where-clause builder.
  • QPaginatedParameters: Query-parameter entity for pagination and filtering.
  • QSortField, QSearchField, QSearchByEntity: Entities used by QPaginatedParameters.

QRepository​

CRUD and lookup methods:

  • get(findOptions?).
  • find(findOptions?).
  • findOrNull(findOptions?).
  • findFirst(findOptions?).
  • findFirstOrNull(findOptions?).
  • findLast(findOptions?).
  • findLastOrNull(findOptions?).
  • findById(id, options?).
  • create(data).
  • bulkCreate(data).
  • bulkUpdate(ids, data).
  • update(id, data).
  • bulkDelete(ids, force?).
  • delete(id, force?).
  • deleteBy(where, force?).
  • restore(id).
  • findDeleted(findOptions?) — return only soft-deleted rows.
  • findWithDeleted(findOptions?) — return all rows including soft-deleted.
  • process(...items).
  • count(findOptions?).
  • countBy(findOptions).
  • paged(parameters?, relations?).

Query and Relation Types​

The repository module publicly exports:

  • QFindOptions.
  • QGroupedCountOptions, QGroupSelectOption, QCountAggregateOption.
  • QRelation, QFindRelations, QRelationOptions, QRelationFindOption.
  • QWhereCondition, QWhereClause, QWhereOperator.
  • QSortOption.

Store and Decorators​

  • RepositoryStore.register(...repositories).
  • RepositoryStore.load(folder).
  • RepositoryStore.constructor(repository).
  • RepositoryStore.get(repository).
  • RepositoryStore.has(repository).
  • RepositoryStore.all().
  • RepositoryStore.clear(repository?).
  • GetRepository(repository).

Helper Namespaces​

  • Conditions: Factory functions returning plain QWhereCondition objects.
  • Clauses: Factory functions returning QWhereBuilder instances.
  • Conditions.Typed<TModel>(): Typed condition helpers with direct model-field autocomplete.
  • Clauses.Typed<TModel>(): Typed clause helpers with direct model-field autocomplete.
  • RelationsUtils.fromList(relations): Convert flat relation paths into nested relation definitions.

Migrations and Seeds​

The package ships migration and seed engines together with CLI commands registered on boot.

Available commands:

  • migration:new <name>.
  • migration:generate <name> --module <module>.
  • migration:show.
  • migration:apply [--force].
  • migration:undo.
  • migration:update-table [--force].
  • seeds:new <name>.
  • seeds:show.
  • seeds:run.

Migration and seed classes must include a timestamp suffix in the class name, for example CreateUsers_20250101093000, to be discovered.

Applied migrations store execution metadata including execution time, batch id, and source checksum. Use migration:update-table before applying new migrations if you need to upgrade an existing migrations table to the latest metadata format. Pass --force to migration:update-table to refresh stored checksums for applied migrations whose source files changed, or to migration:apply when you intentionally want to continue a migration run after reconciling those checksum errors.

The migrations table is also upgraded automatically: every time migration:apply/migration:undo (or any other engine operation) resolves the migrations table, missing columns (executedAt, batchId, checksum, schema, ...) are added additively before continuing. Running migration:update-table manually is only needed to force a checksum refresh or to run the upgrade ahead of time; it is not required for the table to pick up new metadata columns such as schema.

Generating Migrations from Model Changes​

migration:generate <name> --module <module> diffs the currently registered @Model() classes' column definitions against a stored snapshot — not the live database — and writes a migration file with the resulting up()/down() schema-builder calls, updating the snapshot to match. This is snapshot-diffing, the same approach Prisma Migrate/Django use, chosen over live-DB introspection because it needs no database connection to run and isn't dialect-fragile.

migration:generate create-order --module core

Notes and current limitations:

  • The snapshot is global, not scoped per module — it covers every registered model regardless of which module's migration folder the generated file lands in, since models have no module association today. Running migration:generate for module A will still pick up schema changes in models that belong to module B.
  • All relevant model files must be imported/registered before running the command — decorators only run when their class file is loaded (the same precondition as getModels()).
  • Snapshot location: db.migrations.snapshotFile config (DATABASE_MIGRATIONS_SNAPSHOT_FILE), defaulting to ./migrations/.schema-snapshot.json. Commit this file — it's the diff baseline for the next run, not a build artifact.
  • Generated migrations are best-effort and must be reviewed before running. New tables/columns render directly as table.<type>(...) calls. Column type/nullable/default changes on existing columns render as .alter() — Knex's own limitations around .alter() apply (notably on SQLite). Enum value-set changes route through alterEnumColumn() instead of .alter(), defaulting to the CHECK-constraint strategy; add nativeType: true (and the original typeName) in the generated call if the column uses a native Postgres enum type. Dropped models/columns generate dropTable/dropColumn — review before running, this is destructive.
  • Not covered yet: indexes, foreign key constraints, primary key changes beyond what a plain .alter() handles, and an apply-directly / auto-sync mode (migration:generate only ever writes a file).

Listing Registered Models​

getModels() and getModelNames() (exported from @quik/database) read from EntityMetadataStore and return only classes decorated with @Model()/@BaseModel() — plain @Entity()-decorated classes (DTOs, query parameters, etc.) are excluded, since @Model() tags its metadata with a model flag.

import { getModelNames, getModels } from '@quik/database';

getModelNames(); // ['User', 'builder.BuilderModule', ...]
getModels(); // full EntityMetadata for each registered model

Models are only visible once their class file has been imported (decorators run at module evaluation time), so call this after all model files are loaded — e.g. after RepositoryStore.load(...)/module setup.

Postgres Schema Support​

@Model()/@BaseModel() accept an optional schema config field (Postgres only) that qualifies the resolved table name, e.g. @Model({ name: "BuilderModule", schema: "builder" }) resolves to table builder.BuilderModule. This relies on Knex's native dot-qualified identifier handling on the pg client; it is not portable to MySQL, where a dotted name means a cross-database reference instead.

Migrations can be scoped to a schema with MigrationStore.setSchema(module, schema). Before that module's migrations run, the engine issues CREATE SCHEMA IF NOT EXISTS against a Postgres connection (once per module per run); on any other dialect a schema-scoped module throws QMigrationError. The migrations bookkeeping table records the schema alongside the module for each applied migration.

uuidDefault(engine) (exported from @quik/database) returns a dialect-aware Knex.Raw default expression for UUID primary keys in hand-written migrations — gen_random_uuid() on Postgres (PG13+, or pgcrypto on older versions) and (UUID()) on MySQL:

import { uuidDefault } from '@quik/database';

public async up(schema, engine) {
  return schema.createTable('Example', (table) => {
    table.uuid('id').primary().defaultTo(uuidDefault(engine));
  });
}

Column.Json(options?) and Column.Jsonb(options?) add sugar factories for JSON/JSONB columns, mirroring Column.Text's { name?, nullable?, unique?, select?, default? } options shape.

Altering enum columns​

alterEnumColumn(engine, options) (exported from @quik/database) changes the allowed value set of an existing enum column in a hand-written migration, using a dialect-appropriate strategy:

  • MySQL — runs ALTER TABLE ... MODIFY COLUMN ... ENUM(...) directly; MySQL has no restriction on adding/removing/reordering enum values.
  • Postgres, CHECK-constraint-backed column (Knex's default table.enum(column, values), without useNative: true) — drops and recreates the CHECK constraint. This is the default (nativeType not set).
  • Postgres, native enum type (table.enum(column, values, { useNative: true, enumName })) — Postgres has no ALTER TYPE ... REMOVE VALUE/reorder support for native enum types, so this uses the standard "recreate type" strategy: create a new type with the desired values, cast the column across with USING column::text::new_type, drop the old type, then rename the new type into place. Pass nativeType: true and the original typeName.
import { alterEnumColumn } from '@quik/database';

public async up(schema, engine) {
  await alterEnumColumn(engine, {
    table: 'Order',
    column: 'status',
    values: ['pending', 'shipped', 'delivered', 'cancelled'],
    nativeType: true,
    typeName: 'order_status'
  });
}

On MySQL, pass nullable/default explicitly if the column has them — MODIFY COLUMN redefines the column from scratch and drops attributes that aren't restated.

Testing and Coverage​

Run package tests with:

pnpm test

See the root instructions for coverage details.

API Reference​

Generated API documentation is available in the database API section.