Swift APIs for SQLite: Type-safe down to the schema. Very, very, fast. Dependency free

Lighter

Lighter is a set of technologies applying code generation to access SQLite3 databases from Swift, e.g. in iOS applications or on the server. Like SwiftGen but for SQLite3.

Type-safe down to the SQL schema.

State of the art: Developer writes Swift structures that match a SQLite table. Enlighter reverses this, the generated Swift code reflects what the SQLite table is actually defined as. No place for mistakes. Remove a “somewhere” in the “it’s always any-o-clock somewhere in the stack”.

Very, very, fast.

Lighter builds upon the database schema and hence directly knows what it looks like at compile time. For common operations no mapping is necessary at all, the generated code runs as fast (usually faster) than hand written code. It directly binds Swift structures to the SQLite API.

Dependency free.

Lighter itself is a small and convenient API to access SQLite databases. Enlighter, the code generator, can however produce code that just uses the SQLite API and doesn’t require any dependencies, i.e. tech debt. Don’t ship 3rd party libraries, directly generate the necessary code into your app.

Lighter is useful for two main scenarios:

Shipping SQLite databases within your app (e.g. containing a product database).

SQLite databases are very resource efficient way to ship and access small and big amounts of data. As an alternative to bundling JSON resources files that are large and have to be parsed fully into memory on each start.

With a SQLite database only the required data needs to be loaded and the database files are extremely compact (e.g. no duplicate keys).

SQLite database are also efficient and useful for downloading data from the network!

Maintaining a fast local SQL cache or database.

If the needs are simpler than a full ORM like CoreData, Lighter can be a great way to produce neat and typesafe APIs for local caches or databases. It is basic but convenient to use and very very fast as no runtime mapping or parsing has to happen at all. The code directly binds the generated structures to the SQLite API.

Databases can be created on the fly or from prefilled database files shipped as part of the application resources.

Linux is also supported, and Lighter can be a great choice for simple servers that primarily access a readonly set or run on a single host.

Overview

Lighter works the reverse from other “mapping” tools or SQLite wrappers. Instead of writing Swift code that generates SQLite tables dynamically, Lighter generates Swift code for a SQLite database. Either literally from SQLite database files, or from SQL files that create SQLite databases.

Small Example Database (stored in either a SQLite db or created from .sql files):

CREATE TABLE person (
  person_id INTEGER PRIMARY KEY NOT NULL,
  name      TEXT NOT NULL,
  title     TEXT NULL
);

CREATE TABLE address (
  address_id INTEGER PRIMARY KEY NOT NULL,
  
  street  VARCHAR NULL,
  city    VARCHAR NULL,
  
  person_id INTEGER,
  FOREIGN KEY(person_id) REFERENCES person(person_id) ON DELETE CASCADE DEFERRABLE
);

Can be converted to a structure like this (in a highly configurable way):

struct ContactsDB {

  struct Person: Identifiable, Hashable {
    var id       : Int
    var name     : String
    var title    : String?
  }

  struct Address: Identifiable, Hashable {
    var id       : Int
    var street   : String?
    var city     : String?
    var personId : Int?
  }
}

The code generator can either generate dependency free code that only uses the raw SQLite3 API or code that uses the Lighter library. The Lighter library is not an ORM, but just a set of Swift protocols that allow for typesafe queries (and it is only intended to be used to support the code generator, not as a standalone library).

How does the code generation work?

The setup is intended to work with the new Swift Package Plugins feature of the Swift Package Manager, available since Swift 5.6 (and exposed in Xcode 14+). If SPM plugins cannot be used yet, the sqlite2swift tool can be called directly as well. If you want to support the project, there is also the Code for SQLite3 app on the Mac AppStore. It does the same code generation as this FOSS project in a little more interactive way.

The Lighter package comes with a “build tool plugin” called Enlighter, that automatically integrates the code generation results into the build process. If it is added to a target, it’ll scan for databases and SQL files and create the Swift accessors for them:

.target(name: "ContactsDB", dependencies: [ "Lighter" ],
        resources: [ .copy("ContactsDB.sqlite3") ],
        plugins: [ "Enlighter" ]) // <== tell SPM to use Enlighter on this target

This variant is fully automatic, i.e. other code within the ContactsDB target has direct access to the database types (e.g. the Person struct above).

As a manual alternative the Generate Code for SQLite “command plugin” is provided. This plugin does the same generation as Enlighter, but is explicitly run by the developer using the Xcode “File / Packages” menu. It places the resulting code into the “Sources” folder of the app (where it can be inspected or modified).

Accessing a database using the higher level Lighter API

// Open a SQLite database embedded in the module resources:
let db = ContactsDB.module!

// Fetch the number of records:
print("Total number of people stored:", 
      try db.people.fetchCount())

// There are various ways to filter, including a plain Swift closure:
let people = try db.people.filter { person in
  person.title == nil
}

// Primary & foreign keys are directly supported:
let person    = try db.people.find(1)
let addresses = try db.addresses.fetch(for: person)

// Updates can be done one-shot or, better, using a transaction:
try await db.transaction { tx in
  var person = try tx.people.find(2)!
  
  // Update a record.
  person.title = "ZEO"
  try tx.update(person)

  // Delete a record.
  try tx.delete(person)
  
  // Reinsert thew same record
  let newPerson = try tx.insert(person) // gets new ID!
}

Fetching Individual Columns

One of the advantages of SQL is that individual columns can be selected and updated for maximum efficiency. Only things that are required need to be fetched (vs. full records):

// Fetch just the `id` and `name` columns:
let people = try await db.select(from: \.people, \.id, \.name) {
  $0.id > 2 && $0.title == nil
}

// Bulk update a specific column:
try db.update(\.people, set: \.title, to: nil, where: { record in
  record.name.hasPrefix("Duck")
})

The references are fully typesafe down to the schema, only columns contained in the person table can be specified.

Dependency free SQLite3 API

The toolkit is also useful for cases in which the extra dependency on Lighter is not desirable. For such the generator can produce database specific Swift APIs that work alongside the regular SQLite API.

// Open the database, can also just use `sqlite3_open_v2`:
var db : OpaquePointer!
sqlite3_open_contacts("contacts.db", &db)
defer { sqlite3_close(db) }

// Fetch a person by primary key:
let person = sqlite3_person_find(db, 2)
  
// Fetch and filter people:
let people = sqlite3_people_fetch(db) {
  $0.name.hasPrefix("Ja")
}

// Insert a record
var person = Person(id: 0, name: "Jason Bourne")
sqlite3_person_insert(db, &person)

There is another style the code generator can produce, it attaches the same functions to the generated types, e.g.:

let people = Person.fetch(in: db) { $0.name.hasPrefix("So") }
var person = Person.find(2, in: db)

person.name = "Bourne"
person.update(in: db)
person.delete(in: db)
person.insert(into: db)

The main advantage of using the raw API is that no extra dependency is necessary at all. The generated functions are completely self-contained and can literally be copied&pasted into places where needed.

Beautiful, autogenerated DocC API Comments

The Lighter code generator can also generate API comments for the database types.

Example: Northwind Database.