Quick Start Guide

This guide will walk you through the essential steps to get up and running with SQLite.swift. We'll cover creating a database, defining a table, inserting data, and running queries.

First, make sure you have followed the Installation instructions and can import the library.

import SQLite

1. Connecting to a Database

All operations in SQLite.swift start with a Connection object. You can create a connection to a database on disk or an in-memory database.

For this guide, we'll use an in-memory database, which is great for testing and temporary storage as it's discarded when the connection is closed.

do {
    let db = try Connection(.inMemory)
    print("Database connection established.")
} catch {
    print("Failed to connect to database: \(error)")
}

!!! info All database operations in SQLite.swift can throw errors, so it's best practice to wrap your code in a do-catch block.

2. Defining Your Table and Columns

Next, define the structure of your table using Table and Expression objects. These objects represent your table and its columns in a type-safe way.

let users = Table("users")

let id = Expression<Int64>("id")
let email = Expression<String>("email")
let name = Expression<String?>("name") // Optional string

• Table("users") creates a reference to a SQL table named "users".
• Expression<Type>("column_name") defines a column. The generic parameter (Int64, String, String?) determines the data type and nullability of the column.

3. Creating the Table

With the table structure defined, you can run the CREATE TABLE statement.

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

• db.run() executes a statement that doesn't return rows.
• The create method uses a builder pattern to define columns and constraints like primaryKey, unique, and check.

4. Inserting Data

Now let's add some rows to our users table using the insert method.

// Insert a single user
let aliceId = try db.run(users.insert(email <- "alice@mac.com", name <- "Alice"))
print("Inserted Alice with ID: \(aliceId)")

// Insert multiple users at once
let lastRowId = try db.run(users.insertMany([
  [email <- "bob@mac.com"],
  [email <- "mallory@evil.com", name <- "Mallory"]
]))
print("Last inserted ID from batch: \(lastRowId)")

• The <- operator is used to create Setter objects, which pair a column with a value.
• The insert method returns the rowid of the newly inserted row.

5. Querying Data

To retrieve data, you use db.prepare() to create a statement that can be iterated over.

print("\nAll Users:")
for user in try db.prepare(users) {
    print("id: \(user[id]), email: \(user[email]), name: \(user[name])")
}
// id: 1, email: alice@mac.com, name: Optional("Alice")
// id: 2, email: bob@mac.com, name: nil
// id: 3, email: mallory@evil.com, name: Optional("Mallory")

You can access column values from the user row object using the same Expression objects as subscripts.

6. Filtering and Updating Data

Let's filter our query to find a specific user and then update their information.

// Find Alice by her ID
let aliceQuery = users.filter(id == aliceId)

if let alice = try db.pluck(aliceQuery) {
    print("\nFound Alice: \(alice[name] ?? "N/A")")
}

// Update Alice's name
try db.run(aliceQuery.update(name <- "Alice Liddell"))

// Verify the update
if let updatedAlice = try db.pluck(aliceQuery) {
    print("Updated Alice's name to: \(updatedAlice[name] ?? "N/A")")
}
// Updated Alice's name to: Alice Liddell

• filter() adds a WHERE clause to your query.
• pluck() fetches the first matching row.
• update() executes an UPDATE statement on the filtered query.

Full Example

Here is the complete code from this guide:

import SQLite

do {
    // 1. Connection
    let db = try Connection(.inMemory)

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

    // 3. Table Creation
    try db.run(users.create { t in
        t.column(id, primaryKey: true)
        t.column(email, unique: true, check: email.like("%@%"))
        t.column(name)
    })

    // 4. Insertion
    let aliceId = try db.run(users.insert(email <- "alice@mac.com", name <- "Alice"))
    _ = try db.run(users.insertMany([
      [email <- "bob@mac.com"],
      [email <- "mallory@evil.com", name <- "Mallory"]
    ]))

    // 5. Querying
    print("All Users:")
    for user in try db.prepare(users) {
        print("  id: \(user[id]), email: \(user[email]), name: \(user[name])")
    }

    // 6. Filtering and Updating
    let aliceQuery = users.filter(id == aliceId)
    try db.run(aliceQuery.update(name <- "Alice Liddell"))

    if let updatedAlice = try db.pluck(aliceQuery) {
        print("\nUpdated Alice's name to: \(updatedAlice[name] ?? "N/A")")
    }

} catch {
    print("An error occurred: \(error)")
}