Tables & CRUD Operations

This guide covers the fundamental Create, Read, Update, and Delete (CRUD) operations in SQLite.swift using the type-safe expression builder.

Creating Tables

You can create CREATE TABLE statements by calling the create method on a Table object. The builder closure allows you to define columns and constraints.

let users = Table("users")
let id = Expression<Int64>("id")
let email = Expression<String>("email")
let name = Expression<String?>("name")

try db.run(users.create { t in
    t.column(id, primaryKey: true)
    t.column(email, unique: true)
    t.column(name)
})
// SQL: CREATE TABLE "users" (
//        "id" INTEGER PRIMARY KEY NOT NULL,
//        "email" TEXT UNIQUE NOT NULL,
//        "name" TEXT
//      )

• Expression<T> columns are NOT NULL by default.
• Expression<T?> columns are nullable by default.

Column Constraints

The column method accepts several parameters to define constraints:

• primaryKey: t.column(id, primaryKey: true) or t.column(id, primaryKey: .autoincrement)
• unique: t.column(email, unique: true)
• check: t.column(email, check: email.like("%@%"))
• defaultValue: t.column(name, defaultValue: "Anonymous")
• collate: t.column(email, collate: .nocase)
• references: t.column(user_id, references: users, id)

Table Constraints

For constraints spanning multiple columns, use table-level functions:

• primaryKey(): t.primaryKey(userId, accountId)
• unique(): t.unique(firstName, lastName)
• check(): t.check(balance >= 0)
• foreignKey(): t.foreignKey(user_id, references: users, id, delete: .setNull)

Inserting and Upserting Rows

Inserting

To insert a row, call the insert method with a list of Setters, created using the <- operator. insert returns the rowid of the new row.

let rowid = try db.run(users.insert(email <- "alice@mac.com", name <- "Alice"))
// INSERT INTO "users" ("email", "name") VALUES ('alice@mac.com', 'Alice')

To handle potential constraint violations, you can specify an OnConflict resolution:

try db.run(users.insert(or: .replace, email <- "alice@mac.com", name <- "Alice B."))
// INSERT OR REPLACE INTO ...

Upserting (Insert or Update)

An "upsert" is an operation that inserts a row, but if the insertion would violate a uniqueness constraint, it performs an update instead. This requires SQLite 3.24.0+.

try db.run(users.upsert(email <- "alice@mac.com", name <- "Alice C.", onConflictOf: email))
// INSERT INTO "users" ("email", "name") VALUES ('alice@mac.com', 'Alice C.')
// ON CONFLICT ("email") DO UPDATE SET "name" = "excluded"."name"

Selecting Rows

Query objects are lazily executed when you iterate over them.

Iterating and Accessing Values

Prepare a query with db.prepare() and loop through the results. Access column values using the expression as a subscript on the Row object.

for user in try db.prepare(users) {
    print("id: \(user[id]), email: \(user[email])")
    // id: 1, email: alice@mac.com
}

For safer error handling, especially in complex iterations, use the RowIterator API:

let rowIterator = try db.prepareRowIterator(users)
while let row = try rowIterator.failableNext() {
    // Handle row
}

Plucking a Single Row

To fetch just the first matching row, use db.pluck().

if let user = try db.pluck(users) {
    print("First user: \(user[email])")
}
// SELECT * FROM "users" LIMIT 1

Building Complex Queries

You can chain methods to build more specific queries:

• select: Choose which columns to return.

  users.select(id, email)
  // SELECT "id", "email" FROM "users"

• join: Combine rows from multiple tables.

  users.join(posts, on: posts[userId] == users[id])
  // SELECT * FROM "users" INNER JOIN "posts" ON ("posts"."user_id" = "users"."id")

• filter / where: Add a WHERE clause.

  users.filter(age >= 21 && name != nil)
  // SELECT * FROM "users" WHERE (("age" >= 21) AND ("name" IS NOT NULL))

• order: Sort the results.

  users.order(email.desc, name.asc)
  // SELECT * FROM "users" ORDER BY "email" DESC, "name" ASC

• limit: Paginate results.

  users.limit(10, offset: 20)
  // SELECT * FROM "users" LIMIT 10 OFFSET 20

Aggregation

Perform aggregate calculations directly on a query.

let count = try db.scalar(users.count)
// SELECT count(*) FROM "users"

let maxAge = try db.scalar(users.select(age.max))
// SELECT max("age") FROM "users"

Available aggregates include count, max, min, average, sum, and total.

Updating Rows

To update rows, chain the update method onto a filtered query. It returns the number of rows changed.

let alice = users.filter(id == 1)
let changes = try db.run(alice.update(email <- "alice.liddell@mac.com"))

if changes > 0 {
    print("Updated Alice's email")
}
// UPDATE "users" SET "email" = 'alice.liddell@mac.com' WHERE ("id" = 1)

Calling update on an unfiltered query will update every row in the table!

Deleting Rows

Similarly, use the delete method on a filtered query. It returns the number of rows deleted.

let alice = users.filter(id == 1)
let changes = try db.run(alice.delete())

if changes > 0 {
    print("Deleted Alice")
}
// DELETE FROM "users" WHERE ("id" = 1)

Calling delete on an unfiltered query will delete every row in the table!