TypeORM

TypeORM connects to DB9 using the pg (node-postgres) driver over pgwire. Decorator-based entity definitions, the repository pattern, QueryBuilder, and relation loading work out of the box.

DB9 passes every test in its TypeORM compatibility suite, covering CRUD, relations, transactions, advanced SQL, and vector operations — with none skipped. The migration runner works against DB9; synchronize: true does not survive being run twice — see Troubleshooting.

Prerequisites

A DB9 database (create one)
Node.js ^20.19.0, ^22.13.0, or >=24.11.0 (the engine range for TypeORM 1.1.2)
TypeORM 0.3.17+

Create a DB9 Database

db9 create --name typeorm-app

db9 create prints the connection string, with the admin password, once — copy it from the Connection String: line. It has this shape:

postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.d0000.db9.io:5432/postgres

YOUR_TENANT_ID is the tenant ID prefix in the generated connection string. Copy the username from the Connection String: line printed by db9 create or from db9 db connect. The examples below use pg.db9.io:5433, which reaches the same server; either host and port works. db9 db status typeorm-app shows the connection string without the password; if you lose it, db9 db reset-password typeorm-app sets a new password and prints a new connection string.

Settings that take the password on its own need it unencoded: the connection string URL-encodes it (%2B is +, %3D is =), while db9 create --show-password and db9 db reset-password also print it as plain text on the Admin Pass: line.

Project Setup

mkdir typeorm-db9 && cd typeorm-db9
npm init -y
npm install typeorm pg reflect-metadata
npm install -D typescript @types/pg @types/node

Enable decorators in tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ESNext",
    "moduleResolution": "bundler",
    "types": ["node"],
    "strict": true,
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "outDir": "./dist"
  }
}

DataSource Configuration

import 'reflect-metadata';
import { DataSource } from 'typeorm';
import { User } from './entities/User';
import { Post } from './entities/Post';
import { Tag } from './entities/Tag';

export const AppDataSource = new DataSource({
  type: 'postgres',
  host: 'pg.db9.io',
  port: 5433,
  username: 'YOUR_TENANT_ID.admin',
  password: 'YOUR_PASSWORD',
  database: 'postgres',
  ssl: { rejectUnauthorized: false },
  entities: [User, Post, Tag],
  synchronize: false,
  logging: process.env.DEBUG === 'true' ? ['query', 'error'] : false,
});

Initialize the connection:

await AppDataSource.initialize();
console.log('Connected to DB9');

Entity Definitions

User entity with common column types

import {
  Entity, PrimaryGeneratedColumn, Column, Index,
  CreateDateColumn, UpdateDateColumn, OneToMany,
} from 'typeorm';
import { Post } from './Post';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', length: 255, unique: true })
  @Index()
  email!: string;

  @Column({ type: 'varchar', length: 100 })
  name!: string;

  @Column({ type: 'int', default: 0 })
  age!: number;

  @Column({ type: 'boolean', default: true })
  isActive!: boolean;

  @Column({ type: 'text', nullable: true })
  bio!: string | null;

  @Column({ type: 'jsonb', nullable: true })
  metadata!: Record<string, unknown> | null;

  @CreateDateColumn({ type: 'timestamptz' })
  createdAt!: Date;

  @UpdateDateColumn({ type: 'timestamptz' })
  updatedAt!: Date;

  @OneToMany(() => Post, (post) => post.author)
  posts!: Post[];
}

Post entity with relations

import {
  Entity, PrimaryGeneratedColumn, Column,
  CreateDateColumn, ManyToOne, ManyToMany, JoinColumn, JoinTable,
} from 'typeorm';
import { User } from './User';
import { Tag } from './Tag';

@Entity('posts')
export class Post {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', length: 500 })
  title!: string;

  @Column({ type: 'text' })
  content!: string;

  @Column({ type: 'boolean', default: false })
  published!: boolean;

  @Column({ type: 'jsonb', nullable: true })
  settings!: Record<string, unknown> | null;

  @CreateDateColumn({ type: 'timestamptz' })
  createdAt!: Date;

  @Column({ type: 'int' })
  authorId!: number;

  @ManyToOne(() => User, (user) => user.posts, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'authorId' })
  author!: User;

  @ManyToMany(() => Tag, (tag) => tag.posts)
  @JoinTable({
    name: 'post_tags',
    joinColumn: { name: 'postId', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'tagId', referencedColumnName: 'id' },
  })
  tags!: Tag[];
}

Tag entity for ManyToMany

import { Entity, PrimaryGeneratedColumn, Column, Index, ManyToMany } from 'typeorm';
import { Post } from './Post';

@Entity('tags')
export class Tag {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', length: 100, unique: true })
  @Index()
  name!: string;

  @ManyToMany(() => Post, (post) => post.tags)
  posts!: Post[];
}

Create Tables

synchronize: true creates the schema on its first run, but do not leave it on against DB9: every second start strips the default from each generated id column, and inserts fail until the next start puts it back — see Troubleshooting. Keep it off, as in the DataSource above, and create tables with raw SQL or the migration runner:

import { AppDataSource } from './datasource';

async function setup() {
  await AppDataSource.initialize();

  await AppDataSource.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(100) NOT NULL,
      age INT DEFAULT 0,
      "isActive" BOOLEAN DEFAULT true,
      bio TEXT,
      metadata JSONB,
      "createdAt" TIMESTAMPTZ DEFAULT now(),
      "updatedAt" TIMESTAMPTZ DEFAULT now()
    )
  `);

  await AppDataSource.query(`
    CREATE TABLE IF NOT EXISTS posts (
      id SERIAL PRIMARY KEY,
      title VARCHAR(500) NOT NULL,
      content TEXT NOT NULL,
      published BOOLEAN DEFAULT false,
      settings JSONB,
      "createdAt" TIMESTAMPTZ DEFAULT now(),
      "authorId" INT NOT NULL REFERENCES users(id) ON DELETE CASCADE
    )
  `);

  await AppDataSource.query(`
    CREATE TABLE IF NOT EXISTS tags (
      id SERIAL PRIMARY KEY,
      name VARCHAR(100) UNIQUE NOT NULL
    )
  `);

  await AppDataSource.query(`
    CREATE TABLE IF NOT EXISTS post_tags (
      "postId" INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
      "tagId" INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
      PRIMARY KEY ("postId", "tagId")
    )
  `);

  console.log('Tables created');
  await AppDataSource.destroy();
}

setup();

CRUD with Repository Pattern

import { AppDataSource } from './datasource';
import { User } from './entities/User';
import { Post } from './entities/Post';

await AppDataSource.initialize();
const userRepo = AppDataSource.getRepository(User);
const postRepo = AppDataSource.getRepository(Post);

// Create
const user = userRepo.create({
  email: 'alice@example.com',
  name: 'Alice',
  age: 30,
  metadata: { role: 'admin' },
});
const saved = await userRepo.save(user);

// Create with relation
const post = postRepo.create({
  title: 'Getting Started with DB9',
  content: 'DB9 is a serverless PostgreSQL-compatible database.',
  authorId: saved.id,
});
await postRepo.save(post);

// Read
const found = await userRepo.findOneBy({ email: 'alice@example.com' });

// Read with relations
const withPosts = await userRepo.findOne({
  where: { id: saved.id },
  relations: { posts: true },
});

// Read with ordering and pagination
const page = await userRepo.find({
  order: { age: 'DESC', name: 'ASC' },
  skip: 0,
  take: 10,
});

// Update
await userRepo.update(saved.id, { name: 'Alice Updated' });

// Upsert (INSERT ... ON CONFLICT)
await AppDataSource.createQueryBuilder()
  .insert()
  .into(User)
  .values({ email: 'alice@example.com', name: 'Alice V2', age: 31 })
  .orUpdate(['name', 'age'], ['email'])
  .execute();

// Delete (cascades to posts)
await userRepo.delete(saved.id);

QueryBuilder

TypeORM’s QueryBuilder provides type-safe query construction:

// SELECT with conditions
const users = await AppDataSource.getRepository(User)
  .createQueryBuilder('user')
  .where('user.age >= :minAge', { minAge: 25 })
  .andWhere('user."isActive" = :active', { active: true })
  .orderBy('user.name', 'ASC')
  .getMany();

// Aggregation with GROUP BY
const ageGroups = await AppDataSource.getRepository(User)
  .createQueryBuilder('user')
  .select('user.age', 'age')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.age')
  .having('COUNT(*) > :min', { min: 1 })
  .orderBy('user.age', 'ASC')
  .getRawMany();

// JOIN with eager loading
const usersWithPosts = await AppDataSource.getRepository(User)
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .where('user.id = :id', { id: 1 })
  .getOne();

// Subquery in WHERE
const aboveAvg = await AppDataSource.getRepository(User)
  .createQueryBuilder('user')
  .where((qb) => {
    const sub = qb.subQuery().select('AVG(u.age)').from(User, 'u').getQuery();
    return `user.age > (${sub})`;
  })
  .getMany();

// INSERT with RETURNING
const result = await AppDataSource.createQueryBuilder()
  .insert()
  .into(User)
  .values({ email: 'new@example.com', name: 'New', age: 25 })
  .returning(['id', 'email', 'createdAt'])
  .execute();

Relations

TypeORM supports OneToMany, ManyToOne, and ManyToMany relations with DB9:

// Load user with nested relations
const user = await userRepo.findOne({
  where: { id: 1 },
  relations: { posts: { tags: true } },
});

// ManyToMany: add tags to a post
const tag = await AppDataSource.getRepository(Tag).save({ name: 'db9' });
const post = await postRepo.findOne({
  where: { id: 1 },
  relations: { tags: true },
});
post!.tags = [...(post!.tags || []), tag];
await postRepo.save(post!);

// INNER JOIN (only users with posts)
const activeAuthors = await AppDataSource.getRepository(User)
  .createQueryBuilder('user')
  .innerJoinAndSelect('user.posts', 'post')
  .getMany();

// LEFT JOIN with filter on relation
const usersPublished = await AppDataSource.getRepository(User)
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post', 'post.published = :pub', { pub: true })
  .getOne();

Transactions

QueryRunner (explicit control)

const queryRunner = AppDataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  const user = await queryRunner.manager.save(User, {
    email: 'txn@example.com',
    name: 'Txn User',
    age: 25,
  });
  await queryRunner.manager.save(Post, {
    title: 'Transactional Post',
    content: 'Created atomically.',
    authorId: user.id,
  });
  await queryRunner.commitTransaction();
} catch (err) {
  await queryRunner.rollbackTransaction();
  throw err;
} finally {
  await queryRunner.release();
}

Transaction manager (automatic rollback)

await AppDataSource.transaction(async (manager) => {
  const user = await manager.save(User, {
    email: 'auto@example.com',
    name: 'Auto User',
    age: 28,
  });
  await manager.save(Post, {
    title: 'Auto Post',
    content: 'Rolls back on error.',
    authorId: user.id,
  });
});

Isolation levels

// Explicit isolation level
await queryRunner.startTransaction('REPEATABLE READ');

Supported: READ COMMITTED, REPEATABLE READ. SERIALIZABLE is not implemented — it does not error, it is silently downgraded to REPEATABLE READ with a server WARNING. Set REPEATABLE READ explicitly so the intent is visible in your code.

Raw SQL for Advanced Features

// Window functions
const ranked = await AppDataSource.query(`
  SELECT name, age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
  FROM users
`);

// CTEs
const result = await AppDataSource.query(`
  WITH active AS (
    SELECT * FROM users WHERE "isActive" = true
  )
  SELECT name, age FROM active ORDER BY age
`);

// DISTINCT ON
const firstPerAge = await AppDataSource.query(`
  SELECT DISTINCT ON (age) name, age
  FROM users ORDER BY age, name
`);

// Parameterized queries
const filtered = await AppDataSource.query(
  'SELECT * FROM users WHERE age > $1 AND age < $2',
  [20, 35]
);

Vector Search

TypeORM does not have a native vector column type. Use text for the entity column and raw SQL for vector operations:

// Create vector table and index
await AppDataSource.query(`
  CREATE EXTENSION IF NOT EXISTS vector;
  CREATE TABLE IF NOT EXISTS embeddings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    embedding vector(3)
  );
  CREATE INDEX IF NOT EXISTS idx_embeddings
    ON embeddings USING hnsw (embedding vector_l2_ops);
`);

// Insert vectors
await AppDataSource.query(
  `INSERT INTO embeddings (name, embedding) VALUES ($1, $2)`,
  ['doc-1', '[1.0, 2.0, 3.0]']
);

// Cosine distance search
const results = await AppDataSource.query(`
  SELECT name, cosine_distance(embedding, '[1.0, 1.0, 1.0]') AS distance
  FROM embeddings
  ORDER BY distance ASC
  LIMIT 5
`);

// L2 distance with operator
const nearest = await AppDataSource.query(`
  SELECT name FROM embeddings
  WHERE embedding <-> '[1.0, 0.0, 0.0]' < 1.0
  ORDER BY embedding <-> '[1.0, 0.0, 0.0]'
`);

DB9 supports HNSW indexes with vector_l2_ops, vector_cosine_ops, and vector_ip_ops; IVFFlat is not available at all. A CREATE INDEX ... USING hnsw builds a real index over both the wire protocol and the HTTP SQL API. The planner only uses it when the query’s distance metric matches the index’s opclass, the probe is a literal or a bound parameter, a LIMIT is present, and the query has no WHERE clause — the examples above build a vector_l2_ops index and then query with cosine_distance or without a LIMIT, so they run as exact scans. See When the index is used. If your code uses CREATE INDEX ... USING ivfflat, change it to USING hnsw.

Schema Changes

TypeORM’s migration runner works against DB9: runMigrations(), showMigrations() and undoLastMigration() all behave normally, and the migrations bookkeeping table is created and maintained for you. Writing the migration bodies as raw SQL is still the clearest option:

// Add a column
await AppDataSource.query('ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT');

// Create an index
await AppDataSource.query('CREATE INDEX IF NOT EXISTS idx_users_age ON users (age)');

// Alter column type
await AppDataSource.query('ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(20)');

Production Notes

Driver: TypeORM uses the pg (node-postgres) driver. Set type: 'postgres' in the DataSource configuration.
reflect-metadata: Must be imported before any TypeORM code runs (import 'reflect-metadata').
Connection pooling: TypeORM manages a pool internally. DB9 supports multiple concurrent connections per tenant.
Type parser conflict: drizzle-orm 0.34.1 and earlier replace the global pg type parsers when drizzle() is called, which changes what TypeORM returns in the same process; 0.35.0 and later do not. See Date columns return strings.
synchronize: Keep it false, in development too — against DB9 a repeated synchronize drops the defaults of generated id columns (see Troubleshooting). Use raw SQL or migrations for schema changes.
Connection string: Use the complete <tenant_id>.admin username from the generated connection string or db9 db connect, with host pg.db9.io and port 5433.

Troubleshooting

`synchronize` and migrations

The migration runner works against DB9: runMigrations(), showMigrations() and undoLastMigration() all behave normally.

synchronize: true does not. The first run creates the schema correctly. The second run finds the existing tables, keeps their rows — and then removes the default from every @PrimaryGeneratedColumn() column:

ALTER TABLE "users" ALTER COLUMN "id" SET DEFAULT nextval('"users_id_seq"')
ALTER TABLE "users" ALTER COLUMN "id" DROP DEFAULT

From then on, any insert that relies on the generated id fails:

null value in column "id" of relation "users" violates not-null constraint   (23502)

The third run sees a column with no default and restores it, the fourth strips it again, and so on — an application started with synchronize: true breaks on every other restart, and no error is raised at startup either way. Measured with TypeORM 0.3.28 and 1.1.2.

The cause is how DB9 spells the default when TypeORM reads it back. information_schema.columns.column_default reports nextval(CAST('public.users_id_seq' AS pg_catalog.regclass)), where TypeORM looks for nextval('users_id_seq'::regclass) to recognise a generated column. It does not find it, decides the column has changed, and drops the default.

Use the migration runner or raw SQL for schema changes. If a table has already lost its default, restore it with:

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq');

If you are porting advice from the Drizzle page, it does not apply here. The DB9 catalog gap that breaks drizzle-kit push is the missing pg_stat_user_indexes view; TypeORM’s schema code is not affected by it. Prisma’s db push trips on the same serial-default spelling as synchronize above — see the Prisma guide.

Date columns return strings

drizzle-orm 0.34.1 and earlier replace the global pg parsers for timestamp, timestamptz, date and interval when drizzle() is called (checked with 0.29.5 through 0.34.1). With TypeORM 1.1.2 in the same process, entities still read timestamp and timestamptz columns as Date objects, but dataSource.query() then returns all four types as strings, and interval columns read as strings instead of objects. drizzle-orm 0.35.0 and later leave the global parsers alone, so upgrade Drizzle or run it in its own process.

Do not restore the date parser with (val) => new Date(val). That reads a date as UTC midnight, where pg uses local midnight, so west of UTC a TypeORM date column then reads one day early (2019-12-31 for a stored 2020-01-01).

Connection refused

Verify the host (pg.db9.io), port (5433), and complete username from the generated connection string. DB9 routes pgwire connections by the tenant ID prefix in that username.

`QueryFailedError: relation does not exist`

Tables must be created before use. With synchronize: false, run your DDL setup script first.

Verified Compatibility

Tested with TypeORM 0.3.28 against DB9. Every test passes, with none skipped:

Category	Status
Connection and pooling	Pass
CRUD (insert, select, update, delete)	Pass
Relations (OneToMany, ManyToOne, ManyToMany)	Pass
QueryBuilder (select, join, subquery, aggregate)	Pass
Transactions and isolation levels	Pass
Schema and DDL operations	Pass
Data types (int, varchar, boolean, jsonb, uuid, timestamptz)	Pass
Error handling (constraint violations, syntax errors)	Pass
Vector operations and HNSW indexing	Pass
Advanced SQL (window, CTE, DISTINCT ON)	Pass

Prisma — Prisma ORM with DB9
Drizzle — Drizzle ORM with DB9
Connect — connection strings and authentication
Vector Extension — HNSW indexes and distance operators
Compatibility Matrix — full PostgreSQL compatibility surface