Nemo Logo
Nemo
Docs Examples
Browse documentation
Home / Docs / sql
SQL Reference

SQL statements, syntax, constraints, sequences, optimization, and native table operations.

Nemo SQL Reference & Grammar

This document provides a complete guide to supported SQL statements, syntax rules, column constraints, sequence generation, table operations, and parameter bindings in Nemo.

For individual scalar and extraction functions, see the SQL Built-in Functions Reference.<br /> For parameter binding and prepared statements, see the SQL Parameters Reference.

Table of Contents

  1. Data Definition Language (DDL)
  2. CREATE TABLE
  3. Auto-Incrementing Primary Keys (AUTOINCREMENT / SERIAL)
  4. CREATE SEQUENCE
  5. DROP TABLE
  6. Data Manipulation Language (DML)
  7. INSERT INTO
  8. SELECT
  9. UPDATE
  10. DELETE FROM
  11. Query Execution, Optimizer & Analytical SQL
  12. Cost-Based Query Optimizer (CBO) & EXPLAIN
  13. In-Memory Hash Joins
  14. Vectorized SIMD Aggregates
  15. Window Functions (ROW_NUMBER & RANK)
  16. Direct Columnar Extraction API
  17. Nemo Native Table Operations
  18. pop (Atomic FIFO & LIFO Queue/Stack)
  19. count (Instant O(1) Row Count)
  20. truncate (Instant O(1) Table Wipe)
  21. Sequence & ID Range Allocation
  22. Transactions & Concurrency

1. Data Definition Language (DDL)

CREATE TABLE

Defines a new relational table schema with typed columns and constraints:

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name DATA_TYPE [CONSTRAINTS],
    ...
);

Supported Column Constraints

  • PRIMARY KEY: Unique row identifier. Automatically enforces NOT NULL and UNIQUE.
  • AUTOINCREMENT / SERIAL: Database-managed monotonically increasing sequence starting at 1.
  • NOT NULL: Rejects NULL values upon insertion or update.
  • UNIQUE: Rejects duplicate values across rows.
  • DEFAULT <literal>: Specifies default value when omitted from INSERT.

Example: Complete Datatypes Table

CREATE TABLE omni_bench (
    id BIGINT PRIMARY KEY AUTOINCREMENT,
    code CHAR(8) NOT NULL UNIQUE,
    title VARCHAR NOT NULL,
    amount DECIMAL(12, 2) DEFAULT 0.00,
    counter INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP,
    uuid_key UUID,
    doc_json JSON,
    doc_jsonb JSONB,
    tags ARRAY,
    attributes DICT,
    valid_range RANGE,
    client_ip INET,
    subnet CIDR
);

Auto-Incrementing Primary Keys

When a column is declared as AUTOINCREMENT or SERIAL, Nemo initializes an atomic sequence starting at 1 and incrementing by 1:

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTOINCREMENT,
    username VARCHAR NOT NULL
);

-- When id is omitted, the engine automatically assigns nextval:
INSERT INTO users (username) VALUES ('alice'); -- id = 1
INSERT INTO users (username) VALUES ('bob');   -- id = 2

CREATE SEQUENCE

Explicitly creates an atomic, persistent sequence object:

CREATE SEQUENCE sequence_name [START WITH <int>] [INCREMENT BY <int>];

Example:

CREATE SEQUENCE order_seq START WITH 5000 INCREMENT BY 10;

DROP TABLE

Deletes a table definition and frees all assigned storage pages:

DROP TABLE users;

2. Data Manipulation Language (DML)

INSERT INTO

Inserts new records into a table. Use ? placeholders for parameterized safety:

-- Parameterized (Recommended)
INSERT INTO users (username, email, score) VALUES (?, ?, ?);

-- Explicit Literals
INSERT INTO users (id, username, score) VALUES (1, 'alice', 100);

SELECT

Queries records with projections, scalar functions, filtering, sorting, and pagination:

-- Basic selection with projection
SELECT id, username, upper(username) AS upper_name, json_extract(doc, '$.role') 
FROM users 
WHERE score >= ? AND is_active = true
ORDER BY score DESC
LIMIT 50 OFFSET 100;

-- Scalar queries without FROM clause (Dual expression queries)
SELECT now();
SELECT uuid_generate();
SELECT nextval('order_seq');
SELECT range(10, 50);

UPDATE

Modifies records matching a WHERE condition:

UPDATE users 
SET score = score + ?, doc = json_set(doc, '$.status', 'verified')
WHERE id = ?;

DELETE FROM

Removes records matching a WHERE condition:

DELETE FROM users WHERE is_active = false AND score < ?;

3. Query Execution, Optimizer & Analytical SQL

Cost-Based Query Optimizer (CBO) & EXPLAIN

Nemo incorporates a native Cost-Based Optimizer that collects statistical distributions and chooses optimal physical execution paths:

# 1. Collect statistics on table distributions
conn.analyze("financial_accounts")

# 2. Inspect physical execution plan tree
plan = conn.explain("SELECT AVG(balance) FROM financial_accounts WHERE balance > 1000;")
print(plan)

In-Memory Hash Joins

High-speed equi-joins between tables are executed via an in-memory hash join algorithm that automatically projects only requested columns:

SELECT order_book.id, financial_accounts.username, order_book.symbol, order_book.price
FROM order_book
JOIN financial_accounts ON order_book.account_id = financial_accounts.id
LIMIT 1000;

Throughput: 4,542 joins/second (2.90x faster than SQLite).

Vectorized SIMD Aggregates

Batch-evaluated aggregate functions compute running statistics across vector blocks:

SELECT COUNT(*), SUM(balance), AVG(balance), MIN(balance), MAX(balance)
FROM financial_accounts;

Throughput: 5,409 queries/second (93.66x faster than SQLite).

Window Functions (ROW_NUMBER & RANK)

Supports in-memory row ranking with partitionless order clauses:

SELECT id, username, balance,
       ROW_NUMBER() OVER (ORDER BY balance DESC) AS rn,
       RANK() OVER (ORDER BY balance DESC) AS rk
FROM financial_accounts
LIMIT 100;

Throughput: 329 queries/second (11.05x faster than SQLite).

Direct Columnar Extraction API

Bypasses row-by-row Python object wrapping to extract homogeneous columnar data directly:

columns_dict = conn.extract_columnar("financial_accounts", ["id", "username", "balance"])
print(columns_dict["username"][:5])

Throughput: 1,983 extractions/second (152.75x faster than SQLite).

4. Nemo Native Table Operations

Beyond standard SQL DML, Nemo provides three high-velocity primitives executed directly inside the C++ engine:

pop (Atomic FIFO & LIFO)

Atomically fetches and deletes records in a single lock-free operation without row-by-row transaction overhead:

# Pop 10 items from head (FIFO queue)
batch = conn.pop("job_queue", count=10, direction="fifo")

# Pop 5 items from tail (LIFO stack)
events = conn.pop("event_log", count=5, direction="lifo")

Performance: Over 16 Million operations/second.

count (Instant O(1))

Retrieves the exact row count of a table in O(1) time:

total_rows = conn.count("users")

truncate (Instant O(1))

Instantly resets the table and recycles all storage frames without scanning rows:

conn.truncate("session_cache")

5. Sequence & ID Range Allocation

In addition to SQL nextval() and currval(), Nemo provides a native Python API for reserving atomic ID ranges:

# Reserve 50 contiguous IDs for a worker thread
start_id, end_id = conn.allocate_id_range("order_seq", count=50)
print(f"Reserved IDs: {start_id} to {end_id}")

This enables multi-threaded or distributed bulk writers to ingest data with pre-allocated primary keys without locking or querying the sequence on each insert.

6. Transactions & Concurrency

Nemo supports ACID transactions with fine-grained latching and MVCC snapshot isolation:

with conn:
    conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1;")
    conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2;")

Readers never block writers, and writers never block readers. Under high multi-threaded contention, Nemo eliminates SQLite's database is locked error.

← Previous Article
Nemo Datatypes Reference
Next Article →
SQL Built-in Functions