Class SimpleDB<Table>

constructor

• new SimpleDB<Table extends SimpleTable = SimpleTable>(
      options?: {
          cacheVerbose?: boolean;
          charsToLog?: number;
          duckDbCache?: boolean | null;
          explainSQL?: boolean;
          expressionSyntax?: "sql" | "js";
          file?: string;
          logDuration?: boolean;
          logSQL?: boolean;
          memoryLimit?: string;
          overwrite?: boolean;
          progressBar?: boolean;
          readOnly?: boolean;
          rowsToLog?: number;
          tempDir?: string;
          typesToLog?: boolean;
      },
  ): SimpleDB<Table>

  Creates a new SimpleDB instance.

  DuckDB sessions use UTC so temporal parsing, extraction, and returned TIMESTAMP WITH TIME ZONE strings are consistent across environments.

  Existing tables and SDA index metadata are restored on first use. The __sda schema is reserved for versioned SDA metadata. SQLite files can be imported with loadDB() but cannot be used as the persistent database.

  Type Parameters

  • Table extends SimpleTable = SimpleTable

  Parameters

  • options: {
        cacheVerbose?: boolean;
        charsToLog?: number;
        duckDbCache?: boolean | null;
        explainSQL?: boolean;
        expressionSyntax?: "sql" | "js";
        file?: string;
        logDuration?: boolean;
        logSQL?: boolean;
        memoryLimit?: string;
        overwrite?: boolean;
        progressBar?: boolean;
        readOnly?: boolean;
        rowsToLog?: number;
        tempDir?: string;
        typesToLog?: boolean;
    } = {}

    Configuration options for the SimpleDB instance.

    • OptionalcacheVerbose?: boolean

      Whether to log cache hits and misses, code and input changes, TTL status, and cache read/write timing.

    • OptionalcharsToLog?: number

      The maximum characters to display for text and stringified nested cells, including truncation markers (default: 75). Independent of column width. Use Infinity for unlimited text.

    • OptionalduckDbCache?: boolean | null

      A flag indicating whether to use DuckDB's external file cache.

    • OptionalexplainSQL?: boolean

      A flag indicating whether to log DuckDB query plans for supported statements.

    • OptionalexpressionSyntax?: "sql" | "js"

      Operator syntax for expressions and custom queries: "js" (default) translates JavaScript-style operators in SQL expressions; "sql" preserves SQL unchanged.

    • Optionalfile?: string

      A .db or .duckdb file to open or create. Defaults to an in-memory database.

    • OptionallogDuration?: boolean

      A flag indicating whether to log the total execution duration.

    • OptionallogSQL?: boolean

      A flag indicating whether to log SQL immediately before execution.

    • OptionalmemoryLimit?: string

      The maximum amount of memory DuckDB is allowed to use (e.g., '4GB'). Defaults to 80% of system RAM.

    • Optionaloverwrite?: boolean

      If true, replaces an existing file on first use. Defaults to false, which opens the existing file.

    • OptionalprogressBar?: boolean

      A flag indicating whether to display a progress bar for long-running operations.

    • OptionalreadOnly?: boolean

      Opens an existing DuckDB file read-only. Defaults to false. Requires a file and cannot be combined with overwrite.

    • OptionalrowsToLog?: number

      The number of rows to display when logging a table.

    • OptionaltempDir?: string

      The path to the directory used for temporary files when data exceeds the memory limit (e.g., '/tmp/duckdb_swap'). Defaults to .tmp for in-memory databases or <file>.tmp for file-based databases. Automatically removed when calling close().

    • OptionaltypesToLog?: boolean

      A flag indicating whether to include data types when logging a table.

  Returns SimpleDB<Table>

  Example

  const sdb = new SimpleDB({
    logSQL: true,
    explainSQL: true,
  });
  Copy

customQuery

• customQuery(
      query: string,
      options?: { returnData?: boolean; table?: string },
  ): Promise<{ [key: string]: unknown }[] | null>

  Executes a custom SQL query directly against the DuckDB instance. With the default SimpleDB.expressionSyntax: "js", queries support JavaScript-style operators (&&, ||, ===, !==). Set expressionSyntax: "sql" for unchanged SQL. Queries run in UTC. When data is returned, temporal values use the same JavaScript representations as SimpleTable.getData().

  customQuery() bypasses the dependency and table-generation tracking used by SimpleTable.cache(). Reading or changing a table with customQuery() can therefore cause cache() to return stale data. Include a value that identifies the custom query's dependencies in the cache's options.inputs (such as a table content hash), or use tracked SimpleTable methods.

  Parameters

  • query: string

    The SQL query string to execute.

  • options: { returnData?: boolean; table?: string } = {}

    Configuration options for the query.

    • OptionalreturnData?: boolean

      If true, the query result is returned. Defaults to false.

    • Optionaltable?: string

      The name of the table associated with the query, primarily used for debugging and logging.

  Returns Promise<{ [key: string]: unknown }[] | null>

  A promise that resolves to the query result as an array of objects if returnData is true, otherwise null.

  Example

  // Execute a query without returning data
  await sdb.customQuery("CREATE TABLE young_employees AS SELECT * FROM employees WHERE age > 30");
  Copy

  Example

  // Execute a query and return the results
  const youngEmployees = await sdb.customQuery(
    "SELECT * FROM employees WHERE age < 30",
    { returnData: true }
  );
  console.log(youngEmployees);
  Copy

getExtensions

• getExtensions(): Promise<{ [key: string]: unknown }[]>

  Returns a list of installed DuckDB extensions.

  Returns Promise<{ [key: string]: unknown }[]>

  A promise that resolves to an array of objects, each representing an installed extension.

  Example

  // Get a list of all installed extensions
  const extensions = await sdb.getExtensions();
  console.log(extensions); // Output: [{ extension_name: "spatial", loaded: true, ... }]
  Copy

loadDB

• loadDB(file: string): Promise<SimpleDB<Table>>

  Imports a copy of a .db or .duckdb (DuckDB) or .sqlite (SQLite) file into the current database. The source is opened read-only and detached after importing; subsequent transformations do not modify the source file. Imports work with in-memory and writable persistent databases. Existing table-name conflicts are rejected. A failed import may leave partial changes in the destination database.

  DuckDB files restore embedded SDA index definitions when present. SQLite imports copy data without SDA index metadata. The __sda schema is reserved for SDA metadata. To edit an existing DuckDB file in place, use new SimpleDB({ file }).

  Parameters

  • file: string

    The relative or absolute path to the database file.

  Returns Promise<SimpleDB<Table>>

  A promise that resolves to the database, so methods can be chained.

  Example

  // Load a DuckDB database file
  await sdb.loadDB("./my_database.db");
  Copy

  Example

  // Import SQLite tables without modifying the original file
  await sdb.loadDB("./my_database.sqlite");
  Copy

  Example

  // Import a copy into a persistent DuckDB database
  const sdb = new SimpleDB({ file: "./analysis.duckdb" });
  await sdb.loadDB("./archive.db");
  await sdb.close();
  Copy

writeDB

• writeDB(
      file: string,
      options?: { metadata?: boolean; overwrite?: boolean },
  ): Promise<SimpleDB<Table>>

  Exports a snapshot of the current database after executing pending work. Does not change the working database or where subsequent changes persist. DuckDB outputs (.db and .duckdb) preserve database objects and embed SDA index definitions in the reserved __sda schema.

  SQLite output (.sqlite) materializes main-schema tables and views as tables. It does not preserve DuckDB schemas, indexes, constraints, or SDA metadata, and SQLite type conversion may lose type information. Unsupported conversions fail without replacing an existing destination.

  Existing files require explicit overwrite permission. The completed export is published only after its connection is detached. Database files attached to this instance, directories, and symbolic links cannot be replaced.

  Parameters

  • file: string

    The relative or absolute path to the output file.

  • options: { metadata?: boolean; overwrite?: boolean } = {}

    Configuration options for writing the database.

    • Optionalmetadata?: boolean

      If false, omits logical SDA index definitions from DuckDB exports. Defaults to true. Does not control physical indexes; SQLite exports never include SDA metadata.

    • Optionaloverwrite?: boolean

      If true, permits atomic replacement of an existing output file. Defaults to false.

  Returns Promise<SimpleDB<Table>>

  A promise that resolves to the database, so methods can be chained.

  Example

  // Write the current database to a DuckDB file
  await sdb.writeDB("./my_exported_database.db");
  Copy

  Example

  // Explicitly replace an earlier snapshot
  await sdb.writeDB("./my_exported_database.duckdb", { overwrite: true });
  Copy

  Example

  // Export table data for use with SQLite
  await sdb.writeDB("./my_exported_database.sqlite");
  Copy

close

• close(): Promise<SimpleDB<SimpleTable>>

  Executes pending transformations, saves SDA metadata for writable persistent databases, and closes the connection and instance. Also cleans up temporary files and the cache. Does not copy, compact, or replace the database file.

  Returns Promise<SimpleDB<SimpleTable>>

  A promise that resolves to the SimpleDB instance after cleanup.

  Example

  // close() executes queued transformations before cleaning up resources.
  table.loadData("data.csv").convert({ price: "number" });
  await sdb.close();
  Copy

  Throws

  An error after cleanup when pending execution or cleanup fails.

run

• run(): Promise<SimpleDB<SimpleTable>>

  Executes all queued methods across every table in the database. Sync builder methods (like filter() or convert()) only queue their operation; execution happens when an async observer method (like getData(), log(), or writeData()) is awaited. Use run() when your script ends in pure mutations with nothing to observe and you want the work done now.

  The whole database is flushed in program order, so operations on different tables execute exactly in the order they were queued. This is the database-level counterpart to SimpleTable.run().

  Returns Promise<SimpleDB<SimpleTable>>

  A promise that resolves to the SimpleDB instance once the queued methods have been executed.

  Example

  // Nothing is observed after the mutations, so run() executes them.
  table1.loadData("data1.csv").convert({ price: "number" });
  table2.loadData("data2.csv").filter(`price > 0`);
  await sdb.run();
  Copy

cacheSourcesUsed

cacheTimeSaved

cacheTimeWriting

cacheVerbose

duckDbCache

durationStart

explainSQL

const sdb = new SimpleDB({ explainSQL: true });
Copy

ReadonlyexpressionSyntax

const sdb = new SimpleDB({ expressionSyntax: "js" }); // The default mode
// || becomes SQL OR: keep rows where at least one condition is true.
await sdb.newTable().loadArray([{ active: true, admin: false }])
  .filter("active || admin").log();
Copy

const sdb = new SimpleDB({ expressionSyntax: "js" });
// && becomes SQL AND: both conditions must be true.
// === becomes SQL =: compare the age with 18.
await sdb.newTable().loadArray([{ active: true, age: 18 }])
  .filter("active && age === 18").log();
Copy

const sdb = new SimpleDB({ expressionSyntax: "js" });
// SQL AND and OR also work in JS mode; they pass through unchanged.
// Keep active adults, or anyone who is an admin.
await sdb.newTable().loadArray([{ active: true, age: 18, admin: false }])
  .filter("(active AND age >= 18) OR admin").log();
Copy

const sdb = new SimpleDB({ expressionSyntax: "js" });
// Bare column names do not establish concatenation. Use concat() explicitly.
await sdb.newTable().loadArray([{ first: "Jane", last: "Doe" }])
  .addColumn("name", "string", "concat(first, ' ', last)").log();
Copy

const sdb = new SimpleDB(); // JS mode is the default
// The string literal ' ' makes concatenation clear, so both || stay unchanged.
await sdb.newTable().loadArray([{ first: "Jane", last: "Doe" }])
  .addColumn("name", "string", "first || ' ' || last").log();
Copy

const sdb = new SimpleDB({ expressionSyntax: "sql" });
// SQL mode preserves || as concatenation: the name becomes "Jane Doe".
await sdb.newTable().loadArray([{ first: "Jane", last: "Doe" }])
  .addColumn("name", "string", "first || ' ' || last").log();
Copy

const sdb = new SimpleDB({ expressionSyntax: "sql" });
// Use SQL AND and OR for logical conditions; && and || are not translated.
// Both active and adult must be true, unless admin is true.
await sdb.newTable().loadArray([{ active: true, age: 18, admin: false }])
  .filter("(active AND age >= 18) OR admin").log();
Copy

file

logDuration

logSQL

const sdb = new SimpleDB({ logSQL: true });
Copy

memoryLimit

overwrite

progressBar

readOnly

const sdb = new SimpleDB({ file: "./archive.duckdb", readOnly: true });
const table = await sdb.getTable("employees");
await table.log();
await sdb.close();
Copy

tableClass

class MyTable extends SimpleTable {
  customMethod() { return "hello"; }
}

class MyDB extends SimpleDB {
  constructor(options?: SimpleDBOptions) {
    super(options);
    this.tableClass = MyTable;
  }
}

const db = new MyDB();
const table = db.newTable("myTable");
console.log(table.customMethod()); // "hello"
Copy

tableIncrement

tempDir

getTable

• getTable(name: string): Promise<Table>

  Retrieves an existing SimpleTable instance from the database.

  Parameters

  • name: string

    The name of the table to retrieve.

  Returns Promise<Table>

  A promise that resolves to the SimpleTable instance if found.

  Example

  // Retrieve the "employees" table
  const employees = await sdb.getTable("employees");
  await employees.log();
  Copy

getTableNames

• getTableNames(): Promise<string[]>

  Returns an array of all table names in the database, sorted alphabetically.

  Returns Promise<string[]>

  A promise that resolves to an array of table names.

  Example

  // Get all table names
  const tableNames = await sdb.getTableNames();
  console.log(tableNames); // Output: ["employees", "customers"]
  Copy

getTables

• getTables(): readonly Table[]

  Returns an array of all SimpleTable instances in the database.

  The returned array is a snapshot and cannot mutate the database's internal table registry.

  Returns readonly Table[]

  A read-only array of SimpleTable instances.

  Example

  // Get all SimpleTable instances
  const tables = sdb.getTables();
  Copy

hasTable

• hasTable(table: string | Table): Promise<boolean>

  Checks if a table exists in the database.

  Parameters

  • table: string | Table

    The name of the table or a SimpleTable instance.

  Returns Promise<boolean>

  A promise that resolves to true if the table exists, false otherwise.

  Example

  // Check if a table named "employees" exists
  const exists = await sdb.hasTable("employees");
  console.log(exists); // Output: true or false
  Copy

  Example

  // Check if a SimpleTable instance exists in the database
  const myTable = sdb.newTable("my_data");
  const existsInstance = await sdb.hasTable(myTable);
  console.log(existsInstance); // Output: true or false
  Copy

logTableNames

• logTableNames(): Promise<SimpleDB<Table>>

  Logs the names of all tables in the database to the console, sorted alphabetically.

  Returns Promise<SimpleDB<Table>>

  A promise that resolves to the database, so methods can be chained.

  Example

  // Log all table names to the console
  await sdb.logTableNames();
  // Example output: SimpleDB - Tables:  ["employees","customers"]
  Copy

newTable

• newTable(name?: string): Table

  Creates a new SimpleTable instance within the database. The name must be unique among registered tables, ignoring ASCII letter case.

  Parameters

  • Optionalname: string

    The name of the new table. If not provided, a default name is generated (e.g., "table1").

  Returns Table

  A new table instance.

  Example

  // Create a table with a default name (e.g., "table1", "table2", etc.)
  const dataTable = await sdb
    .newTable()
    .loadArray([{ value: 1 }])
    .log();
  Copy

  Example

  // Create a table with a specific name
  const employees = await sdb
    .newTable("employees")
    .loadData("employees.csv")
    .log();
  Copy

removeTables

• removeTables(
      tables: string | Table | (string | Table)[],
  ): Promise<SimpleDB<Table>>

  Removes one or more tables from the database.

  Parameters

  • tables: string | Table | (string | Table)[]

    A single table or an array of tables to remove, specified by name or as SimpleTable instances. Pass "all" to remove all tables.

  Returns Promise<SimpleDB<Table>>

  A promise that resolves to the database, so methods can be chained.

  Example

  // Remove a single table by name
  await sdb.removeTables("employees");
  Copy

  Example

  // Remove multiple tables by name
  await sdb.removeTables(["customers", "products"]);
  Copy

  Example

  // Remove a single table using a SimpleTable instance
  const employeesTable = sdb.newTable("employees");
  // ... load data ...
  await sdb.removeTables(employeesTable);
  Copy

  Example

  // Remove all tables
  await sdb.removeTables("all");
  Copy

selectTables

• selectTables(
      tables: string | Table | (string | Table)[],
  ): Promise<SimpleDB<Table>>

  Selects one or more tables to keep in the database, removing all others.

  Parameters

  • tables: string | Table | (string | Table)[]

    A single table or an array of tables to select, specified by name or as SimpleTable instances.

  Returns Promise<SimpleDB<Table>>

  A promise that resolves to the database, so methods can be chained.

  Example

  // Select a single table by name, removing all other tables
  await sdb.selectTables("employees");
  Copy

  Example

  // Select multiple tables by name, removing all other tables
  await sdb.selectTables(["customers", "products"]);
  Copy

  Example

  // Select a single table using a SimpleTable instance
  const employeesTable = sdb.newTable("employees");
  // ... load data ...
  await sdb.selectTables(employeesTable);
  Copy