The Simple Data Analysis Library

June 22, 2026 · View on GitHub

To install the library with Deno, use:

deno add jsr:@nshiab/simple-data-analysis

To install the library with Node.js, use:

npx jsr add @nshiab/simple-data-analysis

To start, create a SimpleDB instance and then a SimpleTable from this instance:

import { SimpleDB } from "@nshiab/simple-data-analysis";

const sdb = new SimpleDB();
const table = sdb.newTable("myTable"); // This returns a SimpleTable instance
await table.loadData("path/to/your/data.csv");

// You can now perform various data analysis operations on the table.

await sdb.done(); // Ensure to call done when you're finished.

class SimpleDB

Manages a DuckDB database instance, providing a simplified interface for database operations. Extends the core SimpleDB class from simple-data-analysis-core to use our extended SimpleTable class which includes additional AI, Google Sheets, and charting methods.

Constructor

Creates a new SimpleDB instance.

Parameters

  • options: Configuration options for the SimpleDB instance.
  • options.file: The path to the database file. If not provided, an in-memory database is used.
  • options.overwrite: A flag indicating whether to overwrite the database file if it already exists.
  • options.logDuration: A flag indicating whether to log the total execution duration.
  • options.nbRowsToLog: The number of rows to display when logging a table.
  • options.nbCharactersToLog: The maximum number of characters to display for text-based cells.
  • options.types: A flag indicating whether to include data types when logging a table.
  • options.cacheVerbose: A flag indicating whether to log verbose cache-related messages.
  • options.debug: A flag indicating whether to log debugging information.
  • options.duckDbCache: A flag indicating whether to use DuckDB's external file cache.
  • options.progressBar: A flag indicating whether to display a progress bar for long-running operations.

Methods

newTable

Creates a new SimpleTable instance within the database.

Signature
newTable(name?: string): Table;
Parameters
  • name: The name of the new table. If not provided, a default name is generated (e.g., "table1").
Returns

A new table instance.

Examples
// Create a table with a default name (e.g., "table1", "table2", etc.)
const dataTable = sdb.newTable();

// Create a table with a specific name
const employees = sdb.newTable("employees");

getTable

Retrieves an existing SimpleTable instance from the database.

Signature
async getTable(name: string): Promise<Table>;
Parameters
  • name: The name of the table to retrieve.
Returns

A promise that resolves to the SimpleTable instance if found.

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

removeTables

Removes one or more tables from the database.

Signature
async removeTables(tables: Table | string | (Table | string)[]): Promise<void>;
Parameters
  • tables: A single table or an array of tables to remove, specified by name or as SimpleTable instances. Pass "all" to remove all tables.
Returns

A promise that resolves when the tables have been removed.

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

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

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

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

selectTables

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

Signature
async selectTables(tables: Table | string | (Table | string)[]): Promise<void>;
Parameters
  • tables: A single table or an array of tables to select, specified by name or as SimpleTable instances.
Returns

A promise that resolves when the tables have been selected.

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

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

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

getTableNames

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

Signature
async getTableNames(): Promise<string[]>;
Returns

A promise that resolves to an array of table names.

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

logTableNames

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

Signature
async logTableNames(): Promise<void>;
Returns

A promise that resolves when the table names have been logged.

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

getTables

Returns an array of all SimpleTable instances in the database.

Signature
async getTables(): Promise<Table[]>;
Returns

A promise that resolves to an array of SimpleTable instances.

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

hasTable

Checks if a table exists in the database.

Signature
async hasTable(table: Table | string): Promise<boolean>;
Parameters
  • table: The name of the table or a SimpleTable instance.
Returns

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

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

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

getExtensions

Returns a list of installed DuckDB extensions.

Signature
async getExtensions(): Promise<Record<string, string | number | boolean | Date | null>[]>;
Returns

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

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

customQuery

Executes a custom SQL query directly against the DuckDB instance.

If you want to force the returned data to match the types of the columns, you can use the types option.

Signature
async customQuery(query: string, options?: { returnDataFrom?: "query" | "none"; table?: string; types?: Record<string, string> }): Promise<Record<string, string | number | boolean | Date | null>[] | null>;
Parameters
  • query: The SQL query string to execute.
  • options: Configuration options for the query.
  • options.returnDataFrom: Specifies whether to return data from the query. Can be "query" to return data or "none" (default) to not return data.
  • options.table: The name of the table associated with the query, primarily used for debugging and logging.
  • options.types: An optional object specifying data types for the query parameters.
Returns

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

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

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

loadDB

Loads a database from a specified file into the current SimpleDB instance. Supported file types are .db (DuckDB) and .sqlite (SQLite).

Signature
async loadDB(file: string, options?: { name?: string; detach?: boolean }): Promise<void>;
Parameters
  • file: The absolute path to the database file (e.g., "./my_database.db").
  • options: Configuration options for loading the database.
  • options.name: The name to assign to the loaded database within the DuckDB instance. Defaults to the file name without extension.
  • options.detach: If true (default), the database is detached after loading its contents into memory. If false, the database remains attached.
Returns

A promise that resolves when the database has been loaded.

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

// Load a SQLite database file and keep it attached
await sdb.loadDB("./my_database.sqlite", { detach: false });

// Load a database with a custom name
await sdb.loadDB("./archive.db", { name: "archive_db" });

writeDB

Writes the current state of the database to a specified file. Supported output file types are .db (DuckDB) and .sqlite (SQLite).

Signature
async writeDB(file: string, options?: { noMetaData?: boolean }): Promise<void>;
Parameters
  • file: The absolute path to the output file (e.g., "./my_exported_database.db").
  • options: Configuration options for writing the database.
  • options.noMetaData: If true, metadata files (indexes) are not created alongside the database file. Defaults to false.
Returns

A promise that resolves when the database has been written to the file.

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

// Write the current database to a SQLite file without metadata
await sdb.writeDB("./my_exported_database.sqlite", { noMetaData: true });

done

Frees up memory by closing the database connection and instance, and cleans up the cache. If the database is file-based, it also compacts the database file to optimize storage.

Signature
async done(): Promise<SimpleDB>;
Returns

A promise that resolves to the SimpleDB instance after cleanup.

Examples
// Close the database and clean up resources
await sdb.done();

Examples

// Create an in-memory database instance
const sdb = new SimpleDB();
// Create a new table named "employees"
const employees = sdb.newTable("employees");
// Load data from a CSV file into the "employees" table
await employees.loadData("./employees.csv");
// Log the first few rows of the "employees" table to the console
await employees.logTable();
// Close the database connection and clean up resources
await sdb.done();

// Create a persistent database instance, saving data to a file
// To load an existing database, use the `loadDB` method instead
const sdb = new SimpleDB({ file: "./my_database.db" });
// Perform database operations...
// Close the database connection, which saves changes to the specified file
await sdb.done();

// Create a database instance with custom options
const sdb = new SimpleDB({
  debug: true, // Enable debugging output
  nbRowsToLog: 20, // Set the number of rows to log by default
});

class SimpleTable

Represents a table within a SimpleDB database, capable of handling tabular, geospatial, and vector data. Extends the core SimpleTable class from simple-data-analysis-core to include additional AI, Google Sheets, and charting methods.

Constructor

Creates an instance of SimpleTable.

Parameters

  • name: The name of the table.
  • simpleDB: The SimpleDB instance that this table belongs to.
  • options: An optional object with configuration options:
  • options.debug: A boolean indicating whether to enable debug mode.
  • options.nbRowsToLog: The number of rows to log when displaying table data.
  • options.nbCharactersToLog: The maximum number of characters to log for strings. Useful to avoid logging large text content.
  • options.types: A boolean indicating whether to include data types when logging a table.

Methods

aiRowByRow

Applies a prompt to the value of each row in a specified column, storing the AI's response in a new column. This method can send requests concurrently and in batches, but is not using a pool, so it may not be the most efficient method for processing very large tables. Check aiRowByRowPool for a different approach, especially regarding error handling.

This method automatically appends instructions to your prompt; set verbose to true to see the full prompt.

This method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_MODEL) or directly via options, with options taking precedence.

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_MODEL to your desired model name. You can also pass your instance of Ollama to the ollama option.

To manage rate limits, use batchSize to process multiple rows per request and rateLimitPerMinute to introduce delays between requests. For higher rate limits (business/professional accounts), concurrent allows parallel requests.

The cache option enables local caching of results in .journalism-cache (from the askAI function in the journalism library). Remember to add .journalism-cache to your .gitignore.

If the AI returns fewer items than expected in a batch, or if a custom test function fails, the retry option (a number greater than 0) will reattempt the request.

Temperature is set to 0 for reproducibility, though consistency cannot be guaranteed.

This method does not support tables containing geometries.

Signature
async aiRowByRow(column: string, newColumn: string | string[], prompt: string, options?: { batchSize?: number; concurrent?: number; cache?: boolean; test?: (result: Record<string, unknown>) => void; retry?: number; model?: string; temperature?: number; apiKey?: string; vertex?: boolean; project?: string; location?: string; ollama?: boolean | Ollama; verbose?: boolean; rateLimitPerMinute?: number; clean?: (response: unknown) => unknown; contextWindow?: number; thinkingBudget?: number; thinkingLevel?: "minimal" | "low" | "medium" | "high"; safetyEnabled?: boolean; webSearch?: boolean; extraInstructions?: string; schemaJson?: unknown; metrics?: { totalCost: number; totalInputTokens: number; totalOutputTokens: number; totalRequests: number } }): Promise<void>;
Parameters
  • column: The name of the column to be used as input for the AI prompt.
  • newColumn: The name of the new column (or an array of column names) where the AI's response will be stored.
  • prompt: The input string to guide the AI's response.
  • options: Configuration options for the AI request.
  • options.batchSize: The number of rows to process in each batch. Defaults to 1.
  • options.concurrent: The number of concurrent requests to send. Defaults to 1.
  • options.cache: If true, the results will be cached locally. Defaults to false.
  • options.test: A function to validate the returned data. If it throws an error, the request will be retried (if retry is set). Defaults to undefined.
  • options.retry: The number of times to retry the request in case of failure. Defaults to 0.
  • options.rateLimitPerMinute: The rate limit for AI requests in requests per minute. The method will wait between requests if necessary. Defaults to undefined (no limit).
  • options.model: The AI model to use. Defaults to the AI_MODEL environment variable.
  • options.temperature: The temperature setting for the AI model, controlling the randomness of the output. Defaults to 0.
  • options.apiKey: The API key for the AI service. Defaults to the AI_KEY environment variable.
  • options.vertex: If true, uses Vertex AI. Automatically set to true if AI_PROJECT and AI_LOCATION are set in the environment. Defaults to false.
  • options.project: The Google Cloud project ID for Vertex AI. Defaults to the AI_PROJECT environment variable.
  • options.location: The Google Cloud location for Vertex AI. Defaults to the AI_LOCATION environment variable.
  • options.ollama: If true, uses Ollama. Defaults to the OLLAMA environment variable. If you want your Ollama instance to be used, you can pass it here too.
  • options.verbose: If true, logs additional debugging information, including the full prompt sent to the AI. Defaults to false.
  • options.clean: A function to clean the AI's response after JSON parsing, testing, caching, and storing. Defaults to undefined.
  • options.contextWindow: An option to specify the context window size for Ollama models. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.thinkingBudget: Sets the reasoning token budget: 0 to disable (default, though some models may reason regardless), -1 for a dynamic budget, or > 0 for a fixed budget. For Ollama models, any non-zero value simply enables reasoning, ignoring the specific budget amount.
  • options.thinkingLevel: Sets the thinking level for reasoning: "minimal", "low", "medium", or "high", which some models expect instead of thinkingBudget. Takes precedence over thinkingBudget if both are provided. For Ollama models, any value enables reasoning.
  • options.safetyEnabled: Controls whether safety filters are enabled. If set to true, filters are active; if false, they are disabled. By default, this is false when using Vertex AI and true otherwise. This setting can be explicitly overridden for any model.
  • options.webSearch: (Gemini only) If true, enables web search grounding for the AI's responses. Be careful of extra costs. Defaults to false.
  • options.schemaJson: A Zod JSON schema object for structured output. This overrides the default schema based on the 'newColumn' names.
  • options.extraInstructions: Additional instructions to append to the prompt, providing more context or guidance for the AI.
  • options.metrics: An object to track cumulative metrics across multiple AI requests. Pass an object with totalCost, totalInputTokens, totalOutputTokens, and totalRequests properties (all initialized to 0). The function will update these values after each request. Note: totalCost is only calculated for Google GenAI models, not for Ollama.
Returns

A promise that resolves when the AI processing is complete.

Examples
// New table with a "name" column.
await table.loadArray([
  { name: "Marie" },
  { name: "John" },
  { name: "Alex" },
]);

// Ask the AI to categorize names into a new "gender" column.
await table.aiRowByRow(
  "name",
  "gender",
  `Guess whether it's a "Man" or a "Woman". If it could be both, return "Neutral".`,
  {
    cache: true, // Cache results locally
    batchSize: 10, // Process 10 rows at once
    test: (data: { [key: string]: unknown }) => { // Validate AI's response
      if (
        typeof data.gender !== "string" ||
        !["Man", "Woman", "Neutral"].includes(data.gender)
      ) {
        throw new Error(`Invalid response: ${data.gender}`);
      }
    },
    retry: 3, // Retry up to 3 times on failure
    rateLimitPerMinute: 15, // Limit requests to 15 per minute
    verbose: true, // Log detailed information
  },
);

// Example results:
// [
//   { name: "Marie", gender: "Woman" },
//   { name: "John", gender: "Man" },
//   { name: "Alex", gender: "Neutral" },
// ]

await table.loadArray([
  { city: "Marrakech" },
  { city: "Kyoto" },
  { city: "Auckland" },
]);

await table.aiRowByRow(
  "city",
  ["country", "continent"], // Multiple new columns
  `Give me the country and continent of the city.`,
  { verbose: true },
);

// Example results:
// [
//   { city: "Marrakech", country: "Morocco", continent: "Africa" },
//   { city: "Kyoto", country: "Japan", continent: "Asia" },
//   { city: "Auckland", country: "New Zealand", continent: "Oceania" },
// ]

aiRowByRowPool

Applies a prompt to the value of each row in a specified column using a pool-based approach, storing the AI's response in new columns and any errors in a designated error column. Unlike aiRowByRow, this method uses a worker pool for better control over concurrent requests and stores errors instead of throwing them, making it ideal for processing large tables where some rows may fail.

This method automatically appends instructions to your prompt; set verbose to true to see the full prompt.

This method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_MODEL).

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_MODEL to your desired model name.

The pool size controls how many concurrent AI requests can run simultaneously. The batchSize option processes multiple rows per request. For example, with poolSize: 5 and batchSize: 10, up to 5 requests can run concurrently, each processing 10 rows.

The cache option enables local caching of results in .journalism-cache (from the askAI function in the journalism library). Remember to add .journalism-cache to your .gitignore.

If the AI returns fewer items than expected in a batch, or if a custom test function fails, the retry option (a number greater than 0) will reattempt the request. The retryCheck function allows conditional retries based on error inspection.

The minRequestDurationMs option sets a minimum duration for each request, useful for respecting rate limits when you know the allowed requests per time period.

Temperature is set to 0 for reproducibility, though consistency cannot be guaranteed.

This method does not support tables containing geometries.

Signature
async aiRowByRowPool(column: string, newColumn: string | string[], errorColumn: string, prompt: string, poolSize: number, options?: { cache?: boolean; batchSize?: number; logProgress?: boolean; verbose?: boolean; includeThoughts?: boolean; test?: (result: Record<string, unknown>) => void; retry?: number; retryCheck?: (error: unknown) => Promise<boolean> | boolean; extraInstructions?: string; minRequestDurationMs?: number; clean?: (response: unknown) => unknown; contextWindow?: number; thinkingBudget?: number; thinkingLevel?: "minimal" | "low" | "medium" | "high"; safetyEnabled?: boolean; webSearch?: boolean; schemaJson?: unknown; model?: string; temperature?: number; apiKey?: string; vertex?: boolean; project?: string; location?: string; ollama?: boolean | Ollama; metrics?: { totalCost: number; totalInputTokens: number; totalOutputTokens: number; totalRequests: number } }): Promise<void>;
Parameters
  • column: The name of the column to be used as input for the AI prompt.
  • newColumn: The name of the new column (or an array of column names) where the AI's response will be stored. If an error occurs for a row, the new column(s) for that row will be set to NULL.
  • errorColumn: The name of the column where error messages will be stored. Successful requests will have NULL in this column.
  • prompt: The input string to guide the AI's response.
  • poolSize: The number of concurrent AI requests to run simultaneously in the pool.
  • options: Configuration options for the AI request.
  • options.cache: If true, the results will be cached locally. Defaults to false.
  • options.batchSize: The number of rows to process in each batch. Defaults to 1.
  • options.logProgress: If true, logs progress information during processing. Defaults to false.
  • options.verbose: If true, logs additional debugging information, including the full prompt sent to the AI. Defaults to false.
  • options.includeThoughts: If true, includes the AI model's reasoning process in the logged output when using models that support extended thinking. Only relevant when used with thinking-capable models. Defaults to false.
  • options.test: A function to validate the returned data. If it throws an error, the request will be retried (if retry is set). Defaults to undefined.
  • options.retry: The number of times to retry the request in case of failure. Defaults to 0.
  • options.retryCheck: A function that receives an error and returns a boolean indicating whether to retry. Useful for conditional retries based on error type. Defaults to undefined.
  • options.extraInstructions: Additional instructions to append to the prompt, providing more context or guidance for the AI.
  • options.minRequestDurationMs: The minimum duration in milliseconds for each request. Useful for respecting rate limits. Defaults to undefined (no minimum).
  • options.clean: A function to clean the AI's response after JSON parsing, testing, caching, and storing. Defaults to undefined.
  • options.contextWindow: An option to specify the context window size for Ollama models. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.thinkingBudget: Sets the reasoning token budget: 0 to disable (default, though some models may reason regardless), -1 for a dynamic budget, or > 0 for a fixed budget. For Ollama models, any non-zero value simply enables reasoning, ignoring the specific budget amount.
  • options.thinkingLevel: Sets the thinking level for reasoning: "minimal", "low", "medium", or "high", which some models expect instead of thinkingBudget. Takes precedence over thinkingBudget if both are provided. For Ollama models, any value enables reasoning.
  • options.safetyEnabled: Controls whether safety filters are enabled. If set to true, filters are active; if false, they are disabled. By default, this is false when using Vertex AI and true otherwise. This setting can be explicitly overridden for any model.
  • options.webSearch: (Gemini only) If true, enables web search grounding for the AI's responses. Be careful of extra costs. Defaults to false.
  • options.schemaJson: A Zod JSON schema object for structured output. This overrides the default schema based on the 'newColumn' names.
  • options.model: The AI model to use. Defaults to the AI_MODEL environment variable.
  • options.temperature: The temperature setting for the AI model, controlling the randomness of the output. Defaults to 0.
  • options.apiKey: The API key for the AI service. Defaults to the AI_KEY environment variable.
  • options.vertex: If true, uses Vertex AI. Automatically set to true if AI_PROJECT and AI_LOCATION are set in the environment. Defaults to false.
  • options.project: The Google Cloud project ID for Vertex AI. Defaults to the AI_PROJECT environment variable.
  • options.location: The Google Cloud location for Vertex AI. Defaults to the AI_LOCATION environment variable.
  • options.ollama: If true, uses Ollama. Defaults to the OLLAMA environment variable. If you want your Ollama instance to be used, you can pass it here too.
  • options.metrics: An object to track cumulative metrics across multiple AI requests. Pass an object with totalCost, totalInputTokens, totalOutputTokens, and totalRequests properties (all initialized to 0). The function will update these values after each request. Note: totalCost is only calculated for Google GenAI models, not for Ollama.
Returns

A promise that resolves when the AI processing is complete.

Examples
// New table with a "review" column.
await table.loadArray([
  { review: "Great product!" },
  { review: "Terrible quality." },
  { review: "Not bad, could be better." },
  { review: "Excellent service!" },
]);

// Analyze sentiment using a pool with 2 concurrent workers, batch size of 2
await table.aiRowByRowPool(
  "review",
  "sentiment",
  "error",
  `Classify the sentiment as "Positive", "Negative", or "Neutral".`,
  2, // poolSize: 2 concurrent requests
  {
    cache: true,
    batchSize: 2, // Process 2 rows per request
    logProgress: true,
    test: (data: { [key: string]: unknown }) => {
      if (
        typeof data.sentiment !== "string" ||
        !["Positive", "Negative", "Neutral"].includes(data.sentiment)
      ) {
        throw new Error(`Invalid sentiment: ${data.sentiment}`);
      }
    },
    retry: 2,
    minRequestDurationMs: 1000, // Respect rate limits: at least 1 second per request
  },
);

// Example results:
// [
//   { review: "Great product!", sentiment: "Positive", error: null },
//   { review: "Terrible quality.", sentiment: "Negative", error: null },
//   { review: "Not bad, could be better.", sentiment: "Neutral", error: null },
//   { review: "Excellent service!", sentiment: "Positive", error: null },
// ]

await table.loadArray([
  { product: "Laptop" },
  { product: "Smartphone" },
  { product: "Tablet" },
]);

// Extract multiple properties using pool-based processing
await table.aiRowByRowPool(
  "product",
  ["category", "typical_price_range"],
  "error",
  `For the given product, provide the category and typical price range.`,
  3, // Process up to 3 products concurrently
  {
    logProgress: true,
    retryCheck: (error) => {
      // Retry only for specific error types
      return error instanceof Error && error.message.includes("rate limit");
    },
  },
);

// Example results:
// [
//   { product: "Laptop", category: "Electronics", typical_price_range: "\$500-\$2000", error: null },
//   { product: "Smartphone", category: "Electronics", typical_price_range: "\$200-\$1200", error: null },
//   { product: "Tablet", category: "Electronics", typical_price_range: "\$200-\$800", error: null },
// ]

aiEmbeddings

Generates embeddings for a specified text column and stores the results in a new column.

This method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_EMBEDDINGS_MODEL) or directly via options, with options taking precedence.

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_EMBEDDINGS_MODEL to your desired model name. You can also pass your instance of Ollama to the ollama option.

To manage rate limits, use rateLimitPerMinute to introduce delays between requests. For higher rate limits (business/professional accounts), concurrent allows parallel requests.

The cache option enables local caching of results in .journalism-cache (from the getEmbedding function in the journalism library). Remember to add .journalism-cache to your .gitignore.

If createIndex is true, an index will be created on the new column using the duckdb-vss extension. This is useful for speeding up the aiVectorSimilarity method. If the index already exists, it will not be recreated unless overwriteIndex is true.

This method does not support tables containing geometries.

Signature
async aiEmbeddings(column: string, newColumn: string, options?: { createIndex?: boolean; overwriteIndex?: boolean; concurrent?: number; cache?: boolean; model?: string; apiKey?: string; vertex?: boolean; project?: string; location?: string; ollama?: boolean | Ollama; verbose?: boolean; rateLimitPerMinute?: number; contextWindow?: number; efConstruction?: number; efSearch?: number; M?: number }): Promise<void>;
Parameters
  • column: The name of the column to be used as input for generating embeddings.
  • newColumn: The name of the new column where the generated embeddings will be stored.
  • options: Configuration options for the AI request.
  • options.createIndex: If true, an index will be created on the new column. Useful for speeding up the aiVectorSimilarity method. Defaults to false.
  • options.overwriteIndex: If true and createIndex is true, drops and recreates the VSS index even if it already exists. Defaults to false.
  • options.efConstruction: The number of candidate vertices to consider during index construction. Higher values result in more accurate indexes but increase build time. Defaults to 128.
  • options.efSearch: The number of candidate vertices to consider during search. Higher values result in more accurate searches but increase search time. Defaults to 64.
  • options.M: The maximum number of neighbors to keep for each vertex in the graph. Higher values result in more accurate indexes but increase build time and memory usage. Defaults to 16.
  • options.concurrent: The number of concurrent requests to send. Defaults to 1.
  • options.cache: If true, the results will be cached locally. Defaults to false.
  • options.rateLimitPerMinute: The rate limit for AI requests in requests per minute. The method will wait between requests if necessary. Defaults to undefined (no limit).
  • options.model: The AI model to use. Defaults to the AI_EMBEDDINGS_MODEL environment variable.
  • options.apiKey: The API key for the AI service. Defaults to the AI_KEY environment variable.
  • options.vertex: If true, uses Vertex AI. Automatically set to true if AI_PROJECT and AI_LOCATION are set in the environment. Defaults to false.
  • options.project: The Google Cloud project ID for Vertex AI. Defaults to the AI_PROJECT environment variable.
  • options.location: The Google Cloud location for Vertex AI. Defaults to the AI_LOCATION environment variable.
  • options.ollama: If true, uses Ollama. Defaults to the OLLAMA environment variable. If you want your Ollama instance to be used, you can pass it here too.
  • options.contextWindow: An option to specify the context window size for Ollama models. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.verbose: If true, logs additional debugging information. Defaults to false.
Returns

A promise that resolves when the embeddings have been generated and stored.

Examples
// New table with a "food" column.
await table.loadArray([
  { food: "pizza" },
  { food: "sushi" },
  { food: "burger" },
  { food: "pasta" },
  { food: "salad" },
  { food: "tacos" },
]);

// Generate embeddings for the "food" column and store them in a new "embeddings" column.
await table.aiEmbeddings("food", "embeddings", {
  cache: true, // Cache results locally
  rateLimitPerMinute: 15, // Limit requests to 15 per minute
  createIndex: true, // Create an index on the new column for faster similarity searches
  verbose: true, // Log detailed information
});

aiVectorSimilarity

Creates an embedding from a specified text and returns the most similar text content based on their embeddings. This method is useful for semantic search and text similarity tasks, computing cosine distance and sorting results by similarity.

To create the embedding, this method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_EMBEDDINGS_MODEL) or directly via options, with options taking precedence.

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_EMBEDDINGS_MODEL to your desired model name. You can also pass your instance of Ollama to the ollama option.

The cache option enables local caching of the specified text's embedding in .journalism-cache (from the getEmbedding function in the journalism library). Remember to add .journalism-cache to your .gitignore.

If createIndex is true, an index will be created on the embeddings column using the duckdb-vss extension to speed up processing. If the index already exists, it will not be recreated unless overwriteIndex is true.

Signature
async aiVectorSimilarity(text: string, column: string, nbResults: number, options?: { createIndex?: boolean; overwriteIndex?: boolean; outputTable?: string; cache?: boolean; model?: string; apiKey?: string; vertex?: boolean; project?: string; location?: string; ollama?: boolean | Ollama; contextWindow?: number; verbose?: boolean; efConstruction?: number; efSearch?: number; M?: number; minSimilarity?: number; similarityColumn?: string }): Promise<SimpleTable>;
Parameters
  • text: The text for which to generate an embedding and find similar content.
  • column: The name of the column containing the embeddings to be used for the similarity search.
  • nbResults: The maximum number of most similar results to return.
  • options: An optional object with configuration options:
  • options.minSimilarity: A threshold between 0.0 and 1.0 to filter out results that are not similar enough. For example, 0.7 ensures only results with a 70% similarity or higher are returned. Defaults to undefined (no threshold).
  • options.similarityColumn: If provided, a new column with this name will be added to the output table containing the calculated similarity score (from 0.0 to 1.0) for each row. Defaults to undefined.
  • options.createIndex: If true, an index will be created on the embeddings column. Defaults to false.
  • options.overwriteIndex: If true and createIndex is true, drops and recreates the VSS index even if it already exists. Defaults to false.
  • options.efConstruction: The number of candidate vertices to consider during index construction. Higher values result in more accurate indexes but increase build time. Defaults to 128.
  • options.efSearch: The number of candidate vertices to consider during search. Higher values result in more accurate searches but increase search time. Defaults to 64.
  • options.M: The maximum number of neighbors to keep for each vertex in the graph. Higher values result in more accurate indexes but increase build time and memory usage. Defaults to 16.
  • options.outputTable: The name of the output table where the results will be stored. If not provided, the current table will be modified. Defaults to undefined.
  • options.cache: If true, the embedding of the input text will be cached locally. Defaults to false.
  • options.model: The AI model to use for generating the embedding. Defaults to the AI_EMBEDDINGS_MODEL environment variable.
  • options.apiKey: The API key for the AI service. Defaults to the AI_KEY environment variable.
  • options.vertex: If true, uses Vertex AI. Automatically set to true if AI_PROJECT and AI_LOCATION are set in the environment. Defaults to false.
  • options.project: The Google Cloud project ID for Vertex AI. Defaults to the AI_PROJECT environment variable.
  • options.location: The Google Cloud location for Vertex AI. Defaults to the AI_LOCATION environment variable.
  • options.ollama: If true, uses Ollama. Defaults to the OLLAMA environment variable. If you want your Ollama instance to be used, you can pass it here too.
  • options.verbose: If true, logs additional debugging information. Defaults to false.
  • options.contextWindow: An option to specify the context window size for Ollama models. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
Returns

A promise that resolves to the SimpleTable instance containing the similarity search results.

Examples
// New table with a "food" column.
await table.loadArray([
  { food: "pizza" },
  { food: "sushi" },
  { food: "burger" },
  { food: "pasta" },
  { food: "salad" },
  { food: "tacos" },
]);

// Generate embeddings for the "food" column.
await table.aiEmbeddings("food", "embeddings", { cache: true });

// Find the 3 most similar foods to "italian food" based on embeddings.
// We only want results with at least 60% similarity and we want to see the score.
const similarFoods = await table.aiVectorSimilarity(
  "italian food",
  "embeddings",
  3,
  {
    createIndex: true, // Create an index on the embeddings column for faster searches
    cache: true, // Cache the embedding of "italian food"
    minSimilarity: 0.6, // Filter out anything below 0.6 similarity
    similarityColumn: "score", // Add a new column named "score" with the similarity math
  },
);

// Log the results
await similarFoods.logTable();

hybridSearch

Performs hybrid text search combining vector similarity and BM25 text search using Reciprocal Rank Fusion (RRF).

This method:

  1. Generates embeddings for the text column if they don't already exist
  2. Runs vector similarity search and BM25 text search in parallel
  3. Fuses the results using Reciprocal Rank Fusion to get the best matches
  4. Returns a new table with the top results ordered by relevance

The embeddings are cached at two levels:

  • At the table level, so renaming the table will invalidate the cache and regenerate embeddings. For often updated tables, you can pass a timestamp to the table name (e.g., mytable_20240901) to keep the cache valid until the next update.
  • At the row level, so if the text content is different or not cached, the embedding will be generated and cached for that specific text. If the text content has been previously cached, the existing embedding will be reused, even if the table has been renamed (as long as the text content is unchanged).

Also, the method creates the column {columnText}_embeddings to store the generated embeddings. If you wrote your DB to a file, and if the column already exists, it will reuse the existing embeddings column directly, before even checking the cache, since the DB file itself serves as a cache. Similarly, the embeddings and BM25 index are reused if they already exist.

To delete the cache, simply remove the .journalism-cache and/or .sda-cache directories in your project or set the cache option to false. Remember to add .journalism-cache and .sda-cache to your .gitignore.

This method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_EMBEDDINGS_MODEL) or directly via options, with options taking precedence.

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_EMBEDDINGS_MODEL to your desired model name. You can also pass your instance of Ollama.

If createIndex is true, both a vector index (using the duckdb-vss extension) and a BM25 full-text search index (using the fts extension) will be created for faster retrieval.

This method does not support tables containing geometries.

Signature
async hybridSearch(query: string, columnId: string, columnText: string, nbResults: number, options?: { cache?: boolean; verbose?: boolean; embeddingsModelContextWindow?: number; createIndex?: boolean; embeddingsModel?: string; ollamaEmbeddings?: boolean; embeddingsConcurrent?: number; stemmer?: "arabic" | "basque" | "catalan" | "danish" | "dutch" | "english" | "finnish" | "french" | "german" | "greek" | "hindi" | "hungarian" | "indonesian" | "irish" | "italian" | "lithuanian" | "nepali" | "norwegian" | "porter" | "portuguese" | "romanian" | "russian" | "serbian" | "spanish" | "swedish" | "tamil" | "turkish" | "none"; stopwords?: string; ignore?: string; stripAccents?: boolean; lower?: boolean; k?: number; b?: number; conjunctive?: boolean; bm25?: boolean; bm25MinScore?: number; bm25ScoreColumn?: string; vectorSearch?: boolean; vectorMinSimilarity?: number; vectorSimilarityColumn?: string; outputTable?: string; efConstruction?: number; efSearch?: number; M?: number; times?: { start?: number; embeddingStart?: number; embeddingEnd?: number; vectorSearchStart?: number; vectorSearchEnd?: number; bm25Start?: number; bm25End?: number } }): Promise<SimpleTable>;
Parameters
  • query: The search query text.
  • columnId: The name of the column containing unique identifiers for each row.
  • columnText: The name of the column containing the text content to search through.
  • nbResults: The number of most similar rows to retrieve.
  • options: Configuration options for the hybrid search.
  • options.cache: If true, embeddings will be cached locally. Defaults to false.
  • options.verbose: If true, logs additional debugging information. Defaults to false.
  • options.embeddingsModelContextWindow: An option to specify the context window size for the embeddings model when using Ollama. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.createIndex: If true, both vector and BM25 indexes will be created for faster retrieval. Defaults to false.
  • options.efConstruction: The number of candidate vertices to consider during index construction. Higher values result in more accurate indexes but increase build time. Defaults to 128.
  • options.efSearch: The number of candidate vertices to consider during search. Higher values result in more accurate searches but increase search time. Defaults to 64.
  • options.M: The maximum number of neighbors to keep for each vertex in the graph. Higher values result in more accurate indexes but increase build time and memory usage. Defaults to 16.
  • options.embeddingsModel: The model to use for generating embeddings. Defaults to the AI_EMBEDDINGS_MODEL environment variable.
  • options.ollamaEmbeddings: If true, forces the use of Ollama for embeddings generation. Defaults to false.
  • options.embeddingsConcurrent: The number of concurrent requests to send to the embeddings service. Defaults to 1.
  • options.stemmer: The language stemmer to apply for BM25 word normalization. Supports multiple languages or "none" to disable stemming. Defaults to 'porter'.
  • options.stopwords: The table containing the stopwords to use for the BM25 FTS index. Supports multiple languages or "none" to disable stopwords. Defaults to "english".
  • options.ignore: The regular expression of patterns to be ignored for the BM25 FTS index. Defaults to "(\.|[^a-z])+".
  • options.stripAccents: A boolean indicating whether to remove accents for the BM25 FTS index. Defaults to true.
  • options.lower: A boolean indicating whether to convert all text to lowercase for the BM25 FTS index. Defaults to true.
  • options.k: The BM25 k parameter controlling term frequency saturation. Defaults to 1.2.
  • options.b: The BM25 b parameter controlling document length normalization (0-1 range). Defaults to 0.75.
  • options.conjunctive: If true, all terms in the query string must be present in order for a document to be retrieved during the BM25 search. Defaults to false.
  • options.bm25: If true, includes BM25 text search in the hybrid search. Defaults to true.
  • options.bm25MinScore: A threshold to filter BM25 results. Only rows with a BM25 score above this value will be included in the final results. Defaults to undefined (no threshold).
  • options.bm25ScoreColumn: If provided, a new column with this name will be added to the output table containing the BM25 score for each row.
  • options.vectorSearch: If true, includes vector similarity search in the hybrid search. Defaults to true.
  • options.vectorMinSimilarity: A threshold between 0.0 and 1.0 to filter out vector search results that are not similar enough. For example, 0.7 ensures only results with a 70% similarity or higher are included in the final results. Defaults to undefined (no threshold).
  • options.vectorSimilarityColumn: If provided, a new column with this name will be added to the output table containing the vector similarity score (from 0.0 to 1.0) for each row.
  • options.outputTable: The name of a new table where the results will be stored. If not provided, the current table will be replaced with the search results.
  • options.times: An optional object to track timing information. If provided, it will be updated with detailed timing breakdowns (embeddingStart, embeddingEnd, vectorSearchStart, vectorSearchEnd, bm25Start, bm25End). Useful when calling from aiRAG to get combined timing information.
Returns

A promise that resolves to a SimpleTable instance containing the search results, ordered by relevance (best matches first).

Examples
// Load a dataset of recipes
const sdb = new SimpleDB();
const table = sdb.newTable("recipes");
await table.loadData("recipes.parquet");

// Perform hybrid search - replaces the current table with top 10 results
await table.hybridSearch(
  "buttery pastry for breakfast",
  "Dish", // Column with unique IDs
  "Recipe", // Column with text to search
  10, // Return top 10 results
  {
    cache: true, // Cache embeddings
    verbose: true, // Log debugging information
  },
);

// Table now contains only the most relevant recipes
await table.logTable();

aiRAG

Performs Retrieval-Augmented Generation (RAG) by combining semantic vector search and BM25 full-text search to retrieve the most relevant context, then passing it to an LLM for answering queries. This hybrid approach uses both aiVectorSimilarity (embeddings-based) and bm25 (keyword-based) methods in parallel, fusing their results using Reciprocal Rank Fusion (RRF) before calling the askAI function from the journalism library.

Internally, this method uses the hybridSearch method to retrieve relevant rows. If you want to perform hybrid search without the LLM step (i.e., to get the table of results directly), use hybridSearch instead.

The embeddings are cached at two levels:

  • At the table level, so renaming the table will invalidate the cache and regenerate embeddings. For often updated tables, you can pass a timestamp to the table name (e.g., mytable_20240901) to keep the cache valid until the next update.
  • At the row level, so if the text content is different or not cached, the embedding will be generated and cached for that specific text. If the text content has been previously cached, the existing embedding will be reused, even if the table has been renamed (as long as the text content is unchanged).

Also, the method creates the column {columnText}_embeddings to store the generated embeddings. If you wrote your DB to a file, and if the column already exists, it will reuse the existing embeddings column directly, before even checking the cache, since the DB file itself serves as a cache. Similarly, the embeddings and BM25 index are reused if they already exist.

To delete the cache, simply remove the .journalism-cache and/or .sda-cache directories in your project or set the cache option to false. Remember to add .journalism-cache and .sda-cache to your .gitignore.

This method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_MODEL, AI_EMBEDDINGS_MODEL) or directly via options, with options taking precedence.

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_MODEL and AI_EMBEDDINGS_MODEL to your desired model names. If you are using Google Gemini or Vertex AI for the LLM, you can still use Ollama embeddings via the ollamaEmbeddings option.

The LLM temperature is set to 0 for reproducibility, though consistency cannot be guaranteed.

If createIndex is true, both a vector index (using the duckdb-vss extension) and a BM25 full-text search index (using the fts extension) will be created for faster retrieval.

This method does not support tables containing geometries.

Signature
async aiRAG(query: string, columnId: string, columnText: string, nbResults: number, options?: { cache?: boolean; verbose?: boolean; includeThoughts?: boolean; systemPrompt?: string; modelContextWindow?: number; embeddingsModelContextWindow?: number; createIndex?: boolean; thinkingBudget?: number; thinkingLevel?: "minimal" | "low" | "medium" | "high"; webSearch?: boolean; safetyEnabled?: boolean; model?: string; temperature?: number; apiKey?: string; vertex?: boolean; project?: string; location?: string; ollama?: boolean | Ollama; metrics?: { totalCost: number; totalInputTokens: number; totalOutputTokens: number; totalRequests: number }; embeddingsModel?: string; ollamaEmbeddings?: boolean; embeddingsConcurrent?: number; stemmer?: "arabic" | "basque" | "catalan" | "danish" | "dutch" | "english" | "finnish" | "french" | "german" | "greek" | "hindi" | "hungarian" | "indonesian" | "irish" | "italian" | "lithuanian" | "nepali" | "norwegian" | "porter" | "portuguese" | "romanian" | "russian" | "serbian" | "spanish" | "swedish" | "tamil" | "turkish" | "none"; stopwords?: string; ignore?: string; stripAccents?: boolean; lower?: boolean; k?: number; b?: number; conjunctive?: boolean; bm25?: boolean; bm25MinScore?: number; bm25ScoreColumn?: string; vectorSearch?: boolean; vectorMinSimilarity?: number; vectorSimilarityColumn?: string; efConstruction?: number; efSearch?: number; M?: number }): Promise<string>;
Parameters
  • query: The question or query to answer using the retrieved context.
  • columnId: The name of the column containing unique identifiers for each row.
  • columnText: The name of the column containing the text content to search through and use as context.
  • nbResults: The number of most similar rows to retrieve and use as context for the AI.
  • options: Configuration options for the RAG process.
  • options.cache: If true, embeddings and LLM responses will be cached locally. Defaults to false.
  • options.verbose: If true, logs additional debugging information. Defaults to false.
  • options.includeThoughts: If true, includes the AI model's reasoning process in the logged output when using models that support extended thinking. Only relevant when used with thinking-capable models. Defaults to false.
  • options.systemPrompt: An option to overwrite the LLM system prompt.
  • options.modelContextWindow: An option to specify the context window size for the LLM model when using Ollama. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.embeddingsModelContextWindow: An option to specify the context window size for the embeddings model when using Ollama. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.thinkingBudget: Sets the reasoning token budget: 0 to disable (default, though some models may reason regardless), -1 for a dynamic budget, or > 0 for a fixed budget. For Ollama models, any non-zero value simply enables reasoning, ignoring the specific budget amount.
  • options.thinkingLevel: Sets the thinking level for reasoning: "minimal", "low", "medium", or "high", which some models expect instead of thinkingBudget. Takes precedence over thinkingBudget if both are provided. For Ollama models, any value enables reasoning.
  • options.safetyEnabled: Controls whether safety filters are enabled. If set to true, filters are active; if false, they are disabled. By default, this is false when using Vertex AI and true otherwise. This setting can be explicitly overridden for any model.
  • options.webSearch: (Gemini only) If true, enables web search grounding for the AI's responses. Be careful of extra costs. Defaults to false.
  • options.model: The LLM model to use for answering the query. Defaults to the AI_MODEL environment variable.
  • options.temperature: The temperature setting for the AI model, controlling the randomness of the output. Defaults to 0.
  • options.apiKey: Your API key for the AI service. Defaults to the AI_KEY environment variable.
  • options.vertex: Set to true to use Vertex AI for authentication. Auto-enables if AI_PROJECT and AI_LOCATION are set. Defaults to false.
  • options.project: Your Google Cloud project ID. Defaults to the AI_PROJECT environment variable.
  • options.location: Your Google Cloud location for your project. Defaults to the AI_LOCATION environment variable.
  • options.ollama: If true, uses Ollama. Defaults to the OLLAMA environment variable. If you want your Ollama instance to be used, you can pass it here too.
  • options.metrics: An object to track cumulative metrics across multiple AI requests. Pass an object with totalCost, totalInputTokens, totalOutputTokens, and totalRequests properties (all initialized to 0). The function will update these values after each request. Note: totalCost is only calculated for Google GenAI models, not for Ollama.
  • options.embeddingsModel: The model to use for generating embeddings. Defaults to the AI_EMBEDDINGS_MODEL environment variable.
  • options.ollamaEmbeddings: If true, forces the use of Ollama for embeddings generation, even if Gemini or Vertex is used for the LLM. Defaults to false.
  • options.embeddingsConcurrent: The number of concurrent requests to send to the embeddings service. Defaults to 1.
  • options.createIndex: If true, both vector and BM25 indexes will be created for faster retrieval. Defaults to false.
  • options.efConstruction: The number of candidate vertices to consider during index construction. Higher values result in more accurate indexes but increase build time. Defaults to 128.
  • options.efSearch: The number of candidate vertices to consider during search. Higher values result in more accurate searches but increase search time. Defaults to 64.
  • options.M: The maximum number of neighbors to keep for each vertex in the graph. Higher values result in more accurate indexes but increase build time and memory usage. Defaults to 16.
  • options.stemmer: The language stemmer to apply for BM25 word normalization. Supports multiple languages or "none" to disable stemming. Defaults to 'porter'.
  • options.stopwords: The table containing the stopwords to use for the BM25 FTS index. Supports multiple languages or "none" to disable stopwords. Defaults to "english".
  • options.ignore: The regular expression of patterns to be ignored for the BM25 FTS index. Defaults to "(\.|[^a-z])+".
  • options.stripAccents: A boolean indicating whether to remove accents for the BM25 FTS index. Defaults to true.
  • options.lower: A boolean indicating whether to convert all text to lowercase for the BM25 FTS index. Defaults to true.
  • options.k: The BM25 k parameter controlling term frequency saturation. Defaults to 1.2.
  • options.b: The BM25 b parameter controlling document length normalization (0-1 range). Defaults to 0.75.
  • options.conjunctive: If true, all terms in the query string must be present in order for a document to be retrieved during the BM25 search. Defaults to false.
  • options.bm25: If true, includes BM25 text search in the hybrid search. Defaults to true.
  • options.bm25MinScore: A threshold to filter BM25 results. Only rows with a BM25 score above this value will be included in the final results. Defaults to undefined (no threshold).
  • options.bm25ScoreColumn: If provided, a new column with this name will be added to the output table containing the BM25 score for each row.
  • options.vectorSearch: If true, includes vector similarity search in the hybrid search. Defaults to true.
  • options.vectorMinSimilarity: A threshold between 0.0 and 1.0 to filter out vector search results that are not similar enough. For example, 0.7 ensures only results with a 70% similarity or higher are included in the final results. Defaults to undefined (no threshold).
  • options.vectorSimilarityColumn: If provided, a new column with this name will be added to the output table containing the vector similarity score (from 0.0 to 1.0) for each row.
Returns

A promise that resolves to the AI's answer to the query based on the retrieved context.

Examples
// Load a dataset of recipes
const sdb = new SimpleDB();
const table = sdb.newTable("recipes");
await table.loadData("recipes.parquet");

// Ask a question using hybrid RAG (vector + BM25 search)
const answer = await table.aiRAG(
  "I want a buttery pastry for breakfast.",
  "Dish", // Column with unique IDs
  "Recipe", // Column with text to search
  10, // The 10 most relevant recipes passed to the LLM
  {
    cache: true, // Cache embeddings
    verbose: true, // Log debugging information and timings
  },
);

console.log(answer);
// Example output: "I recommend croissants.
// They are a classic buttery pastry perfect for breakfast..."

aiQuery

Generates and executes a SQL query based on a prompt. Additional instructions, such as column types, are automatically added to your prompt. Set verbose to true to see the full prompt.

This method supports Google Gemini, Vertex AI, and local models running with Ollama. Credentials and model selection are determined by environment variables (AI_KEY, AI_PROJECT, AI_LOCATION, AI_MODEL) or directly via options, with options taking precedence.

For Ollama, set the OLLAMA environment variable to true, ensure Ollama is running, and set AI_MODEL to your desired model name. You can also pass your instance of Ollama to the ollama option.

Temperature is set to 0 to aim for reproducible results. For future consistency, it's recommended to copy the generated query and execute it manually using await sdb.customQuery(query) or to cache the query using the cache option.

When cache is true, the generated query will be cached locally in .journalism-cache (from the askAI function in the journalism library), saving resources and time. Remember to add .journalism-cache to your .gitignore.

Signature
async aiQuery(prompt: string, options?: { extraInstructions?: string; cache?: boolean; model?: string; apiKey?: string; vertex?: boolean; project?: string; includeThoughts?: boolean; location?: string; ollama?: boolean | Ollama; contextWindow?: number; thinkingBudget?: number; thinkingLevel?: "minimal" | "low" | "medium" | "high"; temperature?: number; safetyEnabled?: boolean; outputTable?: string; verbose?: boolean }): Promise<SimpleTable>;
Parameters
  • prompt: The input string to guide the AI in generating the SQL query.
  • options: Configuration options for the AI request.
  • options.extraInstructions: Additional instructions to append to the prompt, providing more context or guidance for the AI.
  • options.cache: If true, the generated query will be cached locally. Defaults to false.
  • options.model: The AI model to use. Defaults to the AI_MODEL environment variable.
  • options.apiKey: The API key for the AI service. Defaults to the AI_KEY environment variable.
  • options.vertex: If true, uses Vertex AI. Automatically set to true if AI_PROJECT and AI_LOCATION are set in the environment. Defaults to false.
  • options.project: The Google Cloud project ID for Vertex AI. Defaults to the AI_PROJECT environment variable.
  • options.location: The Google Cloud location for Vertex AI. Defaults to the AI_LOCATION environment variable.
  • options.ollama: If true, uses Ollama. Defaults to the OLLAMA environment variable. If you want your Ollama instance to be used, you can pass it here too.
  • options.contextWindow: An option to specify the context window size for Ollama models. By default, Ollama sets this depending on the model, which can be lower than the actual maximum context window size of the model.
  • options.thinkingBudget: Sets the reasoning token budget: 0 to disable (default, though some models may reason regardless), -1 for a dynamic budget, or > 0 for a fixed budget. For Ollama models, any non-zero value simply enables reasoning, ignoring the specific budget amount.
  • options.thinkingLevel: Sets the thinking level for reasoning: "minimal", "low", "medium", or "high", which some models expect instead of thinkingBudget. Takes precedence over thinkingBudget if both are provided. For Ollama models, any value enables reasoning.
  • options.temperature: The temperature setting for the AI model, controlling the randomness of the output. Defaults to 0.
  • options.safetyEnabled: Controls whether safety filters are enabled. If set to true, filters are active; if false, they are disabled. By default, this is false when using Vertex AI and true otherwise. This setting can be explicitly overridden for any model.
  • options.outputTable: The name of a new table where the results will be stored. If not provided, the current table will be replaced with the query results.
  • options.verbose: If true, logs additional debugging information, including the full prompt sent to the AI. Defaults to false.
  • options.includeThoughts: If true, includes the AI model's reasoning process in the logged output when using models that support extended thinking. Only relevant when used with thinking-capable models. Defaults to false.
Returns

A promise that resolves to the SimpleTable instance containing the query results (either the modified current table or a new table).

Examples
// The AI will generate a query that will be executed, and
// the result will replace the existing table.
// If run again, it will use the previous query from the cache.
// Don't forget to add .journalism-cache to your .gitignore file!
await table.aiQuery(
  "Give me the average salary by department",
  { cache: true, verbose: true },
);

// Save results to a new table without modifying the original
const results = await table.aiQuery(
  "Give me the top 10 employees by salary",
  { outputTable: "top_employees" },
);

// Original table remains unchanged
const allEmployees = await table.getNbRows();
console.log(allEmployees); // All employees

// New table contains only query results
const topEmployees = await results.getNbRows();
console.log(topEmployees); // 10

toSheet

Writes the table data to a Google Sheet. This method uses the overwriteSheetData function from the journalism library. Refer to its documentation for more details.

By default, authentication is handled via environment variables (GOOGLE_PRIVATE_KEY and GOOGLE_SERVICE_ACCOUNT_EMAIL). Alternatively, you can use GOOGLE_APPLICATION_CREDENTIALS pointing to a service account JSON file. For detailed setup instructions, refer to the node-google-spreadsheet authentication guide: https://theoephraim.github.io/node-google-spreadsheet/#/guides/authentication.

Signature
async toSheet(sheetUrl: string, options?: { prepend?: string; lastUpdate?: boolean; timeZone?: "Canada/Atlantic" | "Canada/Central" | "Canada/Eastern" | "Canada/Mountain" | "Canada/Newfoundland" | "Canada/Pacific" | "Canada/Saskatchewan" | "Canada/Yukon"; raw?: boolean; apiEmail?: string; apiKey?: string }): Promise<void>;
Parameters
  • sheetUrl: The URL pointing to a specific Google Sheet (e.g., "https://docs.google.com/spreadsheets/d/.../edit#gid=0").
  • options: An optional object with configuration options:
  • options.prepend: A string to prepend to the sheet data (e.g., a title or header).
  • options.lastUpdate: If true, adds a timestamp of the last update to the sheet.
  • options.timeZone: The time zone to use for the last update timestamp.
  • options.raw: If true, writes the data as raw values without formatting.
  • options.apiEmail: If your API email is stored under a different environment variable name, use this option to specify it.
  • options.apiKey: If your API key is stored under a different environment variable name, use this option to specify it.
Returns

A promise that resolves when the data has been written to the sheet.

Examples
// Write the table data to a Google Sheet
await table.toSheet("https://docs.google.com/spreadsheets/d/.../edit#gid=0");

loadSheet

Loads data from a Google Sheet into the table. This method uses the getSheetData function from the journalism library. Refer to its documentation for more details.

By default, authentication is handled via environment variables (GOOGLE_PRIVATE_KEY and GOOGLE_SERVICE_ACCOUNT_EMAIL). Alternatively, you can use GOOGLE_APPLICATION_CREDENTIALS pointing to a service account JSON file. For detailed setup instructions, refer to the node-google-spreadsheet authentication guide: https://theoephraim.github.io/node-google-spreadsheet/#/guides/authentication.

Signature
async loadSheet(sheetUrl: string, options?: { skip?: number; apiEmail?: string; apiKey?: string }): Promise<void>;
Parameters
  • sheetUrl: The URL pointing to a specific Google Sheet (e.g., "https://docs.google.com/spreadsheets/d/.../edit#gid=0").
  • options: An optional object with configuration options:
  • options.skip: The number of rows to skip from the top of the sheet before reading data. Useful when the sheet contains metadata or headers that should not be included in the data.
  • options.apiEmail: If your API email is stored under a different environment variable name, use this option to specify it.
  • options.apiKey: If your API key is stored under a different environment variable name, use this option to specify it.
Returns

A promise that resolves when the data has been loaded into the table.

Examples
// Load data from a Google Sheet
await table.loadSheet("https://docs.google.com/spreadsheets/d/.../edit#gid=0");

// Load data from a Google Sheet, skipping the first 2 rows (e.g., to skip a prepended message and timestamp)
await table.loadSheet("https://docs.google.com/spreadsheets/d/.../edit#gid=0", {
  skip: 2,
});

toDW

Writes the table data as CSV to a Datawrapper chart or table.

Authentication is handled via an API key stored in the environment variable DATAWRAPPER_KEY, or a custom variable name via options.apiKey.

Signature
async toDW(chartId: string, options?: { apiKey?: string; note?: string; republish?: boolean }): Promise<void>;
Parameters
  • chartId: The unique ID of the Datawrapper chart or table to update. This ID can be found in the Datawrapper URL or dashboard.
  • options: An optional object with configuration options:
  • options.apiKey: The name of the environment variable that stores your Datawrapper API key (e.g., "DATAWRAPPER_KEY"). Defaults to "DATAWRAPPER_KEY".
  • options.note: A string to update the chart's notes field with (e.g., a last-updated timestamp).
  • options.republish: If true, republishes the chart after updating the data. Defaults to false.
Returns

A promise that resolves when the data has been sent to Datawrapper.

Examples
// Update a Datawrapper chart with the table data
await table.toDW("myChartId");

// Update data, add a note, and republish
await table.toDW("myChartId", {
  note: `Last updated: ${new Date().toLocaleString()}`,
  republish: true,
});

loadDW

Loads data from a Datawrapper chart or table into the table.

Authentication is handled via an API key stored in the environment variable DATAWRAPPER_KEY, or a custom variable name via options.apiKey.

Signature
async loadDW(chartId: string, options?: { apiKey?: string }): Promise<void>;
Parameters
  • chartId: The unique ID of the Datawrapper chart or table. This ID can be found in the Datawrapper URL or dashboard.
  • options: An optional object with configuration options:
  • options.apiKey: The name of the environment variable that stores your Datawrapper API key (e.g., "DATAWRAPPER_KEY"). Defaults to "DATAWRAPPER_KEY".
Returns

A promise that resolves when the data has been loaded into the table.

Examples
// Load data from a Datawrapper chart
await table.loadDW("myChartId");

toGeoDW

Writes the table's geospatial data as GeoJSON to a Datawrapper map.

Authentication is handled via an API key stored in the environment variable DATAWRAPPER_KEY, or a custom variable name via options.apiKey.

Signature
async toGeoDW(chartId: string, options?: { apiKey?: string; column?: string; note?: string; republish?: boolean }): Promise<void>;
Parameters
  • chartId: The unique ID of the Datawrapper map to update. This ID can be found in the Datawrapper URL or dashboard.
  • options: An optional object with configuration options:
  • options.apiKey: The name of the environment variable that stores your Datawrapper API key (e.g., "DATAWRAPPER_KEY"). Defaults to "DATAWRAPPER_KEY".
  • options.column: The name of the geometry column to use. If omitted, the method will automatically attempt to find a geometry column.
  • options.note: A string to update the map's notes field with.
  • options.republish: If true, republishes the map after updating the data. Defaults to false.
Returns

A promise that resolves when the data has been sent to Datawrapper.

Examples
// Update a Datawrapper map with the table's geo data
await table.toGeoDW("myMapId");

// Update data, add a note, and republish
await table.toGeoDW("myMapId", {
  note: `Last updated: ${new Date().toLocaleString()}`,
  republish: true,
});

loadGeoDW

Loads geospatial data from a Datawrapper map into the table.

Authentication is handled via an API key stored in the environment variable DATAWRAPPER_KEY, or a custom variable name via options.apiKey.

The data is temporarily written to .sda-cache/<chartId>.json and removed after loading. Remember to add .sda-cache to your .gitignore.

Signature
async loadGeoDW(chartId: string, options?: { apiKey?: string }): Promise<void>;
Parameters
  • chartId: The unique ID of the Datawrapper map. This ID can be found in the Datawrapper URL or dashboard.
  • options: An optional object with configuration options:
  • options.apiKey: The name of the environment variable that stores your Datawrapper API key (e.g., "DATAWRAPPER_KEY"). Defaults to "DATAWRAPPER_KEY".
Returns

A promise that resolves when the data has been loaded into the table.

Examples
// Load geo data from a Datawrapper map
await table.loadGeoDW("myMapId");

writeChart

Creates an Observable Plot chart as an image file (.png or .svg) from the table data. To create maps, use the writeMap method.

Signature
async writeChart(chart: (data: unknown[]) => SVGSVGElement | HTMLElement, path: string, options?: { style?: string; dark?: boolean }): Promise<void>;
Parameters
  • chart: A function that takes data (as an array of objects) and returns an Observable Plot chart (an SVGSVGElement or HTMLElement).
  • path: The absolute path where the chart image will be saved (e.g., "./output/chart.png").
  • options: Optional object containing additional settings:
  • options.style: A CSS string to customize the chart's appearance. This is applied to a <div> element wrapping the Plot chart (which has the id chart). Use this if the Plot style option is insufficient.
  • options.dark: If true, switches the chart to dark mode. Defaults to false.
Returns

A promise that resolves when the chart image has been saved.

Examples
import { dot, plot } from "@observablehq/plot";

const sdb = new SimpleDB();
const table = sdb.newTable();
const data = [{ year: 2024, value: 10 }, { year: 2025, value: 15 }];
await table.loadArray(data);

const chartFunction = (plotData: unknown[]) =>
  plot({
    marks: [
      dot(plotData, { x: "year", y: "value" }),
    ],
  });

const outputPath = "output/chart.png";

await table.writeChart(chartFunction, outputPath);

writeMap

Creates an Observable Plot map as an image file (.png or .svg) from the table's geospatial data. To create charts from non-geospatial data, use the writeChart method.

Signature
async writeMap(map: (geoData: { features: { properties: Record<string, unknown> }[] }) => SVGSVGElement | HTMLElement, path: string, options?: { column?: string; rewind?: boolean; style?: string; dark?: boolean }): Promise<void>;
Parameters
  • map: A function that takes geospatial data (in GeoJSON format) and returns an Observable Plot map (an SVGSVGElement or HTMLElement).
  • path: The absolute path where the map image will be saved (e.g., "./output/map.png").
  • options: An optional object with configuration options:
  • options.column: The name of the column storing geometries. If there is only one geometry column, it will be used by default.
  • options.rewind: If true, rewinds the coordinates of polygons to follow the spherical winding order (important for D3.js). Defaults to true.
  • options.style: A CSS string to customize the map's appearance. This is applied to a <div> element wrapping the Plot map (which has the ID chart). Use this if the Plot style option is insufficient.
  • options.dark: If true, switches the map to dark mode. Defaults to false.
Returns

A promise that resolves when the map image has been saved.

Examples
import { geo, plot } from "@observablehq/plot";

const sdb = new SimpleDB();
const table = sdb.newTable();
await table.loadGeoData("./CanadianProvincesAndTerritories.geojson");

const mapFunction = (geoJsonData: { features: unknown[] }) =>
  plot({
    projection: {
      type: "conic-conformal",
      rotate: [100, -60],
      domain: geoJsonData,
    },
    marks: [
      geo(geoJsonData, { stroke: "black", fill: "lightblue" }),
    ],
  });

const outputPath = "./output/map.png";

await table.writeMap(mapFunction, outputPath);

logLineChart

Generates and logs a line chart to the console. The data should be sorted by the x-axis values for accurate representation.

Data Type Requirements:

  • X-axis values: Must be number or Date objects.
  • Y-axis values: Must be number values.
  • All values must be non-null and defined.
Signature
async logLineChart(x: string, y: string, options?: { formatX?: (d: unknown) => string; formatY?: (d: number) => string; smallMultiples?: string; fixedScales?: boolean; smallMultiplesPerRow?: number; width?: number; height?: number }): Promise<void>;
Parameters
  • x: The name of the column to be used for the x-axis. Values must be numbers or Date objects.
  • y: The name of the column to be used for the y-axis. Values must be numbers.
  • options: An optional object with configuration options:
  • options.formatX: A function to format the x-axis values for display. It receives the raw x-value as input and should return a string. If the first data point's x value is a Date, it defaults to formatting the date as "YYYY-MM-DD".
  • options.formatY: A function to format the y-axis values for display. It receives the raw y-value as input and should return a string.
  • options.smallMultiples: The name of a column to create small multiples (also known as facets or trellis charts). Each unique value in this column will generate a separate chart.
  • options.fixedScales: If true, all small multiples will share the same y-axis scale. Defaults to false.
  • options.smallMultiplesPerRow: The number of small multiples to display per row.
  • options.width: The width of the chart in characters.
  • options.height: The height of the chart in characters.
Returns

A promise that resolves when the chart has been logged to the console.

Examples

// Basic line chart

const data = [
  { date: new Date("2023-01-01"), value: 10 },
  { date: new Date("2023-02-01"), value: 20 },
  { date: new Date("2023-03-01"), value: 30 },
  { date: new Date("2023-04-01"), value: 40 },
];
await table.loadArray(data);
await table.convert({ date: "string" }, { datetimeFormat: "%x" });
await table.logLineChart("date", "value");

// Line chart with small multiples

const data = [
  { date: new Date("2023-01-01"), value: 10, category: "A" },
  { date: new Date("2023-02-01"), value: 20, category: "A" },
  { date: new Date("2023-03-01"), value: 30, category: "A" },
  { date: new Date("2023-04-01"), value: 40, category: "A" },
  { date: new Date("2023-01-01"), value: 15, category: "B" },
  { date: new Date("2023-02-01"), value: 25, category: "B" },
  { date: new Date("2023-03-01"), value: 35, category: "B" },
  { date: new Date("2023-04-01"), value: 45, category: "B" },
];
await table.loadArray(data);
await table.convert({ date: "string" }, { datetimeFormat: "%x" });
await table.logLineChart("date", "value", {
  smallMultiples: "category",
});

logDotChart

Generates and logs a dot chart to the console. The data should be sorted by the x-axis values for accurate representation.

Data Type Requirements:

  • X-axis values: Must be number or Date objects.
  • Y-axis values: Must be number values.
  • All values must be non-null and defined.
Signature
async logDotChart(x: string, y: string, options?: { formatX?: (d: unknown) => string; formatY?: (d: number) => string; smallMultiples?: string; fixedScales?: boolean; smallMultiplesPerRow?: number; width?: number; height?: number }): Promise<void>;
Parameters
  • x: The name of the column to be used for the x-axis. Values must be numbers or Date objects.
  • y: The name of the column to be used for the y-axis. Values must be numbers.
  • options: An optional object with configuration options:
  • options.formatX: A function to format the x-axis values for display. It receives the raw x-value as input and should return a string. If the first data point's x value is a Date, it defaults to formatting the date as "YYYY-MM-DD".
  • options.formatY: A function to format the y-axis values for display. It receives the raw y-value as input and should return a string.
  • options.smallMultiples: The name of a column to create small multiples (also known as facets). Each unique value in this column will generate a separate chart.
  • options.fixedScales: If true, all small multiples will share the same y-axis scale. Defaults to false.
  • options.smallMultiplesPerRow: The number of small multiples to display per row.
  • options.width: The width of the chart in characters.
  • options.height: The height of the chart in characters.
Returns

A promise that resolves when the chart has been logged to the console.

Examples

// Basic dot chart

const data = [
  { date: new Date("2023-01-01"), value: 10 },
  { date: new Date("2023-02-01"), value: 20 },
  { date: new Date("2023-03-01"), value: 30 },
  { date: new Date("2023-04-01"), value: 40 },
];
await table.loadArray(data);
await table.convert({ date: "string" }, { datetimeFormat: "%x" });
await table.logDotChart("date", "value");

// Dot chart with small multiples

const data = [
  { date: new Date("2023-01-01"), value: 10, category: "A" },
  { date: new Date("2023-02-01"), value: 20, category: "A" },
  { date: new Date("2023-03-01"), value: 30, category: "A" },
  { date: new Date("2023-04-01"), value: 40, category: "A" },
  { date: new Date("2023-01-01"), value: 15, category: "B" },
  { date: new Date("2023-02-01"), value: 25, category: "B" },
  { date: new Date("2023-03-01"), value: 35, category: "B" },
  { date: new Date("2023-04-01"), value: 45, category: "B" },
];
await table.loadArray(data);
await table.convert({ date: "string" }, { datetimeFormat: "%x" });
await table.logDotChart("date", "value", {
  smallMultiples: "category",
});

logBarChart

Generates and logs a bar chart to the console.

Signature
async logBarChart(labels: string, values: string, options?: { formatLabels?: (d: unknown) => string; formatValues?: (d: number) => string; showPercentages?: boolean; showTotal?: boolean; totalLabel?: string; compact?: boolean; width?: number }): Promise<void>;
Parameters
  • labels: The name of the column to be used for the labels (categories).
  • values: The name of the column to be used for the values.
  • options: An optional object with configuration options:
  • options.formatLabels: A function to format the labels. Defaults to converting the label to a string.
  • options.formatValues: A function to format the values. Defaults to converting the value to a string.
  • options.showPercentages: If true, displays the percentage each bar represents relative to the total. Defaults to false.
  • options.showTotal: If true, calculates and displays a total summary row. Defaults to false.
  • options.totalLabel: Allows customizing the label used for the total row. Defaults to "Total".
  • options.compact: Reduces vertical space in the logged output. Defaults to false.
  • options.width: The width of the chart in characters. Defaults to 40.
Returns

A promise that resolves when the chart has been logged to the console.

Examples
const data = [
  { category: "A", value: 10 },
  { category: "B", value: 20 },
];
await table.loadArray(data);
await table.logBarChart("category", "value");

logHistogram

Generates and logs a histogram of a numeric column to the console.

Signature
async logHistogram(values: string, options?: { bins?: number; formatLabels?: (min: number, max: number) => string; compact?: boolean; width?: number }): Promise<void>;
Parameters
  • values: The name of the numeric column for which to generate the histogram.
  • options: An optional object with configuration options:
  • options.bins: The number of bins (intervals) to use for the histogram. Defaults to 10.
  • options.formatLabels: A function to format the labels for the histogram bins. It receives the lower and upper bounds of each bin as arguments.
  • options.compact: If true, the histogram will be displayed in a more compact format. Defaults to false.
  • options.width: The maximum width of the histogram bars in characters.
Returns

A promise that resolves when the histogram has been logged to the console.

Examples

// Basic histogram of the 'temperature' column

await table.logHistogram("temperature");

// Histogram with 20 bins and custom label formatting

await table.logHistogram("age", {
  bins: 20,
  formatLabels: (min, max) => `${min}-${max} years`,
});

renameTable

Renames the current table.

Signature
async renameTable(name: string): Promise<void>;
Parameters
  • name: The new name for the table.
Returns

A promise that resolves when the table has been renamed.

Examples
// Rename the table to "new_employees"
await table.renameTable("new_employees");

setTypes

Sets the data types for columns in a new table. If the table already exists, it will be replaced. To convert the types of an existing table, use the .convert() method instead.

Signature
async setTypes(types: Record<string, "integer" | "float" | "number" | "string" | "date" | "time" | "datetime" | "datetimeTz" | "bigint" | "double" | "varchar" | "timestamp" | "timestamp with time zone" | "boolean" | geometry('${string}') | GEOMETRY('${string}')>): Promise<void>;
Parameters
  • types: An object specifying the column names and their target data types (JavaScript or SQL types).
Returns

A promise that resolves when the types have been set.

Examples
// Set types for a new table
await table.setTypes({
  name: "string",
  salary: "integer",
  raise: "float",
});

loadArray

Loads an array of JavaScript objects into the table.

Signature
async loadArray(arrayOfObjects: Record<string, unknown>[]): Promise<this>;
Parameters
  • arrayOfObjects: An array of objects, where each object represents a row and its properties represent columns.
Returns

A promise that resolves to the SimpleTable instance after the data has been loaded.

Examples
// Load data from an array of objects
const data = [
  { letter: "a", number: 1 },
  { letter: "b", number: 2 },
];
await table.loadArray(data);

loadData

Loads data from one or more local or remote files into the table. Supported file formats include CSV, JSON, Parquet, and Excel.

Signature
async loadData(files: string | string[], options?: { fileType?: "csv" | "dsv" | "json" | "parquet" | "excel"; autoDetect?: boolean; limit?: number; fileName?: boolean; unifyColumns?: boolean; columnTypes?: Record<string, string>; columns?: string[]; header?: boolean; allText?: boolean; delim?: string; skip?: number; nullPadding?: boolean; ignoreErrors?: boolean; compression?: "none" | "gzip" | "zstd"; encoding?: string; strict?: boolean; jsonFormat?: "unstructured" | "newlineDelimited" | "array"; records?: boolean; sheet?: string }): Promise<this>;
Parameters
  • files: The path(s) or URL(s) of the file(s) containing the data to be loaded.
  • options: An optional object with configuration options:
  • options.fileType: The type of file to load ("csv", "dsv", "json", "parquet", "excel"). Defaults to being inferred from the file extension.
  • options.autoDetect: A boolean indicating whether to automatically detect the data format. Defaults to true.
  • options.limit: A number indicating the maximum number of rows to load. Defaults to all rows.
  • options.fileName: A boolean indicating whether to include the file name as a new column in the loaded data. Defaults to false.
  • options.unifyColumns: A boolean indicating whether to unify columns across multiple files when their structures differ. Missing columns will be filled with NULL values. Defaults to false.
  • options.columnTypes: An object mapping column names to their expected data types. By default, types are inferred.
  • options.columns: An array of column names to load. When provided, only the specified columns are loaded, reducing memory usage and improving load times. Not supported for Excel files — combining columns with Excel files throws an error. If an invalid column name is provided, DuckDB will throw its native error. An empty array behaves the same as omitting the option (loads all columns). Defaults to loading all columns.
  • options.header: A boolean indicating whether the file has a header row. Applicable to CSV files. Defaults to true.
  • options.allText: A boolean indicating whether all columns should be treated as text. Applicable to CSV files. Defaults to false.
  • options.delim: The delimiter used in the file. Applicable to CSV and DSV files. By default, the delimiter is inferred.
  • options.skip: The number of lines to skip at the beginning of the file. Applicable to CSV files. Defaults to 0.
  • options.nullPadding: If true, when a row has fewer columns than expected, the remaining columns on the right will be padded with NULL values. Defaults to false.
  • options.ignoreErrors: If true, parsing errors encountered will be ignored, and rows with errors will be skipped. Defaults to false.
  • options.compression: The compression type of the file. Applicable to CSV files. Defaults to none.
  • options.strict: If true, an error will be thrown when encountering any issues. If false, structurally incorrect files will be parsed tentatively. Defaults to true.
  • options.encoding: The encoding of the file. Applicable to CSV files. Defaults to utf-8.
  • options.jsonFormat: The format of JSON files ("unstructured", "newlineDelimited", "array"). By default, the format is inferred.
  • options.records: A boolean indicating whether each line in a newline-delimited JSON file represents a record. Applicable to JSON files. By default, it's inferred.
  • options.sheet: A string indicating a specific sheet to import from an Excel file. By default, the first sheet is imported.
Returns

A promise that resolves to the SimpleTable instance after the data has been loaded.

Examples
// Load data from a single local CSV file
await table.loadData("./some-data.csv");

// Load data from a remote Parquet file
await table.loadData("https://some-website.com/some-data.parquet");

// Load data from multiple local JSON files
await table.loadData([
  "./some-data1.json",
  "./some-data2.json",
  "./some-data3.json",
]);

// Load data from multiple remote Parquet files with column unification
await table.loadData([
  "https://some-website.com/some-data1.parquet",
  "https://some-website.com/some-data2.parquet",
  "https://some-website.com/some-data3.parquet",
], { unifyColumns: true });

// Load only specific columns from a CSV file
await table.loadData("./employees.csv", { columns: ["name", "salary"] });

loadDataFromDirectory

Loads data from all supported files (CSV, JSON, Parquet, Excel) within a local directory into the table.

Signature
async loadDataFromDirectory(directory: string, options?: { fileType?: "csv" | "dsv" | "json" | "parquet" | "excel"; autoDetect?: boolean; limit?: number; fileName?: boolean; unifyColumns?: boolean; columnTypes?: Record<string, string>; columns?: string[]; header?: boolean; allText?: boolean; delim?: string; skip?: number; nullPadding?: boolean; ignoreErrors?: boolean; compression?: "none" | "gzip" | "zstd"; encoding?: "utf-8" | "utf-16" | "latin-1"; strict?: boolean; jsonFormat?: "unstructured" | "newlineDelimited" | "array"; records?: boolean; sheet?: string }): Promise<this>;
Parameters
  • directory: The absolute path to the directory containing the data files.
  • options: An optional object with configuration options:
  • options.fileType: The type of file to load ("csv", "dsv", "json", "parquet", "excel"). Defaults to being inferred from the file extension.
  • options.autoDetect: A boolean indicating whether to automatically detect the data format. Defaults to true.
  • options.limit: A number indicating the maximum number of rows to load. Defaults to all rows.
  • options.fileName: A boolean indicating whether to include the file name as a new column in the loaded data. Defaults to false.
  • options.unifyColumns: A boolean indicating whether to unify columns across multiple files when their structures differ. Missing columns will be filled with NULL values. Defaults to false.
  • options.columnTypes: An object mapping column names to their expected data types. By default, types are inferred.
  • options.columns: An array of column names to load. When provided, only the specified columns are loaded, reducing memory usage and improving load times. Not supported for Excel files — combining columns with Excel files throws an error. If an invalid column name is provided, DuckDB will throw its native error. An empty array behaves the same as omitting the option (loads all columns). Defaults to loading all columns.
  • options.header: A boolean indicating whether the file has a header row. Applicable to CSV files. Defaults to true.
  • options.allText: A boolean indicating whether all columns should be treated as text. Applicable to CSV files. Defaults to false.
  • options.delim: The delimiter used in the file. Applicable to CSV and DSV files. By default, the delimiter is inferred.
  • options.skip: The number of lines to skip at the beginning of the file. Applicable to CSV files. Defaults to 0.
  • options.nullPadding: If true, when a row has fewer columns than expected, the remaining columns on the right will be padded with NULL values. Defaults to false.
  • options.ignoreErrors: If true, parsing errors encountered will be ignored, and rows with errors will be skipped. Defaults to false.
  • options.compression: The compression type of the file. Applicable to CSV files. Defaults to none.
  • options.strict: If true, an error will be thrown when encountering any issues. If false, structurally incorrect files will be parsed tentatively. Defaults to true.
  • options.encoding: The encoding of the files. Applicable to CSV files. Defaults to utf-8.
  • options.jsonFormat: The format of JSON files ("unstructured", "newlineDelimited", "array"). By default, the format is inferred.
  • options.records: A boolean indicating whether each line in a newline-delimited JSON file represents a record. Applicable to JSON files. By default, it's inferred.
  • options.sheet: A string indicating a specific sheet to import from an Excel file. By default, the first sheet is imported.
Returns

A promise that resolves to the SimpleTable instance after the data has been loaded.

Examples
// Load all supported data files from the "./data/" directory
await table.loadDataFromDirectory("./data/");

// Load only specific columns from all CSV files in a directory
await table.loadDataFromDirectory("./data/", { columns: ["name", "salary"] });

loadGeoData

Loads geospatial data from an external file or URL into the table.

Signature
async loadGeoData(file: string, options?: { toWGS84?: boolean }): Promise<this>;
Parameters
  • file: The URL or absolute path to the external file containing the geospatial data.
  • options: An optional object with configuration options:
  • options.toWGS84: If true, the method will attempt to reproject the data to WGS84.
Returns

A promise that resolves to the SimpleTable instance after the geospatial data has been loaded.

Examples
// Load geospatial data from a URL
await table.loadGeoData("https://some-website.com/some-data.geojson");

// Load geospatial data from a local file
await table.loadGeoData("./some-data.geojson");

// Load geospatial data from a shapefile (with relevant files in the same folder) and reproject to WGS84
await table.loadGeoData("./some-data/some-data.shp", { toWGS84: true });

// Load geospatial data from a zipped shapefile and reproject to WGS84
await table.loadGeoData("./some-data.shp.zip", { toWGS84: true });

createFtsIndex

Creates a full-text search (FTS) index on a specified text column using DuckDB's FTS extension.

If an FTS index already exists on the table, this method will skip creation and log a message (when verbose is enabled), unless the overwrite option is set to true.

Signature
async createFtsIndex(columnId: string, columnText: string, options?: { stemmer?: "arabic" | "basque" | "catalan" | "danish" | "dutch" | "english" | "finnish" | "french" | "german" | "greek" | "hindi" | "hungarian" | "indonesian" | "irish" | "italian" | "lithuanian" | "nepali" | "norwegian" | "porter" | "portuguese" | "romanian" | "russian" | "serbian" | "spanish" | "swedish" | "tamil" | "turkish" | "none"; stopwords?: string; ignore?: string; stripAccents?: boolean; lower?: boolean; overwrite?: boolean; verbose?: boolean }): Promise<this>;
Parameters
  • columnId: The name of the column containing unique identifiers for each row.
  • columnText: The name of the column containing the text to index.
  • options: An optional object with configuration options:
  • options.stemmer: The language stemmer to apply for word normalization. Supports multiple languages or "none" to disable stemming. Defaults to 'porter'.
  • options.stopwords: The table containing the stopwords to use for the FTS index. Supports multiple languages or "none" to disable stopwords. Defaults to "english".
  • options.overwrite: If true, recreates the index even if it already exists. Defaults to false.
  • options.verbose: If true, logs additional debugging information, including index creation status. Defaults to false.
  • columnId: The column containing the document identifiers.
  • columnText: The column containing the text to search.
  • options: An optional object with configuration options:
  • options.stemmer: The stemmer to use for the FTS index. Supports multiple languages or "none" to disable stemming. Defaults to "porter".
  • options.stopwords: The table containing the stopwords to use for the FTS index. Supports multiple languages or "none" to disable stopwords. Defaults to "english".
  • options.ignore: The regular expression of patterns to be ignored. Defaults to "(\.|[^a-z])+".
  • options.stripAccents: A boolean indicating whether to remove accents. Defaults to true.
  • options.lower: A boolean indicating whether to convert all text to lowercase. Defaults to true.
  • options.overwrite: A boolean indicating whether to overwrite the existing FTS index. Defaults to false.
  • options.verbose: A boolean indicating whether to log additional information. Defaults to false.
Returns

A promise that resolves to the SimpleTable instance for method chaining.

Examples
// Load a dataset and create an FTS index
await table.loadData("recipes.parquet");

// Create FTS index for later searches
await table.createFtsIndex("Dish", "Recipe");

// Create an index with a specific language stemmer
await table.createFtsIndex("Dish", "Recipe", {
  stemmer: "french",
});

// Recreate an existing index with different settings
await table.createFtsIndex("Dish", "Recipe", {
  stemmer: "english",
  overwrite: true,
});

// Create index with verbose logging
await table.createFtsIndex("Dish", "Recipe", {
  verbose: true,
});
// Logs: "Creating FTS index on 'Recipe' column..."
// Logs: "FTS index created successfully."

createVssIndex

Creates a vector similarity search (VSS) index on a specified column using DuckDB's VSS extension.

If a VSS index already exists on the table, this method will skip creation and log a message (when verbose is enabled), unless the overwrite option is set to true.

Signature
async createVssIndex(column: string, options?: { overwrite?: boolean; verbose?: boolean; efConstruction?: number; efSearch?: number; M?: number }): Promise<this>;
Parameters
  • column: The name of the column containing vector embeddings (must be FLOAT array type).
  • options: An optional object with configuration options:
  • options.overwrite: If true, drops and recreates the index even if it already exists. Defaults to false.
  • options.verbose: If true, logs additional debugging information, including index creation status. Defaults to false.
  • options.efConstruction: The number of candidate vertices to consider during index construction. Higher values result in more accurate indexes but increase build time. Defaults to 128.
  • options.efSearch: The number of candidate vertices to consider during search. Higher values result in more accurate searches but increase search time. Defaults to 64.
  • options.M: The maximum number of neighbors to keep for each vertex in the graph. Higher values result in more accurate indexes but increase build time and memory usage. Defaults to 16.
Returns

A promise that resolves to the SimpleTable instance for method chaining.

Examples
// Load data that already contains an embedding column
await table.loadData("data.csv");

// Create VSS index for fast similarity searches
await table.createVssIndex("embedding_column");

// Recreate an existing index
await table.createVssIndex("embedding_column", {
  overwrite: true,
});

// Create index with verbose logging
await table.createVssIndex("embedding_column", {
  verbose: true,
});
// Logs: "Creating VSS index on 'embedding_column' column..."
// Logs: "VSS index created successfully."

// Create index with custom HNSW parameters for higher accuracy
await table.createVssIndex("embedding_column", {
  efConstruction: 256,
  efSearch: 128,
  M: 32,
});

bm25

Performs BM25 full-text search on a text column to find the most relevant results. BM25 (Best Matching 25) is a ranking function used in information retrieval that calculates relevance scores based on term frequency and document length normalization.

This method creates a full-text search index on the specified text column using DuckDB's FTS extension. If the index already exists, it will be reused unless the overwriteIndex option is set to true.

Signature
async bm25(text: string, columnId: string, columnText: string, nbResults: number, options?: { outputTable?: string; verbose?: boolean; k?: number; b?: number; stemmer?: "arabic" | "basque" | "catalan" | "danish" | "dutch" | "english" | "finnish" | "french" | "german" | "greek" | "hindi" | "hungarian" | "indonesian" | "irish" | "italian" | "lithuanian" | "nepali" | "norwegian" | "porter" | "portuguese" | "romanian" | "russian" | "serbian" | "spanish" | "swedish" | "tamil" | "turkish" | "none"; stopwords?: string; ignore?: string; stripAccents?: boolean; lower?: boolean; overwriteIndex?: boolean; conjunctive?: boolean; minScore?: number; scoreColumn?: string }): Promise<this>;
Parameters
  • text: The search query text to match against the text column.
  • columnId: The name of the column containing unique identifiers for each row.
  • columnText: The name of the column containing the text to search.
  • nbResults: The number of top-ranked results to return.
  • options: An optional object with configuration options:
  • options.outputTable: The name of a new table where the results will be stored. If not provided, the current table will be replaced with the search results.
  • options.verbose: If true, logs additional debugging information, including FTS index creation status. Defaults to false.
  • options.k: The BM25 k parameter controlling term frequency saturation. Defaults to 1.2.
  • options.b: The BM25 b parameter controlling document length normalization (0-1 range). Defaults to 0.75.
  • options.stemmer: The language stemmer to apply for word normalization. Supports multiple languages or "none" to disable stemming. Defaults to 'porter'.
  • options.stopwords: The table containing the stopwords to use for the FTS index. Supports multiple languages or "none" to disable stopwords. Defaults to "english".
  • options.ignore: The regular expression of patterns to be ignored. Defaults to "(\.|[^a-z])+".
  • options.stripAccents: A boolean indicating whether to remove accents. Defaults to true.
  • options.lower: A boolean indicating whether to convert all text to lowercase. Defaults to true.
  • options.overwriteIndex: If true, drops and recreates the FTS index even if it already exists. Defaults to false.
  • options.conjunctive: If true, all terms in the query string must be present in order for a document to be retrieved. Defaults to false.
  • options.minScore: A threshold to filter out results with a BM25 score below this value.
  • options.scoreColumn: If provided, the BM25 score will be included in the output table under this column name.
Returns

A promise that resolves to a SimpleTable instance containing the search results, ordered by relevance (best matches first).

Examples
// Load a dataset of recipes
await table.loadData("recipes.parquet");

// Search for "italian food" in the Recipe column, return top 5 results
await table.bm25("italian food", "Dish", "Recipe", 5);

// Check the results
const dishes = await table.getValues("Dish");
// Returns: ["Carbonara", "Pizza", "Risotto", "Tiramisu", "Escarole Soup"]

// Search with a specific language stemmer
await table.bm25("french food", "Dish", "Recipe", 5, {
  stemmer: "french",
});

// Recreate the index with different settings and perform search
await table.bm25("italian food", "Dish", "Recipe", 5, {
  stemmer: "english",
  overwriteIndex: true,
});

// Save results to a new table without modifying the original
const italianDishes = await table.bm25("italian food", "Dish", "Recipe", 5, {
  outputTable: "italian_results",
});

// Original table remains unchanged
const allDishes = await table.getValues("Dish");
console.log(allDishes.length); // 336 (all dishes)

// New table contains only search results
const italianOnly = await italianDishes.getValues("Dish");
console.log(italianOnly.length); // 5 (top results)

// Multiple searches reuse the same index for better performance
// The first search creates the index
const italian = await table.bm25("italian food", "Dish", "Recipe", 5, {
  outputTable: "italian",
});

// The second search reuses the existing index, so it's faster
const french = await table.bm25("french food", "Dish", "Recipe", 5, {
  outputTable: "french",
});
  • @example
// Filter results by a minimum BM25 score and include the score in the output
await table.bm25("spicy noodles", "Dish", "Recipe", 10, {
  minScore: 5.5,
  scoreColumn: "bm25_score",
});

// Use the conjunctive option to require all terms
await table.bm25("italian sauce", "Dish", "Recipe", 5, {
  conjunctive: true,
});

insertRows

Inserts rows, provided as an array of JavaScript objects, into the table.

Signature
async insertRows(rows: Record<string, unknown>[]): Promise<void>;
Parameters
  • rows: An array of objects, where each object represents a row to be inserted and its properties correspond to column names.
Returns

A promise that resolves when the rows have been inserted.

Examples
// Insert new rows into the table
const newRows = [
  { letter: "c", number: 3 },
  { letter: "d", number: 4 },
];
await table.insertRows(newRows);

insertTables

Inserts all rows from one or more other tables into this table. If tables do not have the same columns, an error will be thrown unless the unifyColumns option is set to true.

Signature
async insertTables(tablesToInsert: SimpleTable | SimpleTable[], options?: { unifyColumns?: boolean }): Promise<this>;
Parameters
  • tablesToInsert: The name(s) of the table(s) or SimpleTable instance(s) from which rows will be inserted.
  • options: An optional object with configuration options:
  • options.unifyColumns: A boolean indicating whether to unify the columns of the tables. If true, missing columns in a table will be filled with NULL values. Defaults to false.
Returns

The table itself, allowing for method chaining.

Examples
// Insert all rows from 'tableB' into 'tableA'.
await tableA.insertTables("tableB");

// Insert all rows from 'tableB' and 'tableC' into 'tableA'.
await tableA.insertTables(["tableB", "tableC"]);

// Insert rows from multiple tables, unifying columns. Missing columns will be filled with NULL.
await tableA.insertTables(["tableB", "tableC"], { unifyColumns: true });

loadSample

Fetches sample data from the simple-data-analysis-core GitHub repository.

Signature
async loadSample(sample: "fires" | "recipes" | "temperatures" | "temperaturesCities" | "canada" | "firesGeo"): Promise<this>;
Parameters
Examples
// Load the fires sample data
await table.loadSample("fires");

cloneTable

Returns a new table with the same structure and data as this table. The data can be optionally filtered, limited to a specific number of rows, and offset.

If conditions, nbRows, and offset are all used, they are applied in this order: conditions (WHERE clause) first, then offset, and finally nbRows (LIMIT).

Note that cloning large tables can be a slow operation.

Signature
async cloneTable(nameOrOptions?: string | { outputTable?: string; conditions?: string; columns?: string | string[]; nbRows?: number; offset?: number }): Promise<this>;
Parameters
  • nameOrOptions: Either a string specifying the name of the new table, or an optional object with configuration options. If not provided, a default name (e.g., "table1", "table2") will be generated.
  • nameOrOptions.outputTable: The name of the new table to be created in the database. If not provided, a default name (e.g., "table1", "table2") will be generated.
  • nameOrOptions.conditions: A SQL WHERE clause condition to filter the data during cloning. Defaults to no condition (clones all rows).
  • nameOrOptions.columns: An array of column names to include in the cloned table. If not provided, all columns will be included.
  • nameOrOptions.nbRows: The number of rows to include in the cloned table. If provided, only the first X rows (potentially after filtering and offset) will be cloned.
  • nameOrOptions.offset: The number of rows to skip before starting to clone rows.
Returns

A promise that resolves to a new table instance containing the cloned data.

Examples
// Clone tableA to a new table with a default generated name (e.g., "table1")
const tableB = await tableA.cloneTable();

// Clone tableA to a new table named "my_cloned_table" using string parameter
const tableB = await tableA.cloneTable("my_cloned_table");

// Clone tableA to a new table named "my_cloned_table" using options object
const tableB = await tableA.cloneTable({ outputTable: "my_cloned_table" });

// Clone tableA, including only rows where 'column1' is greater than 10
const tableB = await tableA.cloneTable({ conditions: `column1 > 10` });

// Clone tableA with only specific columns
const tableB = await tableA.cloneTable({ columns: ["name", "age", "city"] });

// Clone only the first 10 rows of tableA
const tableB = await tableA.cloneTable({ nbRows: 10 });

// Clone 10 rows after skipping the first 5 rows
const tableB = await tableA.cloneTable({ nbRows: 10, offset: 5 });

// Clone tableA to a specific table name with filtered data, specific columns, and limited rows
const tableB = await tableA.cloneTable({
  outputTable: "filtered_data",
  conditions: `status = 'active' AND created_date >= '2023-01-01'`,
  columns: ["name", "status", "created_date"],
  nbRows: 100,
});

cloneColumn

Clones an existing column in this table, creating a new column with identical values.

Signature
async cloneColumn(originalColumn: string, newColumn: string): Promise<void>;
Parameters
  • originalColumn: The name of the original column to clone.
  • newColumn: The name of the new column to be created.
Returns

A promise that resolves when the column has been cloned.

Examples
// Clone 'firstName' column as 'contactName'
await table.cloneColumn("firstName", "contactName");

cloneColumnWithOffset

Clones a column in the table and offsets its values by a specified number of rows. This is useful for time-series analysis or comparing values across different time points.

Important: The offset is applied based on the current row order in the table. For meaningful results, ensure your data is sorted appropriately (e.g., by date/time for time-series analysis) before calling this method.

Signature
async cloneColumnWithOffset(originalColumn: string, newColumn: string, options?: { offset?: number; categories?: string | string[] }): Promise<void>;
Parameters
  • originalColumn: The name of the original column.
  • newColumn: The name of the new column to be created with offset values.
  • options: An optional object with configuration options:
  • options.offset: The number of rows to offset the values. A positive number shifts values downwards (later rows), a negative number shifts values upwards (earlier rows). Defaults to 1.
  • options.categories: A string or an array of strings representing columns to partition the data by. The offset will be applied independently within each category.
Returns

A promise that resolves when the column has been cloned with offset values.

Examples
// Clone 'value' as 'previous_value', offsetting by 1 row (value of row N-1 goes to row N)
await table.cloneColumnWithOffset("value", "previous_value");

// Clone 'sales' as 'sales_2_days_ago', offsetting by 2 rows
await table.cloneColumnWithOffset("sales", "sales_2_days_ago", { offset: 2 });

// Clone 'temperature' as 'prev_temp_by_city', offsetting by 1 row within each 'city' category
await table.cloneColumnWithOffset("temperature", "prev_temp_by_city", {
  offset: 1,
  categories: "city",
});

// Clone 'stock_price' as 'prev_price_by_stock_and_exchange', offsetting by 1 row within each 'stock_symbol' and 'exchange' category
await table.cloneColumnWithOffset(
  "stock_price",
  "prev_price_by_stock_and_exchange",
  {
    offset: 1,
    categories: ["stock_symbol", "exchange"],
  },
);

fill

Fills NULL values in specified columns. By default, each NULL is replaced with the last non-NULL value from the preceding row. When interpolate is true, NULL values are replaced using linear interpolation (or extrapolation at the ends). Pass interpolateBy with a real numeric or date column name to use it as the X-axis, so that interpolated values are proportional to the actual distances between X-axis values rather than treating every row as equidistant. When interpolateBy is set, interpolate is automatically assumed true.

Signature
async fill(columns: string | string[], options?: { categories?: string | string[]; interpolate?: boolean; interpolateBy?: string }): Promise<void>;
Parameters
  • columns: The column(s) for which to fill NULL values.
  • options: An optional object with configuration options:
  • options.categories: A string or an array of strings representing columns to partition the data by. The fill will be applied independently within each category.
  • options.interpolate: If true, replaces NULL values with linearly interpolated values using DuckDB's fill() window function. When interpolateBy is not set, row positions are used as the X-axis, treating rows as equidistant. For NULL values at the ends, linear extrapolation is used. Both the column values and the X-axis values must support arithmetic. If false or omitted, the previous non-NULL value is used instead. Automatically assumed true when interpolateBy is set.
  • options.interpolateBy: A column name to use as the X-axis for interpolation instead of equidistant row positions. When provided, interpolate is automatically assumed true. Use this when rows are not evenly spaced (e.g., timestamps or non-uniform numeric indices) so that interpolated values are proportional to the actual distance between X-axis values.
Returns

A promise that resolves when the NULL values have been filled.

Examples
// Fill NULL values in 'column1' with the previous non-NULL value
await table.fill("column1");

// Fill NULL values in multiple columns
await table.fill(["columnA", "columnB"]);

// Fill NULL values in 'value' independently within each 'group'
await table.fill("value", { categories: "group" });

// Fill NULL values in 'value' using linear interpolation
await table.fill("value", { interpolate: true });

// Fill NULL values in 'value' using linear interpolation, independently within each 'group'
await table.fill("value", { categories: "group", interpolate: true });

// Fill NULL values in 'value' using linear interpolation proportional to 'x' distances
await table.fill("value", { interpolate: true, interpolateBy: "x" });

// interpolateBy implies interpolate: true, so this is equivalent to the previous example
await table.fill("value", { interpolateBy: "x" });

sort

Sorts the rows of the table based on specified column(s) and order(s). If no columns are specified, all columns are sorted from left to right in ascending order.

Signature
async sort(order?: Record<string, "asc" | "desc"> | null, options?: { lang?: Record<string, string> }): Promise<void>;
Parameters
  • order: An object mapping column names to their sorting order: "asc" for ascending or "desc" for descending. If null, all columns are sorted ascendingly.
  • options: An optional object with configuration options:
  • options.lang: An object mapping column names to language codes for collation (e.g., { column1: "fr" }). See DuckDB Collations documentation for more details: https://duckdb.org/docs/sql/expressions/collations.
Returns

A promise that resolves when the table has been sorted.

Examples
// Sort all columns from left to right in ascending order
await table.sort();

// Sort 'column1' in ascending order
await table.sort({ column1: "asc" });

// Sort 'column1' ascendingly, then 'column2' descendingly
await table.sort({ column1: "asc", column2: "desc" });

// Sort 'column1' considering French accents
await table.sort({ column1: "asc" }, { lang: { column1: "fr" } });

selectColumns

Selects specific columns in the table, removing all others.

Signature
async selectColumns(columns: string | string[]): Promise<void>;
Parameters
  • columns: The name or an array of names of the columns to be selected.
Returns

A promise that resolves when the columns have been selected.

Examples
// Select only the 'firstName' and 'lastName' columns, removing all other columns.
await table.selectColumns(["firstName", "lastName"]);

// Select only the 'productName' column.
await table.selectColumns("productName");

skip

Skips the first n rows of the table, effectively removing them.

Signature
async skip(nbRowsToSkip: number): Promise<void>;
Parameters
  • nbRowsToSkip: The number of rows to skip from the beginning of the table.
Returns

A promise that resolves when the rows have been skipped.

Examples
// Skip the first 10 rows of the table
await table.skip(10);

hasColumn

Checks if a column with the specified name exists in the table.

Signature
async hasColumn(column: string): Promise<boolean>;
Parameters
  • column: The name of the column to check.
Returns

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

Examples
// Check if the table has a column named "age"
const hasAgeColumn = await table.hasColumn("age");
console.log(hasAgeColumn); // Output: true or false

sample

Selects random rows from the table, removing all others. You can optionally specify a seed to ensure repeatable sampling.

Signature
async sample(quantity: number | string, options?: { seed?: number }): Promise<void>;
Parameters
  • quantity: The number of rows to select (e.g., 100) or a percentage string (e.g., "10%") specifying the sampling size.
  • options: An optional object with configuration options:
  • options.seed: A number specifying the seed for repeatable sampling. Using the same seed will always yield the same random rows. Defaults to a random seed.
Returns

A promise that resolves when the sampling is complete.

Examples
// Select 100 random rows from the table
await table.sample(100);

// Select 10% of the rows randomly
await table.sample("10%");

// Select random rows with a specific seed for repeatable results
await table.sample("10%", { seed: 123 });

selectRows

Selects a specified number of rows from this table. An offset can be applied to skip initial rows, and the results can be output to a new table.

Signature
async selectRows(count: number | string, options?: { offset?: number; outputTable?: string | boolean }): Promise<this>;
Parameters
  • count: The number of rows to select.
  • options: An optional object with configuration options:
  • options.offset: The number of rows to skip from the beginning of the table before selecting. Defaults to 0.
  • options.outputTable: If true, the selected rows will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be modified. Defaults to false.
Returns

A promise that resolves to a table instance containing the selected rows (either the modified current table or a new table).

Examples
// Select the first 100 rows of the current table
await table.selectRows(100);

// Select 100 rows after skipping the first 50 rows
await table.selectRows(100, { offset: 50 });

// Select 50 rows and store them in a new table with a generated name
const newTable = await table.selectRows(50, { outputTable: true });

// Select 75 rows and store them in a new table named "top_customers"
const topCustomersTable = await table.selectRows(75, {
  outputTable: "top_customers",
});

removeDuplicates

Removes duplicate rows from this table, keeping only unique rows. Note that the resulting data order might differ from the original.

Signature
async removeDuplicates(options?: { on?: string | string[] }): Promise<void>;
Parameters
  • options: An optional object with configuration options:
  • options.on: A column name or an array of column names to consider when identifying duplicates. If specified, duplicates are determined based only on the values in these columns. If omitted, all columns are considered.
Returns

A promise that resolves when the duplicate rows have been removed.

Examples
// Remove duplicate rows based on all columns
await table.removeDuplicates();

// Remove duplicate rows based only on the 'email' column
await table.removeDuplicates({ on: "email" });

// Remove duplicate rows based on 'firstName' and 'lastName' columns
await table.removeDuplicates({ on: ["firstName", "lastName"] });

removeMissing

Removes rows with missing values from this table. By default, missing values include SQL NULL, as well as string representations like "NULL", "null", "NaN", "undefined", and empty strings "".

Signature
async removeMissing(options?: { columns?: string | string[]; missingValues?: (string | number)[]; invert?: boolean }): Promise<void>;
Parameters
  • options: An optional object with configuration options:
  • options.columns: A string or an array of strings specifying the columns to consider for missing values. If omitted, all columns are considered.
  • options.missingValues: An array of values to be treated as missing values instead of the default ones. Defaults to ["undefined", "NaN", "null", "NULL", ""].
  • options.invert: A boolean indicating whether to invert the condition. If true, only rows containing missing values will be kept. Defaults to false.
Returns

A promise that resolves when the rows with missing values have been removed.

Examples
// Remove rows with missing values in any column
await table.removeMissing();

// Remove rows with missing values only in 'firstName' or 'lastName' columns
await table.removeMissing({ columns: ["firstName", "lastName"] });

// Keep only rows with missing values in any column
await table.removeMissing({ invert: true });

// Remove rows where 'age' is missing or is equal to -1
await table.removeMissing({ columns: "age", missingValues: [-1] });

trim

Trims specified characters from the beginning, end, or both sides of string values in the given columns.

Signature
async trim(columns: string | string[], options?: { character?: string; method?: "leftTrim" | "rightTrim" | "trim" }): Promise<void>;
Parameters
  • columns: The column name or an array of column names to trim.
  • options: An optional object with configuration options:
  • options.character: The string to trim. Defaults to whitespace characters.
  • options.method: The trimming method to apply: "leftTrim" (removes from the beginning), "rightTrim" (removes from the end), or "trim" (removes from both sides). Defaults to "trim".
Returns

A promise that resolves when the trimming operation is complete.

Examples
// Trim whitespace from 'column1'
await table.trim("column1");

// Trim leading and trailing asterisks from 'productCode'
await table.trim("productCode", { character: "*" });

// Right-trim whitespace from 'description' and 'notes' columns
await table.trim(["description", "notes"], { method: "rightTrim" });

filter

Filters rows from this table based on SQL conditions. Note that it's often faster to use the removeRows method for simple removals. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async filter(conditions: string): Promise<void>;
Parameters
  • conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "column1 > 10 AND column2 = 'value'").
Returns

A promise that resolves when the rows have been filtered.

Examples
// Keep only rows where the 'fruit' column is not 'apple'
await table.filter(`fruit != 'apple'`);

// Keep rows where 'price' is greater than 100 AND 'quantity' is greater than 0
await table.filter(`price > 100 && quantity > 0`); // Using JS syntax

// Keep rows where 'category' is 'Electronics' OR 'Appliances'
await table.filter(`category === 'Electronics' || category === 'Appliances'`); // Using JS syntax

// Keep rows where 'lastPurchaseDate' is on or after '2023-01-01'
await table.filter(`lastPurchaseDate >= '2023-01-01'`);

keep

Keeps rows in this table that have specific values in specified columns, removing all other rows.

Signature
async keep(columnsAndValues: Record<string, (number | string | Date | boolean | null)[] | (number | string | Date | boolean | null)>): Promise<void>;
Parameters
  • columnsAndValues: An object where keys are column names and values are the specific values (or an array of values) to keep in those columns.
Returns

A promise that resolves when the rows have been filtered.

Examples
// Keep only rows where 'job' is 'accountant' or 'developer', AND 'city' is 'Montreal'
await table.keep({ job: ["accountant", "developer"], city: "Montreal" });

// Keep only rows where 'status' is 'active'
await table.keep({ status: "active" });

remove

Removes rows from this table that have specific values in specified columns.

Signature
async remove(columnsAndValues: Record<string, (number | string | Date | boolean | null)[] | (number | string | Date | boolean | null)>): Promise<void>;
Parameters
  • columnsAndValues: An object where keys are column names and values are the specific values (or an array of values) to remove from those columns.
Returns

A promise that resolves when the rows have been removed.

Examples
// Remove rows where 'job' is 'accountant' or 'developer', AND 'city' is 'Montreal'
await table.remove({ job: ["accountant", "developer"], city: "Montreal" });

// Remove rows where 'status' is 'inactive'
await table.remove({ status: "inactive" });

removeRows

Removes rows from this table based on SQL conditions. This method is similar to filter(), but removes rows instead of keeping them. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async removeRows(conditions: string): Promise<void>;
Parameters
  • conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "fruit = 'apple'").
Returns

A promise that resolves when the rows have been removed.

Examples
// Remove rows where the 'fruit' column is 'apple'
await table.removeRows(`fruit = 'apple'`);

// Remove rows where 'quantity' is less than 5
await table.removeRows(`quantity < 5`);

// Remove rows where 'price' is less than 100 AND 'quantity' is 0
await table.removeRows(`price < 100 && quantity === 0`); // Using JS syntax

// Remove rows where 'category' is 'Electronics' OR 'Appliances'
await table.removeRows(
  `category === 'Electronics' || category === 'Appliances'`,
); // Using JS syntax

renameColumns

Renames one or more columns in the table.

Signature
async renameColumns(names: Record<string, string>): Promise<void>;
Parameters
  • names: An object mapping old column names to their new column names (e.g., { "oldName": "newName", "anotherOld": "anotherNew" }).
Returns

A promise that resolves when the columns have been renamed.

Examples
// Rename "How old?" to "age" and "Man or woman?" to "sex"
await table.renameColumns({ "How old?": "age", "Man or woman?": "sex" });

// Rename a single column
await table.renameColumns({ "product_id": "productId" });

cleanColumnNames

Cleans column names by removing non-alphanumeric characters and formatting them to camel case.

Signature
async cleanColumnNames(): Promise<void>;
Returns

A promise that resolves when the column names have been cleaned.

Examples
// Clean all column names in the table
// e.g., "First Name" becomes "firstName", "Product ID" becomes "productId"
await table.cleanColumnNames();

longer

Restructures this table by stacking (unpivoting) columns. This is useful for tidying up data from a wide format to a long format.

For example, given a table showing employee counts per department per year:

Department202120222023
Accounting10915
Sales527598

We can restructure it by putting all year columns into a new column named Year and their corresponding employee counts into a new column named Employees.

Signature
async longer(columns: string[], columnsTo: string, valuesTo: string): Promise<void>;
Parameters
  • columns: An array of strings representing the names of the columns to be stacked (unpivoted).
  • columnsTo: The name of the new column that will contain the original column names (e.g., "Year").
  • valuesTo: The name of the new column that will contain the values from the stacked columns (e.g., "Employees").
Returns

A promise that resolves when the table has been restructured.

Examples
// Restructure the table by stacking year columns into 'year' and 'employees'
await table.longer(["2021", "2022", "2023"], "year", "employees");

The table will then look like this:

DepartmentYearEmployees
Accounting202110
Accounting20229
Accounting202315
Sales202152
Sales202275
Sales202398

wider

Restructures this table by unstacking (pivoting) values, transforming data from a long format to a wide format.

For example, given a table showing employee counts per department per year:

DepartmentYearEmployees
Accounting202110
Accounting20229
Accounting202315
Sales202152
Sales202275
Sales202398

We can restructure it by creating new columns for each year, with the associated employee counts as values.

Signature
async wider(columnsFrom: string, valuesFrom: string): Promise<void>;
Parameters
  • columnsFrom: The name of the column containing the values that will be transformed into new column headers (e.g., "Year").
  • valuesFrom: The name of the column containing the values to be spread across the new columns (e.g., "Employees").
Returns

A promise that resolves when the table has been restructured.

Examples
// Restructure the table by pivoting 'Year' into new columns with 'Employees' as values
await table.wider("Year", "Employees");

The table will then look like this:

Department202120222023
Accounting10915
Sales527598

convert

Converts data types of specified columns to target types (JavaScript or SQL types).

When converting timestamps, dates, or times to/from strings, you must provide a datetimeFormat option using DuckDB's format specifiers.

When converting timestamps, dates, or times to/from numbers, the numerical representation will be in milliseconds since the Unix epoch (1970-01-01 00:00:00 UTC).

When converting strings to numbers, commas (often used as thousand separators) will be automatically removed before conversion.

Signature
async convert(types: Record<string, "integer" | "float" | "number" | "string" | "date" | "time" | "datetime" | "datetimeTz" | "bigint" | "double" | "varchar" | "timestamp" | "timestamp with time zone" | "boolean">, options?: { try?: boolean; datetimeFormat?: string }): Promise<void>;
Parameters
  • types: An object mapping column names to their target data types for conversion.
  • options: An optional object with configuration options:
  • options.try: If true, values that cannot be converted will be replaced by NULL instead of throwing an error. Defaults to false.
  • options.datetimeFormat: A string specifying the format for date and time conversions. Uses strftime and strptime functions from DuckDB. For format specifiers, see DuckDB's documentation.
Returns

A promise that resolves when the column types have been converted.

Examples
// Convert 'column1' to string and 'column2' to integer (JavaScript types)
await table.convert({ column1: "string", column2: "integer" });

// Convert 'column1' to VARCHAR and 'column2' to BIGINT (SQL types)
await table.convert({ column1: "varchar", column2: "bigint" });

// Convert strings in 'column3' to datetime using a specific format
await table.convert({ column3: "datetime" }, { datetimeFormat: "%Y-%m-%d" });

// Convert datetime values in 'column3' to strings using a specific format
await table.convert({ column3: "string" }, {
  datetimeFormat: "%Y-%m-%d %H:%M:%S",
});

// Convert 'amount' to float, replacing unconvertible values with NULL
await table.convert({ amount: "float" }, { try: true });

removeTable

Removes the table from the database. After this operation, invoking methods on this SimpleTable instance will result in an error.

Signature
async removeTable(): Promise<void>;
Returns

A promise that resolves when the table has been removed.

Examples
// Remove the current table from the database
await table.removeTable();

removeColumns

Removes one or more columns from this table.

Signature
async removeColumns(columns: string | string[]): Promise<void>;
Parameters
  • columns: The name or an array of names of the columns to be removed.
Returns

A promise that resolves when the columns have been removed.

Examples
// Remove 'column1' and 'column2' from the table
await table.removeColumns(["column1", "column2"]);

// Remove a single column named 'tempColumn'
await table.removeColumns("tempColumn");

addColumn

Adds a new column to the table based on a specified data type (JavaScript or SQL types) and a SQL definition.

Signature
async addColumn(newColumn: string, type: "integer" | "float" | "number" | "string" | "date" | "time" | "datetime" | "datetimeTz" | "bigint" | "double" | "varchar" | "timestamp" | "timestamp with time zone" | "boolean" | geometry('${string}') | GEOMETRY('${string}'), definition: string): Promise<void>;
Parameters
  • newColumn: The name of the new column to be added.
  • type: The data type for the new column. Can be a JavaScript type (e.g., "number", "string") or a SQL type (e.g., "integer", "varchar").
  • definition: A SQL expression defining how the values for the new column should be computed (e.g., "column1 + column2", "ST_Centroid(geom_column)").
  • options: An optional object with configuration options:
Returns

A promise that resolves when the new column has been added.

Examples
// Add a new column 'total' as a float, calculated from 'column1' and 'column2'
await table.addColumn("total", "float", "column1 + column2");

// Add a new geometry column 'centroid' using the centroid of an existing 'country' geometry column
await table.addColumn(
  "centroid",
  "geometry('EPSG:4326')",
  `ST_Centroid("country")`,
);

addRowNumber

Adds a new column to the table containing the row number, starting at 0 (like an index).

Signature
async addRowNumber(newColumn: string, options?: { categories?: string | string[] }): Promise<void>;
Parameters
  • newColumn: The name of the new column that will store the row number.
  • options: An optional object with configuration options:
  • options.categories: A string or an array of strings representing columns to partition the data by. The row number will restart at 0 for each unique combination of values in these columns.
Returns

A promise that resolves when the row number column has been added.

Examples
// Add a new column named 'rowNumber' with the row number for each row
await table.addRowNumber("rowNumber");

// Add a new column named 'rowNumber' with the row number for each 'category'
await table.addRowNumber("rowNumber", { categories: "category" });

crossJoin

Performs a cross join operation with another table. A cross join returns the Cartesian product of the rows from both tables, meaning all possible pairs of rows will be in the resulting table. This means that if the left table has n rows and the right table has m rows, the result will have n * m rows.

Signature
async crossJoin(rightTable: SimpleTable, options?: { outputTable?: string | boolean }): Promise<this>;
Parameters
  • rightTable: The SimpleTable instance to cross join with.
  • options: An optional object with configuration options:
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
Returns

A promise that resolves to a table instance containing the cross-joined data (either the modified current table or a new table).

Examples
// Perform a cross join with 'tableB', overwriting the current table (tableA)
await tableA.crossJoin(tableB);

// Perform a cross join with 'tableB' and store the results in a new table with a generated name
const tableC = await tableA.crossJoin(tableB, { outputTable: true });

// Perform a cross join with 'tableB' and store the results in a new table named 'tableC'
const tableC = await tableA.crossJoin(tableB, { outputTable: "tableC" });

join

Merges the data of this table (considered the left table) with another table (the right table) based on a common column or multiple columns. Note that the order of rows in the returned data is not guaranteed to be the same as in the original tables. This operation might create temporary files in a .tmp folder; consider adding .tmp to your .gitignore.

Signature
async join(rightTable: SimpleTable, options?: { commonColumn?: string | string[]; type?: "inner" | "left" | "right" | "full"; outputTable?: string | boolean }): Promise<this>;
Parameters
  • rightTable: The SimpleTable instance to be joined with this table.
  • options: An optional object with configuration options:
  • options.commonColumn: The common column(s) used for the join operation. If omitted, the method automatically searches for a column name that exists in both tables. Can be a single string or an array of strings for multiple join keys.
  • options.type: The type of join operation to perform. Possible values are "inner", "left" (default), "right", or "full".
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
Returns

A promise that resolves to a table instance containing the joined data (either the modified current table or a new table).

Examples
// Perform a left join with 'tableB' on a common column (auto-detected), overwriting tableA
await tableA.join(tableB);

// Perform an inner join with 'tableB' on the 'id' column, storing results in a new table named 'tableC'
const tableC = await tableA.join(tableB, {
  commonColumn: "id",
  type: "inner",
  outputTable: "tableC",
});

// Perform a join on multiple columns ('name' and 'category')
await tableA.join(tableB, { commonColumn: ["name", "category"] });

fuzzyJoin

Performs a fuzzy left join between this table (considered the left table) and another table (the right table) based on string similarity between two text columns. Uses the rapidfuzz DuckDB community extension.

If a similarity score column is added to the results, the rows will be ordered alphabetically by the left column, and then by descending similarity score within each group of identical left column values. Otherwise, the rows will be order alphabetically by the left column and then by the right column.

This operation might create temporary files in a .tmp folder; consider adding .tmp to your .gitignore.

Signature
async fuzzyJoin(rightTable: SimpleTable, leftColumn: string, rightColumn: string, threshold: number, options?: { method?: "ratio" | "partial_ratio" | "token_sort_ratio" | "token_set_ratio"; similarityColumn?: string; outputTable?: string | boolean; preFilterPrefixLen?: number }): Promise<this>;
Parameters
  • rightTable: The SimpleTable instance to be joined with this table.
  • leftColumn: The name of the column in this (left) table containing the text to compare.
  • rightColumn: The name of the column in the right table containing the text to compare.
  • threshold: The minimum similarity score (0–100) required for two rows to be joined. For method: "ratio", a length-based pre-filter is automatically applied based on the threshold to improve performance without losing accuracy.
  • options: An optional object with configuration options:
  • options.method: The rapidfuzz similarity algorithm to use. Defaults to "ratio". - "ratio": Overall similarity (Levenshtein-based). - "partial_ratio": Best partial/substring similarity. - "token_sort_ratio": Similarity after sorting tokens (words), useful for reordered words. - "token_set_ratio": Similarity based on sets of tokens, ignoring duplicates and word order.
  • options.similarityColumn: If provided, a column with this name is added to the result containing the similarity score (0–100). If omitted, the score is not included in the output.
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
  • options.preFilterPrefixLen: An optional prefix length. Only strings sharing the same first N characters are compared. Note that prefix filtering is lossy (e.g. "John" vs. "Phon" will not match despite high similarity).
Returns

A promise that resolves to a table instance containing the fuzzy-joined data (either the modified current table or a new table).

Examples
// Fuzzy left join tableA with tableB on 'name' (left) and 'standardName' (right) with a threshold of 80
// A length-based pre-filter is automatically applied.
await tableA.fuzzyJoin(tableB, "name", "standardName", 80);

// Fuzzy join with a prefix-based pre-filter and a threshold of 80
await tableA.fuzzyJoin(tableB, "name", "standardName", 80, {
  preFilterPrefixLen: 3, // Must share the same first 3 characters
});

// Fuzzy join with a custom threshold and method, storing results in a new table
const tableC = await tableA.fuzzyJoin(tableB, "name", "standardName", 90, {
  method: "token_sort_ratio",
  outputTable: "tableC",
});

// Fuzzy join with a custom similarity column name and a threshold of 80
await tableA.fuzzyJoin(tableB, "name", "standardName", 80, {
  similarityColumn: "matchScore",
});

fuzzyClean

Normalizes string values in a column by detecting fuzzy duplicates and replacing them with a single canonical value.

Similar strings are grouped into clusters. Matching is transitive: if "New York" is similar to "New Yorke" and "New Yorke" is similar to "New Yorkk", all three land in the same cluster even if "New York" and "New Yorkk" would not match directly. Each cluster is then collapsed to one representative value based on the keep strategy.

Similarity is computed using the rapidfuzz DuckDB community extension, which is installed and loaded automatically.

Signature
async fuzzyClean(column: string, newColumn: string, threshold: number, options?: { method?: "ratio" | "partial_ratio" | "token_sort_ratio" | "token_set_ratio"; keep?: "mostCommon" | "longestString" | "shortestString" | "mostCentral" | "maxScore"; preFilterPrefixLen?: number }): Promise<void>;
Parameters
  • column: The name of the column containing the strings to normalize.
  • newColumn: The name of the column to write the normalized values to. Use the same name as column to normalize in-place.
  • threshold: The minimum similarity score (0–100) for two strings to be considered duplicates. For method: "ratio", a length-based pre-filter is automatically applied based on the threshold to improve performance without losing accuracy.
  • options: An optional object with configuration options:
  • options.method: The rapidfuzz similarity algorithm to use. Defaults to "ratio". - "ratio": Overall similarity. - "partial_ratio": Best partial/substring similarity. - "token_sort_ratio": Similarity after sorting tokens (words), useful for reordered words. - "token_set_ratio": Similarity based on sets of tokens, ignoring duplicates and word order.
  • options.keep: The strategy for choosing the canonical value within each cluster of similar strings. Defaults to "mostCommon". - "mostCommon": Keep the value that appears most frequently in the original column. - "longestString": Keep the longest string in the cluster. - "shortestString": Keep the shortest string in the cluster. - "mostCentral": Keep the string with the highest total similarity score to all other cluster members (the most "central" string). - "maxScore": Keep the string that participates in the single highest-scoring pairwise match within the cluster.
  • options.preFilterPrefixLen: An optional prefix length. Only strings sharing the same first N characters are compared. Note that prefix filtering is lossy (e.g. "John" vs. "Phon" will not match despite high similarity).
Returns

A promise that resolves when the column has been normalized.

Examples
// Normalize 'city' into a new 'cityClean' column, keeping the most common string per cluster with a threshold of 80
// A length-based pre-filter is automatically applied.
await table.fuzzyClean("city", "cityClean", 80);

// Normalize with a prefix-based pre-filter and a threshold of 80
await table.fuzzyClean("city", "cityClean", 80, {
  preFilterPrefixLen: 5, // Must share the same first 5 characters
});

// Normalize 'companyName' into a new column using token_sort_ratio and a threshold of 90
await table.fuzzyClean("companyName", "companyNameClean", 90, {
  method: "token_sort_ratio",
});

// Normalize 'category' in-place, keeping the longest string in each cluster and a threshold of 80
await table.fuzzyClean("category", "category", 80, { keep: "longestString" });

replace

Replaces specified strings in the selected columns.

Signature
async replace(columns: "all" | string | string[], strings: Record<string, string>, options?: { entireString?: boolean; regex?: boolean }): Promise<void>;
Parameters
  • columns: The column name, an array of column names, or "all" to apply the replacement to every column in the table.
  • strings: An object mapping old strings to new strings (e.g., { "oldValue": "newValue" }).
  • options: An optional object with configuration options:
  • options.entireString: A boolean indicating whether the entire cell content must match the oldString for replacement to occur. Defaults to false (replaces substrings).
  • options.regex: A boolean indicating whether the oldString should be treated as a regular expression for global replacement. Cannot be used with entireString: true. Defaults to false.
Returns

A promise that resolves when the string replacements are complete.

Examples
// Replace all occurrences of "kilograms" with "kg" in 'column1'
await table.replace("column1", { "kilograms": "kg" });

// Replace "kilograms" with "kg" and "liters" with "l" in 'column1' and 'column2'
await table.replace(["column1", "column2"], {
  "kilograms": "kg",
  "liters": "l",
});

// Replace only if the entire string in 'column1' is "kilograms"
await table.replace("column1", { "kilograms": "kg" }, { entireString: true });

// Replace any sequence of one or more digits with a hyphen in 'column1' using regex
await table.replace("column1", { "\d+": "-" }, { regex: true });

// Replace "%" with "" in all columns
await table.replace("all", { "%": "" });

lower

Converts string values in the specified columns to lowercase.

Signature
async lower(columns: string | string[]): Promise<void>;
Parameters
  • columns: The column name or an array of column names to be converted to lowercase.
Returns

A promise that resolves when the strings have been converted to lowercase.

Examples
// Convert strings in 'column1' to lowercase
await table.lower("column1");

// Convert strings in 'column1' and 'column2' to lowercase
await table.lower(["column1", "column2"]);

upper

Converts string values in the specified columns to uppercase.

Signature
async upper(columns: string | string[]): Promise<void>;
Parameters
  • columns: The column name or an array of column names to be converted to uppercase.
Returns

A promise that resolves when the strings have been converted to uppercase.

Examples
// Convert strings in 'column1' to uppercase
await table.upper("column1");

// Convert strings in 'column1' and 'column2' to uppercase
await table.upper(["column1", "column2"]);

capitalize

Capitalizes the first letter of each string in the specified columns and converts the rest of the string to lowercase.

Signature
async capitalize(columns: string | string[]): Promise<void>;
Parameters
  • columns: The column name or an array of column names to be capitalized.
Returns

A promise that resolves when the strings have been capitalized.

Examples
// Capitalize strings in 'column1' (e.g., "hello world" becomes "Hello world")
await table.capitalize("column1");

// Capitalize strings in 'column1' and 'column2'
await table.capitalize(["column1", "column2"]);

truncate

Truncates string values in a specified column to a maximum number of characters.

Signature
async truncate(column: string, length: number): Promise<void>;
Parameters
  • column: The column name containing strings to be truncated.
  • length: The maximum number of characters to keep.
Returns

A promise that resolves when the strings have been truncated.

Examples
// Truncate strings in 'description' column to 50 characters
await table.truncate("description", 50);

// Truncate strings in 'name' column to 10 characters
await table.truncate("name", 10);

pad

Pads the strings in the specified columns to a target length.

The columns must contain string (VARCHAR) values. An error is thrown if any column is of a different type. null values remain null. If any string already exceeds the target length, an error is thrown (no silent truncation).

Signature
async pad(columns: string | string[], length: number, options?: { method?: "left" | "right"; char?: string }): Promise<void>;
Parameters
  • columns: The column name(s) containing strings to be padded.
  • length: The target length of the padded strings.
  • options: An optional object with configuration options:
  • options.method: Which side to pad. 'left' (default) or 'right'.
  • options.char: The character to use for padding. Defaults to '0'.
Returns

A promise that resolves when the padding operation is complete.

Throws
  • Error: If any column is not of string (VARCHAR) type.
  • Error: If any string value exceeds the target length.
Examples
// Left-pad 'id' column to 3 characters with zeros (default)
await table.pad("id", 3);
// Result: '1' -> '001', '23' -> '023', null -> null

// Right-pad 'code' column to 5 characters with spaces
await table.pad("code", 5, { method: "right", char: " " });
// Result: '123' -> '123  ', '45' -> '45   ', null -> null

// Left-pad multiple columns to 5 characters with dashes
await table.pad(["id", "code"], 5, { method: "left", char: "-" });
// Result: '1' -> '----1', '23' -> '---23'

splitExtract

Splits strings in a specified column by a separator and extracts a substring at a given index, storing the result in a new or existing column. If the index is out of bounds, an empty string will be returned for that row.

Signature
async splitExtract(column: string, separator: string, index: number, newColumn: string): Promise<void>;
Parameters
  • column: The name of the column containing the strings to be split.
  • separator: The substring to use as a delimiter for splitting the strings.
  • index: The zero-based index of the substring to extract after splitting. For example, 0 for the first part, 1 for the second, etc.
  • newColumn: The name of the column where the extracted substrings will be stored. To overwrite the original column, use the same name as column.
Returns

A promise that resolves when the strings have been split and extracted.

Examples
// Split 'address' by comma and extract the second part (index 1) into a new 'city' column
// e.g., "123 Main St, Anytown, USA" -> "Anytown"
await table.splitExtract("address", ",", 1, "city");

// Split 'fileName' by dot and extract the first part (index 0), overwriting 'fileName'
// e.g., "document.pdf" -> "document"
await table.splitExtract("fileName", ".", 0, "fileName");

splitSpread

Splits strings in a specified column by a separator and spreads the resulting parts into multiple new columns.

Each part of the split string will be stored in a separate column. The number of columns created is determined by the length of the newColumns array. If a row has fewer parts than the number of new columns, a warning will be logged and the extra columns will contain empty strings (unless noCheck is set to true). If a row has more parts than the number of new columns, an error will be thrown unless noCheck is set to true.

Signature
async splitSpread(column: string, separator: string, newColumns: string[], options?: { noCheck?: boolean }): Promise<void>;
Parameters
  • column: The name of the column containing the strings to be split.
  • separator: The substring to use as a delimiter for splitting the strings.
  • newColumns: An array of column names for the extracted parts.
  • options: Optional configuration.
  • options.noCheck: If true, skips all validation checks (both max and min parts). Default is false.
Returns

A promise that resolves when the strings have been split and spread into new columns.

Examples
// Split 'fullName' by comma and spread into 'lastName' and 'firstName'
// e.g., "Shiab, Nael" -> lastName: "Shiab", firstName: "Nael"
await table.splitSpread("fullName", ",", ["lastName", "firstName"]);

// Split 'address' by comma and spread into three columns
// e.g., "123 Main St, Anytown, USA" -> street: "123 Main St", city: "Anytown", country: "USA"
await table.splitSpread("address", ",", ["street", "city", "country"]);

// Skip validation for performance with noCheck option
await table.splitSpread("data", "|", ["col1", "col2"], { noCheck: true });

left

Extracts a specific number of characters from the beginning (left side) of string values in the specified column.

Signature
async left(column: string, numberOfCharacters: number): Promise<void>;
Parameters
  • column: The name of the column containing the strings to be modified.
  • numberOfCharacters: The number of characters to extract from the left side of each string.
Returns

A promise that resolves when the strings have been updated.

Examples
// Replace strings in 'productCode' with their first two characters
// e.g., "ABC-123" becomes "AB"
await table.left("productCode", 2);

Extracts a specific number of characters from the end (right side) of string values in the specified column.

Signature
async right(column: string, numberOfCharacters: number): Promise<void>;
Parameters
  • column: The name of the column containing the strings to be modified.
  • numberOfCharacters: The number of characters to extract from the right side of each string.
Returns

A promise that resolves when the strings have been updated.

Examples
// Replace strings in 'productCode' with their last two characters
// e.g., "ABC-123" becomes "23"
await table.right("productCode", 2);

replaceNulls

Replaces NULL values in the specified columns with a given value.

Signature
async replaceNulls(columns: "all" | string | string[], value: number | string | Date | boolean): Promise<void>;
Parameters
  • columns: The column name, an array of column names, or "all" to apply the replacement to every column in the table.
  • value: The value to replace NULL occurrences with.
Returns

A promise that resolves when the NULL values have been replaced.

Examples
// Replace NULL values in 'column1' with 0
await table.replaceNulls("column1", 0);

// Replace NULL values in 'columnA' and 'columnB' with the string "N/A"
await table.replaceNulls(["columnA", "columnB"], "N/A");

// Replace NULL values in 'dateColumn' with a specific date
await table.replaceNulls("dateColumn", new Date("2023-01-01"));

// Replace NULL values in all columns with 0
await table.replaceNulls("all", 0);

concatenate

Concatenates values from specified columns into a new column.

Signature
async concatenate(columns: string[], newColumn: string, options?: { separator?: string }): Promise<void>;
Parameters
  • columns: An array of column names whose values will be concatenated.
  • newColumn: The name of the new column to store the concatenated values.
  • options: An optional object with configuration options:
  • options.separator: The string used to separate concatenated values. Defaults to an empty string ("").
Returns

A promise that resolves when the concatenation is complete.

Examples
// Concatenate 'firstName' and 'lastName' into a new 'fullName' column
await table.concatenate(["firstName", "lastName"], "fullName");

// Concatenate 'city' and 'country' into 'location', separated by a comma and space
await table.concatenate(["city", "country"], "location", { separator: ", " });

concatenateRow

Concatenates values from multiple columns into a new column with labeled rows.

This method creates a new column where each value is a concatenation of the specified columns, with each column value prefixed by its column name and a colon, followed by a newline. Column entries are separated by double newlines ("\n\n").

All values must be string, otherwise an error will be thrown. Use the convert() method first to convert non-string columns to string.

If a column value is NULL, it will be replaced by 'Unknown' in the concatenated result.

Signature
async concatenateRow(columns: string[], newColumn: string): Promise<void>;
Parameters
  • columns: An array of column names whose values will be concatenated with labels.
  • newColumn: The name of the new column to create with the concatenated values.
Returns

A promise that resolves when the concatenation is complete.

Examples
// Concatenate multiple string columns into a labeled text field
await table.concatenateRow(
  ["summary", "findings", "context", "date", "quote"],
  "fullText",
);
// Result in "fullText" will look like:
// summary:
// [value]
//
// findings:
// [value]
//
// context:
// [value]
//
// date:
// [value]
//
// quote:
// [value]

// Convert numeric columns to strings first, then concatenate
// NULL values will appear as 'Unknown'
await table.convert({ age: "string", salary: "string" });
await table.concatenateRow(["name", "age", "salary"], "profile");

unnest

Unnests (expands) rows by splitting a column's string values into multiple rows based on a separator.

Each value in the specified column is split using the provided separator, and a new row is created for each resulting substring. All other column values are duplicated across the newly created rows.

Signature
async unnest(column: string, separator: string): Promise<void>;
Parameters
  • column: The name of the column containing string values to be split and unnested.
  • separator: The delimiter string used to split the column values.
Returns

A promise that resolves when the unnesting is complete.

Examples
// Unnest 'tags' column separated by commas
// Before: [{ id: 1, tags: "red,blue,green" }]
// After:  [{ id: 1, tags: "red" }, { id: 1, tags: "blue" }, { id: 1, tags: "green" }]
await table.unnest("tags", ",");

// Unnest 'neighborhoods' column separated by " / "
// Before: [{ city: "Montreal", neighborhoods: "Old Montreal / Chinatown / Griffintown" }]
// After:  [{ city: "Montreal", neighborhoods: "Old Montreal" },
//         { city: "Montreal", neighborhoods: "Chinatown" },
//         { city: "Montreal", neighborhoods: "Griffintown" }]
await table.unnest("neighborhoods", " / ");

repeatRows

Repeats rows based on the values in a column.

If a row has a value of 3 in the specified column, it will be repeated 3 times. If the value is 0 or negative, the row will be removed.

Signature
async repeatRows(column: string, options?: { index?: string }): Promise<void>;
Parameters
  • column: The name of the column containing the number of times each row should be repeated.
  • options: An optional object with configuration options:
  • options.index: The name of a new column to store the index of the repeated row (starting at 0).
Examples
// Before: [{ id: 1, count: 2, category: "A" }, { id: 2, count: 3, category: "B" }]
await table.repeatRows("count");
// After:  [{ id: 1, count: 2, category: "A" }, { id: 1, count: 2, category: "A" },
//          { id: 2, count: 3, category: "B" }, { id: 2, count: 3, category: "B" }, { id: 2, count: 3, category: "B" }]

// With an index column
await table.repeatRows("count", { index: "copyId" });
// After:  [{ id: 1, count: 2, category: "A", copyId: 0 }, { id: 1, count: 2, category: "A", copyId: 1 },
//          { id: 2, count: 3, category: "B", copyId: 0 }, { id: 2, count: 3, category: "B", copyId: 1 }, { id: 2, count: 3, category: "B", copyId: 2 }]

nest

Nests (collapses) rows by aggregating a column's values into a single string per group, separated by a delimiter.

This is the inverse operation of unnest(). Multiple rows are combined into fewer rows by grouping on specified category columns and concatenating the target column values with a separator.

Signature
async nest(column: string, separator: string, categories: string | string[]): Promise<void>;
Parameters
  • column: The name of the column whose values will be aggregated and concatenated.
  • separator: The delimiter string used to join the column values.
  • categories: The column name or an array of column names to group by.
Returns

A promise that resolves when the nesting is complete.

Examples
// Nest 'neighborhoods' column separated by " / " for each city
// Before: [{ city: "Montreal", neighborhoods: "Old Montreal" },
//         { city: "Montreal", neighborhoods: "Chinatown" },
//         { city: "Montreal", neighborhoods: "Griffintown" }]
// After:  [{ city: "Montreal", neighborhoods: "Old Montreal / Chinatown / Griffintown" }]
await table.nest("neighborhoods", " / ", "city");

// Nest with multiple category columns
// Before: [{ country: "Canada", city: "Montreal", tags: "red" },
//         { country: "Canada", city: "Montreal", tags: "blue" }]
// After:  [{ country: "Canada", city: "Montreal", tags: "red,blue" }]
await table.nest("tags", ",", ["country", "city"]);

round

Rounds numeric values in specified columns.

Signature
async round(columns: string | string[], options?: number | { decimals?: number; method?: "round" | "ceiling" | "floor" }): Promise<void>;
Parameters
  • columns: The column name or an array of column names containing numeric values to be rounded.
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round to. Defaults to 0 (rounds to the nearest integer).
  • options.method: The rounding method to use: "round" (rounds to the nearest integer, with halves rounding up), "ceiling" (rounds up to the nearest integer), or "floor" (rounds down to the nearest integer). Defaults to "round".
Returns

A promise that resolves when the numeric values have been rounded.

Examples
// Round 'column1' values to the nearest integer
await table.round("column1");

// Round 'column1' values to 2 decimal places
await table.round("column1", { decimals: 2 });

// Round 'column1' values down to the nearest integer (floor)
await table.round("column1", { method: "floor" });

// Round 'columnA' and 'columnB' values to 1 decimal place using ceiling method
await table.round(["columnA", "columnB"], { decimals: 1, method: "ceiling" });

// Round 'column1' values to 2 decimal places using the shorthand
await table.round("column1", 2);

updateColumn

Updates values in a specified column using a SQL expression.

Signature
async updateColumn(column: string, definition: string): Promise<void>;
Parameters
  • column: The name of the column to be updated.
  • definition: The SQL expression used to set the new values in the column (e.g., "column1 * 2", "UPPER(column_name)").
Returns

A promise that resolves when the column has been updated.

Examples
// Update 'column1' with the left 5 characters of 'column2'
await table.updateColumn("column1", `LEFT(column2, 5)`);

// Double the values in 'price' column
await table.updateColumn("price", `price * 2`);

// Set 'status' to 'active' where 'isActive' is true
await table.updateColumn(
  "status",
  `CASE WHEN isActive THEN 'active' ELSE 'inactive' END`,
);

ranks

Assigns ranks to rows in a new column based on the values of a specified column.

Signature
async ranks(values: string, newColumn: string, options?: { order?: "asc" | "desc"; categories?: string | string[]; noGaps?: boolean }): Promise<void>;
Parameters
  • values: The column containing the values to be used for ranking.
  • newColumn: The name of the new column where the ranks will be stored.
  • options: An optional object with configuration options:
  • options.order: The order of values for ranking: "asc" for ascending (default) or "desc" for descending.
  • options.categories: The column name or an array of column names that define categories for ranking. Ranks will be assigned independently within each category.
  • options.noGaps: A boolean indicating whether to assign ranks without gaps (dense ranking). If true, ranks will be consecutive integers (e.g., 1, 2, 2, 3). If false (default), ranks might have gaps (e.g., 1, 2, 2, 4).
Returns

A promise that resolves when the ranks have been assigned.

Examples
// Compute ranks in a new 'rank' column based on 'score' values (ascending)
await table.ranks("score", "rank");

// Compute ranks in a new 'descRank' column based on 'score' values (descending)
await table.ranks("score", "descRank", { order: "desc" });

// Compute ranks within 'department' categories, based on 'salary' values, without gaps
await table.ranks("salary", "salaryRank", {
  categories: "department",
  noGaps: true,
});

// Compute ranks within multiple categories ('department' and 'city')
await table.ranks("sales", "salesRank", { categories: ["department", "city"] });

quantiles

Assigns quantiles to rows in a new column based on specified column values.

Signature
async quantiles(values: string, nbQuantiles: number, newColumn: string, options?: { categories?: string | string[] }): Promise<void>;
Parameters
  • values: The column containing values from which quantiles will be assigned.
  • nbQuantiles: The number of quantiles to divide the data into (e.g., 4 for quartiles, 10 for deciles).
  • newColumn: The name of the new column where the assigned quantiles will be stored.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories for computing quantiles. Quantiles will be assigned independently within each category.
Returns

A promise that resolves when the quantiles have been assigned.

Examples
// Assigns a quantile from 1 to 10 for each row in a new 'quantiles' column, based on 'column1' values.
await table.quantiles("column1", 10, "quantiles");

// Assigns quantiles within 'column2' categories, based on 'column1' values.
await table.quantiles("column1", 10, "quantiles", { categories: "column2" });

// Assigns quartiles (4 quantiles) to 'sales' data, storing results in 'salesQuartile'
await table.quantiles("sales", 4, "salesQuartile");

bins

Assigns bins for specified column values based on an interval size.

Signature
async bins(values: string, interval: number, newColumn: string, options?: { startValue?: number }): Promise<void>;
Parameters
  • values: The column containing values from which bins will be computed.
  • interval: The interval size for binning the values.
  • newColumn: The name of the new column where the bins will be stored.
  • options: An optional object with configuration options:
  • options.startValue: The starting value for binning. Defaults to the minimum value in the specified column.
Returns

A promise that resolves when the bins have been assigned.

Examples
// Assigns a bin for each row in a new 'bins' column based on 'column1' values, with an interval of 10.
// If the minimum value in 'column1' is 5, the bins will follow this pattern: "[5-14]", "[15-24]", etc.
await table.bins("column1", 10, "bins");

// Assigns bins starting at a specific value (0) with an interval of 10.
// The bins will follow this pattern: "[0-9]", "[10-19]", "[20-29]", etc.
await table.bins("column1", 10, "bins", { startValue: 0 });

proportionsHorizontal

Computes proportions horizontally across specified columns for each row.

For example, given a table showing counts of men, women, and non-binary individuals per year:

YearMenWomenNonBinary
2021564685145
202235427856
2023856321221

This method computes the proportion of men, women, and non-binary individuals on each row, adding new columns for these proportions.

Signature
async proportionsHorizontal(columns: string[], options?: { suffix?: string; decimals?: number }): Promise<void>;
Parameters
  • columns: An array of column names for which proportions will be computed on each row.
  • options: An optional object with configuration options:
  • options.suffix: A string suffix to append to the names of the new columns storing the computed proportions. Defaults to "Perc".
  • options.decimals: The number of decimal places to round the computed proportions. Defaults to undefined (no rounding).
Returns

A promise that resolves when the horizontal proportions have been computed.

Examples
// Compute horizontal proportions for 'Men', 'Women', and 'NonBinary' columns, rounded to 2 decimal places
await table.proportionsHorizontal(["Men", "Women", "NonBinary"], {
  decimals: 2,
});

The table will then look like this:

YearMenWomenNonBinaryMenPercWomenPercNonBinaryPerc
20215646851450.40.490.10
2022354278560.510.40.08
20238563212210.610.230.16

By default, the new columns will be named with a suffix of "Perc". You can customize this suffix using the suffix option.

// Compute horizontal proportions with a custom suffix "Prop"
await table.proportionsHorizontal(["Men", "Women", "NonBinary"], {
  suffix: "Prop",
  decimals: 2,
});

The table will then look like this:

YearMenWomenNonBinaryMenPropWomenPropNonBinaryProp
20215646851450.40.490.10
2022354278560.510.40.08
20238563212210.610.230.16

proportionsVertical

Computes proportions vertically over a column's values, relative to the sum of all values in that column (or within specified categories).

Signature
async proportionsVertical(column: string, newColumn: string, options?: { categories?: string | string[]; decimals?: number }): Promise<void>;
Parameters
  • column: The column containing values for which proportions will be computed. The proportions are calculated based on the sum of values in the specified column.
  • newColumn: The name of the new column where the proportions will be stored.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories for computing proportions. Proportions will be calculated independently within each category.
  • options.decimals: The number of decimal places to round the computed proportions. Defaults to undefined (no rounding).
Returns

A promise that resolves when the vertical proportions have been computed.

Examples
// Add a new column 'perc' with each 'column1' value divided by the sum of all 'column1' values
await table.proportionsVertical("column1", "perc");

// Compute proportions for 'column1' within 'column2' categories, rounded to two decimal places
await table.proportionsVertical("column1", "perc", {
  categories: "column2",
  decimals: 2,
});

// Compute proportions for 'sales' within 'region' and 'product_type' categories
await table.proportionsVertical("sales", "sales_proportion", {
  categories: ["region", "product_type"],
});

summarize

Creates a summary table based on specified values, categories, and summary operations. This method allows you to aggregate data, calculate statistics (e.g., count, mean, sum), and group results by categorical columns.

Signature
async summarize(options?: { values?: string | string[]; categories?: string | string[]; summaries?: ("count" | "countUnique" | "countNull" | "min" | "max" | "mean" | "median" | "sum" | "skew" | "stdDev" | "var") | ("count" | "countUnique" | "countNull" | "min" | "max" | "mean" | "median" | "sum" | "skew" | "stdDev" | "var")[] | Record<string, "count" | "countUnique" | "countNull" | "min" | "max" | "mean" | "median" | "sum" | "skew" | "stdDev" | "var">; decimals?: number; outputTable?: string | boolean; toMs?: boolean; noColumnValue?: boolean }): Promise<this>;
Parameters
  • options: An object with configuration options for summarization:
  • options.values: The column name or an array of column names whose values will be summarized. If omitted, all columns will be summarized.
  • options.categories: The column name or an array of column names that define categories for the summarization. Results will be grouped by these categories.
  • options.summaries: The summary operations to be performed. Can be a single operation (e.g., "mean"), an array of operations (e.g., ["min", "max"]), or an object mapping new column names to operations (e.g., { avgSalary: "mean" }). Supported operations include: "count", "countUnique", "countNull", "min", "max", "mean", "median", "sum", "skew", "stdDev", "var".
  • options.decimals: The number of decimal places to round the summarized values. Defaults to undefined (no rounding).
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
  • options.toMs: If true, timestamps, dates, and times will be converted to milliseconds before summarizing. This is useful when summarizing mixed data types (numbers and dates) as values must be of the same type for aggregation.
  • options.noColumnValue: If true, the default value column will be removed. This option only works when summarizing a single column without categories. Defaults to false.
Returns

A promise that resolves to a table instance containing the summarized data (either the modified current table or a new table).

Examples
// Summarize all columns with all available summary operations, overwriting the current table
const columns = await table.getColumns();
await table.summarize({ values: columns });

// Summarize all columns and store the results in a new table with a generated name
const columns = await table.getColumns();
const summaryTable = await table.summarize({
  values: columns,
  outputTable: true,
});

// Summarize all columns and store the results in a new table named 'mySummary'
const columns = await table.getColumns();
const mySummaryTable = await table.summarize({
  values: columns,
  outputTable: "mySummary",
});

// Summarize a single column ('sales') with all available summary operations
await table.summarize({ values: "sales" });

// Summarize multiple columns ('sales' and 'profit') with all available summary operations
await table.summarize({ values: ["sales", "profit"] });

// Summarize 'sales' by 'region' (single category)
await table.summarize({ values: "sales", categories: "region" });

// Summarize 'sales' by 'region' and 'product_type' (multiple categories)
await table.summarize({
  values: "sales",
  categories: ["region", "product_type"],
});

// Summarize 'sales' by 'region' with a specific summary operation (mean)
await table.summarize({
  values: "sales",
  categories: "region",
  summaries: "mean",
});

// Summarize 'sales' by 'region' with specific summary operations (mean and sum)
await table.summarize({
  values: "sales",
  categories: "region",
  summaries: ["mean", "sum"],
});

// Summarize 'sales' by 'region' with custom named summary operations
await table.summarize({
  values: "sales",
  categories: "region",
  summaries: { averageSales: "mean", totalSales: "sum" },
});

// Summarize 'price' and 'cost', rounding aggregated values to 2 decimal places
await table.summarize({ values: ["price", "cost"], decimals: 2 });

// Summarize 'timestamp_column' by converting to milliseconds first
await table.summarize({
  values: "timestamp_column",
  toMs: true,
  summaries: "mean",
});

// Summarize a single column 'value_column' without the default 'value' column in the output
await table.summarize({ values: "value_column", noColumnValue: true });

accumulate

Computes the cumulative sum of values in a column. For this method to work properly, ensure your data is sorted first.

Signature
async accumulate(column: string, newColumn: string, options?: { categories?: string | string[] }): Promise<void>;
Parameters
  • column: The name of the column storing the values to be accumulated.
  • newColumn: The name of the new column in which the computed cumulative values will be stored.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories for the accumulation. Accumulation will be performed independently within each category.
Returns

A promise that resolves when the cumulative sum has been computed.

Examples
// Compute the cumulative sum of 'sales' in a new 'cumulativeSales' column
// Ensure the table is sorted by a relevant column (e.g., date) before calling this method.
await table.accumulate("sales", "cumulativeSales");

// Compute the cumulative sum of 'orders' within 'customer_id' categories
// Ensure the table is sorted by 'customer_id' and then by a relevant order column (e.g., order_date).
await table.accumulate("orders", "cumulativeOrders", {
  categories: "customer_id",
});

// Compute the cumulative sum of 'revenue' within 'region' and 'product_category' categories
await table.accumulate("revenue", "cumulativeRevenue", {
  categories: ["region", "product_category"],
});

rolling

Computes rolling aggregations (e.g., rolling average, min, max) over a specified column. For rows without enough preceding or following rows to form a complete window, NULL will be returned. For this method to work properly, ensure your data is sorted by the relevant column(s) first.

Signature
async rolling(column: string, newColumn: string, summary: "min" | "max" | "mean" | "median" | "sum", preceding: number, following: number, options?: { categories?: string | string[]; decimals?: number }): Promise<void>;
Parameters
  • column: The name of the column storing the values to be aggregated.
  • newColumn: The name of the new column in which the computed rolling values will be stored.
  • summary: The aggregation function to apply: "min", "max", "mean", "median", or "sum".
  • preceding: The number of preceding rows to include in the rolling window.
  • following: The number of following rows to include in the rolling window.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories for the aggregation. Rolling aggregations will be computed independently within each category.
  • options.decimals: The number of decimal places to round the aggregated values. Defaults to undefined (no rounding).
Returns

A promise that resolves when the rolling aggregation is complete.

Examples
// Compute a 7-day rolling average of 'sales' with 3 preceding and 3 following rows
// (total window size of 7: 3 preceding + current + 3 following)
await table.rolling("sales", "rollingAvgSales", "mean", 3, 3);

// Compute a rolling sum of 'transactions' within 'customer_id' categories
await table.rolling("transactions", "rollingSumTransactions", "sum", 5, 0, {
  categories: "customer_id",
});

// Compute a rolling maximum of 'temperature' rounded to 1 decimal place
await table.rolling("temperature", "rollingMaxTemp", "max", 2, 2, {
  decimals: 1,
});

correlations

Calculates correlations between columns. If no x and y columns are specified, the method computes the correlations for all numeric column combinations. Note that correlation is symmetrical: the correlation of x with y is the same as y with x.

Signature
async correlations(options?: { x?: string; y?: string; categories?: string | string[]; decimals?: number; outputTable?: string | boolean }): Promise<this>;
Parameters
  • options: An optional object with configuration options:
  • options.x: The name of the column for the x-values. If omitted, correlations will be computed for all numeric columns.
  • options.y: The name of the column for the y-values. If omitted, correlations will be computed for all numeric columns.
  • options.categories: The column name or an array of column names that define categories. Correlation calculations will be performed independently for each category.
  • options.decimals: The number of decimal places to round the correlation values. Defaults to undefined (no rounding).
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
Returns

A promise that resolves to a table instance containing the correlation results (either the modified current table or a new table).

Examples
// Compute correlations between all numeric columns, overwriting the current table
await table.correlations();

// Compute correlations between 'column1' and all other numeric columns
await table.correlations({ x: "column1" });

// Compute the correlation between 'column1' and 'column2'
await table.correlations({ x: "column1", y: "column2" });

// Compute correlations within 'categoryColumn' and store results in a new table
const correlationTable = await table.correlations({
  categories: "categoryColumn",
  outputTable: true,
});

// Compute correlations, rounded to 2 decimal places
await table.correlations({ decimals: 2 });

linearRegressions

Performs linear regression analysis. The results include the slope, the y-intercept, and the R-squared value. If no x and y columns are specified, the method computes linear regression analysis for all numeric column permutations. Note that linear regression analysis is asymmetrical: the linear regression of x over y is not the same as y over x.

Signature
async linearRegressions(options?: { x?: string; y?: string; categories?: string | string[]; decimals?: number; outputTable?: string | boolean }): Promise<this>;
Parameters
  • options: An optional object with configuration options:
  • options.x: The name of the column for the independent variable (x-values). If omitted, linear regressions will be computed for all numeric columns as x.
  • options.y: The name of the column for the dependent variable (y-values). If omitted, linear regressions will be computed for all numeric columns as y.
  • options.categories: The column name or an array of column names that define categories. Linear regression analysis will be performed independently for each category.
  • options.decimals: The number of decimal places to round the regression values (slope, intercept, r-squared). Defaults to undefined (no rounding).
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
Returns

A promise that resolves to a table instance containing the linear regression results (either the modified current table or a new table).

Examples
// Compute all linear regressions between all numeric columns, overwriting the current table
await table.linearRegressions();

// Compute linear regressions with 'column1' as the independent variable and all other numeric columns as dependent variables
await table.linearRegressions({ x: "column1" });

// Compute the linear regression of 'sales' (y) over 'advertising' (x)
await table.linearRegressions({ x: "advertising", y: "sales" });

// Compute linear regressions within 'region' categories and store results in a new table
const regressionTable = await table.linearRegressions({
  categories: "region",
  outputTable: true,
});

// Compute linear regressions, rounded to 3 decimal places
await table.linearRegressions({ decimals: 3 });

outliersIQR

Identifies outliers in a specified column using the Interquartile Range (IQR) method.

Signature
async outliersIQR(column: string, newColumn: string, options?: { categories?: string | string[] }): Promise<void>;
Parameters
  • column: The name of the column in which outliers will be identified.
  • newColumn: The name of the new column where the boolean results (TRUE for outlier, FALSE otherwise) will be stored.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories. Outlier detection will be performed independently within each category.
Returns

A promise that resolves when the outliers have been identified.

Examples
// Look for outliers in the 'age' column and store results in a new 'isOutlier' column
await table.outliersIQR("age", "isOutlier");

// Look for outliers in 'salary' within 'gender' categories
await table.outliersIQR("salary", "salaryOutlier", { categories: "gender" });

zScore

Computes the Z-score for values in a specified column.

Signature
async zScore(column: string, newColumn: string, options?: { categories?: string | string[]; decimals?: number }): Promise<void>;
Parameters
  • column: The name of the column for which Z-scores will be calculated.
  • newColumn: The name of the new column where the computed Z-scores will be stored.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories. Z-scores will be calculated independently within each category.
  • options.decimals: The number of decimal places to round the Z-score values. Defaults to undefined (no rounding).
Returns

A promise that resolves when the Z-scores have been computed.

Examples
// Calculate the Z-score for 'age' values and store results in a new 'ageZScore' column
await table.zScore("age", "ageZScore");

// Calculate Z-scores for 'salary' within 'department' categories
await table.zScore("salary", "salaryZScore", { categories: "department" });

// Calculate Z-scores for 'score', rounded to 2 decimal places
await table.zScore("score", "scoreZScore", { decimals: 2 });

normalize

Normalizes the values in a column using min-max normalization.

Signature
async normalize(column: string, newColumn: string, options?: { categories?: string | string[]; decimals?: number }): Promise<void>;
Parameters
  • column: The name of the column in which values will be normalized.
  • newColumn: The name of the new column where normalized values will be stored.
  • options: An optional object with configuration options:
  • options.categories: The column name or an array of column names that define categories for the normalization. Normalization will be performed independently within each category.
  • options.decimals: The number of decimal places to round the normalized values. Defaults to undefined (no rounding).
Returns

A promise that resolves when the values have been normalized.

Examples
// Normalize the values in 'column1' and store them in a new 'normalizedColumn1' column
await table.normalize("column1", "normalizedColumn1");

// Normalize 'value' within 'group' categories
await table.normalize("value", "normalizedValue", { categories: "group" });

// Normalize 'data' values, rounded to 2 decimal places
await table.normalize("data", "normalizedData", { decimals: 2 });

updateWithJS

Updates data in the table using a JavaScript function. The function receives the existing rows as an array of objects and must return the modified rows as an array of objects. This method offers high flexibility for data manipulation but can be slow for large tables as it involves transferring data between DuckDB and JavaScript. This method does not work with tables containing geometries.

Signature
async updateWithJS(dataModifier: ((rows: Record<string, number | string | Date | boolean | null>[]) => Promise<Record<string, number | string | Date | boolean | null>[]>) | ((rows: Record<string, number | string | Date | boolean | null>[]) => Record<string, number | string | Date | boolean | null>[])): Promise<void>;
Parameters
  • dataModifier: A synchronous or asynchronous function that takes the existing rows (as an array of objects) and returns the modified rows (as an array of objects).
Returns

A promise that resolves when the data has been updated.

Examples
// Add 1 to values in 'column1'. If values are not numbers, they are replaced by null.
await table.updateWithJS((rows) => {
  const modifiedRows = rows.map((d) => ({
    ...d,
    column1: typeof d.column1 === "number" ? d.column1 + 1 : null,
  }));
  return modifiedRows;
});

// Convert a date string to a Date object in 'dateColumn'
await table.updateWithJS((rows) => {
  const modifiedRows = rows.map((d) => ({
    ...d,
    dateColumn: typeof d.dateColumn === "string"
      ? new Date(d.dateColumn)
      : d.dateColumn,
  }));
  return modifiedRows;
});

getSchema

Returns the schema of the table, including column names and their data types.

Signature
async getSchema(): Promise<Record<string, string | null>[]>;
Returns

A promise that resolves to an array of objects, where each object represents a column with its name and data type.

Examples
// Get the schema of the table
const schema = await table.getSchema();
console.table(schema); // Log the schema in a readable table format

getDescription

Returns descriptive statistical information about the columns, including details like data types, number of null values, and distinct values.

Signature
async getDescription(): Promise<Record<string, unknown>[]>;
Returns

A promise that resolves to an array of objects, each representing descriptive statistics for a column.

Examples
// Get and log descriptive information about the table's columns
const description = await table.getDescription();
console.table(description);

getTableName

Returns the name of the table.

Signature
getTableName(): string;
Returns

The name of the table as a string.

Examples
// Get the table name
const tableName = table.getTableName();
console.log(tableName); // e.g., "employees"

getColumns

Returns a list of all column names in the table.

Signature
async getColumns(): Promise<string[]>;
Returns

A promise that resolves to an array of strings, where each string is a column name.

Examples
// Get all column names from the table
const columns = await table.getColumns();
console.log(columns); // e.g., ["id", "name", "age"]

normalizeString

Normalizes string values in a column by:

  1. Stripping accents
  2. Optionally stripping punctuation (default: true)
  3. Converting to lowercase
  4. Normalizing whitespace (multiple spaces/tabs/newlines → single space)
  5. Trimming leading/trailing whitespace

Produces identical output to journalism-format's normalizeString() function for all common cases including accented Latin characters.

Signature
async normalizeString(column: string, newColumn: string, options?: { stripPunctuation?: boolean }): Promise<void>;
Parameters
  • column: The column containing the text to normalize
  • newColumn: The column to store the normalized results
  • options: Configuration options
  • options.stripPunctuation: Strip punctuation and underscores (default: true)
Returns

A promise that resolves when the operation is complete

Examples
// Normalize text column and store in new column
await table.normalizeString("recipeName", "recipeNameNormalized");
// "Épicerie Parisienne!" → "epicerie parisienne"

// Keep punctuation for emails and URLs
await table.normalizeString("email", "emailNormalized", {
  stripPunctuation: false,
});
// "User@Example.com" → "user@example.com"
await table.normalizeString("url", "urlNormalized", {
  stripPunctuation: false,
});
// "https://Example.com/path" → "https://example.com/path"

getNbColumns

Returns the number of columns in the table.

Signature
async getNbColumns(): Promise<number>;
Returns

A promise that resolves to a number representing the total count of columns.

Examples
// Get the number of columns in the table
const nbColumns = await table.getNbColumns();
console.log(nbColumns); // e.g., 3

getNbCharacters

Returns the total number of characters in a column storing strings.

Signature
async getNbCharacters(column: string): Promise<number>;
Parameters
  • column: The name of the string column to count characters from.
Returns

A promise that resolves to the total number of characters across all rows in the specified column.

Examples
// Get the total number of characters in the 'name' column
const totalChars = await table.getNbCharacters("name");
console.log(totalChars); // e.g., 523

getNbRows

Returns the number of rows in the table.

Signature
async getNbRows(options?: { conditions?: string }): Promise<number>;
Parameters
  • options: An optional object with configuration options:
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Book'").
Returns

A promise that resolves to a number representing the total count of rows.

Examples
// Get the number of rows in the table
const nbRows = await table.getNbRows();
console.log(nbRows); // e.g., 100

// Get the number of rows where 'category' is 'Book'
const nbBooks = await table.getNbRows({ conditions: "category = 'Book'" });
console.log(nbBooks);

getNbValues

Returns the total number of values in the table (number of columns multiplied by the number of rows).

Signature
async getNbValues(): Promise<number>;
Returns

A promise that resolves to a number representing the total count of values.

Examples
// Get the total number of values in the table
const nbValues = await table.getNbValues();
console.log(nbValues); // e.g., 300 (if 3 columns and 100 rows)

getTypes

Returns the data types of all columns in the table.

Signature
async getTypes(): Promise<Record<string, string>>;
Returns

A promise that resolves to an object where keys are column names and values are their corresponding data types (e.g., { "id": "BIGINT", "name": "VARCHAR" }).

Examples
// Get the data types of all columns
const dataTypes = await table.getTypes();
console.log(dataTypes);

getValues

Returns all values from a specific column.

Signature
async getValues(column: string): Promise<(string | number | boolean | Date | null)[]>;
Parameters
  • column: The name of the column from which to retrieve values.
Returns

A promise that resolves to an array containing all values from the specified column.

Examples
// Get all values from the 'productName' column
const productNames = await table.getValues("productName");
console.log(productNames); // e.g., ["Laptop", "Mouse", "Keyboard"]

getMin

Returns the minimum value from a specific column.

Signature
async getMin(column: string): Promise<string | number | boolean | Date | null>;
Parameters
  • column: The name of the column from which to retrieve the minimum value.
Returns

A promise that resolves to the minimum value of the specified column.

Examples
// Get the minimum value from the 'price' column
const minPrice = await table.getMin("price");
console.log(minPrice); // e.g., 10.50

getMax

Returns the maximum value from a specific column.

Signature
async getMax(column: string): Promise<string | number | boolean | Date | null>;
Parameters
  • column: The name of the column from which to retrieve the maximum value.
Returns

A promise that resolves to the maximum value of the specified column.

Examples
// Get the maximum value from the 'price' column
const maxPrice = await table.getMax("price");
console.log(maxPrice); // e.g., 99.99

getExtent

Returns the extent (minimum and maximum values) of a specific column as an array.

Signature
async getExtent(column: string): Promise<[string | number | boolean | Date | null, string | number | boolean | Date | null]>;
Parameters
  • column: The name of the column from which to retrieve the extent.
Returns

A promise that resolves to an array [min, max] containing the minimum and maximum values of the specified column.

Examples
// Get the extent of the 'temperature' column
const tempExtent = await table.getExtent("temperature");
console.log(tempExtent); // e.g., [15.2, 30.1]

getMean

Returns the mean (average) value from a specific numeric column.

Signature
async getMean(column: string, options?: { decimals?: number }): Promise<number>;
Parameters
  • column: The name of the numeric column from which to retrieve the mean value.
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round the result to. Defaults to undefined (no rounding).
Returns

A promise that resolves to the mean value of the specified column.

Examples
// Get the mean of the 'age' column
const meanAge = await table.getMean("age");
console.log(meanAge); // e.g., 35.75

// Get the mean of the 'salary' column, rounded to 2 decimal places
const meanSalary = await table.getMean("salary", { decimals: 2 });
console.log(meanSalary); // e.g., 55000.23

getMedian

Returns the median value from a specific numeric column.

Signature
async getMedian(column: string, options?: { decimals?: number }): Promise<number>;
Parameters
  • column: The name of the numeric column from which to retrieve the median value.
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round the result to. Defaults to undefined (no rounding).
Returns

A promise that resolves to the median value of the specified column.

Examples
// Get the median of the 'age' column
const medianAge = await table.getMedian("age");
console.log(medianAge); // e.g., 30

// Get the median of the 'salary' column, rounded to 2 decimal places
const medianSalary = await table.getMedian("salary", { decimals: 2 });
console.log(medianSalary); // e.g., 50000.00

getSum

Returns the sum of values from a specific numeric column.

Signature
async getSum(column: string): Promise<number>;
Parameters
  • column: The name of the numeric column from which to retrieve the sum.
Returns

A promise that resolves to the sum of values in the specified column.

Examples
// Get the sum of the 'quantity' column
const totalQuantity = await table.getSum("quantity");
console.log(totalQuantity); // e.g., 1250

getSkew

Returns the skewness of values from a specific numeric column.

Signature
async getSkew(column: string, options?: { decimals?: number }): Promise<number>;
Parameters
  • column: The name of the numeric column from which to retrieve the skewness.
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round the result to. Defaults to undefined (no rounding).
Returns

A promise that resolves to the skewness value of the specified column.

Examples
// Get the skewness of the 'data' column
const dataSkew = await table.getSkew("data");
console.log(dataSkew); // e.g., 0.5

// Get the skewness of the 'values' column, rounded to 2 decimal places
const valuesSkew = await table.getSkew("values", { decimals: 2 });
console.log(valuesSkew); // e.g., -0.25

getStdDev

Returns the standard deviation of values from a specific numeric column.

Signature
async getStdDev(column: string, options?: { decimals?: number }): Promise<number>;
Parameters
  • column: The name of the numeric column from which to retrieve the standard deviation.
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round the result to. Defaults to undefined (no rounding).
Returns

A promise that resolves to the standard deviation value of the specified column.

Examples
// Get the standard deviation of the 'height' column
const heightStdDev = await table.getStdDev("height");
console.log(heightStdDev); // e.g., 5.2

// Get the standard deviation of the 'score' column, rounded to 3 decimal places
const scoreStdDev = await table.getStdDev("score", { decimals: 3 });
console.log(scoreStdDev); // e.g., 12.345

getVar

Returns the variance of values from a specific numeric column.

Signature
async getVar(column: string, options?: { decimals?: number }): Promise<number>;
Parameters
  • column: The name of the numeric column from which to retrieve the variance.
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round the result to. Defaults to undefined (no rounding).
Returns

A promise that resolves to the variance value of the specified column.

Examples
// Get the variance of the 'data' column
const dataVariance = await table.getVar("data");
console.log(dataVariance); // e.g., 25.5

// Get the variance of the 'values' column, rounded to 2 decimal places
const valuesVariance = await table.getVar("values", { decimals: 2 });
console.log(valuesVariance); // e.g., 10.23

getQuantile

Returns the value of a specific quantile from the values in a given numeric column.

Signature
async getQuantile(column: string, quantile: number, options?: { decimals?: number }): Promise<number>;
Parameters
  • column: The name of the numeric column from which to calculate the quantile.
  • quantile: The quantile to calculate, expressed as a number between 0 and 1 (e.g., 0.25 for the first quartile, 0.5 for the median, 0.75 for the third quartile).
  • options: An optional object with configuration options:
  • options.decimals: The number of decimal places to round the result to. Defaults to undefined (no rounding).
Returns

A promise that resolves to the quantile value of the specified column.

Examples
// Get the first quartile (25th percentile) of 'column1'
const firstQuartile = await table.getQuantile("column1", 0.25);
console.log(firstQuartile); // e.g., 15.7

// Get the 90th percentile of 'score' values, rounded to 2 decimal places
const ninetiethPercentile = await table.getQuantile("score", 0.9, {
  decimals: 2,
});
console.log(ninetiethPercentile); // e.g., 88.55

getUniques

Returns unique values from a specific column. The values are returned in ascending order.

Signature
async getUniques(column: string): Promise<(string | number | boolean | Date | null)[]>;
Parameters
  • column: The name of the column from which to retrieve unique values.
Returns

A promise that resolves to an array containing the unique values from the specified column, sorted in ascending order.

Examples
// Get unique values from the 'category' column
const uniqueCategories = await table.getUniques("category");
console.log(uniqueCategories); // e.g., ["Books", "Clothing", "Electronics"]

getFirstRow

Returns the first row of the table, optionally filtered by SQL conditions. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async getFirstRow(options?: { conditions?: string }): Promise<Record<string, string | number | boolean | Date | null>>;
Parameters
  • options: An optional object with configuration options:
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Book'").
Returns

A promise that resolves to an object representing the first row, or null if no rows match the conditions.

Examples
// Get the very first row of the table
const firstRow = await table.getFirstRow();
console.log(firstRow);

// Get the first row where the 'category' is 'Book'
const firstRowBooks = await table.getFirstRow({
  conditions: `category === 'Book'`,
}); // Using JS syntax
console.log(firstRowBooks);

getLastRow

Returns the last row of the table, optionally filtered by SQL conditions. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async getLastRow(options?: { conditions?: string }): Promise<Record<string, string | number | boolean | Date | null>>;
Parameters
  • options: An optional object with configuration options:
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Book'").
Returns

A promise that resolves to an object representing the last row, or null if no rows match the conditions.

Examples
// Get the very last row of the table
const lastRow = await table.getLastRow();
console.log(lastRow);

// Get the last row where the 'category' is 'Book'
const lastRowBooks = await table.getLastRow({
  conditions: `category === 'Book'`,
}); // Using JS syntax
console.log(lastRowBooks);

getTop

Returns the top n rows of the table, optionally filtered by SQL conditions. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async getTop(count: number, options?: { conditions?: string }): Promise<Record<string, string | number | boolean | Date | null>[]>;
Parameters
  • count: The number of rows to return from the top of the table.
  • options: An optional object with configuration options:
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Books'").
Returns

A promise that resolves to an array of objects representing the top n rows.

Examples
// Get the first 10 rows of the table
const top10 = await table.getTop(10);
console.log(top10);

// Get the first 5 rows where the 'category' is 'Books'
const top5Books = await table.getTop(5, { conditions: `category === 'Books'` }); // Using JS syntax
console.log(top5Books);

getBottom

Returns the bottom n rows of the table, optionally filtered by SQL conditions. By default, the last row will be returned first. To preserve the original order, use the originalOrder option. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async getBottom(count: number, options?: { originalOrder?: boolean; conditions?: string }): Promise<Record<string, string | number | boolean | Date | null>[]>;
Parameters
  • count: The number of rows to return from the bottom of the table.
  • options: An optional object with configuration options:
  • options.originalOrder: A boolean indicating whether the rows should be returned in their original order (true) or in reverse order (last row first, false). Defaults to false.
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Books'").
Returns

A promise that resolves to an array of objects representing the bottom n rows.

Examples
// Get the last 10 rows (last row first)
const bottom10 = await table.getBottom(10);
console.log(bottom10);

// Get the last 10 rows in their original order
const bottom10OriginalOrder = await table.getBottom(10, {
  originalOrder: true,
});
console.log(bottom10OriginalOrder);

// Get the last 5 rows where the 'category' is 'Books' (using JS syntax)
const bottom5Books = await table.getBottom(5, {
  conditions: `category === 'Books'`,
});
console.log(bottom5Books);

getRow

Returns a single row that matches the specified conditions. If no row matches or if more than one row matches, an error is thrown by default. You can also use JavaScript syntax for conditions (e.g., AND, ||, ===, !==).

Signature
async getRow(conditions: string, options?: { noCheck?: boolean }): Promise<Record<string, string | number | boolean | Date | null> | undefined>;
Parameters
  • conditions: The conditions to match, specified as a SQL WHERE clause.
  • options: Optional settings:
  • options.noCheck: If true, no error will be thrown when no row or more than one row match the condition. Defaults to false.
Returns

A promise that resolves to an object representing the matched row.

Throws
  • Error: If noCheck is false and no row or more than one row matches the conditions.
Examples
// Get a row where 'name' is 'John'
const johnsRow = await table.getRow(`name = 'John'`);
console.log(johnsRow);

// Get a row where 'id' is 123 (using JS syntax)
const rowById = await table.getRow(`id === 123`);
console.log(rowById);

// Get a row without throwing an error if multiple matches or no match
const flexibleRow = await table.getRow(`status = 'pending'`, { noCheck: true });
console.log(flexibleRow);

getData

Returns the data from the table as an array of objects, optionally filtered by SQL conditions. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async getData(options?: { columns?: string | string[]; conditions?: string }): Promise<Record<string, string | number | boolean | Date | null>[]>;
Parameters
  • options: An optional object with configuration options:
  • options.columns: An array of column names to include in the result. If omitted, all columns will be included.
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Book'").
Returns

A promise that resolves to an array of objects, where each object represents a row in the table.

Examples
// Get all data from the table
const allData = await table.getData();
console.log(allData);

// Get data filtered by a condition (using JS or SQL syntax)
const booksData = await table.getData({ conditions: `category === 'Book'` });
console.log(booksData);

// Get data filtered by a condition and specific columns
const booksData = await table.getData({
  columns: ["title", "author"],
  conditions: `category === 'Book'`,
});
console.log(booksData);

getDataAsCSV

Returns the data from the table as a CSV string, optionally filtered by SQL conditions. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async getDataAsCSV(options?: { columns?: string | string[]; conditions?: string }): Promise<string>;
Parameters
  • options: An optional object with configuration options:
  • options.columns: An array of column names to include in the CSV. If omitted, all columns will be included.
  • options.conditions: The filtering conditions specified as a SQL WHERE clause (e.g., "category = 'Book'").
Returns

A promise that resolves to a CSV-formatted string representation of the table data.

Examples
// Get all data from the table as CSV
const allDataCSV = await table.getDataAsCSV();
console.log(allDataCSV);

// Get data filtered by a condition (using JS syntax or SQL syntax) as CSV
const booksDataCSV = await table.getDataAsCSV({
  conditions: `category === 'Book'`,
});
console.log(booksDataCSV);

// Get data filtered by a condition and specific columns as CSV
const booksDataCSV = await table.getDataAsCSV({
  columns: ["title", "author"],
  conditions: `category === 'Book'`,
});
console.log(booksDataCSV);

points

Creates point geometries from latitude and longitude columns.

Signature
async points(columnLat: string, columnLon: string, newColumn: string): Promise<void>;
Parameters
  • columnLat: The name of the column storing the latitude values.
  • columnLon: The name of the column storing the longitude values.
  • newColumn: The name of the new column where the point geometries will be stored.
Returns

A promise that resolves when the point geometries have been created.

Examples
// Create point geometries in a new 'geom' column using 'lat' and 'lon' columns
await table.points("lat", "lon", "geom");

isValidGeo

Adds a column with boolean values indicating the validity of geometries.

Signature
async isValidGeo(newColumn: string, options?: { column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the boolean results (TRUE for valid, FALSE for invalid) will be stored.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries to be checked. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the validity check is complete.

Examples
// Check if geometries are valid and store results in a new 'isValid' column
// The method will automatically detect the geometry column.
await table.isValidGeo("isValid");

// Check validity of geometries in a specific column named 'myGeom'
await table.isValidGeo("isValidMyGeom", { column: "myGeom" });

nbVertices

Adds a column with the number of vertices (points) in each geometry.

Signature
async nbVertices(newColumn: string, options?: { column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the vertex counts will be stored.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the vertex counts have been added.

Examples
// Add a new column 'vertexCount' with the number of vertices for each geometry
// The method will automatically detect the geometry column.
await table.nbVertices("vertexCount");

// Add vertex counts for geometries in a specific column named 'myGeom'
await table.nbVertices("myGeomVertices", { column: "myGeom" });

fixGeo

Attempts to make invalid geometries valid without removing any vertices.

Signature
async fixGeo(column?: string): Promise<void>;
Parameters
  • column: The name of the column storing the geometries to be fixed. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the geometries have been processed.

Examples
// Fix invalid geometries in the default geometry column
await table.fixGeo();

// Fix invalid geometries in a specific column named 'myGeom'
await table.fixGeo("myGeom");

isClosedGeo

Adds a column with boolean values indicating whether geometries are closed (e.g., polygons) or open (e.g., linestrings).

Signature
async isClosedGeo(newColumn: string, options?: { column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the boolean results (TRUE for closed, FALSE for open) will be stored.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the closed geometry check is complete.

Examples
// Check if geometries are closed and store results in a new 'isClosed' column
await table.isClosedGeo("isClosed");

// Check closed status of geometries in a specific column named 'boundaryGeom'
await table.isClosedGeo("boundaryClosed", { column: "boundaryGeom" });

typeGeo

Adds a column with the geometry type (e.g., "POINT", "LINESTRING", "POLYGON") for each geometry.

Signature
async typeGeo(newColumn: string, options?: { column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the geometry types will be stored.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the geometry types have been added.

Examples
// Add a new column 'geometryType' with the type of each geometry
await table.typeGeo("geometryType");

// Get the geometry type for geometries in a specific column named 'featureGeom'
await table.typeGeo("featureType", { column: "featureGeom" });

flipCoordinates

Flips the coordinate order of geometries in a specified column (e.g., from [lon, lat] to [lat, lon] or vice-versa). Warning: This method should be used with caution as it directly manipulates coordinate order and can affect the accuracy of geospatial operations if not used correctly.

Signature
async flipCoordinates(column?: string): Promise<void>;
Parameters
  • column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the coordinates have been flipped.

Examples
// Flip coordinates in the default geometry column
await table.flipCoordinates();

// Flip coordinates in a specific column named 'myGeom'
await table.flipCoordinates("myGeom");

reducePrecision

Reduces the precision of geometries in a specified column to a given number of decimal places.

Signature
async reducePrecision(decimals: number, options?: { column?: string }): Promise<void>;
Parameters
  • decimals: The number of decimal places to keep in the coordinates of the geometries.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the precision of the geometries has been reduced.

Examples
// Reduce the precision of geometries in the default column to 3 decimal places
await table.reducePrecision(3);

// Reduce the precision of geometries in a specific column named 'myGeom' to 2 decimal places
await table.reducePrecision(2, { column: "myGeom" });

reproject

Reprojects the geometries in a specified column to another Spatial Reference System (SRS).

Signature
async reproject(to: string, options?: { column?: string }): Promise<void>;
Parameters
  • to: The target SRS (e.g., "EPSG:3347", "WGS84").
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the geometries have been reprojected.

Examples
// Reproject geometries in the default column to EPSG:3347 (NAD83/Statistics Canada Lambert)
await table.reproject("EPSG:3347");

// Reproject geometries in a specific column named 'myGeom' to EPSG:3347
await table.reproject("EPSG:3347", { column: "myGeom" });

area

Computes the area of geometries in square meters ("m2") or optionally square kilometers ("km2"). The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84).

Signature
async area(newColumn: string, options?: { unit?: "m2" | "km2"; column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the computed areas will be stored.
  • options: An optional object with configuration options:
  • options.unit: The unit for the computed area: "m2" (square meters) or "km2" (square kilometers). Defaults to "m2".
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the areas have been computed.

Examples
// Compute the area of geometries in square meters and store in 'area_m2'
await table.area("area_m2");

// Compute the area of geometries in square kilometers and store in 'area_km2'
await table.area("area_km2", { unit: "km2" });

// Compute the area of geometries in a specific column named 'myGeom'
await table.area("myGeomArea", { column: "myGeom" });

length

Computes the length of line geometries in meters ("m") or optionally kilometers ("km"). The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84).

Signature
async length(newColumn: string, options?: { unit?: "m" | "km"; column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the computed lengths will be stored.
  • options: An optional object with configuration options:
  • options.unit: The unit for the computed length: "m" (meters) or "km" (kilometers). Defaults to "m".
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the lengths have been computed.

Examples
// Compute the length of line geometries in meters and store in 'length_m'
await table.length("length_m");

// Compute the length of line geometries in kilometers and store in 'length_km'
await table.length("length_km", { unit: "km" });

// Compute the length of geometries in a specific column named 'routeGeom'
await table.length("routeLength", { column: "routeGeom" });

perimeter

Computes the perimeter of polygon geometries in meters ("m") or optionally kilometers ("km"). The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84).

Signature
async perimeter(newColumn: string, options?: { unit?: "m" | "km"; column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the computed perimeters will be stored.
  • options: An optional object with configuration options:
  • options.unit: The unit for the computed perimeter: "m" (meters) or "km" (kilometers). Defaults to "m".
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the perimeters have been computed.

Examples
// Compute the perimeter of polygon geometries in meters and store in 'perimeter_m'
await table.perimeter("perimeter_m");

// Compute the perimeter of polygon geometries in kilometers and store in 'perimeter_km'
await table.perimeter("perimeter_km", { unit: "km" });

// Compute the perimeter of geometries in a specific column named 'landParcelGeom'
await table.perimeter("landParcelPerimeter", { column: "landParcelGeom" });

buffer

Computes a buffer (a polygon representing a specified distance around a geometry) for geometries in a specified column. The distance is in the Spatial Reference System (SRS) unit of the input geometries.

Signature
async buffer(newColumn: string, distance: number, options?: { column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the buffered geometries will be stored.
  • distance: The distance for the buffer. This value is in the units of the geometry's SRS.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the buffers have been computed.

Examples
// Create a buffer of 1 unit around geometries in the default column, storing results in 'bufferedGeom'
await table.buffer("bufferedGeom", 1);

// Create a buffer of 10 units around geometries in a specific column named 'pointsGeom'
await table.buffer("pointsBuffer", 10, { column: "pointsGeom" });

joinGeo

Merges the data of this table (considered the left table) with another table (the right table) based on a spatial relationship. Note that the order of rows in the returned data is not guaranteed to be the same as in the original tables. This operation might create temporary files in a .tmp folder; consider adding .tmp to your .gitignore.

Signature
async joinGeo(rightTable: SimpleTable, method: "intersect" | "inside" | "within", options?: { leftTableColumn?: string; rightTableColumn?: string; type?: "inner" | "left" | "right" | "full"; distance?: number; distanceMethod?: "srs" | "haversine" | "spheroid"; outputTable?: string | boolean }): Promise<this>;
Parameters
  • rightTable: The SimpleTable instance to be joined with this table.
  • method: The spatial join method to use: "intersect" (geometries overlap), "inside" (geometries of the left table are entirely within geometries of the right table), or "within" (geometries of the left table are within a specified distance of geometries in the right table).
  • options: An optional object with configuration options:
  • options.leftTableColumn: The name of the column storing geometries in the left table (this table). If omitted, the method attempts to find one.
  • options.rightTableColumn: The name of the column storing geometries in the right table. If omitted, the method attempts to find one.
  • options.type: The type of join operation to perform: "inner", "left" (default), "right", or "full". For some types (like "inside"), the table order is important.
  • options.distance: Required if method is "within". The target distance for the spatial join. The unit depends on distanceMethod.
  • options.distanceMethod: The method for distance calculations: "srs" (default, uses the SRS unit), "haversine" (uses meters, requires EPSG:4326 input), or "spheroid" (uses meters, requires EPSG:4326 input, most accurate but slowest).
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
Returns

A promise that resolves to a table instance containing the spatially joined data (either the modified current table or a new table).

Examples
// Merge data based on intersecting geometries, overwriting tableA
await tableA.joinGeo(tableB, "intersect");

// Merge data where geometries in tableA are inside geometries in tableB
await tableA.joinGeo(tableB, "inside");

// Merge data where geometries in tableA are within 10 units (SRS) of geometries in tableB
await tableA.joinGeo(tableB, "within", { distance: 10 });

// Merge data where geometries in tableA are within 10 kilometers (Haversine) of geometries in tableB
// Input geometries must be in EPSG:4326.
await tableA.joinGeo(tableB, "within", {
  distance: 10,
  distanceMethod: "haversine",
  unit: "km",
});

// Merge data with specific geometry columns and an inner join type, storing results in a new table
const tableC = await tableA.joinGeo(tableB, "intersect", {
  leftTableColumn: "geometriesA",
  rightTableColumn: "geometriesB",
  type: "inner",
  outputTable: true,
});

intersection

Computes the intersection of two sets of geometries, creating new geometries where they overlap.

Signature
async intersection(column1: string, column2: string, newColumn: string): Promise<void>;
Parameters
  • column1: The name of the first column storing geometries.
  • column2: The name of the second column storing geometries. Both columns must have the same projection.
  • newColumn: The name of the new column where the computed intersection geometries will be stored.
Returns

A promise that resolves when the intersection geometries have been computed.

Examples
// Compute the intersection of geometries in 'geomA' and 'geomB' columns, storing results in 'intersectGeom'
await table.intersection("geomA", "geomB", "intersectGeom");

removeIntersection

Removes the intersection of two geometries from the first geometry, effectively computing the geometric difference.

Signature
async removeIntersection(column1: string, column2: string, newColumn: string): Promise<void>;
Parameters
  • column1: The name of the column storing the reference geometries. These geometries will have the intersection removed.
  • column2: The name of the column storing the geometries used to compute the intersection. Both columns must have the same projection.
  • newColumn: The name of the new column where the resulting geometries (without the intersection) will be stored.
Returns

A promise that resolves when the geometries have been processed.

Examples
// Remove the intersection of 'geomB' from 'geomA', storing the result in 'geomA_minus_geomB'
await table.removeIntersection("geomA", "geomB", "geomA_minus_geomB");

fillHoles

Fills holes in polygon geometries.

Signature
async fillHoles(column?: string): Promise<void>;
Parameters
  • column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the holes have been filled.

Examples
// Fill holes in geometries in the default geometry column
await table.fillHoles();

// Fill holes in geometries in a specific column named 'polygonGeom'
await table.fillHoles("polygonGeom");

intersect

Returns TRUE if two geometries intersect (overlap in any way), and FALSE otherwise.

Signature
async intersect(column1: string, column2: string, newColumn: string): Promise<void>;
Parameters
  • column1: The name of the first column storing geometries.
  • column2: The name of the second column storing geometries. Both columns must have the same projection.
  • newColumn: The name of the new column where the boolean results (TRUE for intersection, FALSE otherwise) will be stored.
Returns

A promise that resolves when the intersection check is complete.

Examples
// Check if geometries in 'geomA' and 'geomB' intersect, storing results in 'doIntersect'
await table.intersect("geomA", "geomB", "doIntersect");

inside

Returns TRUE if all points of a geometry in column1 lie inside a geometry in column2, and FALSE otherwise.

Signature
async inside(column1: string, column2: string, newColumn: string): Promise<void>;
Parameters
  • column1: The name of the column storing the geometries to be tested for containment.
  • column2: The name of the column storing the geometries to be tested as containers. Both columns must have the same projection.
  • newColumn: The name of the new column where the boolean results (TRUE for inside, FALSE otherwise) will be stored.
Returns

A promise that resolves when the containment check is complete.

Examples
// Check if geometries in 'pointGeom' are inside 'polygonGeom', storing results in 'isInsidePolygon'
await table.inside("pointGeom", "polygonGeom", "isInsidePolygon");

union

Computes the union of two geometries, creating a new geometry that represents the merged area of both.

Signature
async union(column1: string, column2: string, newColumn: string): Promise<void>;
Parameters
  • column1: The name of the first column storing geometries.
  • column2: The name of the second column storing geometries. Both columns must have the same projection.
  • newColumn: The name of the new column where the computed union geometries will be stored.
Returns

A promise that resolves when the union geometries have been computed.

Examples
// Compute the union of geometries in 'geomA' and 'geomB', storing results in 'unionGeom'
await table.union("geomA", "geomB", "unionGeom");

latLon

Extracts the latitude and longitude coordinates from point geometries. The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84).

Signature
async latLon(column: string, columnLat: string, columnLon: string): Promise<void>;
Parameters
  • column: The name of the column storing the point geometries.
  • columnLat: The name of the new column where the extracted latitude values will be stored.
  • columnLon: The name of the new column where the extracted longitude values will be stored.
Returns

A promise that resolves when the latitude and longitude have been extracted.

Examples
// Extract latitude and longitude from 'geom' column into new 'lat' and 'lon' columns
await table.latLon("geom", "lat", "lon");

simplify

Simplifies geometries while preserving their overall coverage. A higher tolerance results in more significant simplification.

Signature
async simplify(tolerance: number, options?: { column?: string; simplifyBoundary?: boolean }): Promise<void>;
Parameters
  • tolerance: A numeric value representing the simplification tolerance. A higher value leads to greater simplification.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
  • options.simplifyBoundary: If true (default), the boundary of the geometries will also be simplified. If false, only the interior of the geometries will be simplified, preserving the original boundary.
Returns

A promise that resolves when the geometries have been simplified.

Examples
// Simplify geometries in the default column with a tolerance of 0.1
await table.simplify(0.1);

// Simplify geometries in 'myGeom' column, preserving the boundary
await table.simplify(0.05, { column: "myGeom", simplifyBoundary: false });

centroid

Computes the centroid of geometries. The values are returned in the SRS unit of the input geometries.

Signature
async centroid(newColumn: string, options?: { column?: string }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the computed centroid geometries will be stored.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the centroids have been computed.

Examples
// Compute the centroid of geometries in the default column, storing results in 'centerPoint'
await table.centroid("centerPoint");

// Compute the centroid of geometries in a specific column named 'areaGeom'
await table.centroid("areaCentroid", { column: "areaGeom" });

randomPoint

Generates a random point within the geometries of a specified column.

Signature
async randomPoint(newColumn: string, nbPointsToTry: number, options?: { column?: string; try?: boolean }): Promise<void>;
Parameters
  • newColumn: The name of the new column where the random points will be stored.
  • nbPointsToTry: The number of points to generate within the bounding box of each geometry to find one that is within the geometry itself.
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries within which the random points will be generated. If omitted, the method will automatically attempt to find a geometry column.
  • options.try: If true, the method will not throw an error if some points cannot be generated. Corresponding rows will have NULL in the new column.
Examples
// Generate a random point for each geometry in the default column, trying 100 points
await table.randomPoint("randomPoint", 100);

// Generate a random point for each geometry in a specific column named 'areaGeom', trying 50 points
await table.randomPoint("pointInArea", 50, { column: "areaGeom" });

// Generate a random point for each geometry, but don't throw if some points cannot be generated
await table.randomPoint("pointInArea", 1, { try: true });

distance

Computes the distance between geometries in two specified columns. By default, the distance is calculated in the Spatial Reference System (SRS) unit of the input geometries. You can optionally specify "spheroid" or "haversine" methods to get results in meters or kilometers. If using "spheroid" or "haversine", the input geometries must be in the EPSG:4326 coordinate system (WGS84).

Signature
async distance(column1: string, column2: string, newColumn: string, options?: { unit?: "m" | "km"; method?: "srs" | "haversine" | "spheroid"; decimals?: number }): Promise<void>;
Parameters
  • column1: The name of the first column storing geometries.
  • column2: The name of the second column storing geometries.
  • newColumn: The name of the new column where the computed distances will be stored.
  • options: An optional object with configuration options:
  • options.method: The method to use for distance calculations: "srs" (default, uses SRS unit), "haversine" (meters, requires EPSG:4326), or "spheroid" (meters, requires EPSG:4326, most accurate but slowest).
  • options.unit: If method is "spheroid" or "haversine", you can choose between "m" (meters, default) or "km" (kilometers).
  • options.decimals: The number of decimal places to round the distance values. Defaults to undefined (no rounding).
Returns

A promise that resolves when the distances have been computed.

Examples
// Compute distance between 'geomA' and 'geomB' in SRS units, store in 'distance_srs'
await table.distance("geomA", "geomB", "distance_srs");

// Compute Haversine distance in meters between 'point1' and 'point2', store in 'distance_m'
// Input geometries must be in EPSG:4326.
await table.distance("point1", "point2", "distance_m", { method: "haversine" });

// Compute Haversine distance in kilometers, rounded to 2 decimal places
// Input geometries must be in EPSG:4326.
await table.distance("point1", "point2", "distance_km", {
  method: "haversine",
  unit: "km",
  decimals: 2,
});

// Compute Spheroid distance in kilometers
// Input geometries must be in EPSG:4326.
await table.distance("area1", "area2", "distance_spheroid_km", {
  method: "spheroid",
  unit: "km",
});

unnestGeo

Unnests geometries recursively, transforming multi-part geometries (e.g., MultiPolygon) into individual single-part geometries (e.g., Polygon).

Signature
async unnestGeo(column?: string): Promise<void>;
Parameters
  • column: The name of the column storing the geometries to be unnested. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the geometries have been unnested.

Examples
// Unnest geometries in the default column
await table.unnestGeo();

// Unnest geometries in a specific column named 'multiGeom'
await table.unnestGeo("multiGeom");

boundingBox

Computes the bounding box of geometries in a specified column, creating four new columns: minLon, minLat, maxLon, and maxLat.

Signature
async boundingBox(options?: { column?: string; decimals?: number }): Promise<void>;
Parameters
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries for which the bounding box will be computed. If omitted, the method will automatically attempt to find a geometry column.
  • options.decimals: The number of decimal places to round the bounding box coordinates. Defaults to undefined (no rounding).
Returns

A promise that resolves when the bounding box coordinates have been computed and stored in new columns.

Examples
// Compute the bounding box for geometries in the default column
await table.boundingBox();
// The table now has minLon, minLat, maxLon, and maxLat columns.

// Compute the bounding box for geometries in 'geom' column and round coordinates to 2 decimal places
await table.boundingBox({ column: "geom", decimals: 2 });
// The table now has minLon, minLat, maxLon, and maxLat columns with values rounded to 2 decimal places.

aggregateGeo

Aggregates geometries in a specified column based on a chosen aggregation method.

Signature
async aggregateGeo(method: "union" | "intersection", options?: { column?: string; categories?: string | string[]; outputTable?: string | boolean }): Promise<this>;
Parameters
  • method: The aggregation method to apply: "union" (combines all geometries into a single multi-geometry) or "intersection" (computes the intersection of all geometries).
  • options: An optional object with configuration options:
  • options.column: The name of the column storing the geometries to be aggregated. If omitted, the method will automatically attempt to find a geometry column.
  • options.categories: The column name or an array of column names that define categories for the aggregation. Aggregation will be performed independently within each category.
  • options.outputTable: If true, the results will be stored in a new table with a generated name. If a string, it will be used as the name for the new table. If false or omitted, the current table will be overwritten. Defaults to false.
Returns

A promise that resolves to a table instance containing the aggregated geometries (either the modified current table or a new table).

Examples
// Aggregate all geometries in the default column into a single union geometry
await table.aggregateGeo("union");

// Aggregate geometries by 'country' and compute their union
await table.aggregateGeo("union", { categories: "country" });

// Aggregate geometries in 'regions' column into their intersection, storing results in a new table
const intersectionTable = await table.aggregateGeo("intersection", {
  column: "regions",
  outputTable: true,
});

linesToPolygons

Transforms closed linestring geometries into polygon geometries.

Signature
async linesToPolygons(column?: string): Promise<void>;
Parameters
  • column: The name of the column storing the linestring geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves when the transformation is complete.

Examples
// Transform closed linestrings in the default geometry column into polygons
await table.linesToPolygons();

// Transform closed linestrings in a specific column named 'routeLines' into polygons
await table.linesToPolygons("routeLines");

getBoundingBox

Returns the bounding box of geometries in [minLon, minLat, maxLon, maxLat] order. By default, the method will try to find the column with the geometries. The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84).

Signature
async getBoundingBox(column?: string): Promise<[number, number, number, number]>;
Parameters
  • column: The name of the column storing geometries. If omitted, the method will automatically attempt to find a geometry column.
Returns

A promise that resolves to an array [minLon, minLat, maxLon, maxLat] representing the bounding box.

Examples
// Get the bounding box of geometries in the default column
const bbox = await table.getBoundingBox();
console.log(bbox); // e.g., [-75.0, 45.0, -73.0, 46.0]

// Get the bounding box of geometries in a specific column named 'areaGeom'
const areaBbox = await table.getBoundingBox("areaGeom");
console.log(areaBbox);

getGeoData

Returns the table's geospatial data as a GeoJSON object. If the table has multiple geometry columns, you must specify which one to use.

Signature
async getGeoData(column?: string, options?: { rewind?: boolean }): Promise<{ type: string; features: unknown[] }>;
Parameters
  • column: The name of the column storing the geometries. If omitted, the method will automatically attempt to find a geometry column.
  • options: An optional object with configuration options:
  • options.rewind: If true, rewinds the coordinates of polygons to follow the spherical winding order (important for D3.js). Defaults to false.
Returns

A promise that resolves to a GeoJSON object representing the table's geospatial data.

Examples
// Get GeoJSON data from the default geometry column
const geojson = await table.getGeoData();
console.log(geojson);

// Get GeoJSON data from a specific geometry column named 'myGeometries'
const myGeomJson = await table.getGeoData("myGeometries");
console.log(myGeomJson);

// Get GeoJSON data and rewind polygon coordinates for D3.js compatibility
const rewoundGeojson = await table.getGeoData(undefined, { rewind: true });
console.log(rewoundGeojson);

writeData

Writes the table's data to a file in various formats (CSV, JSON, Parquet, DuckDB, SQLite). If the specified path does not exist, it will be created.

Signature
async writeData(file: string, options?: { compression?: boolean; dataAsArrays?: boolean; formatDates?: boolean }): Promise<void>;
Parameters
  • file: The absolute path to the output file (e.g., "./output.csv", "./output.json").
  • options: An optional object with configuration options:
  • options.compression: A boolean indicating whether to compress the output file. If true, CSV and JSON files will be compressed with GZIP, while Parquet files will use ZSTD. Defaults to false.
  • options.dataAsArrays: For JSON files only. If true, JSON files are written as a single object with arrays for each column (e.g., { "col1": [v1, v2], "col2": [v3, v4] }) instead of an array of objects. This can reduce file size for web projects. You can use the arraysToData function from the journalism library to convert it back.
  • options.formatDates: For CSV and JSON files only. If true, date and timestamp columns will be formatted as ISO 8601 strings (e.g., "2025-01-01T01:00:00.000Z"). Defaults to false.
Returns

A promise that resolves when the data has been written to the file.

Examples
// Write data to a CSV file
await table.writeData("./output.csv");

// Write data to a JSON file with GZIP compression.
// The output file will be named output.json.gz.
await table.writeData("./output.json", { compression: true });

// Write data to a Parquet file
await table.writeData("./output.parquet");

// Write data to a DuckDB database file
await table.writeData("./my_database.db");

// Write data to a SQLite database file
await table.writeData("./my_database.sqlite");

// Write JSON data with dates formatted as ISO strings
await table.writeData("./output_dates.json", { formatDates: true });

writeGeoData

Writes the table's geospatial data to a file in GeoJSON, GeoParquet, or Shapefile format. If the specified path does not exist, it will be created.

Signature
async writeGeoData(file: string, options?: { precision?: number; compression?: boolean; rewind?: boolean; metadata?: unknown; formatDates?: boolean }): Promise<void>;
Parameters
  • file: The absolute path to the output file (e.g., "./output.geojson", "./output.geoparquet", "./shapefile-folder/output.shp").
  • options: An optional object with configuration options:
  • options.precision: For GeoJSON, the maximum number of figures after the decimal separator to write in coordinates. Defaults to undefined (full precision).
  • options.compression: For GeoParquet, if true, the output will be ZSTD compressed. Defaults to false.
  • options.rewind: For GeoJSON, if true, rewinds the coordinates of polygons to follow the right-hand rule (RFC 7946). Defaults to false.
  • options.metadata: For GeoJSON, an object to be added as top-level metadata to the GeoJSON output.
  • options.formatDates: For GeoJSON, if true, formats date and timestamp columns to ISO 8601 strings. Defaults to false.
Returns

A promise that resolves when the geospatial data has been written to the file.

Examples
// Write geospatial data to a GeoJSON file
await table.writeGeoData("./output.geojson");

// Write geospatial data to a compressed GeoParquet file
await table.writeGeoData("./output.geoparquet", { compression: true });

// Write geospatial data to a Shapefile with all relevant files  in the same folder
await table.writeGeoData("./shapefile-folder/output.shp");

// Write GeoJSON with specific precision and metadata
await table.writeGeoData("./output_high_precision.geojson", {
  precision: 6,
  metadata: { source: "SimpleDataAnalysis" },
});

cache

Caches the results of computations in ./.sda-cache. You should add ./.sda-cache to your .gitignore file.

Signature
async cache(run: () => Promise<void>, options?: { ttl?: number }): Promise<void>;
Parameters
  • run: A function wrapping the computations to be cached. This function will be executed on the first run or if the cached data is invalid/expired.
  • options: An optional object with configuration options:
  • options.ttl: Time to live (in seconds). If the data in the cache is older than this duration, the run function will be executed again to refresh the cache. By default, there is no TTL, meaning the cache is only invalidated if the run function's content changes.
Returns

A promise that resolves when the computations are complete or the data is loaded from cache.

Examples
// Basic usage: computations are cached and re-run only if the function content changes
const sdb = new SimpleDB();
const table = sdb.newTable();

await table.cache(async () => {
  await table.loadData("items.csv");
  await table.summarize({
    values: "price",
    categories: "department",
    summaries: ["min", "max", "mean"],
  });
});

// It's important to call done() on the SimpleDB instance to clean up the cache.
// This prevents the cache from growing indefinitely.
await sdb.done();

// Cache with a Time-To-Live (TTL) of 60 seconds
// The computations will be re-run if the cached data is older than 1 minute or if the function content changes.
const sdb = new SimpleDB();
const table = sdb.newTable();

await table.cache(async () => {
  await table.loadData("items.csv");
  await table.summarize({
    values: "price",
    categories: "department",
    summaries: ["min", "max", "mean"],
  });
}, { ttl: 60 });

await sdb.done();

// Enable verbose logging for cache operations via SimpleDB instance
const sdb = new SimpleDB({ cacheVerbose: true });
const table = sdb.newTable();

await table.cache(async () => {
  await table.loadData("items.csv");
  await table.summarize({
    values: "price",
    categories: "department",
    summaries: ["min", "max", "mean"],
  });
});

await sdb.done();

logTable

Logs a specified number of rows from the table to the console. By default, the first 10 rows are logged. You can optionally log the column types and filter the data based on conditions. You can also use JavaScript syntax for conditions (e.g., &&, ||, ===, !==).

Signature
async logTable(options?: "all" | number | { nbRowsToLog?: number | "all"; types?: boolean; conditions?: string }): Promise<void>;
Parameters
  • options: Either the number of rows to log (a specific number or "all") or an object with configuration options:
  • options.nbRowsToLog: The number of rows to log. Defaults to 10 or the value set in the SimpleDB instance. Use "all" to log all rows.
  • options.types: If true, logs the column types along with the data. Defaults to false.
  • options.conditions: A SQL WHERE clause condition to filter the data before logging. Defaults to no condition.
Returns

A promise that resolves when the table data has been logged.

Examples
// Log the first 10 rows (default behavior)
await table.logTable();

// Log the first 50 rows
await table.logTable(50);

// Log all rows
await table.logTable("all");

// Log the first 20 rows and include column types
await table.logTable({ nbRowsToLog: 20, types: true });

// Log rows where 'status' is 'active' (using JS syntax for conditions)
await table.logTable({ conditions: `status === 'active'` });

logDescription

Logs descriptive information about the columns in the table to the console. This includes details such as data types, number of null values, and number of distinct values for each column. It internally calls the getDescription method to retrieve the descriptive statistics.

Signature
async logDescription(): Promise<void>;
Returns

A promise that resolves when the column description has been logged to the console.

Examples
// Log descriptive information for all columns in the table
await table.logDescription();

getProjection

Retrieves the projection of a specified geospatial column.

Signature
async getProjection(column: string): Promise<string>;
Parameters
  • column: The name of the geospatial column for which to retrieve the projection.
Returns

A promise that resolves to the projection of the specified column.

Examples
// Get the projection of the 'geom' column
const projection = await table.getProjection("geom");

logProjections

Logs the projections of the geospatial data (if any) to the console.

Signature
async logProjections(): Promise<this>;
Returns

A promise that resolves to the SimpleTable instance after logging the projections.

Examples
// Log the geospatial projections of the table
await table.logProjections();

logTypes

Logs the types of all columns in the table to the console.

Signature
async logTypes(): Promise<this>;
Returns

A promise that resolves to the SimpleTable instance after logging the column types.

Examples
// Log the data types of all columns in the table
await table.logTypes();

logUniques

Logs unique values for a specified column to the console. By default, a maximum of 100 values are logged (depending on your runtime). You can optionally stringify the values to see them all.

Signature
async logUniques(column: string, options?: { stringify?: boolean }): Promise<this>;
Parameters
  • column: The name of the column from which to retrieve and log unique values.
  • options: An optional object with configuration options:
  • options.stringify: If true, converts the unique values to a JSON string before logging. Defaults to false.
Returns

A promise that resolves to the SimpleTable instance after logging the unique values.

Examples
// Logs unique values for the column "name"
await table.logUniques("name");

// Logs unique values for the column "name" and stringifies them
await table.logUniques("name", { stringify: true });

logColumns

Logs the columns in the table to the console. You can optionally include their data types.

Signature
async logColumns(options?: { types?: boolean }): Promise<this>;
Parameters
  • options: An optional object with configuration options:
  • options.types: If true, logs the column names along with their data types. Defaults to false.
Returns

A promise that resolves to the SimpleTable instance after logging the columns.

Examples
// Log only the column names
await table.logColumns();

// Log column names along with their types
await table.logColumns({ types: true });

logNbRows

Logs the total number of rows in the table to the console.

Signature
async logNbRows(): Promise<this>;
Returns

A promise that resolves to the SimpleTable instance after logging the row count.

Examples
// Log the total number of rows in the table
await table.logNbRows();

logBottom

Logs the bottom n rows of the table to the console. By default, the last row will be returned first. To preserve the original order, use the originalOrder option.

Signature
async logBottom(count?: number, options?: { originalOrder?: boolean }): Promise<void>;
Parameters
  • count: The number of rows to log from the bottom of the table. Defaults to the table's nbRowsToLog option if not specified.
  • options: An optional object with logging preferences.
  • options.originalOrder: If true, the rows are displayed in their original order (top to bottom). Defaults to false.
Returns

A promise that resolves when the rows have been logged to the console.

Examples
// Log bottom rows with default count (uses table's nbRowsToLog option)
await table.logBottom();

// Log the last 10 rows (displayed with last row first)
await table.logBottom(10);

// Log the last 5 rows in original order (top to bottom)
await table.logBottom(5, { originalOrder: true });

logExtent

Logs the extent (minimum and maximum values) of a numeric column to the console.

Signature
async logExtent(column: string): Promise<void>;
Parameters
  • column: The name of the numeric column for which to log the extent.
Returns

A promise that resolves when the column extent has been logged to the console.

Examples
// Log the extent of the 'price' column
await table.logExtent("price");

Examples

// Create a SimpleDB instance (in-memory by default)
const sdb = new SimpleDB();

// Create a new table named "employees" within the database
const employees = sdb.newTable("employees");

// Load data from a CSV file into the "employees" table
await employees.loadData("./employees.csv");

// Log the first few rows of the "employees" table to the console
await employees.logTable();

// Close the database connection and free up resources
await sdb.done();

// Handling geospatial data
// Create a SimpleDB instance
const sdb = new SimpleDB();

// Create a new table for geospatial data
const boundaries = sdb.newTable("boundaries");

// Load geospatial data from a GeoJSON file
await boundaries.loadGeoData("./boundaries.geojson");

// Close the database connection
await sdb.done();