Lighter

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!

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.

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).

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).

// 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 the same record
  let newPerson = try tx.insert(person) // gets new ID!
}

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.

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.

Example: Northwind Database.