duckdb

nim c -r examples/crud.nim

import duckdb

# Persistent database stored on disk
var db = duckdb.open("my_database.duckdb")
var conn = db.connect()
conn.exec(sql"CREATE TABLE IF NOT EXISTS t (i INTEGER);")
conn.disconnect()
db.close()

# In-memory database (lives entirely in RAM, no file is created)
var memoryDb = duckdb.open(":memory:")
var memoryConn = memoryDb.connect()
memoryConn.exec(sql"SELECT 42;")
memoryConn.disconnect()
memoryDb.close()

import duckdb

var db = duckdb.open(":memory:")
var conn = db.connect()

conn.exec(sql"""
  CREATE TABLE IF NOT EXISTS users (
    id   INTEGER,
    name VARCHAR,
    age  INTEGER
  );
""")
conn.exec(sql"INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Carol', 35);")

let res = conn.getAllRows(sql"SELECT * FROM users ORDER BY id;")
echo "columns: ", res.columns
for row in res.rows:
  echo row

conn.disconnect()
db.close()

import duckdb

var db = duckdb.open(":memory:")
var conn = db.connect()

conn.exec(sql"CREATE TABLE accounts (name VARCHAR, balance INTEGER);")
conn.exec(sql"INSERT INTO accounts VALUES ('Alice', 100);")

# Commit the change
conn.exec(sql"BEGIN TRANSACTION;")
conn.exec(sql"UPDATE accounts SET balance = balance - 20 WHERE name = 'Alice';")
conn.exec(sql"COMMIT;")

# Roll back the change
conn.exec(sql"BEGIN TRANSACTION;")
conn.exec(sql"UPDATE accounts SET balance = balance + 100 WHERE name = 'Alice';")
conn.exec(sql"ROLLBACK;")

let res = conn.getAllRows(sql"SELECT * FROM accounts;")
for row in res.rows:
  echo row # Alice is still at 80, the ROLLBACK discarded the +100

conn.disconnect()
db.close()

import duckdb

var db = duckdb.open(":memory:")
var conn = db.connect()

conn.exec(sql"CREATE TABLE users (id INTEGER, name VARCHAR);")
conn.exec(sql"INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');")

# Prepare a statement with a parameter placeholder
var stmt: duckdb_prepared_statement
if DuckDBSuccess != duckdb_prepare(conn, sql"SELECT * FROM users WHERE name = ?;".cstring, stmt.addr):
  raise newException(DuckDBQueryError, "Failed to prepare statement")

# Bind the parameter and execute
if DuckDBSuccess != duckdb_bind_varchar(stmt, 1, "Bob".cstring):
  raise newException(DuckDBQueryError, "Failed to bind parameter")

var res: duckdb_result
if DuckDBSuccess == duckdb_execute_prepared(stmt, res.addr):
  for i in 0 ..< duckdb_row_count(res.addr):
    var row: Row
    for j in 0 ..< duckdb_column_count(res.addr):
      row.add(getDuckDBValue(res.addr, j, i))
    echo row
  duckdb_destroy_result(res.addr)

duckdb_destroy_prepare(stmt.addr)
conn.disconnect()
db.close()

import duckdb

var db = duckdb.open(":memory:")
var conn = db.connect()

# Query JSON documents directly with DuckDB's read_json table function
let res = conn.getAllRows(sql"SELECT * FROM read_json('tests/data/01.json');")
echo "columns: ", res.columns
for row in res.rows:
  echo row

conn.disconnect()
db.close()

import std/os
import duckdb

var db = duckdb.open(":memory:")
var conn = db.connect()

# Write a small CSV file to disk
writeFile("people.csv", """id,name,age
1,Alice,30
2,Bob,25
3,Carol,35
""")

# Read it back with DuckDB
let csv = conn.getAllRows(sql"SELECT * FROM read_csv_auto('people.csv');")
echo "csv columns: ", csv.columns
for row in csv.rows:
  echo row

# Export the same data to Parquet, then read it back
conn.exec(sql"COPY (SELECT * FROM read_csv_auto('people.csv')) TO 'people.parquet' (FORMAT PARQUET);")

let parquet = conn.getAllRows(sql"SELECT * FROM read_parquet('people.parquet') WHERE age > 28;")
echo "parquet rows (age > 28):"
for row in parquet.rows:
  echo row

conn.disconnect()
db.close()

removeFile("people.csv")
removeFile("people.parquet")

import duckdb

var db = duckdb.open(":memory:")
var conn = db.connect()

# tryExec returns true on success, false on failure
if not conn.tryExec(sql"CREATE TABLE t (i INTEGER);"):
  echo "create failed"

if not conn.tryExec(sql"THIS IS NOT VALID SQL;"):
  echo "expected: invalid statement rejected"

# exec raises DuckDBQueryError on failure
try:
  conn.exec(sql"SELECT * FROM does_not_exist;")
except DuckDBQueryError as e:
  echo "caught: ", e.msg

conn.disconnect()
db.close()