Skip to content

First Steps

Now that you have DecentDB installed, let's explore the basics.

Creating Your First Database

DecentDB databases are single files, making them easy to manage:

# The database file is created automatically when you run your first command
# 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)"

Basic Operations

Inserting Data

# Single row (id is auto-assigned when omitted)
decentdb exec --db=myapp.ddb --sql="INSERT INTO users (name) VALUES ('Alice')"

# With explicit id
decentdb exec --db=myapp.ddb --sql="INSERT INTO users VALUES (10, 'Bob')"

# With parameters (id auto-assigned)
decentdb exec --db=myapp.ddb --sql="INSERT INTO users (name) VALUES (\$1)" --params=text:Carol

Querying Data

# All rows
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users"

# With filter
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE id = 1"

# Pattern matching
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE name LIKE 'A%'"

# Case-insensitive pattern matching
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE name ILIKE '%alice%'"

Updating and Deleting

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

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

Working with Multiple Tables

# Create tables with foreign keys
decentdb exec --db=myapp.ddb --sql="CREATE TABLE artists (id INTEGER PRIMARY KEY, name TEXT)"
decentdb exec --db=myapp.ddb --sql="CREATE TABLE albums (id INTEGER PRIMARY KEY, artist_id INTEGER REFERENCES artists(id) ON DELETE CASCADE, title TEXT)"

# Insert related data
# (the first inserted artist will have id = 1)
decentdb exec --db=myapp.ddb --sql="INSERT INTO artists (name) VALUES ('The Beatles')"
decentdb exec --db=myapp.ddb --sql="INSERT INTO albums (artist_id, title) VALUES (1, 'Abbey Road')"

Joining Tables

# Join query
decentdb exec --db=myapp.ddb --sql="SELECT artists.name, albums.title FROM artists JOIN albums ON artists.id = albums.artist_id"

Using Indexes

Creating Indexes

# B-tree index for fast lookups
decentdb exec --db=myapp.ddb --sql="CREATE INDEX idx_users_name ON users(name)"

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

Using Indexed Queries

# Fast exact match (uses index)
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE name = 'Alice'"

# Fast pattern search (uses trigram index)
decentdb exec --db=myapp.ddb --sql="SELECT * FROM users WHERE name LIKE '%lic%'"

Transactions

Group multiple operations into atomic transactions.

decentdb exec --sql can contain multiple ;-separated statements (they run on a single connection). However, all statements are parsed/bound up front against the schema at the start of the call, so DDL followed by dependent statements in the same --sql string can fail (e.g. CREATE TABLE ...; INSERT INTO that_table ...;). For schema changes + dependent statements, use separate exec calls or the REPL.

Example (table already exists):

decentdb exec --db=myapp.ddb --sql="BEGIN; INSERT INTO users (name) VALUES ('Dave'); INSERT INTO users (name) VALUES ('Eve'); COMMIT;"

For interactive transactions (and for DDL followed by dependent statements), use decentdb repl:

decentdb repl --db=myapp.ddb

Then in the REPL:

BEGIN;
INSERT INTO users (name) VALUES ('Dave');
INSERT INTO users (name) VALUES ('Eve');
COMMIT;
-- or ROLLBACK;

Schema Management

Viewing Schema

# 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

Modifying Schema

# Add a column
decentdb exec --db=myapp.ddb --sql="ALTER TABLE users ADD COLUMN email TEXT"

# Rename a column
decentdb exec --db=myapp.ddb --sql="ALTER TABLE users RENAME COLUMN name TO full_name"

# Change a column type
decentdb exec --db=myapp.ddb --sql="ALTER TABLE users ALTER COLUMN full_name TYPE TEXT"

# Drop a column
decentdb exec --db=myapp.ddb --sql="ALTER TABLE users DROP COLUMN email"

# Drop an index
decentdb exec --db=myapp.ddb --sql="DROP INDEX idx_users_name"

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

Next Steps