forge-sql-orm: Type-safe ORM for @forge/sql based on Drizzle
Are you working with
@forge/sqland tired of writing raw SQL with manual typings?
forge-sql-ormbrings 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<City>() 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.
Built 100% on Official Forge SQL API
forge-sql-orm is built entirely on the official, documented Forge SQL API:
Atlassian Forge SQL Reference
- No undocumented features or internal hacks are used.
- All queries run through
@forge/sql.prepare(...)=> bindParams(…) => execute(). - Compatible with the current Forge runtime and safe to use within your app boundaries.
You can safely adopt this tool in your Forge project, knowing it relies only on what Atlassian supports.
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-clithen 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 aversionfield exists:- If it’s a number, it defaults to
1. - If it’s a date/time, it’s set to current timestamp.
- If it’s a number, it defaults to
-
On
crud().updateById():- The version is incremented or updated.
- The version is included in the
WHEREclause 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!
Important — SQL Injection Risk with 
Be careful — this usage does not use bind parameters for 
