💡 forge-sql-orm: Type-safe ORM for @forge/sql based on Drizzle

Are you working with @forge/sql and tired of writing raw SQL with manual typings?

forge-sql-orm brings a structured, type-safe, and developer-friendly experience using Drizzle ORM — fully compatible with Forge’s infrastructure.

Hey everyone! 👋

I'd like to introduce forge-sql-orm — a type-safe ORM designed for working with the Atlassian Forge platform using @forge/sql, built on top of Drizzle ORM.

🔍 Why forge-sql-orm?

🗋 Atlassian provides @forge/sql...

...but you still have to write SQL queries manually and manage typings yourself:

import sql from '@forge/sql';

interface City {
  name: string;
  state?: string;
  country: string;
};

const results = await sql
  .prepare<City>(`SELECT * FROM cities WHERE name = ?`)
  .bindParams('New York')
  .execute();

console.log(results.rows[0].country);

⛔️ You must manually define interfaces, manage aliasing, and ensure column matching for joins.

⚠️ Also, prepare() does not guarantee that the returned SQL data actually matches the City interface — it's just a TypeScript hint, not runtime-validated.

📝 While this works fine for simple queries, it becomes problematic when:

  • Fields are selected dynamically (e.g., from frontend input),
  • The structure of the result changes conditionally,
  • You join multiple tables that may share the same column names. 
SELECT users.name, company.name ... -- which 'name' is which?

You end up needing:

SELECT users.name AS users_name, company.name AS company_name

And then you must manually write:

.prepare<{ users_name: string; company_name: string }>()

🙄 It's verbose and hard to maintain.

✅ forge-sql-orm solves this in 2 ways:

1. Automatically via forgeSQL.select({...}) 

import ForgeSQL from "forge-sql-orm";
const forgeSQL = new ForgeSQL();

const result = await forgeSQL
  .select({ user: users, order: orders })
  .from(orders)
  .innerJoin(users, eq(orders.userId, users.id));

🌟 Columns are aliased by object keys (user.name, order.id) and typed safely.

2. Manually via selectAliased(...) 

import { drizzle } from "drizzle-orm/mysql-proxy";
import { forgeDriver, patchDbWithSelectAliased } from "forge-sql-orm";

const db = patchDbWithSelectAliased(drizzle(forgeDriver()));

const result = await db
  .selectAliased({ user: users, order: orders })
  .from(orders)
  .innerJoin(users, eq(orders.userId, users.id));

📌 Ideal for those working directly with Drizzle's low-level API.

📐 Schema First Migration Strategy

forge-sql-orm and forge-sql-orm-cli follow a Schema First approach — where your database schema is the single source of truth, rather than relying on manually written model definitions.

This means you have two paths to get started:

  • If you already have a working Forge SQL database, you can extract the schema directly from it.
  • Or, if you're starting fresh, you can create a brand-new local or shared MySQL database and define your schema manually.

🧠 What does that mean?

  • You define your tables using SQL or existing Forge migrations.
  • You can extract your current schema from Forge SQL and apply it to a local environment.
  • forge-sql-orm-cli then generates Drizzle models based on the actual schema — not assumptions.

✅ Bonus: it works with any MySQL-compatible database, not just Forge SQL. That means you can use a shared or local dev DB as your source of truth.

This is especially useful if:

  • You already have an existing Forge SQL project with migrations.
  • You want to migrate to a type-safe ORM layer.
  • You work in a team and want consistent, reliable database structure.

🌐 Extracting schema from a Forge app 

import { fetchSchemaWebTrigger } from "forge-sql-orm";

export const fetchSchema = async () => {
  return fetchSchemaWebTrigger();
};

manifest.yml configuration:

webtrigger:
  - key: fetch-schema
    function: fetchSchema
sql:
  - key: main
    engine: mysql
function:
  - key: fetchSchema
    handler: index.fetchSchema

Example output:

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS users (...);
CREATE TABLE IF NOT EXISTS orders (...);
SET foreign_key_checks = 1;

🧪 You can apply this script to a local database and run:

npx forge-sql-orm-cli generate:model

🏗️ Defining a Schema from Scratch

Alternatively, you don't need to start with a Forge SQL database at all:

  • You can create a brand-new local (or shared) MySQL database,
  • Define your schema from scratch or apply an existing SQL script,
  • Then use it as your reference schema for generating models and migrations.

This makes forge-sql-orm ideal for greenfield projects or building outside-in from an existing backend architecture.

To create an initial migration:

npx forge-sql-orm-cli migrations:create

To generate follow-up migrations after schema changes:

npx forge-sql-orm-cli migrations:update
npx forge-sql-orm-cli generate:model

🐳 Docker Example for Local Database 

export MYSQL_ROOT_PASSWORD=admin

docker run -d \
  --name forge-sql-orm-example-db \
  -p 3366:3306 \
  -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
  --security-opt seccomp=unconfined \
  --restart=always \
  mysql

# Wait 30 seconds, then:
docker exec forge-sql-orm-example-db \
  mysql -uroot -padmin -e "create database forgesqlorm"

🧬 UUID as Primary Key (with VARBINARY(16))

Although Atlassian’s examples often use AUTO_INCREMENT for primary keys, TiDB documentation recommends UUIDs.

✅ Why UUID?

  • Better support for distributed systems.
  • Lower chance of key conflicts.
  • More scalable for indexing.
  • When using UUID_TO_BIN, binary UUIDs reduce storage size and improve index performance.
  • Using UUID_TO_BIN(uuid, 1) sorts UUIDs by timestamp, enhancing insertion order and avoiding random I/O bottlenecks.

Recommended pattern:

id VARBINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID()))

To implement UUIDs with varbinary(16) in Drizzle:

export const uuidBinary = customType<{
  data: string;
  driverData: { type: "Buffer"; data: number[] };
  config: [];
}>({
  dataType() {
    return "varbinary(16)";
  },
  toDriver(value) {
    return sql`UUID_TO_BIN(${value})`;
  },
  fromDriver(value) {
    const buffer = Buffer.from(value.data);
    return uuidStringify(new Uint8Array(buffer));
  },
});

Usage:

export const userStatus = mysqlTable("user_status", {
  id: uuidBinary().notNull(),
  name: varchar({ length: 100 }).notNull(),
});

🧠 TiDB also supports UUID_TO_BIN(uuid, 1) to reorder bits and improve clustering/indexing.

🔒 Optimistic Locking Support

To prevent race conditions when multiple users edit the same record, forge-sql-orm supports optimistic locking using a version column.

  • On crud().insert(), if a version field exists:

    • If it's a number, it defaults to 1.
    • If it's a date/time, it's set to current timestamp.

  • On crud().updateById():

    • The version is incremented or updated.
    • The version is included in the WHERE clause to ensure no conflicts.

This helps avoid overwriting someone else's changes by accident.

⚠️ This logic is not available if you use .getDrizzleQueryBuilder().insert(...) — that API is lower-level and does not handle version automatically.

🧪 Insert Examples 

// Single insert
const userId = await forgeSQL.crud().insert(users, [{ id: 1, name: "Smith" }]);

// Bulk insert
await forgeSQL.crud().insert(users, [
  { id: 2, name: "Smith" },
  { id: 3, name: "Vasyl" },
]);

📦 Quick Start 

npm install forge-sql-orm @forge/sql drizzle-orm moment -S
npm install forge-sql-orm-cli -D

🔹 Generate migration:

npx forge-sql-orm-cli migrations:create

🔹 Generate models:

npx forge-sql-orm-cli generate:model

🔹 Query example:

import ForgeSQL from "forge-sql-orm";

const forgeSQL = new ForgeSQL();

const orderWithUser = await forgeSQL
  .select({ user: users, order: orders })
  .from(orders)
  .innerJoin(users, eq(orders.userId, users.id));

💬 Feedback

Give it a try and let me know what you think!

If you're already using @forge/sql and want to level up your developer experience with type safety and migrations, I’d love to hear your feedback.

⭐️ Star the repo on GitHub: vzakharchenko/forge-sql-orm

🧑‍💻 PRs, issues, and questions are welcome — or just drop a comment below!