# OverDrive-DB SQL Reference
> **Complete SQL dialect reference for OverDrive-DB v1.0**
---
## Table of Contents
1. [Data Types & JSON Model](#data-types--json-model)
2. [Database Commands](#database-commands)
3. [Table Commands](#table-commands)
4. [Data Manipulation (CRUD)](#data-manipulation-crud)
5. [Query Features](#query-features)
6. [Transaction Commands](#transaction-commands)
7. [Version Control](#version-control)
8. [Security & Access Control](#security--access-control)
9. [Backup & Restore](#backup--restore)
10. [Utility Commands](#utility-commands)
11. [Connection](#connection)
---
## Data Types & JSON Model
OverDrive-DB is a **document-oriented database** where every record is a JSON object. There is no rigid schema — each record can have different fields.
### Automatic Fields
Every inserted record automatically receives:
| `_id` | `string` | Unique record identifier (UUID) |
| `_ver` | `integer` | Version counter (increments on update) |
| `_ts` | `integer` | Timestamp of last modification |
### Supported JSON Value Types
| String | `"hello world"` |
| Number | `42`, `3.14`, `-1` |
| Boolean | `true`, `false` |
| Null | `null` |
| Array | `[1, 2, 3]` |
| Object | `{"nested": {"key": "value"}}` |
---
## Database Commands
### SHOW DATABASES
Lists all databases on the server.
```sql
SHOW DATABASES;
SHOW DBS; -- alias
SHOW DB; -- alias
```
### CREATE DATABASE
Creates a new `.odb` database file.
```sql
CREATE DATABASE <name>;
CREATE DBS <name>; -- alias
CREATE DB <name>; -- alias
```
**Example:**
```sql
CREATE DB myapp;
-- ✅ Database 'myapp' created successfully
```
### USE
Switches the active database context.
```sql
USE <name>;
```
**Example:**
```sql
USE myapp;
-- ✅ Now using database 'myapp'
-- Prompt changes to: OverDrive [myapp]>
```
### DROP DATABASE
Permanently deletes a database file.
```sql
DROP DATABASE <name>;
DROP DBS <name>; -- alias
DROP DB <name>; -- alias
```
> **⚠️ Warning:** This permanently deletes the `.odb` file and all data inside it.
---
## Table Commands
### SHOW TABLES
Lists all tables in the current database.
```sql
SHOW TABLES;
SHOW TABLE; -- alias
SHOW TB; -- alias
```
**Requires:** An active database (`USE <db>` first).
### CREATE TABLE
Creates a new table. Three modes are available:
#### Unstructured (Schema-free)
```sql
CREATE TABLE <name>;
CREATE TB <name>; -- alias
```
#### Structured (With Schema)
```sql
CREATE TABLE <name> {
<field>: <type>,
<field>: <type>
};
```
Supported types: `string`, `int`, `float`, `bool`
#### From Template
```sql
CREATE TABLE <name> FROM TEMPLATE <template>;
```
Available templates:
| `USERS` | email, password_hash, created_at, roles |
| `SESSIONS` | session_id, token, expires (24h) |
| `OTP` | phone, code, expires (5min), max_attempts (3) |
**Example:**
```sql
CREATE TB products {name: string, price: float, stock: int};
CREATE TB auth FROM TEMPLATE USERS;
```
> **Note:** Creating a table that already exists is idempotent (no error).
### DESCRIBE
Shows table schema and metadata.
```sql
DESCRIBE <table>;
DESC <table>; -- alias
```
### DROP TABLE
Deletes a table and all its records.
```sql
DROP TABLE <name>;
DROP TB <name>; -- alias
```
> **Note:** Dropping a nonexistent table is idempotent (no error).
---
## Data Manipulation (CRUD)
### INSERT INTO
Inserts a JSON record into a table.
```sql
INSERT INTO <table> {<json>};
```
**Examples:**
```sql
INSERT INTO users {
name: "Alice",
email: "alice@example.com",
age: 30,
active: true
};
-- ✅ 1 row inserted (returns generated _id)
-- Nested objects and arrays
INSERT INTO products {
name: "Widget",
tags: ["sale", "new"],
specs: {weight: 1.5, color: "blue"}
};
```
> **Note:** Inserting into a nonexistent table auto-creates it.
### SELECT
Retrieves records from a table.
```sql
-- All records
SELECT * FROM <table>;
-- With WHERE filter
SELECT * FROM <table> WHERE <condition>;
-- With specific columns
SELECT <col1>, <col2> FROM <table>;
-- Full query with all clauses
SELECT * FROM <table>
WHERE <condition>
GROUP BY <column>
```
#### WHERE Conditions
| `=` | `WHERE name = "Alice"` | Equals |
| `!=` | `WHERE status != "deleted"` | Not equals |
| `>` | `WHERE age > 21` | Greater than |
| `<` | `WHERE price < 100` | Less than |
| `>=` | `WHERE score >= 90` | Greater or equal |
| `<=` | `WHERE count <= 5` | Less or equal |
**Examples:**
```sql
SELECT * FROM users WHERE age > 25;
SELECT name, email FROM users WHERE active = true;
SELECT * FROM products WHERE price < 50 ORDER BY price ASC;
SELECT * FROM logs LIMIT 10;
```
### UPDATE
Updates records matching a condition.
```sql
UPDATE <table> SET {<json>} WHERE <condition>;
```
**Example:**
```sql
UPDATE users SET {
active: false,
deactivated_at: "2026-02-21"
} WHERE email = "alice@example.com";
-- ✅ 1 row updated (_ver incremented)
```
> **Note:** Updates increment `_ver` and update `_ts`.
### DELETE
Removes records matching a condition.
```sql
DELETE FROM <table> WHERE <condition>;
```
**Example:**
```sql
DELETE FROM sessions WHERE expired = true;
-- ✅ 3 rows deleted
```
### GET (by ID)
Retrieve a single record by its `_id`.
```sql
GET <table> <id>;
```
**Example:**
```sql
GET users u001;
-- {"_id": "u001", "name": "Alice", ...}
```
### SEARCH
Full-text search across all fields in a table.
```sql
SEARCH '<query>';
```
**Example:**
```sql
SEARCH 'alice';
-- Found in users:
-- {"_id": "u001", "name": "Alice", "email": "alice@example.com"}
```
---
## Query Features
### Aggregation Functions
Used with `SELECT` queries:
| `COUNT(*)` | `SELECT COUNT(*) FROM <table>` | Total record count |
| `COUNT(<col>)` | `SELECT COUNT(score) FROM <table>` | Non-null value count |
| `SUM(<col>)` | `SELECT SUM(amount) FROM <table>` | Numeric sum |
| `AVG(<col>)` | `SELECT AVG(score) FROM <table>` | Numeric average |
| `MIN(<col>)` | `SELECT MIN(price) FROM <table>` | Minimum value |
| `MAX(<col>)` | `SELECT MAX(price) FROM <table>` | Maximum value |
### GROUP BY
Groups records and applies aggregate functions per group.
```sql
SELECT dept, COUNT(*) FROM employees GROUP BY dept;
```
### ORDER BY
Sorts results by one or more columns.
```sql
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM users ORDER BY dept ASC, name DESC;
```
Directions: `ASC` (ascending, default), `DESC` (descending).
### LIMIT
Restricts the number of returned records.
```sql
SELECT * FROM logs ORDER BY _ts DESC LIMIT 100;
```
### JOIN
Combines records from two tables.
```sql
-- Inner Join
SELECT * FROM users
JOIN orders ON users.id = orders.user_id;
-- Left Join (keeps all left-side rows)
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id;
```
---
## Transaction Commands
### BEGIN / COMMIT / ROLLBACK
```sql
BEGIN; -- Start transaction
BEGIN ISOLATION SERIALIZABLE; -- With isolation level
-- ... make changes ...
COMMIT; -- Persist all changes
ROLLBACK; -- Discard all changes
```
### Savepoints
```sql
BEGIN;
INSERT INTO users {name: "Alice"};
SAVEPOINT sp1;
INSERT INTO users {name: "Bob"};
ROLLBACK TO SAVEPOINT sp1; -- Undo Bob, keep Alice
COMMIT;
```
### Isolation Levels
| `READ UNCOMMITTED` | Possible | Possible | Possible |
| `READ COMMITTED` | No | Possible | Possible |
| `REPEATABLE READ` | No | No | Possible |
| `SERIALIZABLE` | No | No | No |
---
## Version Control
OverDrive-DB has built-in Git-like version control using hash chains.
### HISTORY
Shows commit log.
```sql
HISTORY; -- All commits
HISTORY <table>; -- Table-specific
```
### ROLLBACK TO
Restores database state to a previous commit.
```sql
ROLLBACK TO <commit_id>;
```
### DIFF
Compares two commits.
```sql
DIFF <commit1> <commit2>;
```
### VERIFY
Validates the hash chain integrity.
```sql
VERIFY;
-- ✅ Hash chain verified - 42 commits, all valid
```
---
## Security & Access Control
### Authentication
Connects and authenticates to a server.
```sql
CONNECT '<host>:<port>' USER <username> PASSWORD '<password>' [TLS] [INSECURE];
```
### User Management (Admin only)
```sql
-- Create user
CREATE USER <username> PASSWORD '<password>';
-- Remove user
DROP USER <username>;
-- List users
SHOW USERS;
```
### Role-Based Access Control (RBAC)
```sql
-- Grant role
GRANT <role> TO <username>;
-- Revoke role
REVOKE <role> FROM <username>;
```
#### Built-in Roles
| `admin` | All operations (Read, Write, Delete, CreateTable, DropTable, DropDb, Admin) |
| `user` | Read, Write, CreateTable |
| `readonly` | Read only |
### Security Features
- **Argon2id** password hashing (OWASP recommended)
- **Brute-force protection**: Account locks after 5 failed attempts (30-second lockout)
- **Progressive delays**: Exponential backoff on failed login attempts
- **TLS 1.3** encrypted connections
- **Session tokens**: 24-hour TTL with 30-minute idle timeout
---
## Backup & Restore
### BACKUP
Exports the current database to a JSON file.
```sql
.BACKUP <filename>;
```
### RESTORE
Imports a database from a JSON backup file.
```sql
.RESTORE <filename>;
```
---
## Utility Commands
| `STATS` | - | Show database statistics |
| `HELP` | - | Display command reference |
| `EXPLAIN` | - | Show query execution plan |
| `CLEAR` | `CLS` | Clear screen |
| `EXIT` | `QUIT` | Exit the shell |
### STATS
```sql
STATS;
-- Database: myapp
-- Tables: 5
-- Total Records: 1,234
-- DB Size: 15.3 MB
```
### EXPLAIN
Shows the query optimizer's execution plan.
```sql
EXPLAIN SELECT * FROM users WHERE age > 25;
```
---
## Connection Modes
### Embedded Mode
Direct file access (single process):
```bash
overdrive # Interactive shell
overdrive --db myapp # Open specific database
```
### Client-Server Mode
Multi-client access over the network:
```bash
# Start server
overdrive-serve --port 6969
# Connect client
overdrive --connect "localhost:6969" --user admin --password secret
```
### Connection Flags
| `TLS` | Enable TLS 1.3 encryption (default) |
| `INSECURE` | Skip certificate validation (dev only) |
---
## Command Aliases Quick Reference
| `SHOW DATABASES` | `SHOW DBS`, `SHOW DB` |
| `CREATE DATABASE` | `CREATE DBS`, `CREATE DB` |
| `DROP DATABASE` | `DROP DBS`, `DROP DB` |
| `SHOW TABLES` | `SHOW TABLE`, `SHOW TB` |
| `CREATE TABLE` | `CREATE TABLES`, `CREATE TB` |
| `DROP TABLE` | `DROP TABLES`, `DROP TB` |
| `DESCRIBE` | `DESC` |
| `CLEAR` | `CLS` |
| `EXIT` | `QUIT` |
---
## File Format
- **Extension:** `.odb` (OverDrive Database)
- **Storage:** Binary format with B-tree indexing and WAL (Write-Ahead Log)
- **Default Location:** `databases/` directory relative to the server binary
- **Default Port:** `6969`