Forge SQL ORM






Forge-SQL-ORM is an ORM designed for working with @forge/sql in Atlassian Forge. It is built on top of Drizzle ORM and provides advanced capabilities for working with relational databases inside Forge.

Key Features

- ✅ Custom Drizzle Driver for direct integration with @forge/sql
- ✅ Local Cache System (Level 1) for in-memory query optimization within single resolver invocation scope
- ✅ Global Cache System (Level 2) with cross-invocation caching, automatic cache invalidation and context-aware operations (using @forge/kvs )
- ✅ Performance Monitoring: Query execution metrics and analysis capabilities with automatic error analysis for timeout and OOM errors, scheduled slow query monitoring with execution plans, and async query degradation analysis for non-blocking performance monitoring
- ✅ Type-Safe Query Building: Write SQL queries with full TypeScript support
- ✅ Supports complex SQL queries with joins and filtering using Drizzle ORM
- ✅ Advanced Query Methods: selectFrom(), selectDistinctFrom(), selectCacheableFrom(), selectDistinctCacheableFrom() for all-column queries with field aliasing
- ✅ Query Execution with Metadata: executeWithMetadata() method for capturing detailed execution metrics including database execution time, response size, and query analysis capabilities with performance monitoring. Supports two modes for query plan printing: TopSlowest mode (default) and SummaryTable mode
- ✅ Raw SQL Execution: execute(), executeCacheable(), executeDDL(), and executeDDLActions() methods for direct SQL queries with local and global caching
- ✅ Common Table Expressions (CTEs): with() method for complex queries with subqueries
- ✅ Schema migration support, allowing automatic schema evolution
- ✅ Automatic entity generation from MySQL/tidb databases
- ✅ Automatic migration generation from MySQL/tidb databases
- ✅ Drop Migrations Generate a migration to drop all tables and clear migrations history for subsequent schema recreation
- ✅ Schema Fetching Development-only web trigger to retrieve current database schema and generate SQL statements for schema recreation
- ✅ Ready-to-use Migration Triggers Built-in web triggers for applying migrations, dropping tables (development-only), and fetching schema (development-only) with proper error handling and security controls
- ✅ Optimistic Locking Ensures data consistency by preventing conflicts when multiple users update the same record
- ✅ Query Plan Analysis: Detailed execution plan analysis and optimization insights
- ✅ Rovo Integration Secure pattern for natural-language analytics with comprehensive security validations, Row-Level Security (RLS) support, and dynamic SQL query execution

Table of Contents

$3

- Key Features
- Usage Approaches
- Installation
- CLI Commands | CLI Documentation
- Quick Start

$3

- Field Name Collision Prevention
- Drizzle Usage with forge-sql-orm
- Direct Drizzle Usage with Custom Driver

$3

- Fetch Data
- Modify Operations
- SQL Utilities

$3

- Setting Up Caching with @forge/kvs
- Global Cache System (Level 2)
- Cache Context Operations
- Local Cache Operations (Level 1)
- Cache-Aware Query Operations
- Manual Cache Management

$3

- Optimistic Locking
- Rovo Integration - Secure pattern for natural-language analytics with dynamic SQL queries
- Query Analysis and Performance Optimization
- Automatic Error Analysis - Automatic timeout and OOM error detection with execution plans
- Slow Query Monitoring - Scheduled monitoring of slow queries with execution plans
- Date and Time Types

$3

- CLI Commands | CLI Documentation
- Web Triggers for Migrations
- Step-by-Step Migration Workflow
- Drop Migrations

$3

- Simple Example
- Drizzle Driver Example
- Optimistic Locking Example
- Dynamic Queries Example
- Query Analysis Example
- Organization Tracker Example
- Checklist Example
- Cache Example - Advanced caching capabilities with performance monitoring
- Rovo Integration Example - Real-world Rovo AI agent implementation with secure natural-language analytics

$3

- ForgeSqlOrmOptions
- Migration Guide

🚀 Quick Navigation

New to Forge-SQL-ORM? Start here:

- Quick Start - Get up and running in 5 minutes
- Installation - Complete setup guide
- Basic Usage Examples - Simple query examples

Looking for specific features?

- Global Cache System (Level 2) - Cross-invocation persistent caching
- Local Cache System (Level 1) - In-memory invocation caching
- Optimistic Locking - Data consistency
- Rovo Integration - Secure natural-language analytics
- Migration Tools - Database migrations
- Query Analysis - Performance optimization

Looking for practical examples?

- Simple Example - Basic ORM usage
- Optimistic Locking Example - Real-world conflict handling
- Organization Tracker Example - Complex relationships
- Checklist Example - Jira integration
- Cache Example - Advanced caching capabilities
- Rovo Integration Example - Real-world Rovo AI agent with secure analytics

Usage Approaches

$3

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

Best for: Advanced features like optimistic locking, automatic versioning, and automatic field name collision prevention in complex queries.

$3

`typescript
import { drizzle } from "drizzle-orm/mysql-proxy";
import { forgeDriver } from "forge-sql-orm";
const db = drizzle(forgeDriver);
`

Best for: Simple Modify operations without optimistic locking. Note that you need to manually patch drizzle patchDbWithSelectAliased for select fields to prevent field name collisions in Atlassian Forge SQL.

$3

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

// Optimize repeated queries within a single invocation
await forgeSQL.executeWithLocalContext(async () => {
// Multiple queries here will benefit from local caching
const users = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

// This query will use local cache (no database call)
const cachedUsers = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

// Using new methods for better performance
const usersFrom = await forgeSQL.selectFrom(users).where(eq(users.active, true));

// This will use local cache (no database call)
const cachedUsersFrom = await forgeSQL.selectFrom(users).where(eq(users.active, true));

// Raw SQL with local caching
const rawUsers = await forgeSQL.execute("SELECT id, name FROM users WHERE active = ?", [true]);
});
`

Best for: Performance optimization of repeated queries within resolvers or single invocation contexts.

Field Name Collision Prevention in Complex Queries

When working with complex queries involving multiple tables (joins, inner joins, etc.), Atlassian Forge SQL has a specific behavior where fields with the same name from different tables get collapsed into a single field with a null value. This is not a Drizzle ORM issue but rather a characteristic of Atlassian Forge SQL's behavior.

Forge-SQL-ORM provides two ways to handle this:

$3

`typescript
import ForgeSQL from "forge-sql-orm";

const forgeSQL = new ForgeSQL();

// Automatic field name collision prevention
await forgeSQL
.select({ user: users, order: orders })
.from(orders)
.innerJoin(users, eq(orders.userId, users.id));
`

$3

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

const db = patchDbWithSelectAliased(drizzle(forgeDriver));

// Manual field name collision prevention
await db
.selectAliased({ user: users, order: orders })
.from(orders)
.innerJoin(users, eq(orders.userId, users.id));
`

$3

- This is a specific behavior of Atlassian Forge SQL, not Drizzle ORM
- For complex queries involving multiple tables, it's recommended to always specify select fields and avoid using select() without field selection
- The solution automatically creates unique aliases for each field by prefixing them with the table name
- This ensures that fields with the same name from different tables remain distinct in the query results

Installation

Forge-SQL-ORM is designed to work with @forge/sql and requires some additional setup to ensure compatibility within Atlassian Forge.

✅ Step 1: Install Dependencies

Basic installation (without caching):

`sh
npm install forge-sql-orm @forge/sql drizzle-orm -S
`

With caching support:

`sh
npm install forge-sql-orm @forge/sql @forge/kvs drizzle-orm -S
`

⚠️ Important for UI-Kit projects:

If you're installing forge-sql-orm in a UI-Kit project (projects using @forge/react), you may encounter peer dependency conflicts with @types/react. This is due to a conflict between @types/react@18 (required by @forge/react) and @types/react@19 (optional peer dependency from drizzle-orm via bun-types).

To resolve this, use the --legacy-peer-deps flag:

`sh


Basic installation for UI-Kit projects

npm install forge-sql-orm @forge/sql drizzle-orm -S --legacy-peer-deps

With caching support for UI-Kit projects

npm install forge-sql-orm @forge/sql @forge/kvs drizzle-orm -S --legacy-peer-deps
`

Note: The --legacy-peer-deps flag tells npm to ignore peer dependency conflicts. This is safe in this case because bun-types is an optional peer dependency and doesn't affect the functionality of forge-sql-orm in Forge environments.

This will:

- Install Forge-SQL-ORM (the ORM for @forge/sql)
- Install @forge/sql, the Forge database layer
- Install @forge/kvs, the Forge Key-Value Store for caching (optional, only needed for caching features)
- Install Drizzle ORM and its MySQL driver
- Install TypeScript types for MySQL
- Install forge-sql-orm-cli A command-line interface tool for managing Atlassian Forge SQL migrations and model generation with Drizzle ORM integration.

Quick Start

$3

`typescript
import ForgeSQL from "forge-sql-orm";

// Initialize ForgeSQL
const forgeSQL = new ForgeSQL();

// Simple query
const users = await forgeSQL.select().from(users);
`

$3

`typescript
import ForgeSQL from "forge-sql-orm";

// Initialize with caching
const forgeSQL = new ForgeSQL({
cacheEntityName: "cache",
cacheTTL: 300,
});

// Cached query
const users = await forgeSQL
.selectCacheable({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));
`

$3

`typescript
// Optimize repeated queries within a single invocation
await forgeSQL.executeWithLocalContext(async () => {
const users = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

// This query will use local cache (no database call)
const cachedUsers = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

// Using new methods for better performance
const usersFrom = await forgeSQL.selectFrom(users).where(eq(users.active, true));

// Raw SQL with local caching
const rawUsers = await forgeSQL.execute("SELECT id, name FROM users WHERE active = ?", [true]);
});
`

$3

`typescript
// Resolver with performance monitoring
resolver.define("fetch", async (req: Request) => {
try {
return await forgeSQL.executeWithMetadata(
async () => {
// Resolver logic with multiple queries
const users = await forgeSQL.selectFrom(demoUsers);
const orders = await forgeSQL
.selectFrom(demoOrders)
.where(eq(demoOrders.userId, demoUsers.id));
return { users, orders };
},
async (totalDbExecutionTime, totalResponseSize, printQueriesWithPlan) => {
const threshold = 500; // ms baseline for this resolver

if (totalDbExecutionTime > threshold * 1.5) {
console.warn(
[Performance Warning fetch] Resolver exceeded DB time: ${totalDbExecutionTime} ms,
);
await printQueriesWithPlan(); // Optionally log or capture diagnostics for further analysis
} else if (totalDbExecutionTime > threshold) {
console.debug([Performance Debug fetch] High DB time: ${totalDbExecutionTime} ms);
}
},
{
// Optional: Configure query plan printing behavior
mode: "TopSlowest", // Print top slowest queries (default)
topQueries: 3, // Print top 3 slowest queries
},
);
} catch (e) {
const error = e?.cause?.debug?.sqlMessage ?? e?.cause;
console.error(error, e);
throw error;
}
});
`

Query Plan Printing Options:

The printQueriesWithPlan function supports two modes:

1. TopSlowest Mode (default): Prints execution plans for the slowest queries from the current resolver invocation
- mode: Set to 'TopSlowest' (default)
- topQueries: Number of top slowest queries to analyze (default: 1)

2. SummaryTable Mode: Uses CLUSTER_STATEMENTS_SUMMARY for query analysis
- mode: Set to 'SummaryTable'
- summaryTableWindowTime: Time window in milliseconds (default: 15000ms)
- Only works if queries are executed within the specified time window

$3

`typescript
// Secure dynamic SQL queries for natural-language analytics
const rovo = forgeSQL.rovo();
const settings = await rovo
.rovoSettingBuilder(usersTable, accountId)
.addContextParameter(":currentUserId", accountId)
.useRLS()
.addRlsColumn(usersTable.id)
.addRlsWherePart((alias) => ${alias}.${usersTable.id.name} = '${accountId}')
.finish()
.build();

const result = await rovo.dynamicIsolatedQuery(
"SELECT id, name FROM users WHERE status = 'active' AND userId = :currentUserId",
settings,
);
`

$3

- Full Installation Guide - Complete setup instructions
- Core Features - Learn about key capabilities
- Global Cache System (Level 2) - Cross-invocation caching features
- Local Cache System (Level 1) - In-memory caching features
- Rovo Integration - Secure natural-language analytics
- API Reference - Complete API documentation

Drizzle Usage with forge-sql-orm

If you prefer to use Drizzle ORM with the additional features of Forge-SQL-ORM (like optimistic locking and caching), you can use the enhanced API:

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

// Versioned operations with cache management (recommended)
await forgeSQL.modifyWithVersioningAndEvictCache().insert(Users, [userData]);
await forgeSQL.modifyWithVersioningAndEvictCache().updateById(updateData, Users);

// Versioned operations without cache management
await forgeSQL.modifyWithVersioning().insert(Users, [userData]);
await forgeSQL.modifyWithVersioning().updateById(updateData, Users);

// Non-versioned operations with cache management
await forgeSQL.insertAndEvictCache(Users).values(userData);
await forgeSQL.updateAndEvictCache(Users).set(updateData).where(eq(Users.id, 1));

// Basic Drizzle operations (cache context aware)
await forgeSQL.insert(Users).values(userData);
await forgeSQL.update(Users).set(updateData).where(eq(Users.id, 1));

// Direct Drizzle access
const db = forgeSQL.getDrizzleQueryBuilder();
const users = await db.select().from(users);

// Using new methods for enhanced functionality
const usersFrom = await forgeSQL.selectFrom(users).where(eq(users.active, true));

const usersDistinct = await forgeSQL.selectDistinctFrom(users).where(eq(users.active, true));

const usersCacheable = await forgeSQL.selectCacheableFrom(users).where(eq(users.active, true));

// Raw SQL execution
const rawUsers = await forgeSQL.execute("SELECT * FROM users WHERE active = ?", [true]);

// Raw SQL with caching
// ⚠️ IMPORTANT: When using executeCacheable(), all table names must be wrapped with backticks ()
const cachedRawUsers = await forgeSQL.executeCacheable(
"SELECT * FROM users WHERE active = ?",
[true],
300,
);

// Raw SQL with execution metadata and performance monitoring
const usersWithMetadata = await forgeSQL.executeWithMetadata(
async () => {
const users = await forgeSQL.selectFrom(usersTable);
const orders = await forgeSQL
.selectFrom(ordersTable)
.where(eq(ordersTable.userId, usersTable.id));
return { users, orders };
},
(totalDbExecutionTime, totalResponseSize, printQueriesWithPlan) => {
const threshold = 500; // ms baseline for this resolver

if (totalDbExecutionTime > threshold * 1.5) {
console.warn([Performance Warning] Resolver exceeded DB time: ${totalDbExecutionTime} ms);
await printQueriesWithPlan(); // Analyze and print query execution plans
} else if (totalDbExecutionTime > threshold) {
console.debug([Performance Debug] High DB time: ${totalDbExecutionTime} ms);
}

console.log(DB response size: ${totalResponseSize} bytes);
},
{
// Optional: Configure query plan printing
mode: "TopSlowest", // Print top slowest queries (default)
topQueries: 2, // Print top 2 slowest queries
},
);

// DDL operations for schema modifications
await forgeSQL.executeDDL(
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE
)
);

// Execute regular SQL queries in DDL context for performance monitoring
await forgeSQL.executeDDLActions(async () => {
// Execute regular SQL queries in DDL context for monitoring
const slowQueries = await forgeSQL.execute(
SELECT * FROM INFORMATION_SCHEMA.STATEMENTS_SUMMARY
WHERE AVG_LATENCY > 1000000
);

// Execute complex analysis queries in DDL context
const performanceData = await forgeSQL.execute(
SELECT * FROM INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY
WHERE SUMMARY_END_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
);

return { slowQueries, performanceData };
});

// Common Table Expressions (CTEs)
const userStats = await forgeSQL
.with(
forgeSQL.selectFrom(users).where(eq(users.active, true)).as("activeUsers"),
forgeSQL.selectFrom(orders).where(eq(orders.status, "completed")).as("completedOrders"),
)
.select({
totalActiveUsers: sqlCOUNT(au.id),
totalCompletedOrders: sqlCOUNT(co.id),
})
.from(sqlactiveUsers au)
.leftJoin(sqlcompletedOrders co, eq(sqlau.id, sqlco.userId));

// Rovo Integration for secure dynamic SQL queries
const rovo = forgeSQL.rovo();
const settings = await rovo
.rovoSettingBuilder(usersTable, accountId)
.addContextParameter(":currentUserId", accountId)
.useRLS()
.addRlsColumn(usersTable.id)
.addRlsWherePart((alias) => ${alias}.${usersTable.id.name} = '${accountId}')
.finish()
.build();

const rovoResult = await rovo.dynamicIsolatedQuery(
"SELECT id, name FROM users WHERE status = 'active' AND userId = :currentUserId",
settings,
);
``

This approach gives you direct access to all Drizzle ORM features while still using the @forge/sql backend with enhanced caching and versioning capabilities.

Direct Drizzle Usage with Custom Driver

If you prefer to use Drizzle ORM directly without the additional features of Forge-SQL-ORM (like optimistic locking), you can use the custom driver:

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

// Initialize drizzle with the custom driver and patch it for aliased selects
const db = patchDbWithSelectAliased(drizzle(forgeDriver));

// Use drizzle directly
const users = await db.select().from(users);
const users = await db.selectAliased(getTableColumns(users)).from(users);
const users = await db.selectAliasedDistinct(getTableColumns(users)).from(users);
await db.insert(users)...;
await db.update(users)...;
await db.delete(users)...;
// Use drizzle with kvs cache
const users = await db.selectAliasedCacheable(getTableColumns(users)).from(users);
const users = await db.selectAliasedDistinctCacheable(getTableColumns(users)).from(users);
await db.insertAndEvictCache(users)...;
await db.updateAndEvictCache(users)...;
await db.deleteAndEvictCache(users)...;

// Use drizzle with kvs cache context
await forgeSQL.executeWithCacheContext(async () => {
await db.insertWithCacheContext(users)...;
await db.updateWithCacheContext(users)...;
await db.deleteWithCacheContext(users)...;
// invoke without cache
const users = await db.selectAliasedCacheable(getTableColumns(users)).from(users);
// Cache is cleared only once at the end for all affected tables
});

// Using new methods with direct drizzle
const usersFrom = await forgeSQL.selectFrom(users)
.where(eq(users.active, true));

const usersDistinct = await forgeSQL.selectDistinctFrom(users)
.where(eq(users.active, true));

const usersCacheable = await forgeSQL.selectCacheableFrom(users)
.where(eq(users.active, true));

// Raw SQL execution
const rawUsers = await forgeSQL.execute(
"SELECT * FROM users WHERE active = ?",
[true]
);

// Raw SQL with caching
// ⚠️ IMPORTANT: When using executeCacheable(), all table names must be wrapped with backticks ()
const cachedRawUsers = await forgeSQL.executeCacheable(
"SELECT * FROM users WHERE active = ?",
[true],
300
);

// Raw SQL with execution metadata and performance monitoring
const usersWithMetadata = await forgeSQL.executeWithMetadata(
async () => {
const users = await forgeSQL.selectFrom(usersTable);
const orders = await forgeSQL.selectFrom(ordersTable).where(eq(ordersTable.userId, usersTable.id));
return { users, orders };
},
(totalDbExecutionTime, totalResponseSize, printQueriesWithPlan) => {
const threshold = 500; // ms baseline for this resolver

if (totalDbExecutionTime > threshold * 1.5) {
console.warn([Performance Warning] Resolver exceeded DB time: ${totalDbExecutionTime} ms);
await printQueriesWithPlan(); // Analyze and print query execution plans
} else if (totalDbExecutionTime > threshold) {
console.debug([Performance Debug] High DB time: ${totalDbExecutionTime} ms);
}

console.log(DB response size: ${totalResponseSize} bytes);
},
{
// Optional: Configure query plan printing
mode: 'TopSlowest', // Print top slowest queries (default)
topQueries: 1, // Print top slowest query
},
);
``

Setting Up Caching with @forge/kvs (Optional)

The caching system is optional and only needed if you want to use cache-related features. To enable the caching system, you need to install the required dependency and configure your manifest.

$3

To use caching, you need to use Forge-SQL-ORM methods that support cache management:

Methods that perform cache eviction after execution and in cache context (batch eviction):

- forgeSQL.insertAndEvictCache()
- forgeSQL.updateAndEvictCache()
- forgeSQL.deleteAndEvictCache()
- forgeSQL.modifyWithVersioningAndEvictCache()
- forgeSQL.getDrizzleQueryBuilder().insertAndEvictCache()
- forgeSQL.getDrizzleQueryBuilder().updateAndEvictCache()
- forgeSQL.getDrizzleQueryBuilder().deleteAndEvictCache()

Methods that participate in cache context only (batch eviction):

- All methods except the default Drizzle methods:
- forgeSQL.insert()
- forgeSQL.update()
- forgeSQL.delete()
- forgeSQL.modifyWithVersioning()
- forgeSQL.getDrizzleQueryBuilder().insertWithCacheContext()
- forgeSQL.getDrizzleQueryBuilder().updateWithCacheContext()
- forgeSQL.getDrizzleQueryBuilder().deleteWithCacheContext()

Methods do not do evict cache, better do not use with cache feature:

- forgeSQL.getDrizzleQueryBuilder().insert()
- forgeSQL.getDrizzleQueryBuilder().update()
- forgeSQL.getDrizzleQueryBuilder().delete()

Cacheable methods:

- forgeSQL.selectCacheable()
- forgeSQL.selectDistinctCacheable()
- forgeSQL.getDrizzleQueryBuilder().selectAliasedCacheable()
- forgeSQL.getDrizzleQueryBuilder().selectAliasedDistinctCacheable()

Cache context example:

`typescript
await forgeSQL.executeWithCacheContext(async () => {
// These methods participate in batch cache clearing
await forgeSQL.insert(Users).values(userData);
await forgeSQL.update(Users).set(updateData).where(eq(Users.id, 1));
await forgeSQL.delete(Users).where(eq(Users.id, 1));
// Cache is cleared only once at the end for all affected tables
});
`

The diagram below shows the lifecycle of a cacheable query in Forge-SQL-ORM:

1. Resolver calls forge-sql-orm with a SQL query and parameters.
2. forge-sql-orm generates a cache key = hash(sql, parameters).
3. It asks @forge/kvs for an existing cached result.
- Cache hit → result is returned immediately.
- Cache miss / expired → query is executed against @forge/sql.
4. Fresh result is stored in @forge/kvs with TTL and returned to the caller.

!img.png

The diagram below shows how Evict Cache works in Forge-SQL-ORM:

1. Data modification is executed through @forge/sql (e.g., UPDATE users ...).
2. After a successful update, forge-sql-orm queries the cache entity by using the sql field with filter.contains("users") to find affected cached queries.
3. The returned cache entries are deleted in batches (up to 25 per transaction).
4. Once eviction is complete, the update result is returned to the resolver.
5. Note: Expired entries are not processed here — they are cleaned up separately by the scheduled cache cleanup trigger using the expiration index.

!img.png

The diagram below shows how Scheduled Expiration Cleanup works:

1. A periodic scheduler (Forge trigger) runs cache cleanup independently of data modifications.
2. forge-sql-orm queries the cache entity by the expiration index to find entries with expiration < now.
3. Entries are deleted in batches (up to 25 per transaction) until the page is empty; pagination is done with a cursor (e.g., 100 per page).
4. This keeps the cache footprint small and prevents stale data accumulation.

!img.png

The diagram below shows how Cache Context works:

executeWithCacheContext(fn) lets you group multiple data modifications and perform one consolidated cache eviction at the end:

1. The context starts with an empty affectedTables set.
2. Each successful INSERT/UPDATE/DELETE inside the context registers its table name in affectedTables.
3. Reads inside the same context that target tables present in affectedTables will bypass the cache (read-through to SQL) to avoid serving stale data. These reads also do not write back to cache until eviction completes.
4. On context completion, affectedTables is de-duplicated and used to build one combined KVS query over the sql field with
filter.or(filter.contains(""), filter.contains(""), ...), returning all impacted cache entries in a single scan (paged by cursor, e.g., 100/page).
5. Matching cache entries are deleted in batches (≤25 per transaction) until the page is exhausted; then the next page is fetched via the cursor.
6. Expiration is handled separately by the scheduled cleanup and is not part of the context flow.

!img.png

$3

@forge/kvs Limits:
Please review the official @forge/kvs quotas and limits before implementing caching.

Caching Guidelines:

- Don't cache everything - be selective about what to cache
- Don't cache simple and fast queries - sometimes direct query is faster than cache
- Consider data size and frequency of changes
- Monitor cache usage to stay within quotas
- Use appropriate TTL values

⚠️ Important Cache Limitations:

- Table names starting with a_: Tables whose names start with a_ (case-insensitive) are automatically ignored in cache operations. KVS Cache will not work with such tables, and they will be excluded from cache invalidation and cache key generation.

$3

`bash
npm install @forge/kvs -S
`

$3

Add the storage entity configuration and scheduler trigger to your manifest.yml:

`yaml
modules:
scheduledTrigger:
- key: clear-cache-trigger
function: clearCache
interval: fiveMinute
storage:
entities:
- name: cache
attributes:
sql:
type: string
expiration:
type: integer
data:
type: string
indexes:
- sql
- expiration
sql:
- key: main
engine: mysql
function:
- key: clearCache
handler: index.clearCache
`

`typescript
// Example usage in your Forge app
import { clearCacheSchedulerTrigger } from "forge-sql-orm";

export const clearCache = () => {
return clearCacheSchedulerTrigger({
cacheEntityName: "cache",
});
};
`

$3

Set the cache entity name in your ForgeSQL configuration:

`typescript
const options = {
cacheEntityName: "cache", // Must match the entity name in manifest.yml
cacheTTL: 300, // Default cache TTL in seconds (5 minutes)
cacheWrapTable: true, // Wrap table names with backticks in cache keys
// ... other options
};

const forgeSQL = new ForgeSQL(options);
`

Important Notes:

- The cacheEntityName must exactly match the name in your manifest storage entities
- The entity attributes (sql, expiration, data) are required for proper cache functionality
- Indexes on sql and expiration improve cache lookup performance
- Cache data is automatically cleaned up based on TTL settings
- No additional permissions are required beyond standard Forge app permissions

$3

Basic setup (without caching):

package.json:

`shell
npm install forge-sql-orm @forge/sql drizzle-orm -S


For UI-Kit projects, use: npm install forge-sql-orm @forge/sql drizzle-orm -S --legacy-peer-deps


`

manifest.yml:

`yaml
modules:
sql:
- key: main
engine: mysql
`

index.ts:

`typescript
import ForgeSQL from "forge-sql-orm";

const forgeSQL = new ForgeSQL();

// simple insert
await forgeSQL.insert(Users, [userData]);
// Use versioned operations without caching
await forgeSQL.modifyWithVersioning().insert(Users, [userData]);
const users = await forgeSQL.select({ id: Users.id });
`

With caching support:

`shell
npm install forge-sql-orm @forge/sql @forge/kvs drizzle-orm -S


For UI-Kit projects, use: npm install forge-sql-orm @forge/sql @forge/kvs drizzle-orm -S --legacy-peer-deps


`

manifest.yml:

`yaml
modules:
scheduledTrigger:
- key: clear-cache-trigger
function: clearCache
interval: fiveMinute
storage:
entities:
- name: cache
attributes:
sql:
type: string
expiration:
type: integer
data:
type: string
indexes:
- sql
- expiration
sql:
- key: main
engine: mysql
function:
- key: clearCache
handler: index.clearCache
`

index.ts:

`typescript
import ForgeSQL from "forge-sql-orm";

const forgeSQL = new ForgeSQL({
cacheEntityName: "cache",
});

import { clearCacheSchedulerTrigger } from "forge-sql-orm";
import { getTableColumns } from "drizzle-orm";

export const clearCache = () => {
return clearCacheSchedulerTrigger({
cacheEntityName: "cache",
});
};

// Now you can use caching features
const usersData = await forgeSQL
.selectCacheable(getTableColumns(users))
.from(users)
.where(eq(users.active, true));

// simple insert
await forgeSQL.insertAndEvictCache(users, [userData]);
// Use versioned operations with caching
await forgeSQL.modifyWithVersioningAndEvictCache().insert(users, [userData]);

// use Cache Context
const data = await forgeSQL.executeWithCacheContextAndReturnValue(async () => {
// after insert mark users to evict
await forgeSQL.insert(users, [userData]);
// after insertAndEvictCache mark orders to evict
await forgeSQL.insertAndEvictCache(orders, [order1, order2]);
// execute query and put result to local cache
await forgeSQL
.selectCacheable({
userId: users.id,
userName: users.name,
orderId: orders.id,
orderName: orders.name,
})
.from(users)
.innerJoin(orders, eq(orders.userId, users.id))
.where(eq(users.active, true));
// use local cache without @forge/kvs and @forge/sql
return await forgeSQL
.selectCacheable({
userId: users.id,
userName: users.name,
orderId: orders.id,
orderName: orders.name,
})
.from(users)
.innerJoin(orders, eq(orders.userId, users.id))
.where(eq(users.active, true));
});
// execute query and put result to kvs cache
await forgeSQL
.selectCacheable({
userId: users.id,
userName: users.name,
orderId: orders.id,
orderName: orders.name,
})
.from(users)
.innerJoin(orders, eq(orders.userId, users.id))
.where(eq(users.active, true));

// get result from @foge/kvs cache without real @forge/sql call
await forgeSQL
.selectCacheable({
userId: users.id,
userName: users.name,
orderId: orders.id,
orderName: orders.name,
})
.from(users)
.innerJoin(orders, eq(orders.userId, users.id))
.where(eq(users.active, true));

// use Local Cache for performance optimization
const optimizedData = await forgeSQL.executeWithLocalCacheContextAndReturnValue(async () => {
// First query - hits database and caches result
const users = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

// Second query - uses local cache (no database call)
const cachedUsers = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

// Using new methods for better performance
const usersFrom = await forgeSQL.selectFrom(users).where(eq(users.active, true));

// This will use local cache (no database call)
const cachedUsersFrom = await forgeSQL.selectFrom(users).where(eq(users.active, true));

// Raw SQL with local caching
const rawUsers = await forgeSQL.execute("SELECT id, name FROM users WHERE active = ?", [true]);

// Insert operation - evicts local cache
await forgeSQL.insert(users).values({ name: "New User", active: true });

// Third query - hits database again and caches new result
const updatedUsers = await forgeSQL
.select({ id: users.id, name: users.name })
.from(users)
.where(eq(users.active, true));

return { users, cachedUsers, updatedUsers, usersFrom, cachedUsersFrom, rawUsers };
});
`

Choosing the Right Method - ForgeSQL ORM

$3

| Method | Use Case | Versioning | Cache Management |
| ------------------------------------- | ----------------------------------------------------------- | ---------- | -------------------- |
| modifyWithVersioningAndEvictCache() | High-concurrency scenarios with Cache support | ✅ Yes | ✅ Yes |
| modifyWithVersioning() | High-concurrency scenarios | ✅ Yes | Cache Context |
| insertAndEvictCache() | Simple inserts | ❌ No | ✅ Yes |
| updateAndEvictCache() | Simple updates | ❌ No | ✅ Yes |
| deleteAndEvictCache() | Simple deletes | ❌ No | ✅ Yes |
| insert/update/delete | Basic Drizzle operations | ❌ No | Cache Context |
| selectFrom() | All-column queries with field aliasing | ❌ No | Local Cache |
| selectDistinctFrom() | Distinct all-column queries with field aliasing | ❌ No | Local Cache |
| selectCacheableFrom() | All-column queries with field aliasing and caching | ❌ No | Local + Global Cache |
| selectDistinctCacheableFrom() | Distinct all-column queries with field aliasing and caching | ❌ No | Local + Global Cache |
| execute() | Raw SQL queries with local caching | ❌ No | Local Cache |
| executeCacheable() | Raw SQL queries with local and global caching | ❌ No | Local + Global Cache |
| executeDDL() | DDL operations (CREATE, ALTER, DROP, etc.) | ❌ No | No Caching |
| executeDDLActions() | Execute regular SQL queries in DDL operation context | ❌ No | No Caching |
| with() | Common Table Expressions (CTEs) | ❌ No | Local Cache |

Choosing the Right Method - Direct Drizzle

$3

| Method | Use Case | Versioning | Cache Management |
| ---------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------- | ---------- | -------------------- |
| insertWithCacheContext/insertWithCacheContext/updateWithCacheContext | Basic Drizzle operations | ❌ No | Cache Context |
| insertAndEvictCache() | Simple inserts without conflicts | ❌ No | ✅ Yes |
| updateAndEvictCache() | Simple updates without conflicts | ❌ No | ✅ Yes |
| deleteAndEvictCache() | Simple deletes without conflicts | ❌ No | ✅ Yes |
| insert/update/delete | Basic Drizzle operations | ❌ No | ❌ No |
| selectFrom() | All-column queries with field aliasing | ❌ No | Local Cache |
| selectDistinctFrom() | Distinct all-column queries with field aliasing | ❌ No | Local Cache |
| selectCacheableFrom() | All-column queries with field aliasing and caching | ❌ No | Local + Global Cache |
| selectDistinctCacheableFrom() | Distinct all-column queries with field aliasing and caching | ❌ No | Local + Global Cache |
| execute() | Raw SQL queries with local caching | ❌ No | Local Cache |
| executeCacheable() | Raw SQL queries with local and global caching | ❌ No | Local + Global Cache |
| executeWithMetadata() | Resolver-level profiling with execution metrics and configurable query plan printing (TopSlowest or SummaryTable mode) | ❌ No | Local Cache |
| executeDDL() | DDL operations (CREATE, ALTER, DROP, etc.) | ❌ No | No Caching |
| executeDDLActions() | Execute regular SQL queries in DDL operation context | ❌ No | No Caching |
| with() | Common Table Expressions (CTEs) | ❌ No | Local Cache |

where Cache context - allows you to batch cache invalidation events and bypass cache reads for affected tables.

Step-by-Step Migration Workflow

1. Install CLI and setup scripts

`bash
npm install forge-sql-orm-cli -D
npm pkg set scripts.models:create="forge-sql-orm-cli generate:model --output src/entities --saveEnv"
npm pkg set scripts.migration:create="forge-sql-orm-cli migrations:create --force --output src/migration --entitiesPath src/entities"
npm pkg set scripts.migration:update="forge-sql-orm-cli migrations:update --entitiesPath src/entities --output src/migration"
`

_(This is done only once when setting up the project)_

2. Generate initial schema from an existing database

`sh
npm run models:create
`

_(This will prompt for database credentials on first run and save them to .env file)_

3. Create the first migration

`sh
npm run migration:create
`

_(This initializes the database migration structure, also done once)_

4. Deploy to Forge and verify that migrations work
- Deploy your Forge app with migrations.
- Run migrations using a Forge web trigger or Forge scheduler.

5. Modify the database (e.g., add a new column, index, etc.)
- Use DbSchema or manually alter the database schema.

6. Update the migration

`sh
npm run migration:update
`

- ⚠️ Do NOT update schema before this step!
- If schema is updated first, the migration will be empty!

7. Deploy to Forge and verify that the migration runs without issues
- Run the updated migration on Forge.

8. Update the schema

`sh
npm run models:create
`

9. Repeat steps 5-8 as needed

⚠️ WARNING:

- Do NOT swap steps 7 and 5! If you update schema before generating a migration, the migration will be empty!
- Always generate the migration first, then update the schema.

Drop Migrations

The Drop Migrations feature allows you to completely reset your database schema in Atlassian Forge SQL. This is useful when you need to:

- Start fresh with a new schema
- Reset all tables and their data
- Clear migration history
- Ensure your local schema matches the deployed database

$3

Before using Drop Migrations, ensure that:

1. Your local schema exactly matches the current database schema deployed in Atlassian Forge SQL
2. You have a backup of your data if needed
3. You understand that this operation will delete all tables and data

$3

1. First, ensure your local schema matches the deployed database:

`bash
npm run models:create
`

2. Generate the drop migration:

`bash
npm run migration:drop
`

_(Add this script to your package.json: npm pkg set scripts.migration:drop="forge-sql-orm-cli migrations:drop --entitiesPath src/entities --output src/migration")_

3. Deploy and run the migration in your Forge app:

`js
import migrationRunner from "./database/migration";
import { MigrationRunner } from "@forge/sql/out/migration";

const runner = new MigrationRunner();
await migrationRunner(runner);
await runner.run();
`

4. After dropping all tables, you can create a new migration to recreate the schema:
`bash
npm run migration:create
`
The --force parameter is already included in the script to allow creating migrations after dropping all tables.

$3

The generated drop migration will look like this:

`js
import { MigrationRunner } from "@forge/sql/out/migration";

export default (migrationRunner: MigrationRunner): MigrationRunner => {
return migrationRunner
.enqueue("v1_MIGRATION0", "ALTER TABLE orders DROP FOREIGN KEY fk_orders_users")
.enqueue("v1_MIGRATION1", "DROP INDEX idx_orders_user_id ON orders")
.enqueue("v1_MIGRATION2", "DROP TABLE IF EXISTS orders")
.enqueue("v1_MIGRATION3", "DROP TABLE IF EXISTS users")
.enqueue("MIGRATION_V1_1234567890", "DELETE FROM __migrations");
};
`

$3

- This operation is irreversible - all data will be lost
- Make sure your local schema is up-to-date with the deployed database
- Consider backing up your data before running drop migrations
- The migration will clear the __migrations table to allow for fresh migration history
- Drop operations are performed in the correct order: first foreign keys, then indexes, then tables

---

Date and Time Types

When working with date and time fields in your models, you should use the custom types provided by Forge-SQL-ORM to ensure proper handling of date/time values. This is necessary because Forge SQL has specific format requirements for date/time values:

| Date type | Required Format | Example |
| --------- | ------------------------------ | -------------------------- |
| DATE | YYYY-MM-DD | 2024-09-19 |
| TIME | HH:MM:SS[.fraction] | 06:40:34 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS[.fraction] | 2024-09-19 06:40:34.999999 |

`typescript
// ❌ Don't use standard Drizzle date/time types
export const testEntityTimeStampVersion = mysqlTable("test_entity", {
id: int("id").primaryKey().autoincrement(),
time_stamp: timestamp("times_tamp").notNull(),
date_time: datetime("date_time").notNull(),
time: time("time").notNull(),
date: date("date").notNull(),
});

// ✅ Use Forge-SQL-ORM custom types instead
import {
forgeDateTimeString,
forgeDateString,
forgeTimestampString,
forgeTimeString,
} from "forge-sql-orm";

export const testEntityTimeStampVersion = mysqlTable("test_entity", {
id: int("id").primaryKey().autoincrement(),
time_stamp: forgeTimestampString("times_tamp").notNull(),
date_time: forgeDateTimeString("date_time").notNull(),
time: forgeTimeString("time").notNull(),
date: forgeDateString("date").notNull(),
});
`

$3

The custom types in Forge-SQL-ORM handle the conversion between JavaScript Date objects and Forge SQL's required string formats automatically. Without these custom types, you would need to manually format dates like this:

`typescript
// Without custom types, you'd need to do this manually:
const date = moment().format("YYYY-MM-DD");
const time = moment().format("HH:mm:ss.SSS");
const timestamp = moment().format("YYYY-MM-DDTHH:mm:ss.SSS");
`

Our custom types provide:

- Automatic conversion between JavaScript Date objects and Forge SQL's required string formats
- Consistent date/time handling across your application
- Type safety for date/time fields
- Proper handling of timezone conversions
- Support for all Forge SQL date/time types (datetime, timestamp, date, time)

$3

- forgeDateTimeString - For datetime fields (YYYY-MM-DD HH:MM:SS[.fraction])
- forgeTimestampString - For timestamp fields (YYYY-MM-DD HH:MM:SS[.fraction])
- forgeDateString - For date fields (YYYY-MM-DD)
- forgeTimeString - For time fields (HH:MM:SS[.fraction])

Each type ensures that the data is properly formatted according to Forge SQL's requirements while providing a clean, type-safe interface for your application code.

Connection to ORM

`js
import ForgeSQL from "forge-sql-orm";

const forgeSQL = new ForgeSQL();
`

or

`typescript
import { drizzle } from "drizzle-orm/mysql-proxy";
import { forgeDriver } from "forge-sql-orm";

// Initialize drizzle with the custom driver
const db = drizzle(forgeDriver);

// Use drizzle directly
const users = await db.select().from(users);
`

Fetch Data

$3

`js
// Using forgeSQL.select()
const user = await forgeSQL.select({ user: users }).from(users);

// Using forgeSQL.selectDistinct()
const user = await forgeSQL.selectDistinct({ user: users }).from(users);

// Using forgeSQL.selectCacheable()
const user = await forgeSQL.selectCacheable({ user: users }).from(users);

// Using forgeSQL.selectFrom() - Select all columns with field aliasing
const user = await forgeSQL.selectFrom(users).where(eq(users.id, 1));

// Using forgeSQL.selectDistinctFrom() - Select distinct all columns with field aliasing
const user = await forgeSQL.selectDistinctFrom(users).where(eq(users.id, 1));

// Using forgeSQL.selectCacheableFrom() - Select all columns with field aliasing and caching
const user = await forgeSQL.selectCacheableFrom(users).where(eq(users.id, 1));

// Using forgeSQL.selectDistinctCacheableFrom() - Select distinct all columns with field aliasing and caching
const user = await forgeSQL.selectDistinctCacheableFrom(users).where(eq(users.id, 1));

// Using forgeSQL.execute() - Execute raw SQL with local caching
const user = await forgeSQL.execute("SELECT * FROM users WHERE id = ?", [1]);

// Using forgeSQL.executeCacheable() - Execute raw SQL with local and global caching
// ⚠️ IMPORTANT: When using executeCacheable(), all table names in SQL queries must be wrapped with backticks ()
// Example: SELECT FROM users WHERE id = ? (NOT: SELECT FROM users WHERE id = ?)
const user = await forgeSQL.executeCacheable("SELECT * FROM users WHERE id = ?", [1], 300);

// Using forgeSQL.getDrizzleQueryBuilder()
const user = await forgeSQL.getDrizzleQueryBuilder().select().from(Users).where(eq(Users.id, 1));

// OR using direct drizzle with custom driver
const db = drizzle(forgeDriver);
const user = await db.select().from(Users).where(eq(Users.id, 1));
// Returns: { id: 1, name: "John Doe" }

// Using executeQueryOnlyOne for single result with error handling
const user = await forgeSQL
.fetch()
.executeQueryOnlyOne(
forgeSQL.getDrizzleQueryBuilder().select().from(Users).where(eq(Users.id, 1)),
);
// Returns: { id: 1, name: "John Doe" }
// Throws error if multiple records found
// Returns undefined if no records found

// Using with aliases
// With forgeSQL
const usersAlias = alias(Users, "u");
const result = await forgeSQL
.getDrizzleQueryBuilder()
.select({
userId: sql < string > ${usersAlias.id} as \userId\`,
userName: sql < string > ${usersAlias.name} as \userName\,
})
.from(usersAlias);

// OR with direct drizzle
const db = drizzle(forgeDriver);
const result = await db
.select({
userId: sql < string > ${usersAlias.id} as \userId\,
userName: sql < string > ${usersAlias.name} as \userName\,
})
.from(usersAlias);
// Returns: { userId: 1, userName: "John Doe" }
`

$3

`js
// Using joins with automatic field name collision prevention
// With forgeSQL
const orderWithUser = await forgeSQL
.select({ user: users, order: orders })
.from(orders)
.innerJoin(users, eq(orders.userId, users.id));

// Using new selectFrom methods with joins
const orderWithUser = await forgeSQL
.selectFrom(orders)
.innerJoin(users, eq(orders.userId, users.id))
.where(eq(orders.id, 1));

// Using selectCacheableFrom with joins and caching
const orderWithUser = await forgeSQL
.selectCacheableFrom(orders)
.innerJoin(users, eq(orders.userId, users.id))
.where(eq(orders.id, 1));

// Using with() for Common Table Expressions (CTEs)
const userStats = await forgeSQL
.with(
forgeSQL.selectFrom(users).where(eq(users.active, true)).as("activeUsers"),
forgeSQL.selectFrom(orders).where(eq(orders.status, "completed")).as("completedOrders"),
)
.select({
totalActiveUsers: sqlCOUNT(au.id),
totalCompletedOrders: sqlCOUNT(co.id),
})
.from(sqlactiveUsers au)
.leftJoin(sqlcompletedOrders co, eq(sqlau.id, sqlco.userId));

// OR with direct drizzle
const db = patchDbWithSelectAliased(drizzle(forgeDriver));
const orderWithUser = await db
.selectAliased({ user: users, order: orders })
.from(orders)
.innerJoin(users, eq(orders.userId, users.id));
// Returns: {
// user_id: 1,
// user_name: "John Doe",
// order_id: 1,
// order_product: "Product 1"
// }

// Using distinct with aliases
const uniqueUsers = await db.selectAliasedDistinct({ user: users }).from(users);
// Returns unique users with aliased fields

// Using executeQueryOnlyOne for unique results
const userStats = await forgeSQL.fetch().executeQueryOnlyOne(
forgeSQL
.getDrizzleQueryBuilder()
.select({
totalUsers: sqlCOUNT(*) as \totalUsers\,
uniqueNames: sqlCOUNT(DISTINCT name) as \uniqueNames\,
})
.from(Users),
);
// Returns: { totalUsers: 100, uniqueNames: 80 }
// Throws error if multiple records found
`

$3

`js
// Using executeRawSQL for direct SQL queries
const users = await forgeSQL
.fetch()
.executeRawSQL("SELECT * FROM users");

// Using execute() for raw SQL with local caching
const users = await forgeSQL
.execute("SELECT * FROM users WHERE active = ?", [true]);

// Using executeCacheable() for raw SQL with local and global caching
// ⚠️ IMPORTANT: When using executeCacheable(), all table names in SQL queries must be wrapped with backticks ()
// Example: SELECT FROM users WHERE active = ? (NOT: SELECT FROM users WHERE active = ?)
const users = await forgeSQL
.executeCacheable("SELECT * FROM users WHERE active = ?", [true], 300);

// Using executeWithMetadata() for capturing execution metrics and performance monitoring
const usersWithMetadata = await forgeSQL.executeWithMetadata(
async () => {
const users = await forgeSQL.selectFrom(usersTable);
const orders = await forgeSQL.selectFrom(ordersTable).where(eq(ordersTable.userId, usersTable.id));
return { users, orders };
},
(totalDbExecutionTime, totalResponseSize, printQueriesWithPlan) => {
const threshold = 500; // ms baseline for this resolver

if (totalDbExecutionTime > threshold * 1.5) {
console.warn([Performance Warning] Resolver exceeded DB time: ${totalDbExecutionTime} ms);
await printQueriesWithPlan(); // Analyze and print query execution plans
} else if (totalDbExecutionTime > threshold) {
console.debug([Performance Debug] High DB time: ${totalDbExecutionTime} ms);
}

console.log(DB response size: ${totalResponseSize} bytes);
},
{
// Optional: Configure query plan printing
mode: 'TopSlowest', // Print top slowest queries (default)
topQueries: 1, // Print top slowest query
},
);

// Using executeDDL() for DDL operations (CREATE, ALTER, DROP, etc.)
await forgeSQL.executeDDL(
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE
)
);

await forgeSQL.executeDDL(sql
ALTER TABLE users
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

await forgeSQL.executeDDL("DROP TABLE IF EXISTS old_users");

// Using executeDDLActions() for executing regular SQL queries in DDL context
// This method executes a series of actions within a DDL operation context for monitoring
await forgeSQL.executeDDLActions(async () => {
// Execute regular SQL queries in DDL context for performance monitoring
const slowQueries = await forgeSQL.execute(
SELECT * FROM INFORMATION_SCHEMA.STATEMENTS_SUMMARY
WHERE AVG_LATENCY > 1000000
);

// Execute complex analysis queries in DDL context
const performanceData = await forgeSQL.execute(
SELECT * FROM INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY
WHERE SUMMARY_END_TIME > DATE_SUB(NOW(), INTERVAL 1 HOUR)
);

return { slowQueries, performanceData };
});

// Using execute() with complex queries
const userStats = await forgeSQL
.execute(
SELECT
u.id,
u.name,
COUNT(o.id) as order_count,
SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = ?
GROUP BY u.id, u.name
, [true]);
``

Modify Operations

Forge-SQL-ORM provides multiple approaches for Modify operations, each with different characteristics:

$3

These operations work like standard Drizzle methods but participate in cache context when used within executeWithCacheContext():

`js
// Basic insert (participates in cache context when used within executeWithCacheContext)
await forgeSQL.insert(Users).values({ id: 1, name: "Smith" });

// Basic update (participates in cache context when used within executeWithCacheContext)
await forgeSQL.update(Users).set({ name: "Smith Updated" }).where(eq(Users.id, 1));

// Basic delete (participates in cache context when used within executeWithCacheContext)
await forgeSQL.delete(Users).where(eq(Users.id, 1));
`

$3

These operations don't use optimistic locking but provide cache invalidation:

`js
// Insert without versioning but with cache invalidation
await forgeSQL.insertAndEvictCache(Users).values({ id: 1, name: "Smith" });

// Update without versioning but with cache invalidation
await forgeSQL.updateAndEvictCache(Users).set({ name: "Smith Updated" }).where(eq(Users.id, 1));

// Delete without versioning but with cache invalidation
await forgeSQL.deleteAndEvictCache(Users).where(eq(Users.id, 1));
`

$3

These operations use optimistic locking and automatic cache invalidation:

`js
// Insert with versioning and cache management
const userId = await forgeSQL
.modifyWithVersioningAndEvictCache()
.insert(Users, [{ id: 1, name: "Smith" }]);

// Bulk insert with versioning
await forgeSQL.modifyWithVersioningAndEvictCache().insert(Users, [
{ id: 2, name: "Smith" },
{ id: 3, name: "Vasyl" },
]);

// Update by ID with optimistic locking and cache invalidation
await forgeSQL
.modifyWithVersioningAndEvictCache()
.updateById({ id: 1, name: "Smith Updated" }, Users);

// Delete by ID with versioning and cache invalidation
await forgeSQL.modifyWithVersioningAndEvictCache().deleteById(1, Users);
`

$3

These operations use optimistic locking but don't manage cache:

`js
// Insert with versioning only (no cache management)
const userId = await forgeSQL.modifyWithVersioning().insert(Users, [{ id: 1, name: "Smith" }]);

// Update with versioning only
await forgeSQL.modifyWithVersioning().updateById({ id: 1, name: "Smith Updated" }, Users);

// Delete with versioning only
await forgeSQL.modifyWithVersioning().deleteById(1, Users);
`

$3

⚠️ BREAKING CHANGE: The crud() and modify() methods have been completely removed in version 2.1.x.

``js
// ❌ These methods no longer exist in 2.1.x
// const userId = await forgeSQL.crud().insert(Users, [{ id: 1, name: "Smith" }]);
// await forgeSQL.crud().updateById({ id: 1, name: "Smith Updated" }, Users);
// await forgeSQL.crud().deleteById(1, Users);

// ✅ Use the new methods instead
const userId = await forgeSQL.modifyWithVersioning().insert(Users, [{ id: 1, name: "Smith" }]);
await forgeSQL.modifyWithVersioning().updateById({ id: 1, name: "Smith Updated" }, Users);
await forgeSQL.modifyWithVersioning().deleteById(1, U