NestJS ORM: a kastor module, provider and typed services

Project structure

A NestJS app gets one database module, imported once in the root module. Everything else injects the client from it.

Project

src/
  db/schema.ts            tables and relations
  db/db.module.ts         KASTOR token, provider, global module
  posts/posts.service.ts  queries for posts
  posts/posts.controller.ts
  common/kastor-exception.filter.ts
  app.module.ts
  main.ts
kastor.config.ts
kastor/migrations/
.env                      DATABASE_URL

Terminal

npm install kastor-orm pg @nestjs/config
npx kastor-orm init --db postgres
npx kastor migrate dev --name init

The provider and the module

A factory provider builds the client from ConfigService, so the URL comes from the environment like every other setting. The exported Db type is the client's inferred type, which services use to annotate the injected value.

src/db/db.module.ts

import { Global, Inject, Module, type OnApplicationShutdown } from '@nestjs/common'
import { ConfigService } from '@nestjs/config'
import pg from 'pg'
import { kastor } from 'kastor-orm'
import { postgres } from 'kastor-orm/postgres'
import { schema, relations } from './schema'

export const KASTOR = Symbol('KASTOR')

export const createDb = (url: string) =>
  kastor({ adapter: postgres(new pg.Pool({ connectionString: url, max: 10 })), schema, relations })

export type Db = ReturnType<typeof createDb>

@Global()
@Module({
  providers: [
    {
      provide: KASTOR,
      inject: [ConfigService],
      useFactory: (config: ConfigService) => createDb(config.getOrThrow<string>('DATABASE_URL')),
    },
  ],
  exports: [KASTOR],
})
export class DbModule implements OnApplicationShutdown {
  constructor(@Inject(KASTOR) private readonly db: Db) {}

  async onApplicationShutdown() {
    await this.db.$disconnect()
  }
}

src/app.module.ts

import { Module } from '@nestjs/common'
import { ConfigModule } from '@nestjs/config'
import { DbModule } from './db/db.module'
import { PostsController } from './posts/posts.controller'
import { PostsService } from './posts/posts.service'

@Module({
  imports: [ConfigModule.forRoot({ isGlobal: true }), DbModule],
  controllers: [PostsController],
  providers: [PostsService],
})
export class AppModule {}

Shutdown and hot reload

Nest only calls shutdown hooks when you enable them. With them on, SIGTERM from Docker or Kubernetes closes the HTTP server first and then the pool, so in flight queries finish.

src/main.ts

import { NestFactory } from '@nestjs/core'
import { AppModule } from './app.module'
import { KastorExceptionFilter } from './common/kastor-exception.filter'

async function main() {
  const app = await NestFactory.create(AppModule)
  app.enableShutdownHooks()
  app.useGlobalFilters(new KastorExceptionFilter())
  await app.listen(process.env.PORT ?? 3000)
}
main()

nest start --watch restarts the whole process on change, so there is no module cache to worry about: the old process closes its pool through the shutdown hook and the new one opens a fresh one. If you use webpack hot module replacement instead, add module.hot.dispose(() => app.close()) as the Nest docs show, which runs the same hook.

Services and controllers

A service injects the client and owns the queries for one area. Because the return types are inferred, the controller's responses are typed too, with no DTO classes for output unless you want them.

src/posts/posts.service.ts

import { Inject, Injectable } from '@nestjs/common'
import { KASTOR, type Db } from '../db/db.module'

@Injectable()
export class PostsService {
  constructor(@Inject(KASTOR) private readonly db: Db) {}

  list(take = 20) {
    return this.db.posts.findMany({
      where: { published: true },
      include: { author: { select: { name: true } }, tags: true },
      orderBy: { id: 'desc' },
      take,
    })
  }

  get(id: number) {
    return this.db.posts.findUniqueOrThrow({ where: { id }, include: { author: true } })
  }

  create(input: { title: string; authorId: number; tags?: string[] }) {
    return this.db.posts.create({
      data: {
        title: input.title,
        authorId: input.authorId,
        tags: { connectOrCreate: (input.tags ?? []).map((name) => ({ where: { name }, create: { name } })) },
      },
      include: { tags: true },
    })
  }

  publishAll(authorId: number) {
    return this.db.$transaction(async (tx) => {
      const { count } = await tx.posts.updateMany({ where: { authorId, published: false }, data: { published: true } })
      await tx.users.update({ where: { id: authorId }, data: { role: 'admin' } })
      return count
    })
  }
}

src/posts/posts.controller.ts

import { Body, Controller, Get, Param, ParseIntPipe, Post, Query } from '@nestjs/common'
import { PostsService } from './posts.service'

@Controller('posts')
export class PostsController {
  constructor(private readonly posts: PostsService) {}

  @Get()
  list(@Query('take') take?: string) {
    return this.posts.list(take ? Number(take) : undefined)
  }

  @Get(':id')
  get(@Param('id', ParseIntPipe) id: number) {
    return this.posts.get(id)
  }

  @Post()
  create(@Body() body: { title: string; authorId: number; tags?: string[] }) {
    return this.posts.create(body)
  }
}

Errors, tests and migrations

Every kastor failure is a KastorError with a stable code. One exception filter turns them into HTTP responses, so services can use findUniqueOrThrow and let NOT_FOUND become a 404.

src/common/kastor-exception.filter.ts

import { Catch, type ArgumentsHost, type ExceptionFilter } from '@nestjs/common'
import { KastorError } from 'kastor-orm'

const STATUS: Record<string, number> = { NOT_FOUND: 404, UNIQUE_VIOLATION: 409, FOREIGN_KEY_VIOLATION: 422, NOT_NULL_VIOLATION: 422 }

@Catch(KastorError)
export class KastorExceptionFilter implements ExceptionFilter {
  catch(error: KastorError, host: ArgumentsHost) {
    const res = host.switchToHttp().getResponse()
    const status = STATUS[error.code] ?? 500
    res.status(status).json({ statusCode: status, code: error.code, message: status === 500 ? 'Internal error' : error.message })
  }
}

In tests, override the provider with a client on a test database: Test.createTestingModule({ imports: [AppModule] }).overrideProvider(KASTOR).useValue(createDb(testUrl)). For migrations, keep them out of application startup and run them in the pipeline before the new version starts:

.github/workflows/deploy.yml

- run: npm ci
- run: npm run build
- run: npx kastor migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
- run: ./deploy.sh

Organizing queries as the app grows

In a large NestJS codebase, the question is less where the client lives and more where queries live. A good default is one service per feature module (PostsService, BillingService) that owns every query for that area and exposes methods named after what the domain needs, such as listPublished or archiveDrafts. Controllers call services; services call this.db. Because each method's return type is inferred from its query, there is no separate layer of interfaces to keep in sync.

When one use case spans several services, open the transaction in the outer service and pass the transaction client down. The tx argument of $transaction has the same type as the client, so a method can accept db: Db and work with either. That keeps transactions explicit, instead of hiding them in a request scoped provider.

Multi tenant applications often need a filter on every query. Rather than intercepting queries, keep a typed where object per tenant (for example { tenantId }) in the service and spread it into each call, or use database row level security set inside a transaction. Both are visible in the code and easy to test.

Production checklist

Before the first deploy, check these points. Each one prevents a problem that otherwise shows up only under real traffic, usually as exhausted connections, a leaked secret or a schema that does not match the code that is running:

The KASTOR provider is a singleton exported by a global module.
Shutdown hooks are enabled, and the module disconnects the pool.
One exception filter maps KastorError codes to HTTP responses.
Pool max times the number of replicas stays under the database limit.
Migrations run in the pipeline, not in onModuleInit.

Pitfalls

Request scoped providers. Do not make the KASTOR provider request scoped; that would build a pool per request. Keep it a singleton, the default.
Several app instances. Each replica has its own pool. Size max so replicas times max stays under the database limit.
Serialization. Nest serializes responses with JSON.stringify; bigint values throw. Convert t.bigId() keys in the service or with an interceptor.
Coming from TypeORM. Remove TypeOrmModule.forRoot once nothing injects repositories. The TypeORM migration guide covers entities and migration history.

Questions people ask

Q1How do I register kastor in NestJS?

Create a factory provider under a token such as KASTOR that returns kastor({ adapter, schema, relations }), put it in a @Global() module, and export it. Services then inject it with @Inject(KASTOR).

Q2Do I need entity classes or repositories?

No. Row types come from the schema through InferSelect and from each query's return type. A service that wraps the queries for one area plays the role of a repository.

Q3How do I close the connection pool when Nest shuts down?

Call app.enableShutdownHooks() in main.ts and implement OnApplicationShutdown in the module, calling db.$disconnect().

Q4How do I test services that use kastor?

Override the KASTOR provider in Test.createTestingModule with a client on a test database, or on SQLite in memory for fast unit tests of query logic.

#Project structure

#The provider and the module

#Shutdown and hot reload

#Services and controllers

#Errors, tests and migrations

#Organizing queries as the app grows

#Production checklist

#Pitfalls

#Questions people ask

#Related guides