Skip to content
Drivine

SQLite

SQLite 3.45 or later, over JDBC with xerial’s driver: a graph in a file, with no server to run. Drivine keeps it in the same two tables as on Oracle and Postgres, so models, the query DSL and StatelessGraphObjectManager are the same as on the other stores. Add the driver yourself: org.xerial:sqlite-jdbc.

database:
  datasources:
    graph:
      type: SQLITE
      database-name: /var/data/app.db  # the file, made if it is not there
      default-graph-path: drivine      # names the graph's tables; this is the default

database-name is required, and names a file: an in-memory database is refused, as each pooled connection would open one of its own. A host, a protocol or a cypher-dialect means nothing here and is ignored with a warning; a port, a user and a password are not read. The pool is HikariCP with pool-max. Each connection is opened with a write-ahead log, foreign keys enforced, a wait of 30 seconds for a locked file, and the functions Drivine’s statements call registered on it, so make the provider with JdbcConnectionProvider.sqlite, not over a DataSource of your own:

val provider = JdbcConnectionProvider.sqlite("graph", "/var/data/app.db", graphName = "drivine")

How a graph is stored. <graph>_node (nid, labels, props, stamp) and <graph>_edge (eid, src, dst, type, props), made, where they are not there yet, before the datasource’s first connection, with labels and props as JSON text (not SQLite’s binary jsonb, so Drivine for TypeScript and for Python read the same file), and <graph>_schema for what schema management made. A save is the plan of steps the other stores carry out, carried out by the function drivine_write that Drivine registers on each connection: applied whole or not at all, and stamped and checked as elsewhere. Deleting a node’s row deletes its relationships with it.

Notes:

  • A statement written by hand is SQL. SQLite has no JSON type, so JSON a statement of your own returns is text; one that opens with /* drivine:json */ has each of its text values read as the JSON it is.
  • A CypherStatement is compiled to SQL over the graph’s tables, as on Oracle and Postgres (see Cypher on a relational database), and gives the rows a graph engine gives. A read is a query; a write is a procedure of steps, as on the other stores, which the function drivine_cypher carries out on the statement’s connection under a savepoint, so it is applied whole or not at all. A value is JSON text: two compare as Cypher compares them (1 equal to 1.0, a map to a map of the same entries in any order) by functions Drivine registers, which also add lists and texts and work out temporal values with java.time. A query that reads the row it is in (OPTIONAL MATCH, CALL { … }, a variable-length walk, a shortest path) is gathered as JSON and joined with json_each, as SQLite joins no lateral query. Text compares and sorts by SQLite’s BINARY collation; toLower and toUpper change the case of any letter, where SQLite’s own lower and upper change ASCII alone.
  • sum() and avg() add floats as doubles, as Neo4j does, so 0.1 + 0.2 sums to 0.30000000000000004 here too. SQLite also corrects for rounding as it adds (Kahan–Babuška summation), so a long sum of floats, or one that mixes an integer past 2^53 with a float, can be more accurate than Neo4j’s in its last digit: 9007199254740993 and 0.5 sum to 9.007199254740994E15, where Neo4j gives 9.007199254740992E15. A result still comes back as a float. A sum() of integers that passes a long’s range fails with integer overflow, where Neo4j gives a float.
  • An infinity is the number SQLite’s JSON writes for one, and is stored. Its JSON holds no NaN: a statement given one as a parameter writes it as {"$numberDouble": "NaN"}, works with it as the float it is, as Neo4j does, and gives it back as NaN. A statement that writes and is given NaN is refused, rather than store that map, and one that writes NaN it works out from an infinity ($i - $i, n.inf + n.negative) fails. A statement that may work NaN or an infinity out, as on Postgres (dividing or taking % by anything but a number other than 0, toFloat of a text), is compiled as one given one: a number divided by a float zero is NaN or an infinity, as on Neo4j, and so is a float by an integer zero where both are written in the statement (1.0 / 0); an infinity it works out is stored. A number divided by any other integer zero is null, where Neo4j fails. NaN worked out from a stored infinity by any other statement (n.inf - n.inf, n.inf * 0) is null, where Neo4j gives NaN, but for the two infinities added with +, summed with sum() or averaged with avg(), which give NaN.
  • A raw whereClause is a SQL predicate, as on Postgres: n.note = 'unverified', or SQL over n.props, n.labels and n.stamp. SQLite’s ->> gives a JSON value as the SQL value it is (true as 1), where Postgres gives its text.
  • Temporal values are their ISO 8601 text, and a moment is compared and ordered as nanoseconds since the epoch, whatever offset it was written with. A year before 1 is numbered as Neo4j numbers it: 0000 is 1 BC and -0044 is 45 BC.
  • SQLite has one writer at a time, and transactions are serializable:
    • A transaction takes the file’s write lock as it begins (BEGIN IMMEDIATE), so no write in it is turned away part of the way through. Another writer, and another transaction even if it only reads, waits for it to end: a Drivine connection for up to busyTimeoutMillis (30 seconds unless you set it), and then fails as busy. Keep transactions short: one that holds the lock for more than 5 seconds is logged as a warning when it ends.
    • A read outside a transaction does not wait for a writer. The file is in WAL mode: a read sees what was committed when it started, and nothing of a transaction still open.
    • Outside a transaction, each statement is a transaction of its own. A save or a delete the file stays locked against for longer than the wait, or that read before another connection wrote, is run again, as a contended save is on the other stores.
    • Each statement Drivine writes applies whole or not at all, in a transaction or outside one: a statement that fails part of the way undoes what it wrote.
    • A DataSource of your own is opened with none of this: JdbcConnectionProvider.sqlite(…) makes the connections, with their write lock, WAL mode and the functions Drivine’s statements call.
  • Index the id of each node type you save, with a uniqueness constraint or a range index: each is a partial index over the property for the nodes of one label.
  • matches takes a regular expression in Java’s dialect, matched against the whole value. A leading (?i) ignores case.
  • A vector index and a full-text index are recorded and nothing is made for them: loadNearest and loadMatching measure every node of the label, and are exact. loadMatching scores by BM25, as Postgres does.
  • The test kit’s Cypher fixtures (SqlCypherFixtures) work here as on Oracle and Postgres, and so does TestCleanup.beforeEach. SQLite’s tests need no container.