Build an ORM from Scratch

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();