Skip to content
Drivine

SQLite

SQLite keeps a graph in a file, with no server: the same tables as Oracle and Postgres, so a file drivine4j or drivine-python wrote is read and written here, and the other way round. Drivine opens it through Node’s own driver, node:sqlite, in Node 22.13 or later, which brings SQLite 3.45 or later; nothing else is installed. Node 22 and 24 print an ExperimentalWarning the first time node:sqlite is loaded. Models, the DSL and the object manager are the same as on the graph engines; PersistenceManager runs plain SQL, with cursors and transactions, and Cypher, compiled to SQLite’s SQL as drivine4j compiles it (a cursor runs SQL alone). What Cypher does that SQLite’s JSON functions don’t, Drivine’s functions registered on each connection do.

Which Node. The SQLite comes with Node, so use a Node whose SQLite writes a float it computes in JSON to 17 significant digits, which read back as it: json_quote(0.1 + 0.2) is 0.30000000000000004. SQLite 3.53 does so by default, but a build may still write 15 (one Python’s 3.53.1 does), so Drivine asks SQLite rather than reading its version. Node 22.23.4, 24.22.2 and 26.13, for example, were measured to write 17. A SQLite that writes 15 significant digits, as every one before 3.53 does, rounds what it computes, and every value compiled Cypher gives is JSON: 0.1 + 0.2 comes back as 0.3, 1.0/3 as 0.333333333333333, a value passed through WITH and multiplied back as 0.999999999999999 where 1.0 is wanted, and a computed float a statement SETs is stored rounded. Parameters, stored values and Drivine’s own functions, such as pi(), keep their digits. Drivine detects this at run time: when it first opens a file, it asks SQLite for json_quote(0.1 + 0.2), and warns once where the answer is rounded; it runs all the same. The warning is a Node process warning, so it shows whatever DRIVINE_LOG_LEVEL is, and its code is DRIVINE_SQLITE_ROUNDED_FLOATS. node --disable-warning=DRIVINE_SQLITE_ROUNDED_FLOATS (or the same in NODE_OPTIONS) silences it alone, and node --no-warnings with every other process warning.

Node, measured SQLite json_quote(0.1 + 0.2)
24.7.0 3.50.4 0.3, warned of
22.23.4 3.53.4 0.30000000000000004
24.22.2 3.53.4 0.30000000000000004
26.13.0 3.54.0 0.30000000000000004
Terminal window
DATABASE_TYPE=SQLITE
DATABASE_NAME=./data/graph.db          # the path of the file, made if it is not there; the folder must be
DATABASE_DEFAULT_GRAPH_PATH=drivine    # names the graph's tables; this is the default
DATABASE_CONNECTION_TIMEOUT=30000      # how long a statement waits for the file's lock, in ms; this is the default

A SQLite store has no host, port, user or password. :memory: is refused: each pooled connection would open a database of its own. The tables are made on the first connection, when they are not there, as on Postgres.

How a graph is stored. drivine4j’s layout, so the three libraries read one file: <graph>_node (nid INTEGER PRIMARY KEY, labels TEXT, props TEXT, stamp TEXT), <graph>_edge (eid INTEGER PRIMARY KEY, src, dst REFERENCES node ON DELETE CASCADE, type, props TEXT), and <graph>_schema. Labels and properties are JSON text, not SQLite’s binary jsonb. Labels are written sorted, with no character escaped that JSON does not require, because a label is found by its text, "Label", within labels. The stamp is a column. A moment is ISO 8601 text.

What a transaction gives.

  • One writer at a time. A transaction takes the file’s write lock as it begins (BEGIN IMMEDIATE), so even one that only reads waits for the lock, up to the connection timeout. One that holds the lock longer than five seconds is warned of when it ends, committed, rolled back or released.
  • A read outside a transaction doesn’t wait for a writer: the journal is a write-ahead log, and a read sees what was committed.
  • A save or a delete outside a transaction that finds the file locked is run again, as one turned away as contended is on the other stores.
  • Each statement Drivine writes applies whole or not at all: a save is a call of drivine_write, a function each connection registers, which carries out the save’s steps under a savepoint.
  • Node’s driver is synchronous, so a connection that finds the file locked waits for it by trying again after a pause, which leaves the rest of the process running. A connection you open yourself on the file has none of this, nor the functions Drivine’s statements call.

Notes:

  • A statement you write yourself is SQL; parameters are named $name, and a statement is bound only the ones it names. A statement Drivine writes whose text values are each JSON opens with /* drivine:json */, and is read as JSON.
  • A predicate you write yourself is SQL over the root’s row: its props, labels and stamp, e.g. (n.props ->> 'name') = $name. A property of the root may be written as Cypher writes it, n.name = $name, and is read as that property, as text, as on Postgres. SQLite’s own ->> reads true as 1 and a number as a number.
  • Each connection registers the functions Drivine’s statements call, by drivine4j’s names: drivine_write, drivine_delete, drivine_lower_copies, drivine_similarity, drivine_epoch (a moment as nanoseconds since the epoch), drivine_lower (a Unicode lower), drivine_regexp_count, and regexp, which SQLite’s REGEXP calls.
  • A range index or a uniqueness constraint is a partial index over the property’s text, for the nodes of the label. A vector or a full-text index is recorded and not made: loadNearest measures every node of the label that holds an embedding of the vector’s size, and loadMatching reads every node of the label and scores by BM25, as Postgres does.
  • matches takes a JavaScript regular expression, matched against the whole value: \w, \d and \b are ASCII, as JavaScript reads them, where drivine4j reads them as Unicode (#560).
  • Text compares and sorts by SQLite’s BINARY collation, the order of its code points, so a page of mixed-case text is in the order Neo4j gives (B, a, b), not by a collation as on Postgres.
  • A whole number past 2^53 that a statement gives back is refused by Node’s driver; one a save leaves alone keeps its digits.