Building Type-Safe SQL

SQLite.swift features a powerful, type-safe expression layer that maps Swift types directly to their SQLite counterparts. This system allows you to construct complex queries with the confidence that they are syntactically correct and type-safe, all checked at compile time.

Swift to SQLite Type Mapping

Here are the fundamental type mappings used by the library:

Swift Type	SQLite Type
Int64*	INTEGER
Double	REAL
String	TEXT
nil	NULL
SQLite.Blob†	BLOB
URL	TEXT
UUID	TEXT
Date	TEXT

• While Int64 is the raw type (to preserve 64-bit integers on 32-bit platforms), Int and Bool are supported transparently.
• †SQLite.swift defines its own Blob structure to safely wrap byte data.

For information on supporting other types, see Advanced Topics: Custom Types.

Expressions

At the core of the expression builder are Expression objects. An Expression is a generic struct that describes a SQL entity, such as a column name, and its associated Swift type.

You will typically define expressions for each of your table's columns.

// Corresponds to: "id" INTEGER
let id = Expression<Int64>("id")

// Corresponds to: "email" TEXT
let email = Expression<String>("email")

// Corresponds to: "balance" REAL
let balance = Expression<Double>("balance")

// Corresponds to: "verified" INTEGER (used as BOOLEAN)
let verified = Expression<Bool>("verified")

To define a nullable column, use an optional type for the generic parameter:

// Corresponds to: "name" TEXT NULL
let name = Expression<String?>("name")

When creating tables, Expression<T> columns will automatically have a NOT NULL constraint, while Expression<T?> columns will be nullable.

Compound Expressions

These basic expressions are the building blocks for more complex logic. You can combine them using standard Swift operators, which SQLite.swift overloads to produce SQL fragments.

// WHERE ("age" >= 35)
age >= 35

// WHERE ("verified" AND "name" IS NOT NULL)
verified && name != nil

// WHERE (("balance" * 1.05) > 1000.0)
balance * 1.05 > 1000

These compound expressions are used throughout the library for filtering, ordering, and updating data.

Queries

A query object, typically created by instantiating a Table, View, or VirtualTable, represents a database table and serves as the starting point for building statements.

let users = Table("users")

With this users object, you can now chain methods to build and execute CREATE, INSERT, SELECT, UPDATE, and DELETE statements.

// SELECT * FROM "users" WHERE ("admin" = 1)
let adminUsersQuery = users.filter(admin == true)

// INSERT INTO "users" ("email") VALUES ('admin@example.com')
let insertAdmin = users.insert(email <- "admin@example.com")