# Introducing forge-sql-orm: Type-Safe ORM for @forge/sql Using Drizzle

**URL:** <https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956>\
**Category:** Forge Platform and Tools\
**Tags:** database, open-source, forge-sql, forge\
**Created:** [April 6, 2025, 12:22am UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956 "2025-04-06T00:22:13Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![vzakharchenko](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/vzakharchenko/32/53609_2.png) [@vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko)\
**Post date:** [April 6, 2025, 12:22am UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/1 "2025-04-06T00:22:13Z")

</div>

## 💡 `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`](https://github.com/vzakharchenko/forge-sql-orm) — a type-safe ORM designed for working with the Atlassian Forge platform using `@forge/sql`, built on top of [Drizzle ORM](https://orm.drizzle.team/).

* * *

### 🔍 Why `forge-sql-orm`?

#### 🗋 Atlassian provides `@forge/sql`…

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

```ts
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.

```sql
SELECT users.name, company.name ... -- which 'name' is which?

```

You end up needing:

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

```

And then you must manually write:

```ts
.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({...})`

```ts
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(...)`

```ts
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](https://developer.atlassian.com/platform/forge/storage-reference/sql-api/)

- 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-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

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

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

```

`manifest.yml` configuration:

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

```

Example output:

```sql
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:

```bash
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:

```bash
npx forge-sql-orm-cli migrations:create

```

To generate follow-up migrations after schema changes:

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

```

* * *

### 🐳 Docker Example for Local Database

```bash
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](https://docs.pingcap.com/tidb/stable/uuid/) 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:

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

```

To implement UUIDs with `varbinary(16)` in Drizzle:

```ts
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:

```ts
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:

- On `crud().updateById()`:

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

```ts
// 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

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

```

🔹 Generate migration:

```bash
npx forge-sql-orm-cli migrations:create

```

🔹 Generate models:

```bash
npx forge-sql-orm-cli generate:model

```

🔹 Query example:

```ts
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](https://github.com/vzakharchenko/forge-sql-orm)

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

---

<div class="post-metadata">

**Author:** ![vzakharchenko](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/vzakharchenko/32/53609_2.png) [@vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko)\
**Post date:** [April 6, 2025, 11:07am UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/2 "2025-04-06T11:07:15Z")

</div>

### 💡 Pagination with `COUNT(*) OVER()` and Window Functions in Forge SQL

Since **Forge SQL is built on TiDB** , which supports **MySQL-compatible window functions** , you can simplify pagination logic with a single query instead of two.

Instead of doing this:

- Query 1: `SELECT * FROM users LIMIT 10 OFFSET 20`
- Query 2: `SELECT COUNT(*) FROM users`

You can do:

```sql
SELECT 
  id,
  name,
  COUNT(*) OVER() AS total_count
FROM users
ORDER BY name
LIMIT 10 OFFSET 20;

```

🧠 This returns a paginated result set where each row includes `total_count`, useful for frontend pagination components.

* * *

### ✅ Example in `forge-sql-orm`

```ts
import { sql } from "drizzle-orm";
import ForgeSQL from "forge-sql-orm";
import { users } from "./schema";

const forgeSQL = new ForgeSQL();

const result = await forgeSQL
  .select({
    id: users.id,
    name: users.name,
    totalCount: sql<number>`COUNT(*) OVER()`,
  })
  .from(users)
  .orderBy(users.name)
  .offset(formatLimitOffset(offset))
  .limit(formatLimitOffset(limit));

```

* * *

### ⚠ Caveat: Forge SQL does **not** support parameterized `LIMIT`/`OFFSET`

By default, Drizzle uses parameterized queries like:

```sql
SELECT ... FROM users ORDER BY name LIMIT ? OFFSET ?

```

But **Forge SQL does not support that** — it throws an error.

So you must pass values inline. To do it safely and avoid SQL injection:

```ts
export function formatLimitOffset(limitOrOffset: number): number {
  if (typeof limitOrOffset !== "number" || isNaN(limitOrOffset)) {
    throw new Error("limitOrOffset must be a valid number");
  }
  return sql.raw(`${limitOrOffset}`) as unknown as number;
}

```

Then use:

```ts
.offset(formatLimitOffset(offset))
.limit(formatLimitOffset(limit))

```

* * *

### 🔐 Important — SQL Injection Risk with `@forge/sql.prepare(...)`

Even if you’re using `@forge/sql` with `.prepare(...)`, like this:

```ts
const result = await sql
  .prepare<User>(`SELECT * FROM users LIMIT ${limit} OFFSET ${offset}`)
  .execute();

```

🛑 Be careful — this usage **does not use bind parameters** for `LIMIT` and `OFFSET`.  
When you write `${limit}` and `${offset}` inside the template string, they are directly inlined into the SQL query — not passed as safe bind values.

This means:

- If `limit` or `offset` come from user input (e.g. frontend query params),
- And you **don’t validate them properly** ,
- You’re at risk of a **SQL injection** , even when using `.prepare()`.

Also:  
Even if TypeScript says `limit: number`, that doesn’t mean it’s a number at runtime. A malicious user could still pass a string or invalid input.

✅ Always sanitize values like this:

```ts
function safeLimitOffset(value: unknown): number {
  if (typeof value !== "number" || isNaN(value)) {
    throw new Error("Invalid limit or offset");
  }
  return value;
}

const result = await sql
  .prepare<User>(
    `SELECT * FROM users LIMIT ${safeLimitOffset(limit)} OFFSET ${safeLimitOffset(offset)}`
  )
  .execute();

```

---

<div class="post-metadata">

**Author:** ![varun](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/varun/32/38255_2.png) [@varun](https://community.developer.atlassian.com/u/varun)\
**Post date:** [April 8, 2025, 1:24am UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/3 "2025-04-08T01:24:22Z")

</div>

This is excellent work. Thank you for authoring and sharing this.

The API has been designed to work with HTTP based drivers and we are glad to see this use case 🙂

Until recently, drizzle had a [peer-dependency on react](https://github.com/drizzle-team/drizzle-orm/issues/2251) that created incompatibility related errors with Forge. This has since been resolved, so we invite you (and the broader community) to share feedback with the ORM.

We are continuously evolving our SQL (and other storage) offerings. Please let us know if you have any specific requests/feedback. We’d love to hear more from the community!

–  
Varun

---

<div class="post-metadata">

**Author:** ![tbinna](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/tbinna/32/35870_2.png) [@tbinna](https://community.developer.atlassian.com/u/tbinna)\
**Post date:** [April 8, 2025, 9:15am UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/4 "2025-04-08T09:15:14Z")

</div>

@varun, I appreciate your comment and involvement in the dev community. Could you clarify on [Forge SQL: Enable sql.storageApi again for ORM usage](https://community.developer.atlassian.com/t/forge-sql-enable-sql-storageapi-again-for-orm-usage/88631) if the React dependency has something to do with the removal of `storageAPI`? I like to understand if this was intentional and if there were any announcements about the change. Thanks!

---

<div class="post-metadata">

**Author:** ![vzakharchenko](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/vzakharchenko/32/53609_2.png) [@vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko)\
**Post date:** [April 8, 2025, 2:20pm UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/5 "2025-04-08T14:20:24Z")

</div>

Hi Varun, thanks a lot for the kind words and support — it really means a lot!

I wasn’t affected by the Drizzle + Forge issue myself — I started using Drizzle when it was already resolved,  
but it’s great to know the path is clearer for everyone else now.

* * *

### Feedback from working with Forge SQL:

1. **Timeout-sensitive migrations**  
If a migration contains many steps or involves heavier `ALTER TABLE` operations, there’s a risk of hitting Forge web/scheduler timeouts.  
This can lead to `ALTER TABLE` being successfully applied, but the corresponding insert into the migration log never completing — causing the next run to retry the same schema change and fail with an error like “column already exists.”
2. **Concurrent migration execution**  
There’s currently no way to guarantee that only one migration process runs at a time.  
If multiple web triggers, event triggers, or scheduled tasks execute concurrently, they may all try to apply pending migrations — leading to race conditions or conflicting DDL operations.  
One potential direction could be support for SQL-level locking via `GET_LOCK(...)` / `RELEASE_LOCK(...)` (which TiDB supports),  
or perhaps another mechanism for coordinating safe execution — like using `app.storage` as a lightweight lock.

At the moment, these types of coordination issues (timeouts, concurrent execution) are left for developers to handle themselves at the application level.  
It would be great to see Forge offer some built-in primitives or patterns for safe, idempotent orchestration across triggers and scheduler functions.

Thanks again for listening!

— Vasiliy

---

<div class="post-metadata">

**Author:** ![vzakharchenko](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/vzakharchenko/32/53609_2.png) [@vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko)\
**Post date:** [April 8, 2025, 2:34pm UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/6 "2025-04-08T14:34:29Z")

</div>

Hi! Just to clarify — `@forge/sql` works perfectly fine with Drizzle ORM **without** relying on the deprecated `sql.storageApi`.  
You can check out how it’s implemented in my project — it works with the latest version of `@forge/sql`.

Here’s the custom Forge driver used for `drizzle-orm/mysql-proxy`:  
👉 [forge-sql-orm/src/utils/forgeDriver.ts](https://github.com/vzakharchenko/forge-sql-orm/blob/master/src/utils/forgeDriver.ts)

Feel free to explore or adapt it!

---

<div class="post-metadata">

**Author:** ![varun](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/varun/32/38255_2.png) [@varun](https://community.developer.atlassian.com/u/varun)\
**Post date:** [April 14, 2025, 12:52pm UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/7 "2025-04-14T12:52:50Z")

</div>

> [@vzakharchenko](#):
>
> If a migration contains many steps or involves heavier `ALTER TABLE` operations, there’s a risk of hitting Forge web/scheduler timeouts.  
> This can lead to `ALTER TABLE` being successfully applied, but the corresponding insert into the migration log never completing — causing the next run to retry the same schema change and fail with an error like “column already exists.”

Thanks for sharing feedback on the limitations of timeout and running migrations. There’s a community post/RFC coming soon on how we want to move away from triggers and, potentially, manage running migrations using industry standard migration runners. This will alleviate the problems with timeouts and concurrent migration runs.

We may still provide the DDL endpoint; however we see most migration runs happening via developer actions or when the app is installed.

---

<div class="post-metadata">

**Author:** ![varun](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/varun/32/38255_2.png) [@varun](https://community.developer.atlassian.com/u/varun)\
**Post date:** [April 14, 2025, 12:55pm UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/8 "2025-04-14T12:55:56Z")

</div>

Apologies for the delayed response, but yeah as [vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko) mentions above, this is not affected by `storageAPI`

The library wraps over the Forge SQL SDK methods (which in turn call REST endpoints) and execute the statement that drizzle provides.

---

<div class="post-metadata">

**Author:** ![vzakharchenko](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/vzakharchenko/32/53609_2.png) [@vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko)\
**Post date:** [April 17, 2025, 2:37pm UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/9 "2025-04-17T14:37:52Z")

</div>

> [@varun](#):
>
> Thanks for sharing feedback on the limitations of timeout and running migrations. There’s a community post/RFC coming soon on how we want to move away from triggers and, potentially, manage running migrations using industry standard migration runners. This will alleviate the problems with timeouts and concurrent migration runs.
> 
> We may still provide the DDL endpoint; however we see most migration runs happening via developer actions or when the app is installed.

Thanks! We’re definitely looking forward to the upcoming support for managing migrations using industry-standard migration runners — that sounds like a great improvement, especially considering the current limitations with timeouts and concurrent migration runs.

I also wanted to share a use case where DDL access is essential:

Let’s say the app has entities created via migrations. At some point, **end-users** may want to **customize those entities** — for example, by adding their own fields — while keeping the original migration structure intact. And at the same time, they still want to **query, filter, and sort** based on both standard and custom fields.

One way to achieve this is by creating a **separate table via `CREATE TABLE`** , where the primary key matches the one from the original entity (`1:1` relationship). As end-users define new fields, we can use `ALTER TABLE ADD COLUMN`. These tables can then be joined using `LEFT JOIN` on the primary key, enabling seamless access to both base and custom data.

An alternative would be to store custom fields in a JSON column within the main table. While this provides flexibility, it comes with several downsides:

- You **can’t add indexes** on individual JSON fields, making queries involving filtering and sorting **much slower**.
- Queries become **harder to write, read, and maintain** , since they rely on functions like `json_extract`.
- You lose **type safety and schema validation** at the ORM level, which limits the benefits of using a type-safe ORM like Drizzle.

In contrast, the `1:1` table approach allows for:

- Indexing on custom fields,
- Better **memory and performance efficiency** compared to storing a large number of custom fields in a single JSON blob.

So while most use cases are covered by standard migrations, having runtime DDL access opens up powerful patterns for advanced customization that are otherwise hard to implement efficiently.

---

<div class="post-metadata">

**Author:** ![vzakharchenko](https://sea1.discourse-cdn.com/atlassiandeveloper/user_avatar/community.developer.atlassian.com/vzakharchenko/32/53609_2.png) [@vzakharchenko](https://community.developer.atlassian.com/u/vzakharchenko)\
**Post date:** [April 22, 2026, 8:44am UTC](https://community.developer.atlassian.com/t/introducing-forge-sql-orm-type-safe-orm-for-forge-sql-using-drizzle/90956/10 "2026-04-22T08:44:17Z")

</div>

Since its introduction, the goal of [forge-sql-orm](https://github.com/forge-sql-orm/forge-sql-orm) has been to provide a **“no-magic,” “type-safe”** experience for developers using [@forge/sql](https://developer.atlassian.com/platform/forge/storage-reference/sql/). As the Forge platform has matured, the library has evolved to solve the most critical technical boundaries: the 5-second query timeout and the 16MB memory limit.

# Major Milestones in the Evolution

## Advanced SQL Optimization

Handling large-scale data in Forge requires more than just basic queries. The library enables developers to leverage tools like EXPLAIN and EXPLAIN ANALYZE to identify bottlenecks in complex joins and pagination over hundreds of thousands of rows.

- _Atlassian Blog:_ [Optimizing Forge SQL on a 600K database with TiDB EXPLAIN](https://www.atlassian.com/blog/developer/optimizing-forge-sql-on-a-600k-database-with-tidb-explain)

## Performance & Multi-Level Caching

When a query cannot be optimized further via SQL rewriting, the library provides the architectural layers needed to keep apps responsive. By implementing a tiered caching strategy, it effectively creates “Virtual Materialized Views” at the application layer, ensuring that expensive joins and aggregations aren’t recomputed on every request.

- **Level 1 (Local):** In-memory caching scoped to a single resolver invocation.
- **Level 2 (Global):** Persistent cross-invocation caching using @forge/kvs.
- _Deep Dive:_ [Virtual Materialized Views: A Two-Level Caching Approach](https://community.developer.atlassian.com/t/virtual-materialized-views-for-atlassian-forge-sql-a-two-level-caching-approach-in-forge-sql-orm/99616)

## Reliable Data Storage (Optimistic Locking)

Forge SQL limits developers to one query per .execute() call, making traditional multi-step transactions difficult. The library provides built-in support for **Optimistic Locking** , using version fields to prevent race conditions and accidental data overwrites in collaborative environments.

- _Atlassian Blog:_ [Reliable Data Storage Using Optimistic Locking in Forge SQL](https://www.atlassian.com/blog/developer/reliable-data-storage-using-optimistic-locking-in-forge-sql)

## Deep Observability

Forge SQL can often be a “black box” due to the lack of direct database access. The library features an observability layer to make performance measurable and transparent:

- **Automatic Query Plan Analysis:** Logs detailed EXPLAIN output for the slowest queries.
- **Post-Mortem Analysis:** If a query fails due to a **Timeout** or **OOM (Out of Memory)**, the library automatically captures the execution plan to identify the specific bottleneck.
- **Slow Query Monitoring:** Scheduled triggers to track performance regressions over time.
- _Read the Guide:_ [Practical SQL Observability for Forge Apps](https://community.developer.atlassian.com/t/practical-sql-observability-for-forge-apps-with-forge-sql-orm/97237)

## AI & Vector Search

The library provides native support for **TiDB Vector Search** , enabling semantic search—searching by meaning rather than just keywords. This serves as a foundation for Retrieval-Augmented Generation (RAG) within Forge.

- _Learn More:_ [AI Magic: Local Semantic Search with Forge SQL](https://community.developer.atlassian.com/t/ai-magic-in-atlassian-forge-local-semantic-search-with-forge-sql/100163)

# New in v2.1.27: Native Binary & BLOB Support

The latest update introduces custom types for handling binary data safely and efficiently through the Forge infrastructure.

## Defining the Schema

Binary fields are far more efficient than strings for IDs and metadata. Here is how to define a high-performance model:

```js
import { mysqlTable, varchar } from "drizzle-orm/mysql-core";
import { forgeBLOB, uuidBinary, forgeVarBinary } from "forge-sql-orm";

export const securityConfig = mysqlTable("security_config", {
  id: uuidBinary("id").primaryKey(), // High-performance Binary(16) index
  
  // Link to your Atlassian entity (ari:cloud:jira:site/...)
  contextAri: varchar("context_ari", { length: 255 }).notNull(),
  
  // Store compact encrypted metadata or serialized settings
  encryptedSettings: forgeBLOB("encrypted_settings").notNull(), 
  
  // SHA-256 hash for data integrity
  contentHash: forgeVarBinary("content_hash", { length: 32 }) 
});

```

## What actually goes to Forge?

To ensure reliability, the library prepares data specifically for the Forge SQL engine:

### Scenario 1: Optimized IDs (uuidBinary)

The library allows the use of standard UUID strings in code while maintaining a fast 16-byte binary index in the database.

- **TypeScript:**

```js
forgeSQL.selectFrom(users).where(eq(users.id, "550e8400..."))

```

- Generated SQL:

```sql
SELECT * FROM users WHERE id = UUID_TO_BIN(?)
-- Parameters: ["550e8400..."]

```

### Scenario 2: Secure BLOB Handling

Passing raw binary through the Forge bridge can cause encoding errors. The library uses a **Base64 + [FROM\_BASE64](https://docs.pingcap.com/tidb/stable/string-functions/#from_base64)** strategy to ensure data integrity.

- **TypeScript:**

```js
await forgeSQL.insert(securityConfig).values({
  encryptedSettings: Buffer.from([0xDE, 0xAD, 0xBE, 0xEF]) 
});

```

- **Generated SQL:**

```sql
INSERT INTO config (encrypted_settings) VALUES (FROM_BASE64(?))
-- Parameters: ["3q2+7w=="] (Base64 string)

```

### Automatic Deserialization

The library handles the reverse conversion automatically so you don’t have to deal with raw buffers or binary-to-string logic manually

## Summary of New Types

| **Type** | **SQL Mapping** | **Recommended Use Case** |
| --- | --- | --- |
| **uuidBinary** | BINARY(16) | Primary keys (Significant performance boost). |
| **forgeVarBinary** | VARBINARY(n) | Hashes, signatures, or encrypted tokens. |
| **forgeBLOB** | BLOB | Serialized metadata or binary configurations. |
| **forgeBinary** | BINARY(n) | Fixed-size hashes (e.g., SHA-256). |

The **forge-sql-orm** library continues to bridge the gap between high-level Developer Experience (DX) and the unique technical constraints of the Atlassian Forge platform.