Loading
Create a TypeScript ORM with model definitions, a fluent query builder, migrations, and type-safe relationships.
ORMs (Object-Relational Mappers) bridge the gap between your application's objects and the database's tables. Instead of writing raw SQL, you work with typed models and a fluent query API. Tools like Prisma, Drizzle, and TypeORM all follow this pattern.
In this tutorial, you will build a complete ORM from scratch in TypeScript. It includes model definitions with decorators, a chainable query builder that generates SQL, a migration system for schema changes, and relationship support with hasMany and belongsTo. The database layer uses SQLite via better-sqlite3 so everything runs locally on macOS, Windows, and Linux with no external services.
The schema system describes tables, columns, and their types without decorators so it works with plain TypeScript.
Convert schema definitions and queries into raw SQL strings.
Wrap better-sqlite3 with a connection class that handles initialization and queries.
A fluent, chainable API for constructing SELECT queries.
Models represent database tables and provide CRUD operations.
Implement hasMany and belongsTo for querying related records.
Track and apply schema changes over time.
Create migrations for a blog with users and posts.
Create User and Post models with proper TypeScript interfaces.
Populate the database with sample data.
Demonstrate the full ORM API with queries, relationships, and the query builder.
Run with npx tsx src/main.ts. You will see records created, queried with the fluent builder, relationships traversed, and the raw SQL output for debugging. The ORM handles table creation, parameterized queries, and type safety throughout.
From here, you could add eager loading to solve N+1 queries, transactions, a CLI for running migrations, or soft deletes with a deleted_at column filter.
mkdir my-orm && cd my-orm
npm init -y
npm install better-sqlite3
npm install -D typescript @types/better-sqlite3 @types/node tsx
npx tsc --init --strict --target ES2022 --module NodeNext --moduleResolution NodeNext --outDir dist// src/schema.ts
export type ColumnType = "TEXT" | "INTEGER" | "REAL" | "BOOLEAN" | "DATETIME";
export interface ColumnDefinition {
name: string;
type: ColumnType;
primaryKey?: boolean;
autoIncrement?: boolean;
nullable?: boolean;
defaultValue?: string | number | boolean | null;
unique?: boolean;
references?: { table: string; column: string };
}
export interface TableSchema {
tableName: string;
columns: ColumnDefinition[];
}
export function defineTable(tableName: string, columns: ColumnDefinition[]): TableSchema {
return { tableName, columns };
}
// Helper functions for defining columns
export function column(
name: string,
type: ColumnType,
options: Partial<ColumnDefinition> = {}
): ColumnDefinition {
return { name, type, ...options };
}
export function primaryKey(name: string = "id"): ColumnDefinition {
return { name, type: "INTEGER", primaryKey: true, autoIncrement: true };
}
export function foreignKey(
name: string,
references: { table: string; column: string }
): ColumnDefinition {
return { name, type: "INTEGER", nullable: false, references };
}
export function timestamps(): ColumnDefinition[] {
return [
{ name: "created_at", type: "DATETIME", defaultValue: "CURRENT_TIMESTAMP" },
{ name: "updated_at", type: "DATETIME", defaultValue: "CURRENT_TIMESTAMP" },
];
}// src/sql.ts
import { ColumnDefinition, TableSchema } from "./schema.js";
export function generateCreateTable(schema: TableSchema): string {
const cols = schema.columns.map((col) => {
let sql = `"${col.name}" ${col.type}`;
if (col.primaryKey) sql += " PRIMARY KEY";
if (col.autoIncrement) sql += " AUTOINCREMENT";
if (!col.nullable && !col.primaryKey) sql += " NOT NULL";
if (col.unique) sql += " UNIQUE";
if (col.defaultValue !== undefined) {
sql += ` DEFAULT ${typeof col.defaultValue === "string" ? `'${col.defaultValue}'` : col.defaultValue}`;
}
if (col.references) {
sql += ` REFERENCES "${col.references.table}"("${col.references.column}")`;
}
return sql;
});
return `CREATE TABLE IF NOT EXISTS "${schema.tableName}" (${cols.join(", ")})`;
}
export function generateInsert(
table: string,
data: Record<string, unknown>
): { sql: string; params: unknown[] } {
const keys = Object.keys(data);
const placeholders = keys.map(() => "?");
return {
sql: `INSERT INTO "${table}" (${keys.map((k) => `"${k}"`).join(", ")}) VALUES (${placeholders.join(", ")})`,
params: keys.map((k) => data[k]),
};
}
export function generateUpdate(
table: string,
data: Record<string, unknown>,
whereClause: string,
whereParams: unknown[]
): { sql: string; params: unknown[] } {
const sets = Object.keys(data).map((k) => `"${k}" = ?`);
return {
sql: `UPDATE "${table}" SET ${sets.join(", ")} WHERE ${whereClause}`,
params: [...Object.values(data), ...whereParams],
};
}
export function generateDelete(
table: string,
whereClause: string,
whereParams: unknown[]
): { sql: string; params: unknown[] } {
return {
sql: `DELETE FROM "${table}" WHERE ${whereClause}`,
params: whereParams,
};
}// src/connection.ts
import Database from "better-sqlite3";
export class Connection {
private db: Database.Database;
constructor(filepath: string = ":memory:") {
this.db = new Database(filepath);
this.db.pragma("journal_mode = WAL");
this.db.pragma("foreign_keys = ON");
}
execute(sql: string, params: unknown[] = []): Database.RunResult {
return this.db.prepare(sql).run(...params);
}
query<T = Record<string, unknown>>(sql: string, params: unknown[] = []): T[] {
return this.db.prepare(sql).all(...params) as T[];
}
queryOne<T = Record<string, unknown>>(sql: string, params: unknown[] = []): T | undefined {
return this.db.prepare(sql).get(...params) as T | undefined;
}
close(): void {
this.db.close();
}
getDb(): Database.Database {
return this.db;
}
}// src/query-builder.ts
import { Connection } from "./connection.js";
interface WhereCondition {
clause: string;
params: unknown[];
}
export class QueryBuilder<T = Record<string, unknown>> {
private table: string;
private conn: Connection;
private selectColumns: string[] = ["*"];
private whereClauses: WhereCondition[] = [];
private orderByColumns: string[] = [];
private limitValue: number | null = null;
private offsetValue: number | null = null;
private joinClauses: string[] = [];
constructor(table: string, conn: Connection) {
this.table = table;
this.conn = conn;
}
select(...columns: string[]): this {
this.selectColumns = columns;
return this;
}
where(column: string, operator: string, value: unknown): this {
this.whereClauses.push({ clause: `"${column}" ${operator} ?`, params: [value] });
return this;
}
whereIn(column: string, values: unknown[]): this {
const placeholders = values.map(() => "?").join(", ");
this.whereClauses.push({ clause: `"${column}" IN (${placeholders})`, params: values });
return this;
}
whereNull(column: string): this {
this.whereClauses.push({ clause: `"${column}" IS NULL`, params: [] });
return this;
}
whereNotNull(column: string): this {
this.whereClauses.push({ clause: `"${column}" IS NOT NULL`, params: [] });
return this;
}
orderBy(column: string, direction: "ASC" | "DESC" = "ASC"): this {
this.orderByColumns.push(`"${column}" ${direction}`);
return this;
}
limit(n: number): this {
this.limitValue = n;
return this;
}
offset(n: number): this {
this.offsetValue = n;
return this;
}
join(table: string, leftCol: string, rightCol: string): this {
this.joinClauses.push(`JOIN "${table}" ON ${leftCol} = ${rightCol}`);
return this;
}
toSQL(): { sql: string; params: unknown[] } {
const parts: string[] = [];
const params: unknown[] = [];
parts.push(`SELECT ${this.selectColumns.join(", ")} FROM "${this.table}"`);
for (const join of this.joinClauses) parts.push(join);
if (this.whereClauses.length > 0) {
const conditions = this.whereClauses.map((w) => w.clause).join(" AND ");
parts.push(`WHERE ${conditions}`);
for (const w of this.whereClauses) params.push(...w.params);
}
if (this.orderByColumns.length > 0) {
parts.push(`ORDER BY ${this.orderByColumns.join(", ")}`);
}
if (this.limitValue !== null) parts.push(`LIMIT ${this.limitValue}`);
if (this.offsetValue !== null) parts.push(`OFFSET ${this.offsetValue}`);
return { sql: parts.join(" "), params };
}
execute(): T[] {
const { sql, params } = this.toSQL();
return this.conn.query<T>(sql, params);
}
first(): T | undefined {
this.limitValue = 1;
const { sql, params } = this.toSQL();
return this.conn.queryOne<T>(sql, params);
}
count(): number {
this.selectColumns = ["COUNT(*) as count"];
const result = this.conn.queryOne<{ count: number }>(this.toSQL().sql, this.toSQL().params);
return result?.count ?? 0;
}
}// src/model.ts
import { Connection } from "./connection.js";
import { TableSchema } from "./schema.js";
import { QueryBuilder } from "./query-builder.js";
import { generateCreateTable, generateInsert, generateUpdate, generateDelete } from "./sql.js";
export class Model<T extends Record<string, unknown>> {
constructor(
protected schema: TableSchema,
protected conn: Connection
) {}
createTable(): void {
this.conn.execute(generateCreateTable(this.schema));
}
query(): QueryBuilder<T> {
return new QueryBuilder<T>(this.schema.tableName, this.conn);
}
findById(id: number): T | undefined {
return this.query().where("id", "=", id).first();
}
findAll(): T[] {
return this.query().execute();
}
create(data: Omit<T, "id" | "created_at" | "updated_at">): T {
const { sql, params } = generateInsert(this.schema.tableName, data as Record<string, unknown>);
const result = this.conn.execute(sql, params);
return this.findById(Number(result.lastInsertRowid)) as T;
}
update(id: number, data: Partial<T>): T | undefined {
const { sql, params } = generateUpdate(
this.schema.tableName,
{ ...(data as Record<string, unknown>), updated_at: new Date().toISOString() },
'"id" = ?',
[id]
);
this.conn.execute(sql, params);
return this.findById(id);
}
delete(id: number): boolean {
const { sql, params } = generateDelete(this.schema.tableName, '"id" = ?', [id]);
const result = this.conn.execute(sql, params);
return result.changes > 0;
}
}// src/relationships.ts
import { Connection } from "./connection.js";
import { QueryBuilder } from "./query-builder.js";
export function hasMany<T>(
conn: Connection,
relatedTable: string,
foreignKey: string,
parentId: number
): QueryBuilder<T> {
return new QueryBuilder<T>(relatedTable, conn).where(foreignKey, "=", parentId);
}
export function belongsTo<T>(
conn: Connection,
relatedTable: string,
foreignKeyValue: number
): T | undefined {
return new QueryBuilder<T>(relatedTable, conn).where("id", "=", foreignKeyValue).first();
}
// Mixin for models with relationships
export function withRelations<T extends Record<string, unknown>>(conn: Connection) {
return {
hasMany<R>(table: string, foreignKey: string, parentId: number): R[] {
return hasMany<R>(conn, table, foreignKey, parentId).execute();
},
belongsTo<R>(table: string, foreignKeyValue: number): R | undefined {
return belongsTo<R>(conn, table, foreignKeyValue);
},
};
}// src/migrations.ts
import { Connection } from "./connection.js";
export interface Migration {
name: string;
up(conn: Connection): void;
down(conn: Connection): void;
}
export class MigrationRunner {
constructor(private conn: Connection) {
this.conn.execute(`
CREATE TABLE IF NOT EXISTS "_migrations" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL UNIQUE,
"applied_at" DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
}
getApplied(): string[] {
const rows = this.conn.query<{ name: string }>(
'SELECT "name" FROM "_migrations" ORDER BY "id"'
);
return rows.map((r) => r.name);
}
migrate(migrations: Migration[]): void {
const applied = new Set(this.getApplied());
for (const migration of migrations) {
if (applied.has(migration.name)) continue;
console.log(`Applying migration: ${migration.name}`);
migration.up(this.conn);
this.conn.execute('INSERT INTO "_migrations" ("name") VALUES (?)', [migration.name]);
}
}
rollback(migrations: Migration[], steps: number = 1): void {
const applied = this.getApplied();
const toRollback = applied.slice(-steps).reverse();
for (const name of toRollback) {
const migration = migrations.find((m) => m.name === name);
if (!migration) {
console.warn(`Migration ${name} not found`);
continue;
}
console.log(`Rolling back: ${name}`);
migration.down(this.conn);
this.conn.execute('DELETE FROM "_migrations" WHERE "name" = ?', [name]);
}
}
}// src/migrations/001-create-users.ts
import { Migration } from "../migrations.js";
export const createUsers: Migration = {
name: "001-create-users",
up(conn) {
conn.execute(`
CREATE TABLE "users" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL,
"email" TEXT NOT NULL UNIQUE,
"created_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
"updated_at" DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
},
down(conn) {
conn.execute('DROP TABLE IF EXISTS "users"');
},
};
export const createPosts: Migration = {
name: "002-create-posts",
up(conn) {
conn.execute(`
CREATE TABLE "posts" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" TEXT NOT NULL,
"body" TEXT NOT NULL,
"published" INTEGER NOT NULL DEFAULT 0,
"user_id" INTEGER NOT NULL REFERENCES "users"("id"),
"created_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
"updated_at" DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
},
down(conn) {
conn.execute('DROP TABLE IF EXISTS "posts"');
},
};// src/models/user.ts
import { Model } from "../model.js";
import { Connection } from "../connection.js";
import { defineTable, primaryKey, column, timestamps } from "../schema.js";
import { hasMany } from "../relationships.js";
export interface User {
id: number;
name: string;
email: string;
created_at: string;
updated_at: string;
}
const userSchema = defineTable("users", [
primaryKey(),
column("name", "TEXT"),
column("email", "TEXT", { unique: true }),
...timestamps(),
]);
export class UserModel extends Model<User> {
constructor(conn: Connection) {
super(userSchema, conn);
}
posts(userId: number): import("../query-builder.js").QueryBuilder<import("./post.js").Post> {
return hasMany(this.conn, "posts", "user_id", userId);
}
}// src/models/post.ts
import { Model } from "../model.js";
import { Connection } from "../connection.js";
import { defineTable, primaryKey, column, foreignKey, timestamps } from "../schema.js";
import { belongsTo } from "../relationships.js";
import { User } from "./user.js";
export interface Post {
id: number;
title: string;
body: string;
published: number;
user_id: number;
created_at: string;
updated_at: string;
}
const postSchema = defineTable("posts", [
primaryKey(),
column("title", "TEXT"),
column("body", "TEXT"),
column("published", "BOOLEAN", { defaultValue: 0 }),
foreignKey("user_id", { table: "users", column: "id" }),
...timestamps(),
]);
export class PostModel extends Model<Post> {
constructor(conn: Connection) {
super(postSchema, conn);
}
author(post: Post): User | undefined {
return belongsTo<User>(this.conn, "users", post.user_id);
}
published(): Post[] {
return this.query().where("published", "=", 1).orderBy("created_at", "DESC").execute();
}
}// src/seed.ts
import { Connection } from "./connection.js";
import { MigrationRunner } from "./migrations.js";
import { createUsers, createPosts } from "./migrations/001-create-users.js";
import { UserModel } from "./models/user.js";
import { PostModel } from "./models/post.js";
const conn = new Connection("./blog.db");
const runner = new MigrationRunner(conn);
runner.migrate([createUsers, createPosts]);
const users = new UserModel(conn);
const posts = new PostModel(conn);
const alice = users.create({ name: "Alice", email: "alice@example.com" });
const bob = users.create({ name: "Bob", email: "bob@example.com" });
posts.create({ title: "Hello World", body: "My first post!", published: 1, user_id: alice.id });
posts.create({
title: "TypeScript Tips",
body: "Use strict mode.",
published: 1,
user_id: alice.id,
});
posts.create({ title: "Draft Post", body: "Work in progress.", published: 0, user_id: alice.id });
posts.create({ title: "Bob's Post", body: "Hello from Bob!", published: 1, user_id: bob.id });
console.log("Seeded database.");
conn.close();// src/main.ts
import { Connection } from "./connection.js";
import { MigrationRunner } from "./migrations.js";
import { createUsers, createPosts } from "./migrations/001-create-users.js";
import { UserModel } from "./models/user.js";
import { PostModel } from "./models/post.js";
const conn = new Connection("./blog.db");
const runner = new MigrationRunner(conn);
runner.migrate([createUsers, createPosts]);
const users = new UserModel(conn);
const posts = new PostModel(conn);
// Create records
const alice = users.create({ name: "Alice", email: "alice@example.com" });
console.log("Created user:", alice);
const post = posts.create({
title: "Hello ORM",
body: "Built from scratch!",
published: 1,
user_id: alice.id,
});
console.log("Created post:", post);
// Query builder
const published = posts
.query()
.where("published", "=", 1)
.orderBy("created_at", "DESC")
.limit(10)
.execute();
console.log("Published posts:", published);
// Relationships
const alicePosts = users.posts(alice.id).execute();
console.log("Alice's posts:", alicePosts);
const author = posts.author(post);
console.log("Post author:", author);
// Update
users.update(alice.id, { name: "Alice Updated" });
// Count
const total = posts.query().count();
console.log("Total posts:", total);
// Generated SQL for debugging
const { sql, params } = posts
.query()
.select("title", "body")
.where("published", "=", 1)
.whereNotNull("title")
.orderBy("created_at", "DESC")
.limit(5)
.toSQL();
console.log("Generated SQL:", sql);
console.log("Parameters:", params);
conn.close();