Sequelize

Sequelize connects to DB9 using the pg (node-postgres) driver over pgwire. Model definitions with Model.init(), associations (hasMany, belongsTo, belongsToMany), the Op query operators, and managed transactions work out of the box.

DB9 passes 100% of Sequelize compatibility tests (87/87) covering CRUD, associations, transactions, advanced SQL, and vector operations.

Prerequisites

A DB9 database (create one)
Node.js 18+
Sequelize 6.35+

Create a DB9 Database

db9 create --name sequelize-app

Get the connection string:

db9 db status sequelize-app

Project Setup

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

Connection

import { Sequelize } from 'sequelize';

const sequelize = new Sequelize({
  dialect: 'postgres',
  host: 'pg.db9.io',
  port: 5433,
  username: 'sequelize-app.admin',
  password: 'YOUR_PASSWORD',
  database: 'postgres',
  logging: false,
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
});

export default sequelize;

Test the connection:

await sequelize.authenticate();
console.log('Connected to DB9');

Model Definitions

Sequelize uses Model.init() with column type definitions:

import { Model, DataTypes, Optional } from 'sequelize';
import sequelize from '../connection';

interface UserAttributes {
  id: number;
  email: string;
  name: string;
  age: number;
  isActive: boolean;
  bio: string | null;
  metadata: Record<string, unknown> | null;
}

interface UserCreationAttributes
  extends Optional<UserAttributes, 'id' | 'age' | 'isActive' | 'bio' | 'metadata'> {}

export class User extends Model<UserAttributes, UserCreationAttributes>
  implements UserAttributes {
  declare id: number;
  declare email: string;
  declare name: string;
  declare age: number;
  declare isActive: boolean;
  declare bio: string | null;
  declare metadata: Record<string, unknown> | null;
  declare createdAt: Date;
  declare updatedAt: Date;
  declare posts?: Post[];
}

User.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
    name: { type: DataTypes.STRING(100), allowNull: false },
    age: { type: DataTypes.INTEGER, defaultValue: 0 },
    isActive: { type: DataTypes.BOOLEAN, defaultValue: true },
    bio: { type: DataTypes.TEXT, allowNull: true },
    metadata: { type: DataTypes.JSONB, allowNull: true },
  },
  { sequelize, tableName: 'users', timestamps: true }
);

import { Model, DataTypes, Optional } from 'sequelize';
import sequelize from '../connection';

interface PostAttributes {
  id: number;
  title: string;
  content: string;
  published: boolean;
  authorId: number;
}

interface PostCreationAttributes
  extends Optional<PostAttributes, 'id' | 'published'> {}

export class Post extends Model<PostAttributes, PostCreationAttributes>
  implements PostAttributes {
  declare id: number;
  declare title: string;
  declare content: string;
  declare published: boolean;
  declare authorId: number;
  declare createdAt: Date;
}

Post.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    title: { type: DataTypes.STRING(500), allowNull: false },
    content: { type: DataTypes.TEXT, allowNull: false },
    published: { type: DataTypes.BOOLEAN, defaultValue: false },
    authorId: { type: DataTypes.INTEGER, allowNull: false },
  },
  { sequelize, tableName: 'posts', timestamps: true }
);

import { Model, DataTypes, Optional } from 'sequelize';
import sequelize from '../connection';

export class Tag extends Model {
  declare id: number;
  declare name: string;
}

Tag.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    name: { type: DataTypes.STRING(100), allowNull: false, unique: true },
  },
  { sequelize, tableName: 'tags', timestamps: false }
);

Associations

Define associations after all models are initialized:

import { User } from './User';
import { Post } from './Post';
import { Tag } from './Tag';

// One-to-Many
User.hasMany(Post, { foreignKey: 'authorId', as: 'posts', onDelete: 'CASCADE' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

// Many-to-Many
Post.belongsToMany(Tag, { through: 'post_tags', as: 'tags' });
Tag.belongsToMany(Post, { through: 'post_tags', as: 'posts' });

export { User, Post, Tag };

Create Tables

Use raw SQL rather than sequelize.sync() for production, as Sequelize’s schema sync relies on information_schema introspection that may not fully work with DB9:

import sequelize from './connection';

async function setup() {
  await sequelize.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 sequelize.query(`
    CREATE TABLE IF NOT EXISTS posts (
      id SERIAL PRIMARY KEY,
      title VARCHAR(500) NOT NULL,
      content TEXT NOT NULL,
      published BOOLEAN DEFAULT false,
      "authorId" INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      "createdAt" TIMESTAMPTZ DEFAULT now(),
      "updatedAt" TIMESTAMPTZ DEFAULT now()
    )
  `);

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

  await sequelize.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,
      "createdAt" TIMESTAMPTZ DEFAULT now(),
      "updatedAt" TIMESTAMPTZ DEFAULT now(),
      PRIMARY KEY ("postId", "tagId")
    )
  `);

  console.log('Tables created');
}

setup();

CRUD Operations

import { Op } from 'sequelize';
import { User, Post, Tag } from './models';

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

// Bulk create
const users = await User.bulkCreate([
  { email: 'bob@example.com', name: 'Bob', age: 25 },
  { email: 'carol@example.com', name: 'Carol', age: 35 },
]);

// Read
const found = await User.findOne({ where: { email: 'alice@example.com' } });
const byId = await User.findByPk(user.id);
const all = await User.findAll({
  where: { age: { [Op.gte]: 25 } },
  order: [['age', 'DESC']],
  limit: 10,
  offset: 0,
});

// Select specific columns
const names = await User.findAll({
  attributes: ['id', 'name'],
  where: { isActive: true },
});

// Aggregates
const count = await User.count();
const totalAge = await User.sum('age');

// Update
await User.update({ name: 'Alice Updated' }, { where: { id: user.id } });

// Instance update
const alice = await User.findByPk(user.id);
alice!.age = 31;
await alice!.save();

// Upsert
const [upserted, created] = await User.upsert({
  email: 'alice@example.com',
  name: 'Alice V2',
  age: 32,
});

// Delete
await User.destroy({ where: { id: user.id } });

Operator queries

import { Op } from 'sequelize';

// Comparison
await User.findAll({ where: { age: { [Op.gt]: 25 } } });
await User.findAll({ where: { age: { [Op.lte]: 30 } } });
await User.findAll({ where: { name: { [Op.in]: ['Alice', 'Bob'] } } });

// Pattern matching
await User.findAll({ where: { name: { [Op.like]: '%li%' } } });
await User.findAll({ where: { name: { [Op.iLike]: '%alice%' } } });

// NULL checks
await User.findAll({ where: { bio: { [Op.is]: null } } });

// Logical operators
await User.findAll({
  where: {
    [Op.or]: [
      { [Op.and]: [{ age: { [Op.gte]: 30 } }, { bio: { [Op.not]: null } }] },
      { name: 'Alice' },
    ],
  },
});

Loading Associations

// Eager load (LEFT JOIN)
const userWithPosts = await User.findByPk(1, {
  include: [{ model: Post, as: 'posts' }],
});
console.log(userWithPosts!.posts);

// Nested includes
const userFull = await User.findByPk(1, {
  include: [{
    model: Post,
    as: 'posts',
    include: [{ model: Tag, as: 'tags' }],
  }],
});

// Filtered include
const userPublished = await User.findByPk(1, {
  include: [{
    model: Post,
    as: 'posts',
    where: { published: true },
  }],
});

// INNER JOIN (only users with posts)
const authors = await User.findAll({
  include: [{ model: Post, as: 'posts', required: true }],
});

// Many-to-Many: add and remove tags
const post = await Post.findByPk(1);
const tag = await Tag.create({ name: 'db9' });
await post!.addTag(tag);
await post!.removeTag(tag);

Transactions

Managed transactions (auto-commit/rollback)

import sequelize from './connection';

await sequelize.transaction(async (t) => {
  const user = await User.create(
    { email: 'txn@example.com', name: 'Txn User', age: 30 },
    { transaction: t }
  );

  await Post.create(
    { title: 'Transactional Post', content: 'Atomic.', authorId: user.id },
    { transaction: t }
  );
  // Auto-commits on success, auto-rolls back on error
});

Unmanaged transactions (manual control)

const t = await sequelize.transaction();

try {
  await User.create(
    { email: 'manual@example.com', name: 'Manual', age: 28 },
    { transaction: t }
  );
  await t.commit();
} catch (err) {
  await t.rollback();
  throw err;
}

Isolation levels

import { Transaction } from 'sequelize';

await sequelize.transaction(
  { isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ },
  async (t) => {
    const users = await User.findAll({ transaction: t });
    return users;
  }
);

Supported: READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE (runs as REPEATABLE READ on DB9).

Raw SQL for Advanced Features

import { QueryTypes } from 'sequelize';

// Window functions
const ranked = await sequelize.query(
  `SELECT name, age,
     ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
   FROM users ORDER BY rank`,
  { type: QueryTypes.SELECT }
);

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

// DISTINCT ON
const firstPerAge = await sequelize.query(
  `SELECT DISTINCT ON (age) name, age
   FROM users ORDER BY age, name`,
  { type: QueryTypes.SELECT }
);

// Parameterized queries
const filtered = await sequelize.query(
  'SELECT * FROM users WHERE age > $1 AND age < $2',
  { type: QueryTypes.SELECT, bind: [20, 35] }
);

Vector Search

Sequelize does not have a native vector column type. Use raw SQL for vector operations:

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

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

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

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

DB9 supports HNSW indexes with vector_l2_ops, vector_cosine_ops, and vector_ip_ops. IVFFlat is not supported.

Schema Changes

Manage migrations with raw SQL rather than Sequelize’s built-in migration tools:

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

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

Production Notes

Driver: Sequelize uses pg (node-postgres). Set dialect: 'postgres' in the configuration.
Connection pooling: Configure pool.max based on your workload. DB9 supports multiple concurrent connections per tenant.
Timestamps: Use timestamps: true in model options for automatic createdAt/updatedAt management.
Upsert behavior: Sequelize’s upsert() works with DB9, but the created return value may be null instead of a boolean. Check both true and null if you rely on the return value.
Connection string: Use sequelize-app.admin as the username, with host pg.db9.io and port 5433.

Troubleshooting

`sequelize.sync()` creates unexpected schema

DB9’s information_schema support is limited. Sequelize’s schema sync may not detect existing tables correctly. Use raw DDL and avoid sync({ force: true }) in production.

Upsert `created` flag is `null`

DB9 may return null for the created flag on upsert instead of true/false. If your logic depends on knowing whether a row was inserted or updated, query the row after upsert.

Connection timeout

Verify the host (pg.db9.io), port (5433), and username format ({database-name}.admin). DB9 routes connections by parsing the tenant from the username.

JSONB queries

Sequelize’s JSONB operators work with DB9. For advanced operations not covered by the ORM API, use sequelize.query() with PostgreSQL’s native JSONB operators (->, ->>, @>).

Verified Compatibility

Tested with Sequelize 6.35+ against DB9. All 87 tests pass covering:

Category	Status
Connection and pooling	Pass
CRUD with operator queries	Pass
Associations (hasMany, belongsTo, belongsToMany)	Pass
Transactions and isolation levels	Pass
Advanced SQL (window, CTE, DISTINCT ON)	Pass
Vector operations and HNSW	Pass
DDL operations	Pass

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