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 98% of TypeORM compatibility tests (147/150, 3 skipped) covering CRUD, relations, transactions, advanced SQL, and vector operations.

Prerequisites

A DB9 database (create one)
Node.js 18+
TypeORM 0.3.17+

Create a DB9 Database

db9 create --name typeorm-app

Get the connection string:

db9 db status typeorm-app

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",
    "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: 'typeorm-app.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

Use raw SQL to create tables rather than synchronize: true, which relies on schema introspection that may not fully work with DB9:

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'],
});

// 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', 'posts.tags'],
});

// 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'],
});
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 (runs as REPEATABLE READ on DB9).

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 supported.

Schema Changes

Since TypeORM’s synchronize and migration runner rely on schema introspection that may not fully work with DB9, manage migrations with raw SQL:

// 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: If you run TypeORM alongside Drizzle in the same process, Drizzle overrides global pg type parsers. Restore date parsers explicitly if needed.
synchronize: Set to false in production. Use raw SQL for schema changes.
Connection string: Use typeorm-app.admin as the username, with host pg.db9.io and port 5433.

Troubleshooting

`synchronize` creates unexpected schema

DB9’s information_schema support is limited. TypeORM’s schema synchronization may not detect existing tables correctly. Use raw DDL and set synchronize: false.

Date columns return strings

If date columns return strings instead of Date objects, another library (such as Drizzle) may have modified the global pg type parsers. Restore them:

import { types } from 'pg';

types.setTypeParser(types.builtins.TIMESTAMPTZ, (val: string) => new Date(val));
types.setTypeParser(types.builtins.TIMESTAMP, (val: string) => new Date(val));
types.setTypeParser(types.builtins.DATE, (val: string) => new Date(val));

Connection refused

Verify the host (pg.db9.io), port (5433), and username format ({database-name}.admin). DB9 routes connections by parsing the tenant from the 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.17+ against DB9. 147 of 150 tests pass (3 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	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