
Security News
Bun 1.2.19 Adds Isolated Installs for Better Monorepo Support
Bun 1.2.19 introduces isolated installs for smoother monorepo workflows, along with performance boosts, new tooling, and key compatibility fixes.
forge-sql-orm
Advanced tools
Drizzle ORM integration for Forge-SQL in Atlassian Forge applications.
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.
import { drizzle } from "drizzle-orm/mysql-proxy";
import { forgeDriver } from "forge-sql-orm";
const db = drizzle(forgeDriver);
Best for: Simple CRUD 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.
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.
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:
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));
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));
select()
without field selectionForge-SQL-ORM is designed to work with @forge/sql and requires some additional setup to ensure compatibility within Atlassian Forge.
✅ Step 1: Install Dependencies
npm install forge-sql-orm @forge/sql drizzle-orm momment -S
npm install forge-sql-orm-cli -D
This will:
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:
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);
If you prefer to use Drizzle ORM with the additional features of Forge-SQL-ORM (like optimistic locking), you can use the custom driver:
import ForgeSQL from "forge-sql-orm";
const forgeSQL = new ForgeSQL();
forgeSQL.crud().insert(...);
forgeSQL.crud().updateById(...);
const db = forgeSQL.getDrizzleQueryBuilder();
// Use drizzle
const users = await db.select().from(users);
This approach gives you direct access to all Drizzle ORM features while still using the @forge/sql backend.
Generate initial schema from an existing database
npx forge-sql-orm-cli generate:model --dbName testDb --output ./database/schema
(This is done only once when setting up the project)
Create the first migration
npx forge-sql-orm-cli migrations:create --dbName testDb --entitiesPath ./database/schema --output ./database/migration
(This initializes the database migration structure, also done once)
Deploy to Forge and verify that migrations work
Modify the database (e.g., add a new column, index, etc.)
Update the migration
npx forge-sql-orm-cli migrations:update --dbName testDb --entitiesPath ./database/schema --output ./database/migration
Deploy to Forge and verify that the migration runs without issues
Update the schema
npx forge-sql-orm-cli generate:model --dbName testDb --output ./database/schema
Repeat steps 4-7 as needed
⚠️ WARNING:
The Drop Migrations feature allows you to completely reset your database schema in Atlassian Forge SQL. This is useful when you need to:
Before using Drop Migrations, ensure that:
First, ensure your local schema matches the deployed database:
npx forge-sql-orm-cli generate:model --output ./database/schema
Generate the drop migration:
npx forge-sql-orm-cli migrations:drop --entitiesPath ./database/schema --output ./database/migration
Deploy and run the migration in your Forge app:
import migrationRunner from "./database/migration";
import { MigrationRunner } from "@forge/sql/out/migration";
const runner = new MigrationRunner();
await migrationRunner(runner);
await runner.run();
After dropping all tables, you can create a new migration to recreate the schema:
npx forge-sql-orm-cli migrations:create --entitiesPath ./database/schema --output ./database/migration --force
The --force
parameter is required here because we're creating a new migration after dropping all tables.
The generated drop migration will look like this:
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");
};
__migrations
table to allow for fresh migration historyWhen 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 |
// ❌ 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(),
});
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:
// 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:
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.
import ForgeSQL from "forge-sql-orm";
const forgeSQL = new ForgeSQL();
or
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);
// 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" }
// 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));
// 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: sql`COUNT(*) as \`totalUsers\``,
uniqueNames: sql`COUNT(DISTINCT name) as \`uniqueNames\``
}).from(Users)
);
// Returns: { totalUsers: 100, uniqueNames: 80 }
// Throws error if multiple records found
// Using executeRawSQL for direct SQL queries
const users = await forgeSQL
.fetch()
.executeRawSQL<Users>("SELECT * FROM users");
// 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" },
]);
// Insert with duplicate handling
await forgeSQL.crud().insert(
Users,
[
{ id: 4, name: "Smith" },
{ id: 4, name: "Vasyl" },
],
true
);
// Insert with sequence (nextVal)
import { nextVal } from "forge-sql-orm";
const user = {
id: nextVal('user_id_seq'),
name: "user test",
organization_id: 1
};
const id = await forgeSQL.modify().insert(appUser, [user]);
// The generated SQL will be:
// INSERT INTO app_user (id, name, organization_id)
// VALUES (NEXTVAL(user_id_seq), ?, ?) -- params: ["user test", 1]
// Update by ID with optimistic locking
await forgeSQL.crud().updateById({ id: 1, name: "Smith Updated" }, Users);
// Update specific fields
await forgeSQL.crud().updateById(
{ id: 1, age: 35 },
Users
);
// Update with custom WHERE condition
await forgeSQL.crud().updateFields(
{ name: "New Name", age: 35 },
Users,
eq(Users.email, "smith@example.com")
);
// Delete by ID
await forgeSQL.crud().deleteById(1, Users);
The formatLimitOffset
utility function is used to safely insert numeric values directly into SQL queries for LIMIT and OFFSET clauses. This is necessary because Atlassian Forge SQL doesn't support parameterized queries for these clauses.
import { formatLimitOffset } from "forge-sql-orm";
// Example usage in a query
const result = await forgeSQL
.select()
.from(orderItem)
.orderBy(asc(orderItem.createdAt))
.limit(formatLimitOffset(10))
.offset(formatLimitOffset(350000));
// The generated SQL will be:
// SELECT * FROM order_item
// ORDER BY created_at ASC
// LIMIT 10
// OFFSET 350000
Important Notes:
Security Considerations:
Optimistic locking is a concurrency control mechanism that prevents data conflicts when multiple transactions attempt to update the same record concurrently. Instead of using locks, this technique relies on a version field in your entity models.
datetime
- Timestamp-based versioningtimestamp
- Timestamp-based versioninginteger
- Numeric version incrementdecimal
- Numeric version incrementconst options = {
additionalMetadata: {
users: {
tableName: "users",
versionField: {
fieldName: "updatedAt",
}
}
}
};
const forgeSQL = new ForgeSQL(options);
// The version field will be automatically handled
await forgeSQL.crud().updateById(
{
id: 1,
name: "Updated Name",
updatedAt: new Date() // Will be automatically set if not provided
},
Users
);
The ForgeSqlOrmOptions
object allows customization of ORM behavior:
Option | Type | Description |
---|---|---|
logRawSqlQuery | boolean | Enables logging of raw SQL queries in the Atlassian Forge Developer Console. Useful for debugging and monitoring. Defaults to false . |
disableOptimisticLocking | boolean | Disables optimistic locking. When set to true , no additional condition (e.g., a version check) is added during record updates, which can improve performance. However, this may lead to conflicts when multiple transactions attempt to update the same record concurrently. |
additionalMetadata | object | Allows adding custom metadata to all entities. This is useful for tracking common fields across all tables (e.g., createdAt , updatedAt , createdBy , etc.). The metadata will be automatically added to all generated entities. |
Documentation here
Forge-SQL-ORM provides web triggers for managing database migrations in Atlassian Forge:
This trigger allows you to apply database migrations through a web endpoint. It's useful for:
// Example usage in your Forge app
import { applySchemaMigrations } from "forge-sql-orm";
import migration from "./migration";
export const handlerMigration = async () => {
return applySchemaMigrations(migration);
};
Configure in manifest.yml
:
webtrigger:
- key: invoke-schema-migration
function: runSchemaMigration
security:
egress:
allowDataEgress: false
allowedResponses:
- statusCode: 200
body: '{"body": "Migrations successfully executed"}'
sql:
- key: main
engine: mysql
function:
- key: runSchemaMigration
handler: index.handlerMigration
⚠️ WARNING: This trigger will permanently delete all data in the specified tables and clear the migrations history. This operation cannot be undone!
This trigger allows you to completely reset your database schema. It's useful for:
Important: The trigger will drop all tables including migration.
// Example usage in your Forge app
import { dropSchemaMigrations } from "forge-sql-orm";
export const dropMigrations = () => {
return dropSchemaMigrations();
};
Configure in manifest.yml
:
webtrigger:
- key: drop-schema-migration
function: dropMigrations
sql:
- key: main
engine: mysql
function:
- key: dropMigrations
handler: index.dropMigrations
⚠️ DEVELOPMENT ONLY: This trigger is designed for development environments only and should not be used in production.
This trigger retrieves the current database schema from Atlassian Forge SQL and generates SQL statements that can be used to recreate the database structure. It's useful for:
Security Considerations:
// Example usage in your Forge app
import { fetchSchemaWebTrigger } from "forge-sql-orm";
export const fetchSchema = async () => {
return fetchSchemaWebTrigger();
};
Configure in manifest.yml
:
webtrigger:
- key: fetch-schema
function: fetchSchema
sql:
- key: main
engine: mysql
function:
- key: fetchSchema
handler: index.fetchSchema
The response will contain SQL statements like:
SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS users (...);
CREATE TABLE IF NOT EXISTS orders (...);
SET foreign_key_checks = 1;
Security Considerations:
Best Practices:
⚠️ IMPORTANT NOTE: The query analysis features described below are experimental and should be used only for troubleshooting purposes. These features rely on TiDB's information_schema
and performance_schema
which may change in future updates. As of April 2025, these features are available but their future availability is not guaranteed.
Atlassian already provides comprehensive query analysis tools in the development console, including:
Our analysis tools are designed to complement these built-in features by providing additional insights directly from TiDB's system schemas. However, they should be used with caution and only for troubleshooting purposes.
Development and Troubleshooting Only
Schema Stability
information_schema
and performance_schema
Current Availability (April 2025)
information_schema
based analysis is currently functionalimport ForgeSQL from "forge-sql-orm";
const forgeSQL = new ForgeSQL();
const analyzeForgeSql = forgeSQL.analyze();
⚠️ For Troubleshooting Only: This feature should only be used during development and debugging sessions.
// Example usage for troubleshooting a specific query
const forgeSQL = new ForgeSQL();
const analyzeForgeSql = forgeSQL.analyze();
// Analyze a Drizzle query
const plan = await analyzeForgeSql.explain(
forgeSQL.select({
table1: testEntityJoin1,
table2: { name: testEntityJoin2.name, email: testEntityJoin2.email },
count: rawSql<number>`COUNT(*)`,
table3: {
table12: testEntityJoin1.name,
table22: testEntityJoin2.email,
table32: testEntity.id
},
})
.from(testEntityJoin1)
.innerJoin(testEntityJoin2, eq(testEntityJoin1.id, testEntityJoin2.id))
);
// Analyze a raw SQL query
const rawPlan = await analyzeForgeSql.explainRaw(
"SELECT * FROM users WHERE id = ?",
[1]
);
This analysis helps you understand:
This project is licensed under the MIT License.
Feel free to use it for commercial and personal projects.
FAQs
Drizzle ORM integration for Forge-SQL in Atlassian Forge applications.
The npm package forge-sql-orm receives a total of 220 weekly downloads. As such, forge-sql-orm popularity was classified as not popular.
We found that forge-sql-orm demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Bun 1.2.19 introduces isolated installs for smoother monorepo workflows, along with performance boosts, new tooling, and key compatibility fixes.
Security News
Popular npm packages like eslint-config-prettier were compromised after a phishing attack stole a maintainer’s token, spreading malicious updates.
Security News
/Research
A phishing attack targeted developers using a typosquatted npm domain (npnjs.com) to steal credentials via fake login pages - watch out for similar scams.