Build with
Inillucent

Embedded means there isn't a database server to install or keep alive, your program opens a file, sends SQL to a library in the same process, and receives typed rows back. We can reach the same engine from a terminal through inillucent-shell.

The SQL dialect follows SQLite 3.53.4 closely. If you have used SQLite, most statements will look familiar. If SQL is new to you, start with the next four chapters and type the examples in order.

• Relational tables use B+trees, snapshot isolation, and a redo write-ahead log.
• VECTOR(N) columns and inillucent_hnsw indexes put nearest-neighbor search in ordinary SQL.
• inillucent_search combines lexical and vector retrieval, adds calibrated confidence, and can decline to answer.
• Unsupported features fail with a named error instead of returning an approximate answer.

One line installs Inillucent, and the two commands are the first example below rather than part of this sentence, because a command set in a paragraph is a command a reader has to retype. Each downloads the archive for the machine, checks its SHA-256 against the published sums, and puts the four programs on PATH without administrator rights. There is no macOS archive yet, so a Mac builds from a checkout; https://inillucent.com/install.md carries the steps for all three.

An .rdb suffix is a convention, not a requirement. The path can be relative or absolute. Keep the file on a local filesystem. One process owns it by default, because PRAGMA locking_mode is exclusive; set it to normal and several processes can share one file, over the same lock protocol SQLite uses.

Dot commands configure the shell and don't go to the SQL parser, while SQL statements do. We use .headers on with .mode table while learning, because column names make a result easier to check.

The -bail option stops a script at its first error. That is useful in automation, where continuing after a failed CREATE TABLE can make every later message misleading.

• .help lists the shell commands available in this build.
• .tables lists the tables and views in the open database.
• .schema prints the stored CREATE statement for each object.
• .mode changes how every following result is rendered.
• .parameter manages bound values that can be reused safely.
• .quit closes the current session and its database file.

NULL means missing or unknown, and it isn't the same value as 0 or an empty string. We use IS NULL and IS NOT NULL to test it, because NULL = NULL is unknown rather than true.

Ordinary tables use SQLite affinity. A value such as the text 42 is converted when it enters an INTEGER column. STRICT adds type enforcement after affinity has had that chance to convert it.

• INTEGER stores signed whole numbers without a fractional part.
• REAL stores floating-point numbers with a fractional part.
• TEXT stores text as a sequence of UTF-8 bytes.
• BLOB stores arbitrary bytes without trying to interpret them.
• VECTOR(N) stores exactly N little-endian 32-bit floating-point numbers.
• ANY in a STRICT table deliberately accepts every SQL value type.

INTEGER PRIMARY KEY is the table key and receives a number automatically when it is omitted. AUTOINCREMENT prevents a deleted key from being reused. WITHOUT ROWID stores a declared primary key directly, which is useful when the natural key is not an integer.

Foreign keys are checked when PRAGMA foreign_keys is ON. They support immediate and deferred checks plus CASCADE, SET NULL, SET DEFAULT, RESTRICT, and NO ACTION for updates and deletes.

• NOT NULL requires every stored row to have a value.
• UNIQUE prevents two rows from repeating the same column combination.
• CHECK requires its expression to evaluate to true or NULL.
• DEFAULT supplies a value when an INSERT omits the column.
• STRICT rejects values that cannot become the declared column type.
• Generated columns, composite keys, and named constraints follow SQLite syntax.

A WHERE clause decides which rows UPDATE or DELETE touches. When the condition is new, we'll run the same WHERE with SELECT first, because leaving WHERE out means every row.

ON CONFLICT handles a uniqueness collision. DO NOTHING skips the incoming row, while DO UPDATE changes the existing row. INSERT OR IGNORE and INSERT OR REPLACE provide SQLite-compatible conflict policies.

SQL is declarative, so we describe the answer instead of the steps used to find it, and the planner chooses a table scan, index lookup, join strategy, temporary sort, etc. We don't have to encode that physical plan in the query.

LIKE matches text patterns, where % means any sequence and _ means one character. GLOB uses shell-style * and ?. COLLATE NOCASE provides ASCII case-insensitive ordering and comparison, while RTRIM ignores trailing spaces.

• DISTINCT removes duplicate result rows.
• LIMIT count OFFSET skip pages through a result.
• CASE chooses a value from conditions.
• CAST converts a value deliberately.
• IN and BETWEEN express common ranges and sets.
• IS and IS NOT handle NULL safely.

INNER JOIN returns matching pairs. LEFT, RIGHT, and FULL OUTER JOIN preserve unmatched rows from one or both sides. CROSS JOIN produces every possible pair and normally needs a small input.

A subquery may appear in WHERE, FROM, or as a value. It may be correlated, which means it refers to the current row of its outer query. WITH RECURSIVE can walk a hierarchy or generate a sequence one row at a time.

GROUP BY makes one result row per group. HAVING filters those groups after aggregates are calculated. COUNT, SUM, AVG, MIN, MAX, group_concat, json_group_array, and json_group_object are available.

UNION removes duplicates, UNION ALL keeps them, INTERSECT keeps common rows, and EXCEPT keeps rows found only on the left.

Window functions run. OVER (...) with PARTITION BY, the ROWS, RANGE and GROUPS frame clauses, every EXCLUDE bound, and all eleven window-only functions answer the way SQLite 3.53.4 answers: row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value and nth_value.

Savepoints create smaller rollback boundaries inside a transaction. ROLLBACK TO removes changes after a savepoint and keeps the transaction open, while RELEASE removes the savepoint name.

Readers use snapshots and don't see partial writes, while one writer may commit at a time. busy_timeout controls how long another writer waits, and synchronous OFF, NORMAL, or FULL chooses the durability tradeoff.

• Deferred foreign keys are checked when the transaction commits.
• Search shadow tables participate in the same commit and rollback.
• ATTACH can commit changes across two files with a super-journal.
• Recovery runs on open and replays complete WAL records idempotently.

A regular CREATE INDEX builds a B+tree from one or more columns. EXPLAIN QUERY PLAN shows whether a query scans a table, searches an index, uses a vector index, or needs a temporary sort.

CREATE TRIGGER supports BEFORE, AFTER, and INSTEAD OF, FOR EACH ROW, WHEN, OLD and NEW values, and RAISE. ALTER TABLE can rename a table, rename a column, add a column, or drop a column.

• CREATE VIEW stores a reusable SELECT.
• ANALYZE writes sqlite_stat1 for the planner.
• REINDEX rebuilds indexes after a collation change.
• DROP TABLE, VIEW, INDEX, and TRIGGER remove schema objects.
• Partial indexes, expression indexes, and indexes on WITHOUT ROWID tables all run.

Common scalar functions include abs, coalesce, hex, instr, length, lower, upper, printf, random, replace, round, substr, trim, typeof, unicode, and zeroblob. The math set includes trig, logarithm, power, floor, ceil, sqrt, etc.

Date and time functions include date, time, datetime, julianday, unixepoch, strftime, and timediff. Modifiers such as start of month and +1 day follow the SQLite model.

• Core values and text: changes, char, coalesce, concat, concat_ws, format, glob, hex, ifnull, iif, instr, last_insert_rowid, length, like, likelihood, likely, lower, ltrim, nullif, octet_length, printf, quote, random, randomblob, replace, rtrim, sqlite_source_id, sqlite_version, substr, substring, total_changes, trim, typeof, unhex, unicode, unlikely, upper, and zeroblob.
• Math: abs, acos, acosh, asin, asinh, atan, atan2, atanh, ceil, ceiling, cos, cosh, degrees, exp, floor, ln, log, log10, log2, mod, pi, pow, power, radians, round, sign, sin, sinh, sqrt, tan, tanh, and trunc.
• Date and time: date, datetime, julianday, strftime, time, timediff, and unixepoch.
• Aggregates: avg, count, group_concat, json_group_array, json_group_object, jsonb_group_array, jsonb_group_object, max, min, string_agg, sum, and total.
• Windows: cume_dist, dense_rank, first_value, lag, last_value, lead, nth_value, ntile, percent_rank, rank, and row_number. Each needs an OVER clause, and each answers the way SQLite 3.53.4 answers.
• Vectors: vector_distance_cos, vector_distance_l2, and vector_dot. The complete JSON and JSONB families are listed in the next chapter.

A JSON path starts with $. Object labels follow a dot, and array positions use brackets. A missing path returns NULL. A malformed path or malformed document returns an error instead of an invented value.

The scalar set includes json, jsonb, json_array, json_object, json_extract, json_set, json_insert, json_replace, json_patch, json_remove, json_type, json_valid, json_pretty, json_quote, their JSONB forms, group aggregates, etc. json_each and json_tree are registered internally but aren't yet reachable from SQL.

Create an FTS5 virtual table with the text columns you want to search. It can store its own content or follow an external content table. Prefix, phrase, Boolean, and column-restricted queries use SQLite FTS5 syntax.

The index lives in ordinary database trees and follows the surrounding transaction, so an insert that rolls back isn't searchable later. Rebuild and delete-all maintenance commands follow the FTS5 control-column form.

The first column is an id. Every dimension then has a minimum and maximum column, so a two-dimensional table has id, min_x, max_x, min_y, and max_y. A table may have one to five dimensions.

The query below asks which stored rectangle overlaps the square from 8 through 12 on both axes. The minimum must be below the query maximum, and the maximum must be above the query minimum.

Embeddings arrive as a BLOB of little-endian f32 values. Bind that byte array as a parameter in application code. The hexadecimal literals below make the bytes visible for a two-dimensional teaching example.

vector_distance_cos returns 0 for the same direction and 1 for orthogonal unit vectors. vector_distance_l2 returns Euclidean distance, and vector_dot returns a dot product. ORDER BY distance with LIMIT k is the shape the planner recognizes for HNSW. An index minimizes cosine unless it was declared WITH (metric = 'l2'), and a query whose distance function does not match the metric its index was built under plans as an exhaustive scan rather than a probe.

Most applications supply their own vectors. Inillucent can also produce them: inillucent setup-embeddings all downloads ONNX Runtime and nomic-embed-text-v1.5 on Windows, macOS or Linux, checks every byte against a digest pinned in the build, and leaves the SQL function embed(TEXT) answering with nothing to export by hand. It is about 620 MB once, and a build without the feature refuses by name rather than returning a vector of zeroes.

Loading the model costs 650 to 800 milliseconds and one embedding costs 12 to 36 milliseconds, so when the weights are in memory is a decision rather than a detail. The resident profile keeps them for the life of the process, on-demand drops them after every call, and the default idle profile keeps them through a burst of questions and lets them go a few minutes later. A process that answers one question and exits wants on-demand; an ingestion run wants resident.

• The column refuses the wrong byte width.
• CREATE INDEX backfills existing rows and follows later writes.
• A query may use a WHERE predicate with nearest-neighbor ordering.
• The index uses the same retrieval store as inillucent_search rather than a second HNSW implementation.
• embed(TEXT) returns the 3,072 bytes a VECTOR(768) column holds, computed in this process, and is called once for the statement rather than once per row.
• Write a computed vector wherever an expression goes: a VALUES row, an UPDATE ... SET, a RETURNING clause or an INSERT ... SELECT.
• One command installs the runtime and the weights on all three platforms, verified by digest.

Declare the text columns and optionally dims, mode, threads, tokenizer, metric, compact, segment_merge, and merge_budget. With no dims the table is lexical-only. With dims it also accepts a vector column whose bytes use the same format as VECTOR(N). The distance metric is cosine by default and l2 is the other one; it is fixed when the index is built, because a cosine index normalizes every vector it stores and an l2 index must not.

MATCH supplies the text query and k controls the requested depth. Hidden result values include rank, score(), confidence(), and origin(). Score orders candidates. Confidence is calibrated against absolute evidence and may support an application choosing to return no answer.

• Filters are compiled into the walk instead of applied after it.
• Exact search is a plan the optimizer chooses for narrow admitted sets, not a fallback.
• Lexical scoring adds coverage and proximity to BM25.
• Fusion modes include reciprocal-rank, weighted, and calibrated fusion.
• Inserts, updates, deletes, compaction, rebuilds, commit, rollback, and reopen are durable.
• A commit writes a new immutable segment out of its own batch rather than rewriting the whole index, and segments merge on a rule with the work one commit may do bounded.
• The virtual table can join ordinary relational tables by rowid.

Schema inspection includes table_info, table_xinfo, table_list, index_list, index_xinfo, and database_list. Storage inspection includes page_size, page_count, and freelist_count.

Operational checks include integrity_check, quick_check, foreign_key_check, and wal_checkpoint. Connection settings include cache_size, synchronous, busy_timeout, and foreign_keys.

Prefix an object with its schema, such as archive.books. DETACH closes the attached schema after statements using it have finished. database_list shows the names and paths currently available.

A commit that writes two durable files uses a super-journal so recovery can decide the transaction consistently. Temporary objects use their own connection-local database and are gone after the connection closes.

Values stay typed as Null, Integer, Real, Text or Blob. A result is materialized before it is handed back, so it carries an exact total, and a flag saying whether the row limit left any rows behind.

Unsupported is its own status and it names the construct the engine has not implemented, so an application can tell that apart from a mistyped statement. The capability table answers the same question before a statement is composed. Today 23 of 24 capabilities are supported, and cancellation is the declared exception.

• A Database owns one file and creates connections.
• Bound parameters are copied before the call returns.
• Read only mode is decided by the binder, which classifies each statement.
• A transaction is held open, so a write can be checked before it commits.
• Every C handle has a free function, and returned pointers stay valid until it is freed.
• The reference Python binding uses ctypes and the standard library.

All eight call the same C ABI and give the same API, so a program written against one reads the same in the next. Values stay typed, NULL is never the empty string, and a result carries an exact total beside the rows a limit handed back.

The tabs below show the same program in each language: create a person table, insert two rows, read them back by column name, and update one. Every output is what that program really printed.

Each library is graded by the same conformance suite this engine’s own Rust driver runs, so a client passes when it agrees with the engine. The libraries live in the inillucent-clients repository, and each has a README with the full reference.

Migration is copy-based. The source is never written to, which makes going back a path choice rather than a reconstruction. The report and manifest record what was copied and how it was checked.

A path is a SQLite file. A postgres:// or mysql:// URL is a running server, read over its own wire protocol inside one repeatable-read snapshot, so every table and the schema itself are as of one instant. Values with no equivalent here are carried as the server's own text rendering rather than rounded into a float.

Inillucent doesn't use SQLite page format, so it can't open an existing .sqlite file directly. We import it instead, recreating the supported SQLite views, triggers, indexes, constraints, rows, etc. in the Inillucent file.

Meta pages are double-written and a rollback journal keeps a pre-image of both, so a crash while a checkpoint is recording itself is undone along with the pages that checkpoint moved. Each WAL record has a crc32c checksum, as does each rollback-journal page image, and recovery runs on every open and stops at the first record whose bytes are not the bytes that were written. Checkpoints can retire old segments after their pages are durable. Values too large for a leaf move into blob extents.

One writer at a time, and readers use a version log so a writer doesn't expose half of a transaction. Several processes can share one file under PRAGMA locking_mode = normal, over the same lock protocol SQLite uses; the default is exclusive, because releasing the file between statements means reading the meta record again before every one. Threads inside a single process are not supported. Group commit lets writers waiting at the same boundary share the durable flush.

• Page sizes from 8 KiB through 64 KiB are allowed.
• The default buffer pool has 4,096 frames.
• synchronous OFF, NORMAL, and FULL are implemented.
• busy_timeout controls writer waiting.
• WAL and rollback-journal modes are understood by the transaction layer, and a checkpoint is undoable in both: the log is logical, so a page a checkpoint half wrote is content the log has stopped describing, and a rollback journal holds its previous image until the checkpoint is durable.
• Deterministic crash campaigns cover torn writes, I/O errors, disk full, recovery, and checkpointing, one cut point of a commit at a time, in DELETE, TRUNCATE and PERSIST mode.

Of the 13 that are not byte-equal, six are vector-search features SQLite has no equivalent for, so there is nothing to be equal to. Three print numbers about SQLite's own C structures, three are a page size and a locking mode this engine chose and measured, and one is two pinned SQLite artifacts disagreeing with each other. There are no known wrong answers.

Partial indexes, indexes on expressions, and indexes on WITHOUT ROWID tables all run, as do json_each, json_tree, generate_series and the table-valued pragma forms. VACUUM and VACUUM INTO both rebuild the file. Several processes can share one file under PRAGMA locking_mode = normal, though the default is exclusive because releasing the file between statements costs real speed.

What is outside the surface: SQLite's file format, which is imported rather than opened; threads inside one process; a second writer; and online cancellation. The relational engine is 339% faster than SQLite at 100,000 rows on the measured Windows gate, and six of its thirty workloads are slower, the largest being compiling SELECT 1 on every call.

• RIGHT and FULL OUTER JOIN run.
• Recursive CTEs and correlated subqueries run.
• STRICT tables, generated columns, CHECK, UNIQUE, and foreign keys run.
• BEFORE, AFTER, and INSTEAD OF triggers run.
• All four ALTER TABLE forms run.
• Partial indexes, expression indexes, and indexes on WITHOUT ROWID tables run.
• FTS5, R-Tree, JSON, vector columns, HNSW, and inillucent_search run.
• ATTACH, DETACH, temporary objects, savepoints, and cross-file commit run.
• 172 of the 177 function names the pinned SQLite library answers, 67 of 67 pragmas, and 63 of 65 shell dot commands answer.
• The shell and driver return Unsupported separately from syntax, constraint, and I/O errors.