Skip to content

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::{build_select, build_insert, build_update, …}
15 functions Database
/ filter jk navigate Esc clear
Functions (15)
  1. build_select Builds a SELECT SQL statement
  2. build_insert Builds an INSERT SQL statement
  3. build_update Builds an UPDATE SQL statement
  4. build_delete Builds a DELETE SQL statement
  5. build_upsert Builds an INSERT ... ON CONFLICT upsert
  6. build_create_table Builds a CREATE TABLE statement
  7. build_drop_table Builds a DROP TABLE statement
  8. build_alter_add_column Builds an ALTER TABLE ADD COLUMN statement
  9. build_join Builds a SELECT with JOIN clause
  10. build_aggregate Builds an aggregate SELECT statement
  11. build_copy_csv Builds a COPY ... FROM CSV statement
  12. parameterize Substitutes $1, $2, ... placeholders
  13. escape_string Escapes single quotes in a string value
  14. quote_identifier Wraps an identifier in double quotes
  15. format_result_table Formats query result rows as ASCII table

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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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 *).

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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.

zolo▶ Playground
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
enespt-br