Database reference

API reference for GenSX Cloud SQL database components.

Installation


npm install @gensx/storage

DatabaseProvider

Provides SQL database capabilities to its child components.

Import


import { DatabaseProvider } from "@gensx/storage";

Props

Prop	Type	Default	Description
`kind`	`"filesystem" \| "cloud"`	Auto-detected	The storage backend to use. Defaults filesystem when running locally and cloud when deployed to the serverless runtime.
`rootDir`	`string`	`".gensx/databases"`	Root directory for storing database files (filesystem only)

Example


import { DatabaseProvider } from "@gensx/storage";
 
// Cloud storage (production)
const Workflow = gensx.Component("DatabaseWorkflow", ({ input }) => (
  <DatabaseProvider>
    <YourComponent input={input} />
  </DatabaseProvider>
));
 
// Local filesystem storage (development)
const DevWorkflow = gensx.Component("DevDatabaseWorkflow", ({ input }) => (
  <DatabaseProvider kind="filesystem" rootDir="./data">
    <YourComponent input={input} />
  </DatabaseProvider>
));

useDatabase

Hook that provides access to a specific SQL database.

Import


import { useDatabase } from "@gensx/storage";

Signature


function useDatabase(name: string): Database;

Parameters

Parameter	Type	Description
`name`	`string`	The database name to access

Returns

Returns a database object with methods to interact with SQL database.

Example


const db = await useDatabase("users");
const result = await db.execute("SELECT * FROM users WHERE id = ?", [
  "user-123",
]);

Database methods

The database object returned by useDatabase provides these methods:

execute

Executes a single SQL statement with optional parameters.


async execute(sql: string, params?: InArgs): Promise<DatabaseResult>

Parameters

Parameter	Type	Description
`sql`	`string`	SQL statement to execute
`params`	`InArgs`	Optional parameters for prepared statement

InArgs can be provided as an array of values or as a record with named parameters. Values can be primitives, booleans, Uint8Array, or Date objects.

Example


// Query with parameters
const result = await db.execute("SELECT * FROM users WHERE email = ?", [
  "user@example.com",
]);
 
// Insert data
await db.execute("INSERT INTO users (id, name, email) VALUES (?, ?, ?)", [
  "user-123",
  "John Doe",
  "john@example.com",
]);
 
// Update data
await db.execute("UPDATE users SET last_login = ? WHERE id = ?", [
  new Date().toISOString(),
  "user-123",
]);

Return value

Returns a result object with the following properties:


{
  columns: string[];        // Column names from result set
  rows: unknown[][];        // Array of result rows as arrays
  rowsAffected: number;     // Number of rows affected by statement
  lastInsertId?: number;    // ID of last inserted row (for INSERT statements)
}

batch

Executes multiple SQL statements in a single transaction.


async batch(statements: DatabaseStatement[]): Promise<DatabaseBatchResult>

Parameters

Parameter	Type	Description
`statements`	`DatabaseStatement[]`	Array of SQL statements with optional parameters

DatabaseStatement format


{
  sql: string;     // SQL statement
  params?: InArgs; // Optional parameters
}

Example


const results = await db.batch([
  {
    sql: "INSERT INTO users (id, name) VALUES (?, ?)",
    params: ["user-123", "John Doe"],
  },
  {
    sql: "INSERT INTO user_preferences (user_id, theme) VALUES (?, ?)",
    params: ["user-123", "dark"],
  },
]);

Return value

Returns a result object containing an array of individual results:


{
  results: [
    {
      columns: [],
      rows: [],
      rowsAffected: 1,
      lastInsertId: 42
    },
    {
      columns: [],
      rows: [],
      rowsAffected: 1,
      lastInsertId: 43
    }
  ]
}

executeMultiple

Executes multiple SQL statements as a script (without transaction semantics).


async executeMultiple(sql: string): Promise<DatabaseBatchResult>

Parameters

Parameter	Type	Description
`sql`	`string`	Multiple SQL statements separated by semicolons

Example


const results = await db.executeMultiple(`
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  );
 
  CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
`);

Return value

Returns a result object containing an array of individual results, one for each statement executed.

migrate

Executes SQL migration statements with foreign keys disabled.


async migrate(sql: string): Promise<DatabaseBatchResult>

Parameters

Parameter	Type	Description
`sql`	`string`	SQL migration statements

Example


const results = await db.migrate(`
  -- Migration v1: Initial schema
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  );
 
  -- Migration v2: Add last_login field
  ALTER TABLE users ADD COLUMN last_login TEXT;
`);

Return value

Returns a result object containing an array of individual results, one for each statement executed.

getInfo

Retrieves metadata about the database.


async getInfo(): Promise<DatabaseInfo>

Example


const info = await db.getInfo();
console.log(info);
// {
//   name: "users",
//   size: 12800,
//   lastModified: Date("2023-07-15T12:34:56Z"),
//   tables: [
//     {
//       name: "users",
//       columns: [
//         {
//           name: "id",
//           type: "TEXT",
//           notNull: true,
//           primaryKey: true
//         },
//         {
//           name: "name",
//           type: "TEXT",
//           notNull: true,
//           primaryKey: false
//         }
//       ]
//     }
//   ]
// }

Database management

Higher-level operations for managing databases are available through the useDatabaseStorage hook:

useDatabaseStorage

Hook that provides access to the database storage instance, which includes higher-level database management functions.


import { useDatabaseStorage } from "@gensx/storage";
 
// Get access to database management functions
const dbStorage = useDatabaseStorage();

The database storage object provides these management methods:

listDatabases

Lists databases in your project.


import { useDatabaseStorage } from "@gensx/storage";
 
const dbStorage = useDatabaseStorage();
const { databases, nextCursor } = await dbStorage.listDatabases();
console.log("Databases:", databases); // ["users", "products", "analytics"]

The method accepts an options object with these properties:

Option	Type	Description
`limit`	`number`	Maximum number of results to return per page
`cursor`	`string`	Cursor for pagination from previous response

Returns an object with:

databases: Array of database names
nextCursor: Cursor for the next page, or undefined if no more results

ensureDatabase

Creates a database if it doesn’t exist.


const dbStorage = useDatabaseStorage();
const { created } = await dbStorage.ensureDatabase("new-database");
if (created) {
  console.log("Database was created");
} else {
  console.log("Database already existed");
}

deleteDatabase

Deletes a database and all its data.


const dbStorage = useDatabaseStorage();
const { deleted } = await dbStorage.deleteDatabase("old-database");
if (deleted) {
  console.log("Database was deleted");
} else {
  console.log("Database could not be deleted");
}

hasEnsuredDatabase

Checks if a database has been ensured in the current session.


const dbStorage = useDatabaseStorage();
const isEnsured = dbStorage.hasEnsuredDatabase("my-database");
 
if (isEnsured) {
  console.log("Database has been ensured in this session");
} else {
  console.log("Database has not been ensured yet");
}

getDatabase

Get a database instance directly (without calling useDatabase).


const dbStorage = useDatabaseStorage();
 
// Get a database directly
// Note: This doesn't ensure the database exists, unlike useDatabase
const db = dbStorage.getDatabase("users");
 
// You may want to ensure it exists first
await dbStorage.ensureDatabase("users");
const db = dbStorage.getDatabase("users");

DatabaseClient

The DatabaseClient class provides a way to interact with GenSX databases outside of the GenSX workflow context, such as from regular Node.js applications or server endpoints.

Import


import { DatabaseClient } from "@gensx/storage";

Constructor


constructor(props?: DatabaseProviderProps)

Parameters

Parameter	Type	Default	Description
`props`	`DatabaseProviderProps`	`{}`	Optional configuration properties

Example


// Default client (uses filesystem locally, cloud in production)
const dbClient = new DatabaseClient();
 
// Explicitly use filesystem storage
const localClient = new DatabaseClient({
  kind: "filesystem",
  rootDir: "./my-data"
});
 
// Explicitly use cloud storage
const cloudClient = new DatabaseClient({ kind: "cloud" });

Methods

getDatabase

Get a database instance and ensure it exists first.


async getDatabase(name: string): Promise<Database>

Example


const db = await dbClient.getDatabase("users");
const results = await db.execute("SELECT * FROM users LIMIT 10");

ensureDatabase

Create a database if it doesn’t exist.


async ensureDatabase(name: string): Promise<EnsureDatabaseResult>

Example


const { created } = await dbClient.ensureDatabase("analytics");
if (created) {
  console.log("Database was created");
}

listDatabases

List databases.


async listDatabases(options?: { limit?: number; cursor?: string }): Promise<{
  databases: string[];
  nextCursor?: string;
}>

Example


const { databases, nextCursor } = await dbClient.listDatabases();
console.log("Available databases:", databases); // ["users", "products", "analytics"]

deleteDatabase

Delete a database.


async deleteDatabase(name: string): Promise<DeleteDatabaseResult>

Example


const { deleted } = await dbClient.deleteDatabase("temp-db");
if (deleted) {
  console.log("Database was removed");
}

databaseExists

Check if a database exists.


async databaseExists(name: string): Promise<boolean>

Example


if (await dbClient.databaseExists("users")) {
  console.log("Users database exists");
} else {
  console.log("Users database doesn't exist yet");
}

Usage in applications

The DatabaseClient is particularly useful when you need to access GenSX databases from:

Regular Express.js or Next.js API routes
Background jobs or workers
Custom scripts or tools
Any Node.js application outside the GenSX workflow context


// Example: Using DatabaseClient in an Express handler
import express from 'express';
import { DatabaseClient } from '@gensx/storage';
 
const app = express();
const dbClient = new DatabaseClient();
 
app.get('/api/users', async (req, res) => {
  try {
    const db = await dbClient.getDatabase('users');
    const result = await db.execute('SELECT * FROM users');
    res.json(result.rows);
  } catch (error) {
    console.error('Database error:', error);
    res.status(500).json({ error: 'Database error' });
  }
});
 
app.listen(3000, () => {
  console.log('Server running on port 3000');
});