duckdb

stable

SQL query builder and formatting utilities for DuckDB, covering SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, JOIN, UPSERT, aggregates, CSV import, and result formatting.

use plugin duckdb

15 functions Database

Runs with the native Zolo host

Plugin snippets can be edited in the Playground, but native capabilities are executed locally with the Zolo CLI.

/ filter jk navigate Esc clear

Functions (15)

build_select Builds a SELECT SQL statement
build_insert Builds an INSERT SQL statement
build_update Builds an UPDATE SQL statement
build_delete Builds a DELETE SQL statement
build_upsert Builds an INSERT ... ON CONFLICT upsert
build_create_table Builds a CREATE TABLE statement
build_drop_table Builds a DROP TABLE statement
build_alter_add_column Builds an ALTER TABLE ADD COLUMN statement
build_join Builds a SELECT with JOIN clause
build_aggregate Builds an aggregate SELECT statement
build_copy_csv Builds a COPY ... FROM CSV statement
parameterize Substitutes $1, $2, ... placeholders
escape_string Escapes single quotes in a string value
quote_identifier Wraps an identifier in double quotes
format_result_table Formats query result rows as ASCII table

From import to result

01Import only what you use
02Copy the smallest example
03Run it with the Zolo CLI

build_select(table, columns?, where_str?, order_by?, limit?) → string

Builds a SELECT SQL statement

Builds a SELECT statement. columns is an array table of column name strings (defaults to *). All other parameters are optional.

use plugin duckdb::{build_select}

let sql = build_select("users")
// "SELECT * FROM users;"

let filtered = build_select("orders", ["id", "total"], "status = 'paid'", "created_at DESC", 100)
// "SELECT id, total FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 100;"

build_insert(table, columns, values) → string

Builds an INSERT SQL statement

Builds an INSERT statement. columns is an array table of column name strings. values is a table of row tables, each containing the values in column order.

use plugin duckdb::{build_insert}

let sql = build_insert("users",
  ["name", "email", "age"],
  [
    ["Alice", "alice@example.com", 30],
    ["Bob",   "bob@example.com",   25]
  ]
)
print(sql)
// INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30), ('Bob', 'bob@example.com', 25);

build_update(table, set, where_str?) → string

Builds an UPDATE SQL statement

Builds an UPDATE statement. set is a table of column: value pairs.

use plugin duckdb::{build_update}

let sql = build_update("users", #{"name": "Carol", "age": 31}, "id = 5")
// "UPDATE users SET name = 'Carol', age = 31 WHERE id = 5;"

let reset = build_update("sessions", #{"active": false})
// "UPDATE sessions SET active = false;"

build_delete(table, where_str?) → string

Builds a DELETE SQL statement

Builds a DELETE statement. Without a where_str, deletes all rows.

use plugin duckdb::{build_delete}

let sql = build_delete("logs", "created_at < '2024-01-01'")
// "DELETE FROM logs WHERE created_at < '2024-01-01';"

let clear = build_delete("temp_data")
// "DELETE FROM temp_data;"

build_upsert(table, columns, values, conflict_columns) → string

Builds an INSERT ... ON CONFLICT upsert

Builds an INSERT INTO ... ON CONFLICT (...) DO UPDATE SET ... upsert statement. Non-conflict columns are updated using EXCLUDED.column.

use plugin duckdb::{build_upsert}

let sql = build_upsert(
  "products",
  ["sku", "name", "price"],
  [["SKU-001", "Widget", 9.99]],
  ["sku"]
)
// INSERT INTO products (sku, name, price) VALUES ('SKU-001', 'Widget', 9.99)
// ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

build_create_table(name, columns) → string

Builds a CREATE TABLE statement

Builds a CREATE TABLE statement. columns is a table of {name, type} entries.

use plugin duckdb::{build_create_table}

let sql = build_create_table("events", [
  #{"name": "id",         "type": "INTEGER PRIMARY KEY"},
  #{"name": "name",       "type": "VARCHAR"},
  #{"name": "created_at", "type": "TIMESTAMP"}
])
// "CREATE TABLE events (id INTEGER PRIMARY KEY, name VARCHAR, created_at TIMESTAMP);"

build_drop_table(name, if_exists?) → string

Builds a DROP TABLE statement

Builds a DROP TABLE statement. Set if_exists to true to add IF EXISTS.

use plugin duckdb::{build_drop_table}

print(build_drop_table("temp_results", true))
// "DROP TABLE IF EXISTS temp_results;"

build_alter_add_column(table, column_name, column_type) → string

Builds an ALTER TABLE ADD COLUMN statement

Builds an ALTER TABLE ADD COLUMN statement.

use plugin duckdb::{build_alter_add_column}

let sql = build_alter_add_column("users", "last_login", "TIMESTAMP")
// "ALTER TABLE users ADD COLUMN last_login TIMESTAMP;"

build_join(left_table, right_table, join_type?, on_clause, columns?, where_str?) → string

Builds a SELECT with JOIN clause

Builds a SELECT with a JOIN. join_type defaults to "INNER". columns is an optional array of column names (defaults to *).

use plugin duckdb::{build_join}

let sql = build_join("orders", "users", "LEFT", "orders.user_id = users.id",
  ["orders.id", "users.name", "orders.total"])
// "SELECT orders.id, users.name, orders.total FROM orders LEFT JOIN users ON orders.user_id = users.id;"

build_aggregate(table, agg_func, column, group_by?) → string

Builds an aggregate SELECT statement

Builds an aggregate SELECT (e.g. SUM, COUNT, AVG). Optionally groups by a column.

use plugin duckdb::{build_aggregate}

print(build_aggregate("sales", "SUM", "amount"))
// "SELECT SUM(amount) AS result FROM sales;"

print(build_aggregate("orders", "COUNT", "*", "status"))
// "SELECT COUNT(*) AS result, status FROM orders GROUP BY status;"

build_copy_csv(table, path, header?) → string

Builds a COPY ... FROM CSV statement

Builds a COPY ... FROM statement for importing a CSV file. header defaults to true.

use plugin duckdb::{build_copy_csv}

let sql = build_copy_csv("products", "/data/products.csv")
// "COPY products FROM '/data/products.csv' (FORMAT CSV, HEADER true);"

parameterize(sql, params) → string

Substitutes $1, $2, ... placeholders

Replaces positional placeholders $1, $2, etc. in a SQL string with the values from a table. String values are single-quoted and escaped.

use plugin duckdb::{parameterize}

let sql = parameterize("SELECT * FROM users WHERE id = $1 AND role = $2", [42, "admin"])
// "SELECT * FROM users WHERE id = 42 AND role = 'admin'"

escape_string(str) → string

Escapes single quotes in a string value

Escapes single quotes in a string by doubling them, making it safe to embed in a SQL string literal.

use plugin duckdb::{escape_string}

let safe = escape_string("it's a test")
// "it''s a test"

quote_identifier(str) → string

Wraps an identifier in double quotes

Wraps an identifier (table name, column name) in double quotes, escaping any internal double quotes by doubling them.

use plugin duckdb::{quote_identifier}

print(quote_identifier("my table"))   // "\"my table\""
print(quote_identifier("user\"id"))   // "\"user\"\"id\""

format_result_table(rows, columns) → string

Formats query result rows as ASCII table

Formats query result rows as a human-readable ASCII table with aligned columns and border separators. rows is a table of row tables; columns is an array of column name strings.

use plugin duckdb::{format_result_table}

let rows = [
  ["Alice", "30", "admin"],
  ["Bob",   "25", "user"]
]
let out = format_result_table(rows, ["name", "age", "role"])
print(out)

View source code