NanoSQL: a tiny, strongly-typed data mapper for SQLite

announcements
1

Today I released NanoSQL, a small and ergonomic data mapper for SQLite.

What?

  • It's just that, a data mapper. Not a full ORM, more like half of it. It doesn't attempt to type check your queries; that's a Hard™ problem. (But I'm working on it!) Think Dapper vs. Entity Framework.
  • Yes, it's SQLite-only. Abstracting over DB engines would be possible, but it's much easier to release a working, convenient, and correct piece of software if one can bake in some assumptions. Also, you may have noticed that I love SQLite.

How?

It's like Uber Serde but for SQL queries. The short story is just 4 steps:

  1. You define a schema by creating structs that implement the Table trait. This describes the structure of the table, e.g. column names and types, indexes, constraints, etc.
  2. You define a typed query that implements the Query trait. This specifies associated types for the input (parameters) and the output (result set). It also writes the raw SQL query to a fmt::Formatter.
  3. You open a Connection and use it to compile the Query into a CompiledStatement. The compiled statement is now fully statically typed.
  4. You use the CompiledStatement to invoke the underlying prepared statement with the correct input and output types. The input must implement the Param trait, the output must implement ResultSet.

Highlights:

  • Param, ResultRecord, and Table can all be #[derive]d. The Table impl can be customized extensively. (There are also additional, convenience derive macros; see the docs.)
  • The Table trait/macro supports most advanced features of the SQLite DDL, such as partial indexes, foreign keys, and generated columns.
  • ResultSet is implemented for std and custom collections with an item type that implements ResultRecord. The ones you'll use the most are Single, Option, and Vec, for 1, 0 or 1, or any rows, respectively.
  • The define_query! declarative macro provides a shorter way of defining Query types.
  • It tries very hard to do the right thing by default:
    • Foreign keys are automatically indexed and DEFERRED, so you can easily insert and look up complex data structures within a transaction.
    • Attributes that require raw SQL to be interpolated are checked for syntax errors at compile time to avoid emitting corrupted SQL, using the sqlparser crate.
    • Transactions are used for all library-provided bulk operations.
    • When using the provided factory methods, NanoSQL will apply the recommended, modern settings of SQLite, e.g. turn on foreign key constraints and activate WAL mode.
    • If lifetimes get you in trouble, feel free to re-compile queries more frequently, because NanoSQL internally uses cached statements with a reasonably large cache size, so subsequent calls to compile() won't actually re-prepare the query.
    • Parameters and result columns are ensured to have the correct names and cardinality at runtime.
  • Enums with unit-like variants are also supported by the derive macros (when sensible), to make domain modeling easier.
  • The ConnectionExt and TransactionExt traits provide convenience methods for frequent tasks, such as creating a table and its indexes, batch insertion of records, or EXPLAIN QUERY PLAN (which outputs a structured tree, pretty printable via Display).

Examples

  1. The basics are demonstrated in the README.
  2. The realistic example showcases a more complex use case, inspired by a real data modeling problem.

Enjoy!

5 Likes