Customizing built-in table schemas

Every service in @colyseus/database is backed by a Drizzle table you can extend with your own columns, constraints, and indexes. Use the per-dialect tables factory to build a table from the built-in columns plus yours, then pass it via the schemas option. Services keep working and return your custom columns with full type inference.

The examples import column builders from drizzle-orm. Install it from the rc dist-tag, as described under Drizzle version.

Built-in tables

Key	Physical table	Used by	Purpose
`users`	`colyseus_users`	`db.auth` + `@colyseus/auth`	Player accounts (email, password hash, anonymous flag, ban fields, token version)
`configs`	`colyseus_configs`	`db.configs`	Live-ops config key/value store
`cloudSaves`	`colyseus_cloud_saves`	`db.saves`	Per-user, per-slot save data with version
`leaderboards`	`colyseus_leaderboards`	`db.leaderboards`	Leaderboard definitions
`leaderboardEntries`	`colyseus_leaderboard_entries`	`db.leaderboards`	Score rows per (board, user, season)
`analyticsEvents`	`colyseus_analytics_events`	`db.analytics`	Event log written by `track()`
`roles`	`colyseus_roles`	`db.moderation`	Moderation roles + scoped collection permissions
`userNotes`	`colyseus_user_notes`	`db.notes`	Admin notes attached to users
`adminAudit`	`colyseus_admin_audit`	`db.audit`	Admin action audit log
`roomCaches`	`colyseus_room_caches`	`DatabaseDriver`	Matchmaker room cache (driver-owned; customize via the driver’s `schema` option, not `schemas`)

Example: extending `users`

src/db/schema.ts

import { tables } from "@colyseus/database";
import { text, integer } from "drizzle-orm/sqlite-core";
 
export const users = tables.sqlite.users("colyseus_users", {
    displayName: text("display_name"),
    level: integer("level").notNull().default(1),
});

src/app.config.ts

import { GameDatabase } from "@colyseus/database";
import { users } from "./db/schema";
 
export const db = new GameDatabase({
    connectionString: "./game.db",
    schemas: { users },
});

The first argument is the physical table name. "colyseus_users" keeps the default name, so existing rows stay in place. Any other name creates a new, empty table: the rows in the built-in one are not migrated.

The factory is available on both dialects: tables.sqlite.<name> with column builders from drizzle-orm/sqlite-core, and tables.pg.<name> with drizzle-orm/pg-core. The tabs on this page remember your choice.

The resolved tables are available as db.tables.<key> (db.tables.users is the table you passed). The raw Drizzle instance is db.drizzle, typed against your schema, for any query the services don’t cover:

import { eq } from "drizzle-orm";
 
await db.drizzle.update(db.tables.users)
    .set({ displayName: "Alice" })
    .where(eq(db.tables.users.id, userId));

⚠️

The built-in inserts (/auth/register, /auth/anonymous, OAuth sign-in) fill the built-in columns only. Give custom columns a default or keep them nullable, or those routes fail with a NOT NULL error.

Constraints and indexes

The factory’s third argument mirrors sqliteTable() / pgTable(): a callback that receives the built columns and returns indexes, CHECK constraints, and UNIQUE constraints. The example below adds a handle that is unique regardless of case, so JohnSmith and johnsmith cannot coexist:

src/db/schema.ts

import { tables } from "@colyseus/database";
import { text, uniqueIndex, check } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";
 
export const users = tables.sqlite.users("colyseus_users", {
    handle: text("handle"),
    handleLower: text("handle_lower"),
}, (t) => [
    uniqueIndex("users_handle_lower_idx").on(t.handleLower),
    check("users_handle_lower_chk", sql`${t.handleLower} = lower(${t.handle})`),
]);

Write both columns together to keep the check satisfied:

await db.drizzle.update(db.tables.users)
    .set({ handle, handleLower: handle.toLowerCase() })
    .where(eq(db.tables.users.id, userId));
// a second user with the handle "johnsmith" now fails with a UNIQUE error

migrations: "auto" applies these in two ways:

When the table is created: UNIQUE (column-level .unique() and unique().on(...)) and CHECK constraints are part of CREATE TABLE. They are not added to a table that already exists.
On every boot: index() and uniqueIndex() run as CREATE INDEX IF NOT EXISTS, so an index declared later still reaches an existing table.

Prefer uniqueIndex() over .unique() for a table that may already exist in production. For any other change to an existing table, use drizzle-kit migrations.

⚠️

The PostgreSQL factory’s third argument currently fails type-checking. tsc rejects .on(t.handleLower) with “Property ‘dimensions’ is missing”, although the code runs. Until the fix ships, use the hand-rolled pgTable form, which type-checks. SQLite is not affected.

Hand-rolled tables with `columns`

For full control, spread the column maps (columns.sqlite.<name> / columns.pg.<name>) into your own sqliteTable(...) / pgTable(...). The factory does the same internally:

src/db/schema.ts

import { columns } from "@colyseus/database";
import { sqliteTable, text, uniqueIndex, check } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";
 
export const users = sqliteTable("colyseus_users", {
    ...columns.sqlite.users,
    handle: text("handle"),
    handleLower: text("handle_lower"),
}, (t) => [
    uniqueIndex("users_handle_lower_idx").on(t.handleLower),
    check("users_handle_lower_chk", sql`${t.handleLower} = lower(${t.handle})`),
]);

cloudSaves and leaderboardEntries have composite primary keys: (userId, slot) and (boardId, userId, season). When you hand-roll those two, add the primaryKey(...) yourself. The factory adds it for you.

⚠️

Spread columns.sqlite.users / columns.pg.users, not the tables.* factories. Those are functions, so spreading them adds no columns. The server still boots. The first login or room join then fails with TypeError: Cannot convert undefined or null to object. The table has none of the built-in columns.

Migrations with drizzle-kit

For production schemas, generate SQL migrations with drizzle-kit and apply them with migrations: { files }. drizzle-kit reads every table exported from one schema file. Re-export the tables you did not customize from @colyseus/database/sqlite-defaults (or @colyseus/database/pg-defaults). That way drizzle-kit generates them too:

src/db/schema.ts

import { tables } from "@colyseus/database";
import { text, uniqueIndex, check } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";
 
export const users = tables.sqlite.users("colyseus_users", {
    handle: text("handle"),
    handleLower: text("handle_lower"),
}, (t) => [
    uniqueIndex("users_handle_lower_idx").on(t.handleLower),
    check("users_handle_lower_chk", sql`${t.handleLower} = lower(${t.handle})`),
]);
 
export {
    configs, cloudSaves, leaderboards, leaderboardEntries,
    analyticsEvents, roles, userNotes, adminAudit,
} from "@colyseus/database/sqlite-defaults";

drizzle.config.ts

import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
    dialect: "sqlite",
    schema: "./src/db/schema.ts",
    out: "./drizzle",
    dbCredentials: { url: "./colyseus.db" },
});

With drizzle-kit@rc installed (see Drizzle version), generate the migration:

npx drizzle-kit generate

The generated migration.sql contains every table, including the unique index and the check constraint. Point GameDatabase at the folder:

src/app.config.ts

import { GameDatabase } from "@colyseus/database";
import * as schema from "./db/schema";
 
export const db = new GameDatabase({
    connectionString: "./colyseus.db",
    schemas: schema,
    migrations: { files: "./drizzle" },
});

Drizzle records applied migrations in its own table, so every boot re-runs safely. After a schema change, run drizzle-kit generate again and commit the new folder.

Services Bring your own database

Customizing built-in table schemas

Built-in tables

Example: extending users

Constraints and indexes

Hand-rolled tables with columns

Migrations with drizzle-kit

Example: extending `users`

Hand-rolled tables with `columns`