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 every test in its Sequelize compatibility suite, covering CRUD, associations, transactions, advanced SQL, and vector operations. sequelize.sync(), sync({ alter: true }) and describeTable() work, with one exception that matters: for a model with an ENUM column, only the first sync() succeeds, and every later one throws — see Troubleshooting.

Prerequisites

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

Create a DB9 Database

db9 create --name sequelize-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 sequelize-app shows the connection string without the password; if you lose it, db9 db reset-password sequelize-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 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: 'YOUR_TENANT_ID.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';
import type { Post } from './Post';

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

sequelize.sync() and sync({ force: true }) work against DB9, and a later plain sync() detects the existing tables and preserves their rows. sync({ alter: true }) works too. The exception is a model with an ENUM column: once its table exists, sync() and sync({ alter: true }) both throw, so an app that calls sequelize.sync() at startup fails on its second boot — see Troubleshooting. Raw SQL is still the safer choice for production, because it keeps the DDL under review:

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 is not implemented — Sequelize will not error, but the transaction 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

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 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, a LIMIT is present, and the query has no WHERE clause; a bound query vector is fine. 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

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 the complete <tenant_id>.admin username from the generated connection string or db9 db connect, with host pg.db9.io and port 5433.

Troubleshooting

Schema introspection fails on `ENUM` columns

sync(), sync({ alter: true }) and describeTable() work against DB9 — alter adds the columns you add to a model, and re-running any of them with an unchanged model succeeds — unless the model has an ENUM column. For such a model the first sync() creates the table, and from then on all three throw, from inside Sequelize rather than from the database:

TypeError: text.replace is not a function

That includes a plain sequelize.sync(): an app that syncs at startup boots once and fails on every start after that. It throws identically when your model has not changed.

On every sync of an existing table Sequelize reads each enum’s labels from the catalog (ensureEnums, and again in describeTable()), and DB9 returns them as a text[]. The pg driver parses a text[] into a JavaScript array before Sequelize sees it, and Sequelize then calls a string method on that array.

If a model has an ENUM column, do not call sync() once its table exists. Create the schema once — with a first sync(), or with raw DDL — and apply later changes with raw DDL:

await sequelize.query('ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname VARCHAR(50)');

sync({ force: true }) still works on such a model, run after run — but it drops and recreates the table, so it discards the rows. Queries through an ENUM model are unaffected.

One thing to watch when alter does run: every run adds another UNIQUE constraint for a column declared unique: true (users_email_key, users_email_key1, users_email_key2, …).

The rest of QueryInterface is unaffected — showAllTables(), showIndex() and getForeignKeyReferencesForTable(). This is a pg_catalog typing difference, not an information_schema limitation; DB9’s information_schema is complete enough that Knex’s schema builder works against it.

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 complete username from the generated connection string. DB9 routes pgwire connections by the tenant ID prefix in that 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 (->, ->>, @>).

A bind value works unchanged as the key, path or operand of ->, ->>, #>, #>>, #-, @>, <@, ? and - (delete key). The any/all key-existence operators ?| and ?&, and jsonb_extract_path() / jsonb_extract_path_text(), are the exception: DB9 cannot infer a bound parameter’s type there, and the query fails with 42P18 could not determine data type of parameter $1. Cast the parameter — the cast is also valid PostgreSQL. (This applies to bind only — Sequelize’s replacements are inlined as literals before the query reaches the driver, so they are never bind parameters and need no cast.)

// Works: ->>, @> and ? infer a bound key or operand
await sequelize.query('SELECT metadata ->> $key FROM users', {
  bind: { key: 'role' },
});
await sequelize.query('SELECT id FROM users WHERE metadata @> $filter', {
  bind: { filter: JSON.stringify({ role: 'admin' }) },
});
await sequelize.query('SELECT id FROM users WHERE metadata ? $key', {
  bind: { key: 'role' },
});

// Fails: a bound key array for the ?| operator
await sequelize.query('SELECT id FROM users WHERE metadata ?| $keys', {
  bind: { keys: ['role', 'team'] },
});

// Works: cast the parameter
await sequelize.query('SELECT id FROM users WHERE metadata ?| $keys::text[]', {
  bind: { keys: ['role', 'team'] },
});

Use ::text[] for ?| and ?&, and ::text for the jsonb_extract_path key — the full table is in the compatibility matrix.

The key-existence operators need one more precaution: ? is also Sequelize’s positional replacement token, so metadata ? ? with a replacements array fails before the query is sent (Positional replacement (?) 1 has no entry in the replacement map). Use named replacements or bind, which do not collide:

// Fails: the ? operator is read as a positional replacement
await sequelize.query('SELECT id FROM users WHERE metadata ? ?', {
  replacements: ['role'],
});

// Works: named replacement, no collision
await sequelize.query('SELECT id FROM users WHERE metadata ? :key', {
  replacements: { key: 'role' },
});

Verified Compatibility

Tested with Sequelize 6.37 against DB9. Every test passes, 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 indexing	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