Alopex DB v0.8.8 Tutorial, Part 2: Querying Articles with SQL

Part 1 stored and retrieved values one key at a time, using keys such as article:1.

The SQL in this part was verified with the published v0.8.8 Python library and the v0.8.8 release verification. Part 5 will run the same SQL against the same data through the embedded, HTTP, gRPC, and cluster-aware entry points.

Key-by-key access has limits. Finding every article with at least 800 views would require reading every key, inspecting each value, and selecting the matches in application code. Part 2 creates a table in the same database and expresses that condition in SQL.

1. Environment

The environment is the same as Part 1.

2. Creating a table

Pass SQL to execute_sql(). Unlike the put and get calls in Part 1, this method can be called directly without first opening a transaction.

from alopex import Database

db = Database.new()

db.execute_sql("""CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  views INTEGER,
  rating REAL
)""")

The rest of the tutorial uses this articles table. Insert five rows.

rows = [
    "(1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
    "(2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
    "(3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
    "(4, '組み込みDBの選び方', 'ren', 600, 4.0)",
    "(5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
]
for row in rows:
    db.execute_sql(f"INSERT INTO articles VALUES {row}")

The rating for id = 5 is NULL, representing an article that has not yet been rated. Later sections use this row to demonstrate NULL handling.

3. SELECT

print(db.execute_sql("SELECT id, title, views FROM articles ORDER BY id"))

[{'id': 1, 'title': 'Rustで書くLSMツリー', 'views': 1200},
 {'id': 2, 'title': 'SQLパーサをNimで実装する', 'views': 800},
 {'id': 3, 'title': 'ベクトル検索の基礎', 'views': 1500},
 {'id': 4, 'title': '組み込みDBの選び方', 'views': 600},
 {'id': 5, 'title': 'WALとクラッシュ復旧', 'views': 300}]

The result is a list of dictionaries. Column names become keys, so a value can be read as row["title"].

ORDER BY id establishes the result order. Without it, row order is not guaranteed. Include an ordering whenever order matters.

4. WHERE and ORDER BY

Write the query for articles with at least 800 views.

print(db.execute_sql(
    "SELECT title, views FROM articles WHERE views >= 800 ORDER BY views DESC"
))

[{'title': 'ベクトル検索の基礎', 'views': 1500},
 {'title': 'Rustで書くLSMツリー', 'views': 1200},
 {'title': 'SQLパーサをNimで実装する', 'views': 800}]

WHERE supplies the condition, and ORDER BY ... DESC sorts the matches in descending order. One statement replaces the key-by-key filtering that application code would otherwise perform.

5. INNER JOIN and LEFT JOIN

Store each author’s team in another table.

db.execute_sql("CREATE TABLE authors (name TEXT, team TEXT)")
db.execute_sql("INSERT INTO authors VALUES ('mio', 'コア')")
db.execute_sql("INSERT INTO authors VALUES ('ren', '検索')")

sora is not registered, which makes the difference between the two joins visible.

Join articles to their authors’ teams.

print(db.execute_sql("""
SELECT articles.title, authors.team
FROM articles
INNER JOIN authors ON articles.author = authors.name
ORDER BY articles.id
"""))

[{'title': 'Rustで書くLSMツリー', 'team': 'コア'},
 {'title': 'SQLパーサをNimで実装する', 'team': 'コア'},
 {'title': 'ベクトル検索の基礎', 'team': '検索'},
 {'title': '組み込みDBの選び方', 'team': '検索'}]

Only four of the five articles remain. Because sora has no matching row in authors, the row with id = 5 is excluded. INNER JOIN returns only rows with a match in both tables.

Use LEFT JOIN to retain every article.

print(db.execute_sql("""
SELECT articles.id, articles.author, authors.team
FROM articles
LEFT JOIN authors ON articles.author = authors.name
ORDER BY articles.id
"""))

[{'id': 1, 'author': 'mio', 'team': 'コア'},
 {'id': 2, 'author': 'mio', 'team': 'コア'},
 {'id': 3, 'author': 'ren', 'team': '検索'},
 {'id': 4, 'author': 'ren', 'team': '検索'},
 {'id': 5, 'author': 'sora', 'team': None}]

All five rows are returned, and the unmatched team is None. LEFT JOIN preserves rows from the left-hand table. Choose between the joins according to whether unmatched rows should remain.

6. GROUP BY and aggregate functions

Calculate the number of articles and total views for each author.

print(db.execute_sql("""
SELECT author, COUNT(*) AS n, SUM(views) AS total
FROM articles
GROUP BY author
ORDER BY author
"""))

[{'author': 'mio', 'n': 2, 'total': 2000},
 {'author': 'ren', 'n': 2, 'total': 2100},
 {'author': 'sora', 'n': 1, 'total': 300}]

GROUP BY author groups rows by author, COUNT(*) counts articles, and SUM(views) adds their views. An alias such as AS n becomes the key in the returned dictionary.

Available aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

7. Subqueries

Find articles with more views than the average. SQL can calculate the average inside the query.

print(db.execute_sql("""
SELECT title
FROM articles
WHERE views > (SELECT AVG(views) FROM articles)
ORDER BY id
"""))

[{'title': 'Rustで書くLSMツリー'},
 {'title': 'ベクトル検索の基礎'}]

The parenthesized SELECT AVG(views) FROM articles is evaluated first, and its value becomes the right side of views >. The average of the five rows is 880, leaving the rows with 1,200 and 1,500 views.

This nested query is a subquery. Subqueries can also be combined with IN and EXISTS.

8. SQL transactions

The SQL calls so far used db.execute_sql() directly, without the begin() and commit() calls from Part 1. execute_sql() opens an internal transaction and commits it automatically when the call succeeds. Transaction boundaries can therefore be implicit in the API call or explicit through db.begin().

Implicit transactions: automatic commit in execute_sql

One call is one transaction, even when the call contains multiple statements separated by semicolons.

from alopex import Database

db = Database.new()
db.execute_sql("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, author TEXT, views INTEGER, rating REAL)")

db.execute_sql("""
INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5);
INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5);
""")
print(db.execute_sql("SELECT id, title FROM articles ORDER BY id"))

[{'id': 1, 'title': 'Rustで書くLSMツリー'}, {'id': 2, 'title': 'SQLパーサをNimで実装する'}]

If an intermediate statement fails, all writes from that call are rolled back. The second statement below fails because it duplicates a primary key.

try:
    db.execute_sql("""
    INSERT INTO articles VALUES (3, 'ベクトル検索の基礎', 'ren', 1500, 4.5);
    INSERT INTO articles VALUES (1, '重複するid', 'sora', 100, 3.0);
    """)
except Exception as e:
    print(e)

print(db.execute_sql("SELECT id, title FROM articles ORDER BY id"))

error[ALOPEX-E999]: constraint violation: PRIMARY KEY constraint violated on columns: ["id"], value: None
[{'id': 1, 'title': 'Rustで書くLSMツリー'}, {'id': 2, 'title': 'SQLパーサをNimで実装する'}]

The first statement’s id = 3 is also absent because the entire call, rather than each statement, is the commit unit.

Explicit transactions: defining the boundary with begin

Transactions opened by db.begin() also provide execute_sql(). Calls made before commit() or rollback() share one transaction.

from alopex import Database, TxnMode

with db.begin(TxnMode.READ_WRITE) as txn:
    txn.execute_sql("UPDATE articles SET views = 1300 WHERE id = 1")
    txn.execute_sql("INSERT INTO articles VALUES (3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)")
    txn.commit()

print(db.execute_sql("SELECT id, title, views FROM articles ORDER BY id"))

[{'id': 1, 'title': 'Rustで書くLSMツリー', 'views': 1300},
 {'id': 2, 'title': 'SQLパーサをNimで実装する', 'views': 800},
 {'id': 3, 'title': 'ベクトル検索の基礎', 'views': 1500}]

The UPDATE and INSERT share one boundary. Replacing commit() with rollback() discards both.

with db.begin(TxnMode.READ_WRITE) as txn:
    txn.execute_sql("UPDATE articles SET views = 9999 WHERE id = 1")
    txn.execute_sql("DELETE FROM articles WHERE id = 2")
    print(txn.execute_sql("SELECT id, views FROM articles ORDER BY id"))
    txn.rollback()

print(db.execute_sql("SELECT id, views FROM articles ORDER BY id"))

[{'id': 1, 'views': 9999}, {'id': 3, 'views': 1500}]
[{'id': 1, 'views': 1300}, {'id': 2, 'views': 800}, {'id': 3, 'views': 1500}]

Inside the transaction, views is 9999 and id = 2 is absent. Both changes revert after rollback(). Until a transaction commits, its changes are visible only within that transaction.

Leaving the block without calling commit() discards the changes, just as rollback() does. The statements themselves succeed; their changes are discarded when the transaction closes.

In a READ_ONLY transaction, a write statement fails immediately in execute_sql() rather than waiting for the transaction to close. Reads can continue afterward.

Writing BEGIN, COMMIT, ROLLBACK, or SAVEPOINT as SQL statements is not currently supported.

9. Combining key-value data and SQL

Reads fall into two different groups: filtering multiple records by a condition, and retrieving one record when its key is already known. The former benefits from query optimization; the latter only needs a direct path to the requested location.

A conventional RDBMS expresses the second case as SELECT ... WHERE id = ?. The statement passes through an SQL parser, receives a query plan, and returns a result set. It does not expose a direct key-value path.

Alopex DB supports both paths in the same database. Part 1’s get() bypasses the SQL parser, accepts a key, and returns bytes. Article metadata can live in a table while article bodies live in key-value entries, allowing each to use its appropriate access path.

from alopex import Database, TxnMode

db = Database.new()

db.execute_sql("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, author TEXT, views INTEGER, rating REAL)")
db.execute_sql("INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)")

with db.begin(TxnMode.READ_WRITE) as txn:
    txn.put(b"body:1", "LSMツリーは書き込みをメモリ上のテーブルへ集める。".encode())
    txn.commit()

row = db.execute_sql("SELECT id, title FROM articles WHERE views >= 1000")[0]
print(row)

with db.begin(TxnMode.READ_ONLY) as txn:
    print(txn.get(f"body:{row['id']}".encode()).decode())

{'id': 1, 'title': 'Rustで書くLSMツリー'}
LSMツリーは書き込みをメモリ上のテーブルへ集める。

SQL finds an article that meets the condition, and its ID retrieves the body through the key-value API. If a large body is stored in a table column, even a list query may read it. Storing it behind a key lets the application retrieve only the body it needs.

Writing both in one transaction

Separating metadata and body requires two write operations. If only one commits, the result is either an article without a body or a body without an article. Place both operations inside the db.begin() boundary from the previous section.

with db.begin(TxnMode.READ_WRITE) as txn:
    txn.execute_sql("INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)")
    txn.put(b"body:2", "Nimのマクロで構文木を組み立てる。".encode())
    txn.rollback()

print(db.execute_sql("SELECT id FROM articles WHERE id = 2"))
with db.begin(TxnMode.READ_ONLY) as txn:
    print(txn.get(b"body:2"))

[]
None

rollback() removes both the table row and the key. commit() would retain both. The operations remain separate, but their commit outcome is shared.

This is the central result of Part 2. The v0.8.8 recording places an SQL row and a key-value entry in the same transaction, rolls it back, and then obtains [] from SQL and None from the key-value read. Two data models share one commit unit.

The same transaction applies across the relational and key-value data models. Rows and keys have different schemas and APIs, while commit() and rollback() share one boundary.

Putting the table and key-value store in separate products removes that shared boundary. The application must compensate for a successful write when the other write fails, and must also handle failures in the compensation itself.

10. Persistence with Database.open

As with key-value data in Part 1, using Database.open() persists tables to files. Open the existing ./notes-db, which still contains the greeting key from Part 1.

from alopex import Database

db = Database.open("./notes-db")
db.execute_sql("""CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  views INTEGER,
  rating REAL
)""")
db.execute_sql("INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)")
db.execute_sql("INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)")

Reopen it from another process and read both the table and the key.

from alopex import Database, TxnMode

db = Database.open("./notes-db")
print(db.execute_sql("SELECT id, title, views FROM articles ORDER BY id"))

with db.begin(TxnMode.READ_ONLY) as txn:
    print(txn.get(b"greeting").decode())

[{'id': 1, 'title': 'Rustで書くLSMツリー', 'views': 1200},
 {'id': 2, 'title': 'SQLパーサをNimで実装する', 'views': 800}]
hello

CREATE TABLE and SELECT are unchanged from the in-memory examples. The only change is replacing new() with open("./notes-db").

The greeting key written in Part 1 remains after the table is created. The key-value store and tables coexist in one data directory, without another product or a duplicate copy of the data.

11. Running SQL from Rust

Part 1 used the key-value API from Rust. SQL runs through the same Database.

[dependencies]
alopex-embedded = "=0.8.8"

execute_sql() returns SqlResult. For a SELECT, SqlResult::Query contains columns and rows that can be displayed as follows.

use alopex_embedded::{Database, SqlResult};
use std::path::Path;

fn show(db: &Database, sql: &str) -> Result<(), Box<dyn std::error::Error>> {
    if let SqlResult::Query(q) = db.execute_sql(sql)? {
        let names: Vec<&str> = q.columns.iter().map(|c| c.name.as_str()).collect();
        println!("{}", names.join(" | "));
        for row in &q.rows {
            let cells: Vec<String> = row.iter().map(|v| format!("{v:?}")).collect();
            println!("{}", cells.join(" | "));
        }
    }
    Ok(())
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::open(Path::new("./notes-db"))?;
    db.execute_sql("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, author TEXT, views INTEGER, rating REAL)")?;
    db.execute_sql("INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)")?;
    db.execute_sql("INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)")?;

    show(&db, "SELECT id, title, views FROM articles ORDER BY id")?;
    Ok(())
}

id | title | views
Integer(1) | Text("Rustで書くLSMツリー") | Integer(1200)
Integer(2) | Text("SQLパーサをNimで実装する") | Integer(800)

The SQL is identical to the Python version. Only result handling differs. Python returns a list of dictionaries keyed by column name. Rust returns column definitions in q.columns and ordered rows in q.rows.

Each cell is an enum value named SqlValue. The output includes type names such as Integer(1200) because the enum carries the value’s type. Display it directly when inspecting types, or match a variant such as SqlValue::Integer(n) => n when only the value is needed.

Besides Query, SqlResult has Success and RowsAffected variants. CREATE TABLE returns Success, and INSERT returns RowsAffected(1). The show function handles only Query, so the other two variants pass without output.

12. SQL used in this part

Part 3 covers conditional expressions, set operations, common table expressions, and window functions.

References

• v0.8.8 release verification report