Database (Drizzle) API

Package: @onebun/drizzle

Overview

OneBun provides database integration via Drizzle ORM with support for:

• SQLite (via bun:sqlite)
• PostgreSQL
• Type-safe queries
• Migrations
• Repository pattern

DrizzleModule

SQLite Setup

import { Module } from '@onebun/core';
import { DrizzleModule, DatabaseType } from '@onebun/drizzle';
import { UserController } from './user.controller';
import { UserService } from './user.service';
import * as schema from './schema';

@Module({
  imports: [
    DrizzleModule.forRoot({
      connection: {
        type: DatabaseType.SQLITE,
        options: {
          url: './data/app.db',
        },
      },
      migrationsFolder: './drizzle',
    }),
  ],
  controllers: [UserController],
  providers: [UserService],
})
export class UserModule {}

SQLite pragmas and read-only files

SQLiteConnectionOptions gives every connection a pragma set, applied immediately after the file opens. The default is ['journal_mode = WAL', 'synchronous = NORMAL'], and pragmas replaces it wholesale:

DrizzleModule.forRoot({
  connection: {
    type: DatabaseType.SQLITE,
    options: {
      url: './data/app.db',
      pragmas: ['journal_mode = WAL', 'synchronous = NORMAL', 'foreign_keys = ON'],
    },
  },
})

A read-only connection gets a different default — ['synchronous = NORMAL']:

DrizzleModule.forRoot({
  connection: {
    type: DatabaseType.SQLITE,
    options: {
      url: './data/reference.db',
      options: { readonly: true },   // no pragmas needed: the default set adapts
    },
  },
})

journal_mode is a property of the file rather than of the connection — setting it rewrites the database header — so a read-only handle answers attempt to write a readonly database. That is why the read-only default leaves it out: a read-only SQLite file is an ordinary deployment — a shipped dataset, a mounted read-only volume — and it boots with no pragma list at all.

An explicit pragmas array is always applied exactly as given, read-only or not. Ask for a write pragma on a read-only connection and the boot still fails, naming the pragma and saying it is yours to remove — the framework filters its own defaults, not your list.

Why not just ignore the failure

Because whether it fails depends on the file. Measured under bun:sqlite, PRAGMA journal_mode = WAL on a read-only handle fails only when the file is not already in WAL mode; on a file that is, the identical statement succeeds as a no-op. Swallowing the error would leave a deployment whose boot depends on how the database it was handed happened to be written.

PostgreSQL Setup

DrizzleModule.forRoot({
  connection: {
    type: DatabaseType.POSTGRESQL,
    options: {
      // A URL…
      connectionString: config.get('database.url'),

      // …or the five discrete fields. Not both — see PostgreSQL Connection below.
      // host: 'localhost',
      // port: 5432,
      // database: 'myapp',
      // user: 'postgres',
      // password: 'password',
    },
  },
  // autoMigrate defaults to true — omit unless you need to disable it
  migrationsFolder: './drizzle',
})

PostgreSQL Connection

options takes either a connectionString or the five discrete fields — never a mix, and never a subset. The two shapes are mutually exclusive at the type level, so a half-filled object is a compile error rather than a connection built from undefined that surfaces later as an unreachable server:

// A URL
options: { connectionString: 'postgresql://user:password@host:5432/database' }

// Or every discrete field
options: { host: 'localhost', port: 5432, user: 'postgres', password: 'secret', database: 'app' }

Prefer the URL when the value comes from configuration. A connectionString is passed to the driver untouched, so query parameters it carries — ?sslmode=require, ?application_name=… — reach the server. The discrete fields carry no query string, so they cannot express those at all.

Neither shape is ever turned into the other. The discrete fields are handed to the driver as fields, so a password is whatever byte sequence you wrote — @, /, % and : included, with nothing to escape and nothing that a URL parser can reinterpret. In a connectionString the usual URL rules still apply: it is a URL, so %40 in it means @.

Do not percent-encode a discrete password

A discrete password is sent exactly as written: password: 'p%40ss' authenticates as the literal p%40ss, not as p@ss. Write the real password, URL-special characters and all. Percent-encoding belongs only in a connectionString, which is a URL and has URL semantics.

Options arriving from an untyped source (a JSON config, a cast) are validated at initialize() and rejected with an error naming the problem: which discrete fields accompanied a connectionString, or which of the five are missing. Neither case is resolved by picking a winner.

pool is accepted alongside either shape, and every option in it reaches the driver:

DrizzleModule.forRoot({
  connection: {
    type: DatabaseType.POSTGRESQL,
    options: {
      connectionString: 'postgresql://user:password@host:5432/app',
      pool: {
        max: 20,            // connections the pool may open (driver default: 10)
        idleTimeout: 30000, // ms an idle connection is kept (default: kept forever)
        timeout: 2000,      // ms a connect may take (driver default: 30000)
      },
    },
  },
})

The block is typed as PostgreSQLPoolOptions. Both timeouts are milliseconds, like every other duration in this framework. The driver takes seconds, so they are divided on the way through — it accepts fractional values, so a 250 ms timeout stays 250 ms rather than rounding to nothing.

timeout has one meaning in both places it is used: it bounds the driver's own connect and the startup reachability probe. It is one number, so the two cannot drift apart.

A zero or negative value is not forwarded. To the driver a zero timeout means no timeout, so passing it on would turn a misconfiguration into an unbounded connect; the driver's default applies instead.

There is no pool.min

Bun's SQL opens connections on demand and has no minimum-pool concept, so pool has no min option and TypeScript rejects one. Leave it out of your configuration: no pool behaviour depends on a minimum.

Global Module (Default Behavior)

By default, DrizzleModule is a global module. This means that once you import it in your root module, DrizzleService is automatically available in all submodules without explicit imports.

// app.module.ts - Import once in root module
import { Module } from '@onebun/core';
import { DrizzleModule, DatabaseType } from '@onebun/drizzle';
import { UserModule } from './user/user.module';
import { PostModule } from './post/post.module';

@Module({
  imports: [
    DrizzleModule.forRoot({
      connection: {
        type: DatabaseType.POSTGRESQL,
        options: { host: 'localhost', port: 5432, user: 'app', password: 'secret', database: 'myapp' },
      },
    }),
    UserModule,
    PostModule,
  ],
})
export class AppModule {}

// user/user.module.ts - DrizzleService available without importing DrizzleModule
import { Module } from '@onebun/core';
import { UserController } from './user.controller';
import { UserService } from './user.service';

@Module({
  controllers: [UserController],
  providers: [UserService], // UserService can inject DrizzleService
})
export class UserModule {}

// user/user.service.ts - DrizzleService is automatically available
import { Service, BaseService } from '@onebun/core';
import { DrizzleService } from '@onebun/drizzle';
import { users } from './schema';

@Service()
export class UserService extends BaseService {
  constructor(private db: DrizzleService) {
    super();
  }

  async findAll() {
    return this.db.select().from(users);
  }
}

Non-Global Mode

isGlobal: false stops DrizzleService from being ambiently available: a module reaches it only by importing DrizzleModule explicitly. There is still exactly ONE DrizzleService per application — the two modes differ in VISIBILITY, not in how many instances exist.

@Module({
  imports: [
    DrizzleModule.forRoot({
      connection: {
        type: DatabaseType.POSTGRESQL,
        options: { host: 'db', port: 5432, user: 'app', password: 'secret', database: 'app' },
      },
      isGlobal: false,
    }),
  ],
  providers: [ReportService],
})
export class ReportModule {}

Multiple databases

Name each configuration with as, and let each feature module select the one it needs. The token is a symbol or a string:

export const MAIN_DB = Symbol('MAIN_DB');
export const ANALYTICS_DB = Symbol('ANALYTICS_DB');

@Module({
  imports: [
    DrizzleModule.forRoot({ connection: mainConnection,      as: MAIN_DB }),
    DrizzleModule.forRoot({ connection: analyticsConnection, as: ANALYTICS_DB }),
    ReportsModule,
  ],
})
export class AppModule {}

// The feature selects its registration at its own boundary...
@Module({
  imports: [DrizzleModule.forFeature(ANALYTICS_DB)],
  providers: [ReportService],
})
export class ReportsModule {}

// ...and its providers write the ordinary constructor. No @Inject and no token at the
// injection site — the module's import already decided which registration it resolves to.
@Service()
export class ReportService extends BaseService {
  constructor(private db: DrizzleService) { super(); }
}

Registering one token twice throws rather than silently replacing the first, and selecting a token that no forRoot() configured fails at startup naming the missing call.

A module that needs BOTH — a reconciliation job, a migration — names each one with @Inject(TOKEN). Un-annotated parameters in the same constructor keep resolving by type:

@Module({
  imports: [DrizzleModule.forFeature(MAIN_DB), DrizzleModule.forFeature(ANALYTICS_DB)],
  providers: [Reconciler],
})
export class ReconcileModule {}

@Service()
export class Reconciler extends BaseService {
  constructor(
    @Inject(MAIN_DB) private main: DrizzleService,
    @Inject(ANALYTICS_DB) private analytics: DrizzleService,
    private clock: ClockService,
  ) { super(); }
}

Asking for the bare class in such a module fails at startup naming both candidates, and asking for a token the module never selected fails naming what it did select. Both alternatives — picking one silently — are the wrong-database failure this mechanism exists to prevent.

A named registration is never global. That is what makes two of them safe: ambient visibility has one slot per service, so a named registration reaches a module only by being imported. Combining as with isGlobal: true throws; isGlobal: false alongside as is accepted and does nothing, because it asks for what a named registration already guarantees. Neither spelling changes DrizzleModule's own globality — only an unnamed forRoot() decides that, and it is global unless it passes isGlobal: false.

Reaching a registration from outside the tree: app.getService(DrizzleService, ANALYTICS_DB). Without the token there is no correct answer once two registrations exist, so the call throws instead of choosing — an Error whose name is OneBunAmbiguousServiceError, naming both candidates. app.getLayer() without arguments throws the same error rather than silently carrying whichever was merged last: Effect keys a Context by the tag's key, which is the class NAME, so two registrations need two slots where a Context has one. Say which one takes it — app.getLayer([[DrizzleService, ANALYTICS_DB]]) — and the layer builds. Both checks fire only when the tree holds two or more instances under one key; a single registration, named or not, still answers.

Two unnamed forRoot() calls are not two databases

An unnamed registration is identified by the module class itself, so a process holds one of them, and the call evaluated last would decide for every application. Two unnamed forRoot() calls that disagree — about the connection they configure, or about isGlobal — are therefore refused at app.start() with OneBunConflictingRegistrationError, naming both call sites. Two databases always need as, as above.

forFeature() Method

When DrizzleModule is not global (isGlobal: false), a submodule must import it to reach DrizzleService: a non-global service does not travel down the module tree, and exports travels up to the importing module rather than down to children. forFeature() is that import:

// Root module with non-global DrizzleModule
@Module({
  imports: [
    DrizzleModule.forRoot({
      connection: { ... },
      isGlobal: false,
    }),
    UserModule,
  ],
})
export class AppModule {}

// Feature module must explicitly import DrizzleService
@Module({
  imports: [DrizzleModule.forFeature()], // Required when isGlobal: false
  controllers: [UserController],
  providers: [UserService],
})
export class UserModule {}

forFeature() shares the root instance

Every module importing it receives the SAME DrizzleService — one connection pool and one state for the application, not a pool per feature module.

If you want ONE shared instance without making DrizzleModule itself global, a @Global() module that imports it and re-exports the service does that — the re-exported instance then reaches modules that import neither:

@Global()
@Module({
  imports: [DrizzleModule.forRoot({ connection: { /* ... */ }, isGlobal: false })],
  exports: [DrizzleService],
})
export class DatabaseModule {}

Schema Definition

All schema builders are re-exported from @onebun/drizzle:

• PostgreSQL: import { ... } from '@onebun/drizzle/pg'
• SQLite: import { ... } from '@onebun/drizzle/sqlite'
• Common operators: import { eq, and, ... } from '@onebun/drizzle'

SQLite Schema

// schema/users.ts
import { sqliteTable, text, integer } from '@onebun/drizzle/sqlite';
import { sql } from '@onebun/drizzle';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  age: integer('age'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

PostgreSQL Schema

// schema/users.ts
import { pgTable, text, integer, timestamp } from '@onebun/drizzle/pg';

export const users = pgTable('users', {
  // Use generatedAlwaysAsIdentity() for auto-increment integer primary key
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  age: integer('age'),
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'date' }).notNull().defaultNow(),
});

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

Alternative with UUID:

import { pgTable, uuid } from '@onebun/drizzle/pg';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  // ... rest of schema
});

JSON and JSONB columns

Declared as usual, and stored as what they are:

import { pgTable, serial, jsonb, json } from '@onebun/drizzle/pg';

export const events = pgTable('events', {
  id: serial('id').primaryKey(),
  payload: jsonb('payload').$type<{ kind: string; tags: string[] }>(),
  raw: json('raw'),
});

Objects, arrays and scalars all round-trip as values, not as JSON strings, so the SQL side works:

SELECT * FROM events WHERE payload @> '{"kind":"signup"}';
SELECT jsonb_array_length(payload -> 'tags') FROM events;

jsonb[] columns (jsonb('tags').array()) round-trip too.

A double-encoded row stays a string

A row whose value is a jsonb string holding JSON text is, as a rule, a value that was encoded twice — for example by raw SQL that binds ${JSON.stringify(v)}::jsonb (see Without the helper). Every SQL operator, every other service and every report sees a string: jsonb_typeof returns 'string', @> matches nothing, jsonb_array_length fails with cannot get array length of a scalar.

The read path decodes once, so the ORM returns such a row as the string it is on disk, and nothing rewrites these rows automatically. Repair them with Repairing double-encoded JSON before deploying code that reads them as values.

Prepared statements and placeholders

sql.placeholder() on a json/jsonb column round-trips through .prepare(), on both .values() and .set():

const insert = db.insert(events)
  .values({ payload: sql.placeholder('payload') })
  .prepare('insert_event');

await insert.execute({ payload: { kind: 'signup', tags: ['beta'] } });
await insert.execute({ payload: null });   // SQL NULL, not the JSON text `null`

One prepared statement can be executed any number of times with different payloads; the cast is added to its text once.

JSON values in raw SQL

The encoding above lives in the column encoders, so a value interpolated straight into db.execute(sql`...`) never reaches it. For raw SQL, @onebun/drizzle/pg exports jsonbParam() and jsonParam():

import { sql } from '@onebun/drizzle';
import { jsonbParam, jsonParam } from '@onebun/drizzle/pg';

const payload = { kind: 'signup', tags: ['beta'] };

await db.execute(sql`INSERT INTO events (payload) VALUES (${jsonbParam(payload)})`);

await db.execute(sql`
  UPDATE events SET payload = jsonb_set(payload, '{tags}', ${jsonbParam(['beta', 'early'])})
  WHERE payload @> ${jsonbParam({ kind: 'signup' })}
`);

// a `json` column (or json_typeof, json_build_object) takes jsonParam
await db.execute(sql`INSERT INTO events (raw) VALUES (${jsonParam({ source: 'import' })})`);

Each renders $n::text::jsonb (jsonParam: $n::text::json) with JSON.stringify(value) bound as one text parameter, which the server parses. That is correct for every shape — objects, arrays ([] and one-element arrays included), strings, numbers, booleans — in any expression: @>, ->, jsonb_set, jsonb_build_object, INSERT and UPDATE values. Neither needs column metadata or DrizzleService.

• null is JSON null: jsonb_typeof answers 'null' and IS NULL is false. For SQL NULL, write NULL.
• JSON.stringify rules apply: a Date becomes its ISO string, a nested undefined is dropped, NaN becomes null. jsonb normalizes key order and whitespace.
• undefined, a function, a symbol, a BigInt, a circular structure and drizzle objects (an sql fragment, a column, a placeholder) throw a TypeError naming the helper, instead of binding nothing or a drizzle object's internals. A drizzle object nested anywhere inside the value is refused too: a placeholder must be the whole value, not a field of it.
• jsonParam() is for json targets: a json column, json_typeof, json_build_object. jsonb operators such as @> do not accept json, and json functions do not accept jsonb.
• ORM column writes keep the column path. db.insert(events).values({ payload }) already goes through the column encoder; the helpers are for raw SQL and for expressions that are not a column.

They live in @onebun/drizzle/pg, not in @onebun/drizzle, because the SQL they render is PostgreSQL's. SQLite has its own rule — see JSON in raw SQL on SQLite.

Prepared raw SQL

Pass sql.placeholder() instead of a value, and the value to execute(). It is serialized and checked the same way when the statement runs, and the cast is added once:

import { sql } from '@onebun/drizzle';
import { jsonbParam } from '@onebun/drizzle/pg';

const byPayload = db.select({ id: events.id })
  .from(events)
  .where(sql`${events.payload} @> ${jsonbParam(sql.placeholder('match'))}`)
  .prepare('events_by_payload');

await byPayload.execute({ match: { kind: 'signup' } });
await byPayload.execute({ match: { tags: ['beta'] } });

null handed to execute() here is JSON null, as it is for jsonbParam(null) — unlike a placeholder on a json column (above), which binds SQL NULL.

Without the helper

Measured against postgres:16 through DrizzleService, every hand-written form is wrong for some shape:

Raw form	What the server gets
${payload}, an object	an object — correct, but for objects only
${['x']}	drizzle renders a one-element array as ($1) and binds its element: the jsonb string "x". [{ a: 1 }] stores the object without the array, [null] stores SQL NULL
${['x', 'y']}	fails: expression is of type record
${[]}	fails: syntax error at or near ")"
${42}, ${true}	fails: expression is of type integer (boolean)
${null}	SQL NULL, not JSON null
${JSON.stringify(v)}::jsonb	always a jsonb string: Bun JSON-encodes a parameter it infers as jsonb, so the text is encoded twice
${JSON.stringify(v)}::text::jsonb	correct — the double cast makes Bun bind the parameter as text. This is what jsonbParam() renders

The raw Bun client, $client, has no helper: there, write $1::text::jsonb yourself and pass JSON.stringify(value).

JSON in raw SQL on SQLite

SQLite has no helper, because nothing there is driver-specific: json(?) is SQLite's own way to mark a text parameter as JSON. Write json(${JSON.stringify(value)}):

import { sql } from '@onebun/drizzle';

await db.run(sql`
  UPDATE events SET payload = json_set(payload, '$.tags', json(${JSON.stringify(['beta'])}))
  WHERE id = ${1}
`);

Without json(...), the same JSON text inside json_set(), json_object() or json_array() is stored as a JSON string — SQLite's own double encoding. Raw values fail in their own ways:

• A top-level array expands into a parameter list, as on PostgreSQL.
• A plain object as the first parameter silently binds NULL for every positional parameter of the statement: bun:sqlite reads it as a map of named parameters. With obj a plain object, sql`INSERT INTO t (data, n) VALUES (${obj}, ${5})` stores NULL in both columns and reports no error — on the default DB_TYPE. An object in any other position fails with Binding expected string, TypedArray, boolean, number, bigint or null.
• true and false are bound as 1 and 0, so json_type answers 'integer'.

A text('col', { mode: 'json' }) column written through the ORM is not affected.

Repairing double-encoded JSON

A double-encoded row holds a jsonb string whose text is the JSON value. Repair such rows before deploying code that reads them, with the guard:

UPDATE t SET c = (c #>> '{}')::jsonb
WHERE jsonb_typeof(c) = 'string' AND (c #>> '{}') ~ '^\s*[\[{]';

The guard is not optional. Without ~ '^\s*[\[{]' the same statement tries to parse every string scalar and fails on the first one that is not JSON — invalid input syntax for type json — taking the whole repair with it. It is a heuristic for the same reason: a legitimately stored jsonb string whose content happens to look like JSON is indistinguishable from a double-encoded row, and this one deliberately errs toward leaving values alone.

Run it before the deploy, not after: mapFromDriverValue is identity, so an unrepaired row reads back as the string it is on disk, while createSelectSchema types a $type<T>() column as T.

Relations

// schema/posts.ts
import { pgTable, text, integer, timestamp } from '@onebun/drizzle/pg';
import { relations } from '@onebun/drizzle';
import { users } from './users';

export const posts = pgTable('posts', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').notNull().references(() => users.id),
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
});

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

Index Schema

// schema/index.ts
export * from './users';
export * from './posts';

DrizzleService

Connection Lifecycle

DrizzleService implements OnModuleDestroy and closes its connection when the application stops, so app.stop() releases the database client rather than leaving it open for the lifetime of the process.

const app = new OneBunApplication(AppModule);
await app.start();
// ... the service holds an open client
await app.stop();
// the client is closed and the service reports no connection

Stop the application in each test suite's teardown. The lifecycle closes the client on app.stop(), so a service built by a suite that neither stops its application nor calls close() keeps its connection open into the next suite — the mechanism behind a suite failing with database "..." does not exist after an earlier suite dropped its throwaway database.

Startup Contract

A configured database is a required one. When an application configures a database — DrizzleModule.forRoot({ connection }), or DB_URL in the environment — the service checks at startup that it can actually be used, and app.start() rejects when it cannot. The HTTP server never binds, so an orchestrator sees a container that refuses to come up instead of one that passes readiness and then fails every request that touches the database.

Three failures reach app.start():

What failed	SQLite	PostgreSQL
The database cannot be opened	the file cannot be created or written — the error names the missing directory, or the permission	the connection options do not describe a server
The database does not answer	— (opening the file is the check)	a bounded SELECT 1 — the connection is otherwise lazy and nothing would touch the server until the first request
A migration failed	a migration file that exists failed to apply	same

const app = new OneBunApplication(AppModule);

await app.start();
// rejects with DrizzleStartupError when the configured database is unreachable:
//   PostgreSQL at postgresql://app:***@db:5432/orders did not answer SELECT 1 within the
//   5000ms connect timeout (waited 5001ms): Connection closed. A configured database is a
//   required one, so the application does not start without it. Set
//   `allowDegradedStart: true` in DrizzleModule.forRoot(...) (or DB_ALLOW_DEGRADED_START=true
//   on the environment path) to start anyway and accept a database that is absent,
//   unreachable or unmigrated.

The error is a DrizzleStartupError carrying stage ('open' | 'connect' | 'migrate'), target, waitedMs and timeoutMs. The password is never printed — not in the error, not in the log line that names the connection.

The connect probe is bounded. A host that accepts the connection and never answers — a dropped route, a stalled proxy — would otherwise hold start() open forever. The bound is pool.timeout (milliseconds) when the connection options carry one, and 5000 ms otherwise; the error states which applied. That is the same number the driver gets as its own connect timeout — see pool.

What does not fail. An application that configures no database at all is untouched: no connection is opened and nothing is checked. A missing migrations folder is no migrations, not a failure — migrationsFolder defaults to ./drizzle, and an application that has never generated a migration starts normally.

allowDegradedStart

One option opts out, and it means "I accept a degraded or absent database at boot" — not "skip the check". The check still runs and still reports; the failure is logged at warn and the application starts anyway.

DrizzleModule.forRoot({
  connection: { /* ... */ },
  // The database may be absent at boot: the application starts, and every request that
  // touches the database fails until it is available.
  allowDegradedStart: true,
})

On the environment path the same switch is DB_ALLOW_DEGRADED_START=true (with the configured envPrefix). Module options are code, so an application configured through forRoot() takes the option and ignores the variable.

Injection

import { Service, BaseService } from '@onebun/core';
import { DrizzleService } from '@onebun/drizzle';

@Service()
export class UserService extends BaseService {
  constructor(private db: DrizzleService) {
    super();
  }
}

Type Inference

DrizzleService automatically infers database types from table schemas. No generic parameter is required:

import { sqliteTable, integer, text } from '@onebun/drizzle/sqlite';
import { pgTable, text as pgText, integer as pgInteger } from '@onebun/drizzle/pg';

// SQLite table
const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

// PostgreSQL table
const orders = pgTable('orders', {
  id: pgInteger('id').primaryKey().generatedAlwaysAsIdentity(),
  total: pgInteger('total').notNull(),
});

@Service()
export class MyService extends BaseService {
  constructor(private db: DrizzleService) {
    super();
  }

  async getUsers() {
    // TypeScript infers SQLite types from `users` table
    return this.db.select().from(users);
  }

  async getOrders() {
    // TypeScript infers PostgreSQL types from `orders` table
    return this.db.select().from(orders);
  }
}

Query Methods

DrizzleService provides direct access to Drizzle ORM query builders:

select()

Create a SELECT query.

// Select all columns
const allUsers = await this.db.select().from(users);

// Select specific columns
const names = await this.db.select({ name: users.name, email: users.email }).from(users);

// Select with conditions
import { eq } from '@onebun/drizzle';

const user = await this.db.select()
  .from(users)
  .where(eq(users.id, id))
  .limit(1);

insert()

Create an INSERT query.

// Insert single row
await this.db.insert(users).values({ name: 'John', email: 'john@example.com' });

// Insert with returning
const [newUser] = await this.db.insert(users)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();

// Insert multiple rows
await this.db.insert(users).values([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
]);

update()

Create an UPDATE query.

import { eq } from '@onebun/drizzle';

// Update rows
await this.db.update(users)
  .set({ name: 'Jane' })
  .where(eq(users.id, id));

// Update with returning
const [updated] = await this.db.update(users)
  .set({ name: 'Jane' })
  .where(eq(users.id, id))
  .returning();

delete()

Create a DELETE query.

import { eq } from '@onebun/drizzle';

// Delete rows
await this.db.delete(users).where(eq(users.id, id));

// Delete with returning
const [deleted] = await this.db.delete(users)
  .where(eq(users.id, id))
  .returning();

transaction()

Execute queries in a transaction. The transaction callback receives a UniversalTransactionClient with the same API as DrizzleService.

async transaction<T>(
  fn: (tx: UniversalTransactionClient) => Promise<T>
): Promise<T>

const result = await this.db.transaction(async (tx) => {
  // All queries in this block are in a transaction
  // tx has the same methods as DrizzleService: select(), insert(), update(), delete()
  const user = await tx.insert(users)
    .values({ name: 'John', email: 'john@example.com' })
    .returning();

  await tx.insert(profiles)
    .values({ userId: user[0].id, bio: 'Hello' });

  return user[0];
});

The callback may await freely. If it throws, the whole transaction is rolled back and the original error reaches the caller — on both dialects:

try {
  await this.db.transaction(async (tx) => {
    await tx.insert(users).values({ name: 'John', email: 'john@example.com' });
    await someSlowCheck();                 // an await in the middle changes nothing
    throw new Error('changed my mind');
  });
} catch (error) {
  // error.message === 'changed my mind', and no user row was written
}

Anything called from inside the callback is in the transaction, on both dialects. A query issued through the service, a repository method, a call into another service — all of them run on the open transaction and are rolled back with it. Nothing has to be rewritten to take tx:

await this.db.transaction(async () => {
  await this.orders.create(order);        // repository — in the transaction
  await this.orderItems.createMany(items); // another repository — same transaction

  throw new Error('nope');                 // rolls back BOTH
});

Passing tx is optional

Code that uses the tx argument and code that uses DrizzleService directly land on the same transaction, on both dialects. Threading tx through repository methods is valid and means the same thing; it is not required for their writes to roll back.

SQLite

SQLite has a single connection, so a transaction owns the database for its whole duration:

• Rollback works across awaits. The transaction is issued as BEGIN / COMMIT | ROLLBACK, not through drizzle's synchronous bun-sqlite transaction, which would commit at the callback's first await.
• Other queries are queued, not enrolled. A query issued elsewhere in the application while the transaction is open waits for it, then runs after the COMMIT or ROLLBACK. It is never rolled back together with the transaction that was in flight.
• Two transactions are serialized. The second one waits for the first; both commit.
• A query issued through the service from inside the callback runs ON the transaction. A repository method — or any code holding DrizzleService rather than the tx argument — does not have to be rewritten to take part: it is issued on the open transaction, and is rolled back with it. Only a NESTED transaction() is refused, because it would wait for a connection its own caller is holding:

await this.db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John', email: 'john@example.com' });

  // Runs on the same transaction, and is undone with it.
  await this.userRepository.create({ name: 'Jane', email: 'jane@example.com' });

  // Throws DrizzleTransactionError (code 'SQLITE_TRANSACTION_NESTED')
  await this.db.transaction(async () => { /* ... */ });
});

Work that OUTLIVES the transaction it was started in is not routed onto it — once the transaction has ended, such a statement queues like any other bystander.

The error carries name === 'DrizzleTransactionError' and one of the codes SQLITE_TRANSACTION_NESTED or SQLITE_TRANSACTION_SYNC_QUERY — the latter for a synchronous .get(), .all(), .run() or .values() issued by a CONCURRENT caller while a transaction holds the connection, which cannot be queued because it returns rows rather than a promise.

PostgreSQL

The transaction runs on its own pooled connection through drizzle's own transaction(). Nothing is queued and nothing is refused — concurrent queries use other connections, and the re-entrancy errors above cannot occur.

• A query issued through the service or a repository from inside the callback runs ON the transaction, exactly as on SQLite, and is rolled back with it.
• A nested transaction() is a SAVEPOINT. It runs on the connection the outer one already holds, so it sees the outer's uncommitted rows, an inner rollback keeps the outer work, and an outer rollback undoes everything. It takes no second connection and begins no independent transaction, so it cannot block on the locks its own caller holds.
• Promise.all inside the callback is safe, and is not parallel. One connection runs one statement at a time; the driver queues them rather than failing.
• Work that OUTLIVES the transaction goes back to the pool. A statement issued after the callback has returned is not put on the finished transaction — it would land on whatever connection the pool has since handed that transaction's slot to, and be rolled back by somebody else's failure.
• Concurrent transactions are independent. Routing is keyed by async context, so two overlapping callbacks each see only their own uncommitted rows, one rollback never touches the other's writes, and a query issued outside any transaction goes to the pool even while one is open. Two DrizzleService instances never see each other's transactions either, including against the same database.

BaseRepository

For common CRUD operations:

import { BaseRepository, DrizzleService, eq } from '@onebun/drizzle';
import { users, type User, type InsertUser } from './schema';

export class UserRepository extends BaseRepository<typeof users> {
  constructor(db: DrizzleService) {
    super(db, users);
  }

  // Inherited methods — ONE type argument, the table; the row and insert types are derived
  // from it, so `User` and `InsertUser` are not passed in:
  // findAll(): Promise<User[]>                    // the whole table — no pagination
  // findById(id: unknown): Promise<User | null>
  // create(data: Partial<InsertUser>): Promise<User>
  // update(id: unknown, data: Partial<InsertUser>): Promise<User | null>
  // delete(id: unknown): Promise<boolean>
  // count(): Promise<number>                      // findAll().length, so O(rows) in memory
  // transaction<R>(cb: (tx: UniversalTransactionClient) => Promise<R>): Promise<R>

  // Custom methods go through the inherited `drizzleService`, NOT `this.db`: `this.db` is the
  // raw dialect union, whose `.from()` has no callable signature.
  async findByEmail(email: string): Promise<User | null> {
    const result = await this.drizzleService.select()
      .from(users)
      .where(eq(users.email, email))
      .limit(1);

    return result[0] ?? null;
  }
}

findAll() takes no arguments and returns the entire table; count() loads it to measure it. For a page, or a count that does not read every row, query through DrizzleService instead — Basic Queries shows both the .limit() / .offset() form and a count(*) projection.

A repository is not a provider. BaseRepository resolves the database in its constructor, and every provider is constructed before DrizzleService has opened one — so a repository carrying @Service() fails to construct with Database not initialized. Call initialize() first. If anything injects it, app.start() then rejects with a DependencyResolutionError naming the repository as the dependency its consumer could not get, because constructing it threw — the original error is in the log as Failed to create service <Repository>. If nothing injects it, the application boots with the repository silently absent from DI. Construct it after the database is up instead — lazily on first use, or in onApplicationInit() — from a service that holds DrizzleService.

@Service()
export class UserService extends BaseService {
  private repository: UserRepository | null = null;

  constructor(private db: DrizzleService) { super(); }

  private repo(): UserRepository {
    this.repository ??= new UserRepository(this.db);

    return this.repository;
  }

  async byEmail(email: string): Promise<User | null> {
    return await this.repo().findByEmail(email);
  }
}

Query Examples

Dialect Resolution

select(), selectDistinct(), insert(), update() and delete() accept a table of either dialect and resolve to that dialect's own query builder. On PostgreSQL that means the whole chain is typed and reachable:

await this.db.select().from(runs).where(eq(runs.id, id)).limit(1);
await this.db.select({ id: outbox.id }).from(outbox).limit(10).for('update', { skipLocked: true });
await this.db.update(runs).set({ status: 'running' }).where(eq(runs.id, id)).returning({ id: runs.id });

.limit(), .offset(), .orderBy(), .for(), .$dynamic() and a projected .returning(fields) are all available on the PostgreSQL path, and SQLite tables resolve to the SQLite builders.

For anything the universal surface does not model — a PostgreSQL-only feature, or a raw sql construction against the typed schema — getPostgreSQLDatabase() and getSQLiteDatabase() return the underlying dialect-specific drizzle instance. Both throw if the configured database is of the other type.

const pg = this.db.getPostgreSQLDatabase();
await pg.execute(sql`REFRESH MATERIALIZED VIEW ${sql.identifier('run_stats')}`);

Basic Queries

// Select specific columns
const names = await this.db.select({ name: users.name, email: users.email }).from(users);

// Count
import { sql } from '@onebun/drizzle';
const countResult = await this.db.select({ count: sql`count(*)` }).from(users);

// Order and limit
import { desc } from '@onebun/drizzle';
const recentUsers = await this.db.select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10);

// Pagination
const page = 1;
const pageSize = 10;
const offset = (page - 1) * pageSize;

const pagedUsers = await this.db.select()
  .from(users)
  .limit(pageSize)
  .offset(offset);

Filtering

import { eq, ne, gt, gte, lt, lte, like, ilike, and, or, not, isNull, isNotNull, inArray, notInArray } from '@onebun/drizzle';

// Equal
const user = await this.db.select().from(users).where(eq(users.id, '123'));

// Multiple conditions (AND)
const admins = await this.db.select().from(users).where(
  and(
    eq(users.role, 'admin'),
    eq(users.active, true)
  )
);

// OR conditions
const filtered = await this.db.select().from(users).where(
  or(
    eq(users.role, 'admin'),
    eq(users.role, 'moderator')
  )
);

// LIKE
const searchResults = await this.db.select().from(users).where(
  like(users.name, '%john%')
);

// IN array
const specific = await this.db.select().from(users).where(
  inArray(users.id, ['1', '2', '3'])
);

// NULL checks
const noAge = await this.db.select().from(users).where(
  isNull(users.age)
);

Joins

// Inner join
const postsWithAuthors = await this.db.select()
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// Left join
const usersWithPosts = await this.db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Aggregations

import { sql, count, sum, avg, min, max } from '@onebun/drizzle';

// Count
const total = await this.db.select({ count: count() }).from(users);

// Group by
const postsByUser = await this.db.select({
  authorId: posts.authorId,
  postCount: count(),
})
  .from(posts)
  .groupBy(posts.authorId);

// Sum
const totalSales = await this.db.select({
  total: sum(orders.amount),
}).from(orders);

Migrations

OneBun uses Drizzle ORM migrations. Typical workflow:

1. Generate migrations - Create SQL files from schema changes (CLI)
2. Apply migrations - Run migrations on app startup (automatic or manual)

Generate Migrations (CLI)

Create a drizzle.config.ts in your project root:

// drizzle.config.ts
import { defineConfig } from '@onebun/drizzle';

export default defineConfig({
  schema: './src/schema/index.ts',
  out: './drizzle',
  dialect: 'postgresql', // or 'sqlite'
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Then run from terminal using onebun-drizzle CLI wrapper (ensures correct version):

# Generate migration after schema changes
bunx onebun-drizzle generate

# Push schema directly to DB (development only, no migration files)
bunx onebun-drizzle push

# Open Drizzle Studio to browse database
bunx onebun-drizzle studio

Add scripts to package.json:

{
  "scripts": {
    "db:generate": "onebun-drizzle generate",
    "db:push": "onebun-drizzle push",
    "db:studio": "onebun-drizzle studio"
  }
}

Note: Use onebun-drizzle instead of drizzle-kit directly. This ensures the correct version of drizzle-kit is used (the one installed with @onebun/drizzle).

Programmatic Generation (Optional)

For build scripts or CI pipelines, use programmatic API:

import { generateMigrations, pushSchema } from '@onebun/drizzle';

// Generate migration files
await generateMigrations({
  schemaPath: './src/schema',
  migrationsFolder: './drizzle',
  dialect: 'postgresql',
});

// Push schema directly (development only)
await pushSchema({
  schemaPath: './src/schema',
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
});

Apply Migrations at Runtime

Use DrizzleService.runMigrations() to apply migrations when the application starts:

// Manual migration
const drizzleService = new DrizzleService();
await drizzleService.initialize({ /* connection options */ });
await drizzleService.runMigrations({ migrationsFolder: './drizzle' });

A manual runMigrations() requires the folder to exist: against one with no meta/_journal.json it throws drizzle-orm's Error: Can't find meta/_journal.json file — a plain Error, not a DrizzleStartupError, so it carries none of the folder/target context the Startup Contract promises. Guard the call, or let autoMigrate run it. The same applies to the two calls under One Journal Per Migration Set.

Or enable automatic migrations in module configuration:

DrizzleModule.forRoot({
  connection: { /* ... */ },
  // autoMigrate defaults to true — migrations run on startup automatically
  migrationsFolder: './drizzle',  // Migration files location
})

A migration that fails fails the boot. app.start() rejects with a DrizzleStartupError naming the folder and the SQL error, and the HTTP server never binds — a half-applied schema is not a state to serve traffic in. On the startup path, a migrations folder that does not exist is no migrations and is not a failure: ./drizzle is the default, an application that has never generated one boots normally, and the skipped step is logged — No migrations to run: "drizzle/meta/_journal.json" does not exist, at debug for the default folder and at warn for one you configured explicitly. That exemption is the startup path's alone — the manual runMigrations() above throws.

The database is checked for reachability whether or not migrations run, so autoMigrate: false — the recommendation for production, where migrations are a deploy step — still refuses to start against a database that is not there. See Startup Contract, including the allowDegradedStart opt-out.

One Journal Per Migration Set

A package that ships migrations of its own needs its own journal table:

// The application's own set — default journal
await drizzleService.runMigrations({ migrationsFolder: './drizzle' });

// A package's set — its own journal, independent of the application's
await drizzleService.runMigrations({
  migrationsFolder: './node_modules/@acme/durable/migrations',
  migrationsTable: '__drizzle_migrations_durable',
});

Why this is not optional. Drizzle decides whether to apply a migration by comparing the timestamp baked into its folder against the newest row in the journal — never by hash. So when two folders share one journal, whichever set was generated earlier is skipped entirely: no error, no log, and the application starts and then fails at the first query against a table that was never created.

To make that impossible to hit by accident, a second migration folder that would share a journal with the first is refused at the point of the call, naming both folders and the option that separates them. Several journals in one process is the supported shape; several folders sharing one journal is not.

migrationsSchema names the schema holding the journal (PostgreSQL only — SQLite has no schemas and the option is ignored there). Both default to drizzle's own drizzle.__drizzle_migrations.

Migrations whose entries end up neither applied nor already recorded are reported with a warn naming each file, so a skipped set is visible even where it is legitimate.

Environment Variables

Variable	Description	Default
DB_TYPE	sqlite or postgresql	sqlite
DB_URL	Connection URL — a file path for SQLite, a full postgresql://… for PostgreSQL	— (none: unset means no database)
DB_AUTO_MIGRATE	Auto-run migrations on startup	true
DB_MIGRATIONS_FOLDER	Path to migrations folder	'./drizzle'
DB_MIGRATIONS_TABLE	Journal table recording applied migrations	'__drizzle_migrations'
DB_MIGRATIONS_SCHEMA	Schema holding the journal (PostgreSQL only)	'drizzle'
DB_SCHEMA_PATH	Path to schema files	-
DB_ALLOW_DEGRADED_START	Start even when the configured database cannot be reached (Startup Contract)	false

migrationsTable and migrationsSchema are also accepted by DrizzleModule.forRoot() and are forwarded on every path that runs migrations, including automatic ones.

DB_TYPE and DB_URL are the environment equivalent of connection in DrizzleModule.forRoot(): set them and the service initializes itself with no module configuration at all. On PostgreSQL the URL reaches the driver untouched, so its query parameters are preserved.

DB_URL has no default. Unset, empty or whitespace-only is not configured rather than :memory:: no connection is opened, app.start() succeeds, and every getDatabase() throws Database not initialized. Call initialize() first. — see Startup Contract. The only :memory: fallback is the drizzle-kit config that pushSchema() generates.

Migration Tracking

Drizzle automatically tracks applied migrations in the journal table. This ensures:

• Migrations are only applied once (idempotency)
• Running runMigrations() multiple times is safe
• No duplicate table creation errors

Migration Logging

When migrations are applied, the service logs each migration filename:

info: Applied migration: 0001_initial_schema
info: Applied migration: 0002_add_users_table
info: SQLite migrations applied { migrationsFolder: './drizzle', newMigrations: 2, appliedFiles: ['0001_initial_schema', '0002_add_users_table'] }

If no new migrations need to be applied:

info: SQLite migrations applied { migrationsFolder: './drizzle', newMigrations: 0, appliedFiles: [] }

Complete Example

// schema/index.ts
import { pgTable, text, timestamp, integer } from '@onebun/drizzle/pg';
import { relations } from '@onebun/drizzle';

export const users = pgTable('users', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
});

export const posts = pgTable('posts', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').notNull().references(() => users.id),
  views: integer('views').default(0),
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type InsertPost = typeof posts.$inferInsert;

// user.repository.ts
import { Service, BaseService } from '@onebun/core';
import { DrizzleService, eq } from '@onebun/drizzle';
import { users, type User, type InsertUser } from './schema';

@Service()
export class UserRepository extends BaseService {
  constructor(private db: DrizzleService) {
    super();
  }

  async findAll(): Promise<User[]> {
    return this.db.select().from(users);
  }

  async findById(id: number): Promise<User | null> {
    const result = await this.db.select()
      .from(users)
      .where(eq(users.id, id))
      .limit(1);
    return result[0] || null;
  }

  async findByEmail(email: string): Promise<User | null> {
    const result = await this.db.select()
      .from(users)
      .where(eq(users.email, email))
      .limit(1);
    return result[0] || null;
  }

  async create(data: InsertUser): Promise<User> {
    const result = await this.db.insert(users).values(data).returning();
    return result[0];
  }

  async update(id: number, data: Partial<InsertUser>): Promise<User | null> {
    const result = await this.db.update(users)
      .set(data)
      .where(eq(users.id, id))
      .returning();
    return result[0] || null;
  }

  async delete(id: number): Promise<boolean> {
    const result = await this.db.delete(users)
      .where(eq(users.id, id))
      .returning();
    return result.length > 0;
  }
}

// user.service.ts
@Service()
export class UserService extends BaseService {
  constructor(
    private userRepository: UserRepository,
    private cacheService: CacheService,
  ) {
    super();
  }

  async findById(id: number): Promise<User | null> {
    const cacheKey = `user:${id}`;
    const cached = await this.cacheService.get<User>(cacheKey);
    if (cached) return cached;

    const user = await this.userRepository.findById(id);
    if (user) {
      await this.cacheService.set(cacheKey, user, { ttl: 300_000 }); // 5 minutes
    }

    return user;
  }

  async create(data: InsertUser): Promise<User> {
    // Check for duplicate email
    const existing = await this.userRepository.findByEmail(data.email);
    if (existing) {
      throw new Error('Email already exists');
    }

    return this.userRepository.create(data);
  }
}

// user.module.ts
import { Module } from '@onebun/core';
import { DrizzleModule, DatabaseType } from '@onebun/drizzle';
import { CacheModule, CacheType } from '@onebun/cache';

@Module({
  imports: [
    DrizzleModule.forRoot({
      connection: {
        type: DatabaseType.POSTGRESQL,
        options: {
          connectionString: config.get('database.url'),
        },
      },
      migrationsFolder: './drizzle',
    }),
    CacheModule.forRoot({ type: CacheType.MEMORY, cacheOptions: { defaultTtl: 300000 } }),
  ],
  controllers: [UserController],
  providers: [UserService, UserRepository],
})
export class UserModule {}

Testing

Testing with DrizzleService

For testing services that depend on DrizzleService, use in-memory SQLite database:

import { describe, test, expect, beforeEach, afterEach } from 'bun:test';
import { Effect } from 'effect';
import { makeMockLoggerLayer, createMockConfig } from '@onebun/core/testing';
import { LoggerService } from '@onebun/logger';
import { DrizzleService, DrizzleModule, DatabaseType } from '@onebun/drizzle';

describe('MyService', () => {
  let drizzleService: DrizzleService;

  beforeEach(async () => {
    // Clear any previous configuration
    DrizzleModule.clearOptions();

    // Configure with in-memory database
    // Note: autoMigrate defaults to true; with no migrations folder it is a no-op
    DrizzleModule.forRoot({
      connection: {
        type: DatabaseType.SQLITE,
        options: { url: ':memory:' },
      },
      autoMigrate: false, // optional — this test creates its tables itself
    });

    // Create and initialize service
    const loggerLayer = makeMockLoggerLayer();
    const logger = Effect.runSync(
      Effect.provide(
        Effect.map(LoggerService, (l) => l),
        loggerLayer,
      ),
    );

    // No generic parameter needed - types are inferred from table schemas
    drizzleService = new DrizzleService();
    drizzleService.initializeService(logger, createMockConfig());
    // onModuleInit() is called automatically by the framework
    // In tests, call it manually to simulate framework behavior
    await drizzleService.onModuleInit();

    // Create test tables manually (this test ships no migrations)
    const sqliteClient = drizzleService.getSQLiteClient();
    sqliteClient!.exec(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL
      )
    `);
  });

  afterEach(async () => {
    await drizzleService.close();
    DrizzleModule.clearOptions();
  });

  test('should perform database operations', async () => {
    const db = drizzleService.getDatabase();
    expect(db).toBeDefined();
  });
});

Testing with Auto-migrations

Migrations run automatically by default. To test with migrations:

import { join } from 'path';

beforeEach(async () => {
  DrizzleModule.forRoot({
    connection: {
      type: DatabaseType.SQLITE,
      options: { url: ':memory:' },
    },
    // autoMigrate defaults to true, so migrations run automatically
    migrationsFolder: join(__dirname, 'test-migrations'),
  });

  // ... create and initialize service
  await drizzleService.onModuleInit();

  // Tables from migrations should now exist
  const sqliteClient = drizzleService.getSQLiteClient();
  const tables = sqliteClient!.query(`
    SELECT name FROM sqlite_master WHERE type='table' AND name='users'
  `).all();
  expect(tables.length).toBe(1);
});

Testing Environment Variables

The service auto-initializes from environment variables. For testing:

beforeEach(async () => {
  // Clear previous state
  DrizzleModule.clearOptions();
  delete process.env.DB_URL;
  delete process.env.DB_TYPE;
  delete process.env.DB_AUTO_MIGRATE;

  // Set test environment
  process.env.DB_URL = ':memory:';
  process.env.DB_TYPE = 'sqlite';
  process.env.DB_AUTO_MIGRATE = 'false'; // optional: skips the migration step (a missing folder is already a no-op)

  // Create service - will auto-initialize from env vars
  // No generic parameter needed
  drizzleService = new DrizzleService();
  drizzleService.initializeService(logger, createMockConfig());
  await drizzleService.onModuleInit();
});

afterEach(() => {
  // Cleanup
  delete process.env.DB_URL;
  delete process.env.DB_TYPE;
  delete process.env.DB_AUTO_MIGRATE;
});

Key Testing Notes

1. Call onModuleInit() in tests - this triggers async initialization that the framework does automatically
2. Use DrizzleModule.clearOptions() in beforeEach/afterEach to ensure test isolation
3. Clean up environment variables when testing env-based initialization
4. Use :memory: SQLite URL for in-memory databases that are faster and don't leave files
5. autoMigrate defaults to true - a missing migrations folder is no migrations, not a failure, so leaving it on is safe; false skips only the migration step, not the reachability check (see Startup Contract)
6. Database is ready after onModuleInit() - no need to call waitForInit() in client code
7. No generic parameter needed - DrizzleService infers types from table schemas automatically