Getting started

docker run -d --name nusadb \
  -p 5678:5678 \
  -v nusadb-data:/var/lib/nusadb \
  -e NUSADB_USER=app \
  -e NUSADB_PASSWORD=change-me \
  nusadb/nusadb

git clone https://github.com/nusadb/nusadb.git
cd nusadb
cargo build --release

./target/release/nusadb-server \
  --listen 127.0.0.1:5678 \
  --data-dir ./data \
  --auth-user app:change-me

nusadb-cli                                                  # trust-on-startup server, as nusadb-root
NUSADB_PASSWORD=change-me nusadb-cli --user app             # authenticated

# inside the container, which already has NUSADB_PASSWORD in its environment
docker exec -it nusadb nusadb-cli --user app

nusadb-cli (NusaDB), type \q to quit
connected to 127.0.0.1:5678 as app
SELECT version();
version
------------
NusaDB 0.1.0
SELECT 1

CREATE TABLE customers (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email   TEXT NOT NULL UNIQUE,
  country TEXT NOT NULL,
  joined  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer BIGINT NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  total    NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  placed   TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX orders_customer ON orders (customer);

INSERT INTO customers (email, country) VALUES
  ('ana@example.com',   'ID'),
  ('budi@example.com',  'ID'),
  ('carla@example.com', 'SG')
RETURNING id, email;
--  id |       email
-- ----+--------------------
--   1 | ana@example.com
--   2 | budi@example.com
--   3 | carla@example.com

INSERT INTO orders (customer, total) VALUES
  (1, 120.00), (1, 80.50), (2, 45.00), (3, 900.00);

SELECT c.country,
       count(*)                                   AS orders,
       sum(o.total)                               AS revenue,
       round(avg(o.total), 2)                     AS average,
       rank() OVER (ORDER BY sum(o.total) DESC)   AS by_revenue
FROM   customers c
JOIN   orders    o ON o.customer = c.id
WHERE  o.placed >= now() - INTERVAL '30 days'
GROUP  BY c.country
ORDER  BY revenue DESC;
--  country | orders | revenue | average | by_revenue
-- ---------+--------+---------+---------+------------
--  SG      |      1 |  900.00 |  900.00 |          1
--  ID      |      3 |  245.50 |   81.83 |          2

BEGIN;
  UPDATE orders SET total = total * 1.1 WHERE customer = 1 RETURNING id, total;
  SAVEPOINT before_delete;
  DELETE FROM orders WHERE total < 50;
  ROLLBACK TO SAVEPOINT before_delete;    -- keep the update, undo the delete
COMMIT;

INSERT INTO orders (customer, total) VALUES (999, 10);
-- ERROR 23503: constraint violation: insert or update on "orders" violates foreign key
--              "orders_fkey1": no matching row in "customers"

INSERT INTO customers (email, country) VALUES ('ana@example.com', 'ID');
-- ERROR 23505: constraint violation: duplicate key violates unique constraint
--              "customers_email_key" on (email)

INSERT INTO orders (customer, total) VALUES (1, -5);
-- ERROR 23514: constraint violation: new row for "orders" violates check constraint "orders_check1"

EXPLAIN SELECT email FROM customers WHERE id = 2;
--  Project (1 column(s))
--    Filter
--      IndexScan: customers using customers_pkey

nusadb-cli [OPTIONS]
      --host <HOST>          Server to connect to            [default: 127.0.0.1:5678]
  -u, --user <USER>          User to connect as              [default: nusadb-root]
  -d, --database <DATABASE>  Database to open                [default: nusadb]
  -W, --password <PASSWORD>  Password; prefer NUSADB_PASSWORD in the environment
  -c, --command <COMMAND>    Run one batch of SQL (statements separated by ;) and exit
  -f, --file <FILE>          Run the SQL in a file and exit
  -F, --format <FORMAT>      aligned (default), expanded, csv, or json
      --tls                  Connect using TLS; requires --tls-ca
      --tls-ca <PATH>        PEM certificate to trust (a private CA or self-signed cert); implies --tls
      --tls-domain <NAME>    Server name to verify the certificate against (default: the host)

nusadb-cli -F csv  -c "SELECT id, email FROM customers ORDER BY id LIMIT 2"
# id,email
# 1,ana@example.com
# 2,budi@example.com

nusadb-cli -F json -c "SELECT id, email FROM customers ORDER BY id LIMIT 2"
# [{"id":"1","email":"ana@example.com"},{"id":"2","email":"budi@example.com"}]

nusadb-cli -F expanded -c "SELECT id, email FROM customers ORDER BY id LIMIT 1"
# -[ RECORD 1 ]-
# id    | 1
# email | ana@example.com

CREATE DATABASE app;              -- a separate physical database
CREATE SCHEMA tenant;             -- a namespace inside this database
CREATE TABLE tenant.t (id INT);
SELECT * FROM tenant.t;           -- resolved through search_path, then public

SET search_path = tenant, public;
SELECT * FROM nusadb_databases;   -- what exists (superuser)

nusadb-cli --database app

# Load: tab-delimited, \N for NULL (the server's text format)
nusadb-cli -c "COPY customers FROM STDIN" < rows.tsv

# CSV with a header line, into named columns
nusadb-cli -c "COPY customers (email, country) FROM STDIN WITH (FORMAT csv, HEADER)" < rows.csv

# Export the same way
nusadb-cli -c "COPY customers TO STDOUT" > rows.tsv
nusadb-cli -c "COPY customers TO STDOUT WITH (FORMAT csv, HEADER)" > rows.csv

ANALYZE customers;
ANALYZE;              -- every table

set -eu
nusadb-cli -f schema.sql
nusadb-cli -c "COPY customers FROM STDIN" < customers.tsv
nusadb-cli -c "ANALYZE customers"

import nusadb

conn = nusadb.connect(host="127.0.0.1", port=5678, user="app", password="change-me",
                      database="nusadb")
cur = conn.cursor()
cur.execute("INSERT INTO customers (email, country) VALUES ($1, $2) RETURNING id",
            ["dewi@example.com", "ID"])
print(cur.fetchone())                       # (4,)
cur.execute("SELECT email FROM customers WHERE country = $1", ["ID"])
for (email,) in cur:
    print(email)
conn.close()

const { connect } = require('nusadb');

const conn = await connect({ host: '127.0.0.1', port: 5678, user: 'app',
                             password: 'change-me', database: 'nusadb' });
const res = await conn.query('SELECT id, email FROM customers WHERE country = $1', ['ID']);
console.log(res.rows);          // [[1, 'ana@example.com'], [2, 'budi@example.com']]
await conn.close();

import (
    "database/sql"
    _ "github.com/nusadb/go"
)

db, err := sql.Open("nusadb", "nusadb://app:change-me@127.0.0.1:5678/nusadb")
var email string
err = db.QueryRow("SELECT email FROM customers WHERE id = $1", 1).Scan(&email)

nusadb-server --data-dir ./data \
  --auth-user nusadb-root:root-secret \
  --auth-user app:change-me \
  --auth-user analyst:analyst-secret

-- connected as nusadb-root
CREATE ROLE analyst LOGIN;
GRANT SELECT ON customers, orders TO analyst;

NUSADB_PASSWORD=analyst-secret nusadb-cli --user analyst -c "SELECT count(*) FROM orders"