Database reference

API reference for GenSX Cloud SQL database components.

Installation


npm install @gensx/storage

useDatabase

Hook that provides access to a specific SQL database.

Import


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

Signature


function useDatabase(
  name: string,
  options?: DatabaseStorageOptions,
): Promise<Database>;

Parameters

Parameter	Type	Description
`name`	`string`	The database name to access
`options`	`DatabaseStorageOptions`	Optional configuration properties

Returns

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

Example


// Simple usage
const db = await useDatabase("users");
const result = await db.execute("SELECT * FROM users WHERE id = ?", [
  "user-123",
]);
 
// With configuration
const db = await useDatabase("users", {
  kind: "cloud",
  project: "my-project",
  environment: "production",
});

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
//         }
//       ]
//     }
//   ]
// }

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(options?: DatabaseStorageOptions)

Parameters

Parameter	Type	Default	Description
`options`	`DatabaseStorageOptions`	`{}`	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");
});

DatabaseStorageOptions

Configuration properties for database operations.

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)
`project`	`string`	Auto-detected	Project to use for cloud storage. If you don’t set this, it’ll first check your `GENSX_PROJECT` environment variable, then look for the project name in your local `gensx.yaml` file.
`environment`	`string`	Auto-detected	Environment to use for cloud storage. If you don’t set this, it’ll first check your `GENSX_ENV` environment variable, then use whatever environment you’ve selected in the CLI with `gensx env select`.