Database

@voltage/database wraps TypeORM for NestJS with a composable query DSL, soft-delete base classes, fixture bundles for seed data, and a CLI for migration and schema management. The central piece is QueryHelper — a single injectable service that works across all entities and combines TypeORM’s Repository API with the built-in query operators.

Installation

yarn add @voltage/database @voltage/cli @voltage/event-manager @voltage/logger @voltage/zod zod

Basic usage

forRoot()

Call forRoot() once in your root module with your PostgreSQL connection details:

import { DatabaseModule } from '@voltage/database';

@Module({
  imports: [
    DatabaseModule.forRoot({
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      username: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      migrations: ['dist/migrations/*.js'],
    }),
  ],
})
export class AppModule {}

forRootAsync()

Use forRootAsync() when connection details come from another provider:

DatabaseModule.forRootAsync({
  inject: [AppConfiguration],
  useFactory: (config: AppConfiguration) => ({
    host: config.database.host,
    port: config.database.port,
    username: config.database.username,
    password: config.database.password,
    database: config.database.name,
    migrations: ['dist/migrations/*.js'],
  }),
});

forFeature()

Register entities in the module that owns them. This is the standard TypeORM pattern and makes entities available for query building:

import { DatabaseModule } from '@voltage/database';
import { User } from './user.entity';

@Module({
  imports: [DatabaseModule.forFeature([User])],
})
export class UserModule {}

DatabaseModule.forFeature is an alias for TypeOrmModule.forFeature — they are interchangeable.

Entities

Base classes

Extend Record for any entity that needs a UUID primary key and timestamps. Extend SoftRecord when rows should be soft-deleted instead of removed:

import { Column, Entity, ManyToOne } from '@voltage/database';
import { SoftRecord } from '@voltage/database';

@Entity()
export class Subscription extends SoftRecord {
  @ManyToOne(() => User)
  user: User;

  @Column()
  tierName: string;

  @Column({ type: 'timestamptz' })
  expiresAt: Date;
}

Record provides id (UUID), createdAt, and updatedAt. SoftRecord adds deletedAt — TypeORM filters soft-deleted rows from all queries automatically.

Note

To include soft-deleted rows in a query, call withDeleted() on the raw query builder: this.query.for(User).getQueryBuilder(...).withDeleted().

Decimal columns

Use @DecimalColumn() for monetary or precision-sensitive values. The column stores the value as decimal in PostgreSQL and maps it to a Decimal instance (from decimal.js) in TypeScript — avoiding floating-point rounding errors:

import { DecimalColumn } from '@voltage/database';
import Decimal from 'decimal.js';

@Entity()
export class Price extends Record {
  @DecimalColumn({ precision: 12, scale: 2 })
  amount: Decimal;
}

The default precision is 10, scale 2.

Querying

QueryHelper is the preferred way to query the database. Inject it once per service — it works across all entities, so you never need a per-entity @InjectRepository(). query.for(Entity) returns a proxy that exposes the full TypeORM Repository<Entity> API — find, findOne, count, save, remove, createQueryBuilder, and everything else — alongside the additional DSL methods documented below. If you know TypeORM, everything you already know still works.

import { QueryHelper } from '@voltage/database';

@Injectable()
export class UserService {
  constructor(private readonly query: QueryHelper) {}
}

Reading data

The DSL read methods all accept operator arrays:

Note

All operator arguments — predicates passed to where(), and operators passed directly to getMany(), getManyAndCount(), etc. — accept falsy values (false, null, undefined). Falsy entries are silently skipped. This applies inside and() and or() as well. The pattern makes conditional filters concise without needing if branches.

// Single record — throws NotFoundException if not found
const user = await this.query.for(User).getOneOrFail(where(id(userId)));

// Single record — returns null if not found
const user = await this.query.for(User).getOne(where(equals('email', email)));

// Many records
const users = await this.query.for(User).getMany(where(equals('locale', 'de')));

// Many records with total count (for pagination)
const [subscriptions, total] = await this.query
  .for(Subscription)
  .getManyAndCount(where(equals('userId', user.id)), paginate({ page: 1, size: 20 }));

// Existence check
const taken = await this.query.for(User).getExists(where(equals('email', email)));

Writing data

The proxy passes through all Repository mutation methods directly:

// Create and persist in one step
const subscription = await this.query
  .for(Subscription)
  .save(this.query.for(Subscription).create({ user, tier, expiresAt }));

// Update specific fields
await this.query.for(Quota).update(quota.id, { pending: quota.pending + 1 });

// Soft delete by entity instance
await this.query.for(Subscription).softRemove(subscription);

// Soft delete by ID
await this.query.for(Export).softDelete(row.id);

Predicates

Predicates are functions that build a SQL condition. Pass one or more to where() to filter queries:

import { where, equals, id, ids, contains, empty, not } from '@voltage/database';

// Match by primary key
where(id(userId));

// Match any of a set of IDs
where(ids([id1, id2, id3]));

// Column equality
where(equals('status', 'active'));

// Column IN (...)
where(contains('role', ['admin', 'moderator']));

// Column IS NULL
where(empty('deletedAt'));

// Negate any predicate
where(not(empty('verifiedAt')));

Comparison predicates for numeric and date columns:

import { lessThan, lessThenOrEqual, moreThan, moreThanOrEqual, between } from '@voltage/database';

where(moreThanOrEqual('expiresAt', new Date()));
where(lessThan('score', 100));
where(between('createdAt', start, end));

Composing predicates

and() and or() combine predicates into a single condition. Both accept falsy values — false, null, undefined — which are silently skipped. This makes conditional filters clean without if branches:

import { and, or, where } from '@voltage/database';

// Both conditions must hold
where(and(equals('locale', 'de'), moreThanOrEqual('expiresAt', new Date())));

// Either condition must hold
where(or(equals('role', 'admin'), equals('role', 'moderator')));

// Falsy values are ignored — useful for optional filters
where(and(filter.locale && equals('locale', filter.locale), filter.active && moreThanOrEqual('expiresAt', new Date())));

Custom predicates

Extract reusable conditions into typed predicate functions. This keeps queries readable and the logic testable in isolation:

import { and, moreThanOrEqual, lessThenOrEqual, Predicate } from '@voltage/database';

export function isActive(): Predicate<Subscription> {
  return moreThanOrEqual('expiresAt', new Date());
}

export function belongsToUser(user: User): Predicate<Subscription> {
  return equals('userId', user.id);
}

export function isActiveForUser(user: User): Predicate<Subscription> {
  return and(belongsToUser(user), isActive());
}

// Usage
const subscription = await this.query.for(Subscription).getOneOrFail(where(isActiveForUser(user)));

When the built-in predicates are not expressive enough, write the SQL directly. A Predicate<T> is a function that receives the query alias and returns a [sql, params] tuple:

export function hasAvailableQuota(amount: number): Predicate<Quota> {
  return (alias) => [`${alias}.available - ${alias}.pending >= :amount`, { amount }];
}

To accept Value<T> in a custom predicate — so callers can pass a literal, a self() reference, or a raw() expression — use resolveValue():

import { Predicate, Value, resolveValue } from '@voltage/database';

export function scoreAbove(threshold: Value<number>): Predicate<Player> {
  return (alias) => {
    const col = `${alias}."score"`;
    const [valueSql, params] = resolveValue('scoreAbove', alias, col, threshold);

    return [`${col} >= ${valueSql}`, params];
  };
}

// literal
where(scoreAbove(100))
// compare against another column in the same scope
where(scoreAbove(self('highScore')))
// raw SQL expression
where(scoreAbove(raw((col) => `${col} * 0.9`)))

Cross-alias references

Inside using(), predicates are scoped to the joined alias. To compare against a column on a different alias — the root entity or another join — use self(), root<T>(), or ref():

import { self, root, ref, using, where, equals } from '@voltage/database';

// self() — current scoped alias (useful in ON predicates on join itself)
join('orders', where(equals('userId', self('id'))))

// root<T>() — root entity alias; pass the root type explicitly since it can't be inferred
using('messages', where(equals('ownerId', root<User>('id'))))

// ref() — typed reference via JoinRelation descriptor
const orders = relation(User, 'orders');
using('messages', where(equals('orderId', ref(orders, 'id'))))

root<T>() takes an explicit generic because the root entity type is not available at the call site — the alias is resolved at runtime.

Pagination and ordering

paginate() applies skip and take from a { page, size } object. orderBy() wraps TypeORM’s ordering with the entity alias pre-applied:

import { paginate, orderBy, whereQuickFilter } from '@voltage/database';

const [users, total] = await this.query
  .for(User)
  .getManyAndCount(paginate({ page, size }), orderBy('createdAt', 'DESC'));

orderBy(), addOrderBy(), groupBy(), addGroupBy(), select(), and addSelect() all accept a raw() expression instead of a property name. The expression receives the current alias when passed as a generator:

import { orderBy, groupBy, addSelect, raw } from '@voltage/database';

// order by a computed expression
orderBy(raw((col) => `${col} + :bonus`, { bonus: 10 }), 'DESC')

// group by a date truncation
groupBy(raw(`DATE_TRUNC('day', entity."createdAt")`))

// select a computed column with an alias
addSelect(raw('ST_Distance(entity.location, :pt)', { pt: point }), 'distance')

All of these scope correctly inside using() — the generator receives the joined alias, not the root alias.

whereQuickFilter() adds a case-insensitive ILIKE search across multiple columns — useful for quick search inputs:

const [users, total] = await this.query
  .for(User)
  .getManyAndCount(
    paginate({ page, size }),
    search && whereQuickFilter(search, ['entity.firstName', 'entity.lastName', 'entity.email']),
  );

Relations

Load relations with relations(). Pass the same shape as TypeORM’s FindOptionsRelations:

Caution

relations() maps to setFindOptions({ relations }) internally. Calling it more than once per query silently overwrites the previous call — only the last one takes effect. Declare all relations in a single relations() call.

import { relations, where, id } from '@voltage/database';

const order = await this.query.for(Order).getOneOrFail(where(id(orderId)), relations({ items: true, customer: true }));

Joins

All four join operators — join (LEFT JOIN), innerJoin (INNER JOIN), joinAndSelect, and innerJoinAndSelect — accept a property name string or a relation() descriptor. Pass the result to using() to scope filters and ordering to the joined entity’s columns:

import { join, joinAndSelect, innerJoin, innerJoinAndSelect, using, where, equals, orderBy } from '@voltage/database';

// LEFT JOIN + select
const users = await this.query.for(User).getMany(
  joinAndSelect('avatar'),
  where(equals('locale', 'en')),
);

// INNER JOIN without select, filter on joined entity
const users = await this.query.for(User).getMany(
  innerJoin('address'),
  using('address', where(equals('country', 'DE'))),
);

// JOIN with ON predicate — filter rows included in the join itself
const users = await this.query.for(User).getMany(
  join('orders', where(equals('status', 'active'))),
);

Pass using() inside another using() to traverse deeper relations. Operators inside each using() are scoped to that level’s alias — orderBy and groupBy prefix the column with the joined alias automatically:

// User → orders → messages, with scoped ordering
const users = await this.query.for(User).getMany(
  joinAndSelect('orders'),
  using('orders',
    joinAndSelect('messages'),
    orderBy('createdAt', 'DESC'),
    using('messages', where(equals('read', false))),
  ),
);

Use relation() when you need ON predicates on the join itself, or want to define the descriptor upfront and reuse it:

import { relation, join, joinAndSelect, using, where, equals } from '@voltage/database';

const orders = relation(User, 'orders', where(equals('status', 'active')));

// same descriptor, different operator depending on context
const withSelect = this.query.for(User).getMany(joinAndSelect(orders));
const withoutSelect = this.query.for(User).getMany(
  join(orders),
  using(orders, where(equals('total', 0))),
);

relation() takes the source entity — the entity that owns the relation property — followed by the property name (which doubles as the alias), and optional ON predicates. It works for nested using() too:

const orders = relation(User, 'orders');
const messages = relation(Order, 'messages', where(equals('read', false)));

const users = await this.query.for(User).getMany(
  join(orders),
  using(orders,
    joinAndSelect(messages),
    orderBy('createdAt', 'DESC'),
    using(messages, where(equals('flagged', true))),
  ),
);

Raw query builder

When the DSL operators are not enough, call getQueryBuilder() to get the underlying SelectQueryBuilder and use TypeORM directly. A common case is streaming large result sets:

const stream = await this.query.for(Export).getQueryBuilder(where(shouldBeDeleted())).stream();

try {
  for await (const row of stream) {
    await this.query.for(Export).softDelete(row.entity_id);
  }
} finally {
  stream.destroy();
}

Transactions

Call query.transaction() with a callback. The callback receives a scoped QueryHelper bound to the transaction’s EntityManager — use it for all queries inside the transaction:

Caution

Use the query argument inside the callback, not this.query. this.query is not bound to the transaction’s EntityManager, so any writes through it will bypass the transaction entirely.

await this.query.transaction(async (query) => {
  const user = await query.for(User).save(query.for(User).create(data));
  await query.for(Profile).save(query.for(Profile).create({ user }));
});

Pass an isolation level as the first argument when needed:

await this.query.transaction('SERIALIZABLE', async (query) => {
  const quota = await query.for(Quota).getOneOrFail(where(id(quotaId)));
  await query.for(Quota).update(quota.id, { pending: quota.pending + 1 });
});

Loading entities from route params

LoadRecordPipe is a pipe factory that loads an entity by the route parameter value. By default it looks up by id:

import { LoadRecordPipe } from '@voltage/database';

@Get(':id')
async view(@Param('id', LoadRecordPipe(User)) user: User) {
  return user;
}

Pass a callback to customise the lookup — for example, to load by a field other than id or to eager-load relations:

@Get(':slug')
async view(
  @Param('slug', LoadRecordPipe(Post, (slug, repo) =>
    repo.createQueryBuilder('post')
      .where('post.slug = :slug', { slug })
      .leftJoinAndSelect('post.author', 'author')
      .getOneOrFail()
  )) post: Post,
) {
  return post;
}

LoadRecordPipe throws a NotFoundException automatically when no record is found.

Fixtures

Fixtures are classes that produce seed data for development and testing environments. Decorate a class with @Bundle() and implement Installable to register it as a named bundle:

Caution

Bundle classes must be decorated with @Injectable() and registered as providers in a module. Without this, the discovery service won’t find them and they’ll be silently skipped during installation.

import { Bundle, fixture, Installable } from '@voltage/database';

@Bundle('demo')
@Injectable()
export class DemoBundle implements Installable {
  constructor(private readonly query: QueryHelper) {}

  users = {
    alice: fixture(User, {
      firstName: 'Alice',
      email: 'alice@example.com',
      locale: 'en',
    }),
    bob: fixture(User, {
      firstName: 'Bob',
      email: 'bob@example.com',
      locale: 'de',
    }),
  } as const;

  collect() {
    return [this.users];
  }
}

fixture() creates a typed entity stub. The collect() method returns all the data to install — plain objects, arrays, or nested fixture maps. The installer flattens and persists them in dependency order.

Referencing another bundle

Inject BundleResolver to access the collected fixtures of another bundle. Call resolver.resolve() inside collect() to build fixtures that reference entities from another bundle, or in AfterInstallBundleEvent to run logic that requires persisted IDs:

import { Bundle, BundleResolver, fixture, Installable } from '@voltage/database';

@Bundle('subscriptions')
@Injectable()
export class SubscriptionBundle implements Installable {
  constructor(private readonly resolver: BundleResolver) {}

  async collect() {
    const { users } = await this.resolver.resolve(UserBundle);

    return [
      fixture(Subscription, { user: users.alice, tierName: 'pro' }),
      fixture(Subscription, { user: users.bob, tierName: 'basic' }),
    ];
  }
}

resolver.resolve() returns the typed return value of the target bundle’s collect() method. The resolved bundle is collected at most once — repeated calls return the cached result.

Reacting to installation

Listen to AfterInstallBundleEvent to run logic after a bundle is installed — for example, creating related records that require persisted IDs:

import { AfterInstallBundleEvent } from '@voltage/database';
import { OnEvent } from '@voltage/event-manager';

@Bundle('demo')
@Injectable()
export class DemoBundle implements Installable {
  constructor(private readonly query: QueryHelper, private readonly subscriptions: SubscriptionService) {}

  users = {
    alice: fixture(User, { email: 'alice@example.com', locale: 'en' }),
  } as const;

  collect() {
    return [this.users];
  }

  @OnEvent(AfterInstallBundleEvent)
  protected async afterInstall(event: AfterInstallBundleEvent) {
    if (event.bundle === DemoBundle) {
      await this.subscriptions.create(this.users.alice, 'pro');
    }
  }
}

Automatic installation at startup

Set the install option in forRoot() to run specific bundles on every startup — useful in development environments to seed a fresh database:

DatabaseModule.forRoot({
  // ... connection config
  install: {
    bundles: ['demo'],
    synchronize: 'migrate', // drop and re-run migrations before installing
  },
});

synchronize can be 'migrate' (drop + migrate), 'synchronize' (drop + schema sync), or 'none' (skip schema changes).

Caution

synchronize: 'migrate' drops the entire database before reinstalling. Never set this in production.

CLI

The database CLI commands are available through @voltage/cli. Run them from the project root:

# Interactive fixture installation
yarn cli database install

# Generate a migration from entity changes
yarn cli database migration generate --name CreateUserTable

# Run pending migrations
yarn cli database migration run

# Revert the last migration
yarn cli database migration revert

# Show migration status
yarn cli database migration show

# Sync schema without migrations (development only)
yarn cli database schema sync

# Drop the entire schema
yarn cli database schema drop

Caution

database schema drop deletes all tables. It has no confirmation prompt.

Configuration

interface DatabaseConfiguration {
  /** PostgreSQL host. */
  host: string;
  /** PostgreSQL port. Defaults to 5432. */
  port?: number;
  /** Database username. */
  username: string;
  /** Database password. */
  password: string;
  /** Database name. */
  database: string;
  /** Glob patterns pointing to compiled migration files. */
  migrations: string[];
  /** Run pending migrations automatically on startup. Defaults to false. */
  migrationsRun?: boolean;
  /** Connection pool size. */
  poolSize?: number;
  /** Additional driver-specific options passed to the underlying pg client. */
  extra?: Record<string, unknown>;
  /** Query result cache configuration passed to TypeORM. */
  cache?: Record<string, unknown>;
  /** Automatic fixture installation on startup. Omit to disable. */
  install?: {
    /** Bundle names to install. */
    bundles: string[];
    /**
     * Schema operation to run before installation.
     * - 'migrate': drop database and run migrations
     * - 'synchronize': drop database and sync schema from entities
     * - 'none': skip schema changes
     */
    synchronize?: 'migrate' | 'synchronize' | 'none';
    /** Named data source to use. Defaults to 'default'. */
    connection?: string;
  };
}