Skip to content

Quick Start

This guide will get you up and running with DecentDB in 5 minutes.

Create Your First Database

# Create a database file (automatically created on first access)
# Note: auto-increment works for a single INT64 PRIMARY KEY column (spelling INT/INTEGER/INT64 doesn’t matter).
decentdb exec --db=myapp.ddb --sql="CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"

Insert Data

# Insert a single row (id is auto-assigned)
decentdb exec --db=myapp.ddb --sql="INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')"

# Insert with explicit id
decentdb exec --db=myapp.ddb --sql="INSERT INTO users VALUES (10, 'Bob', 'bob@example.com')"

# Insert with parameters (id auto-assigned)
decentdb exec --db=myapp.ddb --sql="INSERT INTO users (name, email) VALUES (\$1, \$2)" \
  --params=text:Carol --params=text:carol@example.com

Query Data

# Select all users
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users"

# Select with WHERE clause
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE id = 1"

# Update a user
decentdb exec --db=myapp.ddb --sql="UPDATE users SET name = 'Alice Smith' WHERE id = 1"

# Delete a user
decentdb exec --db=myapp.ddb --sql="DELETE FROM users WHERE id = 2"

Create Indexes

# Create a regular index
decentdb exec --db=myapp.ddb --sql="CREATE INDEX idx_users_email ON users(email)"

# Create a trigram index for text search
decentdb exec --db=myapp.ddb --sql="CREATE INDEX idx_users_name_trgm ON users USING trigram(name)"

# Search with trigram index
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE name LIKE '%ali%'"

Schema Management

# List all tables
decentdb list-tables --db=myapp.ddb

# Describe a table
decentdb describe --db=myapp.ddb --table=users

# List indexes
decentdb list-indexes --db=myapp.ddb

# Drop a table
decentdb exec --db=myapp.ddb --sql="DROP TABLE users"

Transactions

# Execute multiple statements atomically
decentdb exec --db=myapp.ddb --sql="BEGIN; INSERT INTO users VALUES (3, 'Carol', 'carol@example.com'); INSERT INTO users VALUES (4, 'Dave', 'dave@example.com'); COMMIT"

Advanced Queries

# Aggregates and GROUP BY
decentdb exec --db=myapp.ddb --sql="SELECT COUNT(*) FROM users"

# DISTINCT values
decentdb exec --db=myapp.ddb --sql="SELECT DISTINCT name FROM users"

# JOINs
decentdb exec --db=myapp.ddb --sql="SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id"

# Upsert (insert or update on conflict)
decentdb exec --db=myapp.ddb --sql="INSERT INTO users (id, name) VALUES (1, 'Alice Updated') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name"

# INSERT RETURNING (get auto-assigned id)
decentdb exec --db=myapp.ddb --sql="INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com') RETURNING id"

Bulk Loading

For large datasets, use the bulk load API:

# Create a CSV file
cat > users.csv << EOF
id,name,email
1,Alice,alice@example.com
2,Bob,bob@example.com
3,Carol,carol@example.com
EOF

# Import the CSV
decentdb import --db=myapp.ddb --table=users --input=users.csv

Checkpoint and Maintenance

# Force a WAL checkpoint
decentdb checkpoint --db=myapp.ddb

# Get database statistics
decentdb stats --db=myapp.ddb

Next Steps

In-Memory Database

DecentDB supports purely in-memory databases using the :memory: connection string:

import decentdb/engine

let res = openDb(":memory:")
if res.ok:
  let db = res.value
  # db is entirely in memory
  discard db.closeDb()

Each call to openDb(":memory:") creates a new, isolated in-memory database instance. They do not share data, even within the same process. Detection is case-insensitive (:memory:, :MEMORY:, :Memory: all work).

Note: mmap is not supported for in-memory databases, but due to being backed by memory, read and write speeds are virtually instantaneous. In-memory databases do not persist across restarts and are primarily intended for caching or fast unit testing.

Exporting to Disk (SaveAs)

Use saveAs to persist an in-memory database to a durable on-disk file:

let db = openDb(":memory:").value
discard execSql(db, "CREATE TABLE items (id INT PRIMARY KEY, name TEXT)")
discard execSql(db, "INSERT INTO items (id, name) VALUES (1, 'widget')")

let res = saveAs(db, "/tmp/snapshot.ddb")
assert res.ok
discard closeDb(db)

saveAs works with all bindings:

Binding Method
Nim saveAs(db, destPath)
CLI decentdb save-as --db=:memory: --output=dest.ddb
.NET connection.SaveAs(destPath)
Go db.SaveAs(destPath)
Node db.saveAs(destPath)
Python conn.save_as(dest_path)