Repository files navigation

swift-sqlcipher is a C source packaging ofSQLite3with built-inSQLCipher andFull-text search extensions,along with aSQLiteDB Swift package that providesAPI parity with the venerableSQLite.swift project.

This is a stand-alone and platform-agnostic project, anddoes not presume the presence of any SQLite binary.It is therefore suitable for embedded projects orenvironments without any accessible systemsqlite3library (e.g., Android, Windows).

• A pure-Swift interface
• Embeds a modern and consistent sqlite (3.51.1) and sqlcipher (4.12.0) build in the library
• Works on iOS, macOS, Android, Windows, and Linux
• A type-safe, optional-aware SQL expression builder
• A flexible, chainable, lazy-executing query layer
• Automatically-typed data access
• A lightweight, uncomplicated query and parameter binding interface
• Developer-friendly error handling and debugging
• Full-text search support
• Extensively tested
• SQLCipher support using the embeddedLibTomCrypt library
• Schema query/migration

• swift-sqlcipher Documentation
  • Installation
    • Swift Package Manager
  • Getting Started
    • Connecting to a Database
      • Read-Write Databases
      • Read-Only Databases
      • In a shared group container
      • In-Memory Databases
      • URI parameters
      • Thread-Safety
  • Building Type-Safe SQL
    • Expressions
    • Compound Expressions
    • Queries
  • Creating a Table
    • Create Table Options
    • Column Constraints
    • Table Constraints
  • Inserting Rows
    • Handling SQLite errors
    • Setters-Infix Setters-Postfix Setters
  • Selecting Rows
    • Iterating and Accessing Values
      • Failable iteration
    • Plucking Rows
    • Building Complex Queries
      • Selecting Columns
      • Joining Other Tables
        • Column Namespacing
        • Table Aliasing
      • Filtering Rows
        • Filter Operators and Functions
          • Infix Filter Operators
          • Prefix Filter Operators
          • Filtering Functions
      • Sorting Rows
      • Limiting and Paging Results
      • Recursive and Hierarchical Queries
      • Aggregation
  • Upserting Rows
  • Updating Rows
  • Deleting Rows
  • Transactions and Savepoints
  • Querying the Schema
    • Indexes and Columns
  • Altering the Schema
    • Renaming Tables
    • Dropping Tables
    • Adding Columns
      • Added Column Constraints
    • SchemaChanger
      • Adding Columns
      • Renaming Columns
      • Dropping Columns
      • Renaming/Dropping Tables
    • Indexes
      • Creating Indexes
      • Dropping Indexes
    • Migrations and Schema Versioning
  • Custom Types
    • Date-Time Values
    • Binary Data
  • Codable Types
    • Inserting Codable Types
    • Updating Codable Types
    • Retrieving Codable Types
    • Restrictions
  • Other Operators-Other Infix Operators-Other Prefix Operators
  • Core SQLite Functions
  • Aggregate SQLite Functions
  • Window SQLite Functions
  • Date and Time functions
  • Custom SQL Functions
  • Custom Aggregations
  • Custom Collations
  • Full-text Search
    • FTS5
  • Executing Arbitrary SQL
  • Online Database Backup
  • Attaching and detaching databases
  • Logging
  • Database Middleware Packages
  • Communication

TheSwift Package Manager is a tool for managing the distribution ofSwift code. It’s integrated with the Swift build system to automate theprocess of downloading, compiling, and linking dependencies.

1. Add the following to yourPackage.swift file:

dependencies:[.package(url:"https://github.com/skiptools/swift-sqlcipher.git", from:"1.4.0")]

2. Build your project:

$ swift build

If you just want to use the low-level SQLCipher functions instead of SQLite3,you can conditionally import the package:

#if canImport(SQLCipher)import SQLCipher#elseimport SQLite3#endif

SQLCipher is API-compatible with the SQLite3 framework that is included on many platforms,including iOS and Android.

The remainder of this document assumes you want to use the higher-level SQLiteDBinterface atop the SQLCipher package. This interface is based on theSQLite.swift package, butuses SQLCipher rather than SQLite3 and enables various features like encryption,FTS5, and JSON support.

To use SQLiteDB classes or structures in your target’s source file, firstimport theSQLiteDB module.

import SQLiteDB

Database connections are established using theConnection class. Aconnection is initialized with a path to a database. SQLite will attempt tocreate the database file if it does not already exist.

letdb=tryConnection("path/to/database.db")

You can create a writable database in your app’sApplication Supportdirectory.

letpath=URL.applicationSupportDirectory// create parent directory inside application support if it doesn’t existtryFileManager.default.createDirectory(atPath: path, withIntermediateDirectories:true, attributes:nil)letdb=tryConnection(dbURL.appendingPathComponent("database.db").path)

If you bundle a database with your app (i.e., you’ve copied a database fileinto your Xcode project and added it to your application target), you canestablish aread-only connection to it.

letpath=Bundle.main.path(forResource:"db", ofType:"sqlite3")!letdb=tryConnection(path, readonly:true)

It is not recommend to store databases in ashared group container,some users have reported crashes (#1042).

If you omit the path, SQLiteDB will provision anin-memorydatabase.

letdb=tryConnection() // equivalent to `Connection(.inMemory)`

To create a temporary, disk-backed database, pass an empty file name.

letdb=tryConnection(.temporary)

In-memory databases are automatically deleted when the database connection isclosed.

We can pass.uri to theConnection initializer to control more aspects ofthe database connection with the help ofURIQueryParameters:

letdb=tryConnection(.uri("file.sqlite", parameters:[.cache(.private),.noLock(true)]))

SeeUniform Resource Identifiers for more details.

Every Connection comes equipped with its own serial queue for statementexecution and can be safely accessed across threads. Threads that opentransactions and savepoints will block other threads from executingstatements while the transaction is open.

If you maintain multiple connections for a single database, consider setting a timeout(in seconds)or a busy handler. There can only be one active at a time, so setting a busyhandler will effectively overridebusyTimeout.

db.busyTimeout=5 // error after 5 seconds (does multiple retries)db.busyHandler({ triesin    tries<3  // error after 3 tries})

SQLiteDB comes with a typed expression layer that directly mapsSwift typesto theirSQLite counterparts.

*WhileInt64 is the basic, raw type (to preserve 64-bit integers on32-bit platforms),Int andBool work transparently.

†SQLiteDB defines its ownBlob structure, which safely wraps theunderlying bytes.

SeeCustom Types for more information about extendingother classes and structures to work with SQLiteDB.

SeeExecuting Arbitrary SQL to forego the typedlayer and execute raw SQL, instead.

These expressions (in the form of the structure,Expression) build on one another and, with a query(QueryType), can create and execute SQL statements.

SQLExpressions are generic structures associated with a type (built-in orcustom), raw SQL, and(optionally) values to bind to that SQL. Typically, you will only explicitlycreate expressions to describe your columns, and typically only once percolumn.

letid=SQLExpression<Int64>("id")letemail=SQLExpression<String>("email")letbalance=SQLExpression<Double>("balance")letverified=SQLExpression<Bool>("verified")

Use optional generics for expressions that can evaluate toNULL.

letname=SQLExpression<String?>("name")

Expressions can be combined with other expressions and types usingfilter operators and functions(as well as othernon-filter operators andfunctions). These building blocks can create complex SQLite statements.

Queries are structures that reference a database and table name, and can beused to build a variety of statements using expressions. We can create aquery by initializing aTable,View, orVirtualTable.

letusers=Table("users")

Assumingthe table exists, we can immediatelyinsert,select,update, anddelete rows.

We can buildCREATE TABLEstatements by calling thecreate function on aTable. The following is a basic example ofSQLiteDB code (using theexpressions andquery above) and the corresponding SQL it generates.

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

TheTable.create function has several default parameters we can override.

• temporary adds aTEMPORARY clause to theCREATE TABLE statement (tocreate a temporary table that will automatically drop when the databaseconnection closes). Default:false.

  try db.run(users.create(temporary:true){ tin /* ... */})// CREATE TEMPORARY TABLE "users" -- ...

• ifNotExists adds anIF NOT EXISTS clause to theCREATE TABLEstatement (which will bail out gracefully if the table already exists).Default:false.

  try db.run(users.create(ifNotExists:true){ tin /* ... */})// CREATE TABLE "users" IF NOT EXISTS -- ...

Thecolumn function is used for a single column definition. It takes anexpression describing the column name and type, and acceptsseveral parameters that map to various column constraints and clauses.

• primaryKey adds aPRIMARY KEY constraint to a single column.

  t.column(id, primaryKey:true)// "id" INTEGER PRIMARY KEY NOT NULLt.column(id, primaryKey:.autoincrement)// "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

• unique adds aUNIQUE constraint to the column. (See theuniquefunction underTable Constraints for uniquenessover multiple columns).

  t.column(email, unique:true)// "email" TEXT UNIQUE NOT NULL

• check attaches aCHECK constraint to a column definition in the formof a boolean expression (SQLExpression<Bool>). Boolean expressions can beeasily built usingfilter operators and functions.(See also thecheck function underTable Constraints.)

  t.column(email, check: email.like("%@%"))// "email" TEXT NOT NULL CHECK ("email" LIKE '%@%')

• defaultValue adds aDEFAULT clause to a column definition andonlyaccepts a value (or expression) matching the column’s type. This value isused if none is explicitly provided duringanINSERT.

  t.column(name, defaultValue:"Anonymous")// "name" TEXT DEFAULT 'Anonymous'

• collate adds aCOLLATE clause toSQLExpression<String> (andSQLExpression<String?>) column definitions witha collating sequencedefined in theCollation enumeration.

  t.column(email, collate:.nocase)// "email" TEXT NOT NULL COLLATE "NOCASE"t.column(name, collate:.rtrim)// "name" TEXT COLLATE "RTRIM"

• references adds aREFERENCES clause toSQLExpression<Int64> (andSQLExpression<Int64?>) column definitions and accepts a table(SchemaType) or namespaced column expression. (See theforeignKeyfunction underTable Constraints for non-integerforeign key support.)

  t.column(user_id, references: users, id)// "user_id" INTEGER REFERENCES "users" ("id")

Additional constraints may be provided outside the scope of a single columnusing the following functions.

• primaryKey adds aPRIMARY KEY constraint to the table. Unlikethecolumn constraint, above, it supports all SQLitetypes,ascending and descending orders, and composite(multiple column) keys.

  t.primaryKey(email.asc, name)// PRIMARY KEY("email" ASC, "name")

• unique adds aUNIQUE constraint to the table. Unlikethe column constraint, above, itsupports composite (multiplecolumn) constraints.

  t.unique(local, domain)// UNIQUE("local", "domain")

• check adds aCHECK constraint to the table in the form of a booleanexpression (SQLExpression<Bool>). Boolean expressions can be easily builtusingfilter operators and functions.(See also thecheck parameter underColumn Constraints.)

  t.check(balance>=0)// CHECK ("balance" >= 0.0)

• foreignKey adds aFOREIGN KEY constraint to the table. Unlikethereferences constraint, above, it supports allSQLite types, bothON UPDATE andON DELETEactions, andcomposite (multiple column) keys.

  t.foreignKey(user_id, references: users, id, delete:.setNull)// FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE SET NULL

We can insert rows into a table by calling aquery’sinsertfunction with a list ofsetters—typicallytyped columnexpressions and values (which can also be expressions)—eachjoined by the<- operator.

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

Theinsert function, when run successfully, returns anInt64 representingthe inserted row’sROWID.

do{letrowid=try db.run(users.insert(email<-"alice@mac.com"))print("inserted id:\(rowid)")}catch{print("insertion failed:\(error)")}

Multiple rows can be inserted at once by similarly callinginsertMany with an array ofper-rowsetters.

do{letlastRowid=try db.run(users.insertMany([mail<-"alice@mac.com"],[email<-"geoff@mac.com"]))print("last inserted id:\(lastRowid)")}catch{print("insertion failed:\(error)")}

Theupdate anddelete functionsfollow similar patterns.

try db.run(timestamps.insert())// INSERT INTO "timestamps" DEFAULT VALUES

You can pattern match on the error to selectively catch SQLite errors. For example, tospecifically handle constraint errors (SQLITE_CONSTRAINT):

do{try db.run(users.insert(email<-"alice@mac.com"))try db.run(users.insert(email<-"alice@mac.com"))}catchletResult.error(message, code, statement)where code== SQLITE_CONSTRAINT{print("constraint failed:\(message), in\(statement)")}catchlet error{print("insertion failed:\(error)")}

TheResult.error type contains the English-language text that describes the error (message),the errorcode (seeSQLite result code listfor details) and a optional reference to thestatement which produced the error.

SQLiteDB typically uses the<- operator to set values duringinserts andupdates.

try db.run(counter.update(count<-0))// UPDATE "counters" SET "count" = 0 WHERE ("id" = 1)

There are also a number of convenience setters that take the existing valueinto account using native Swift operators.

For example, to atomically increment a column, we can use++:

try db.run(counter.update(count++)) // equivalent to `counter.update(count -> count + 1)`// UPDATE "counters" SET "count" = "count" + 1 WHERE ("id" = 1)

To take an amount and “move” it via transaction, we can use-= and+=:

letamount=100.0try db.transaction{try db.run(alice.update(balance-= amount))try db.run(betty.update(balance+= amount))}// BEGIN DEFERRED TRANSACTION// UPDATE "users" SET "balance" = "balance" - 100.0 WHERE ("id" = 1)// UPDATE "users" SET "balance" = "balance" + 100.0 WHERE ("id" = 2)// COMMIT TRANSACTION

Query structures areSELECT statements waiting to happen. Theyexecute viaiteration andother means of sequence access.

Preparedqueries execute lazily upon iteration. Each row isreturned as aRow object, which can be subscripted with acolumnexpression matching one of the columns returned.

foruserintry db.prepare(users){print("id:\(user[id]), email:\(user[email]), name:\(user[name])")    // id: 1, email: alice@mac.com, name: Optional("Alice")}// SELECT * FROM "users"

SQLExpression<T> column values areautomatically unwrapped (we’ve made apromise to the compiler that they’ll never beNULL), whileSQLExpression<T?>values remain wrapped.

⚠ Column subscripts onRow will force try and abort execution in error cases.If you want to handle this yourself, useRow.get(_ column: SQLExpression<V>):

foruserintry db.prepare(users){do{print("name:\(try user.get(name))")}catch{        // handle}}

Note that the iterator can throwundeclared database errors at any point duringiteration:

letquery=try db.prepare(users)foruserin query{    // 💥 can throw an error here}

It is therefore recommended using theRowIterator API instead,which has explicit error handling:

// option 1: convert results into an Array of rowsletrowIterator=try db.prepareRowIterator(users)foruserintryArray(rowIterator){print("id:\(user[id]), email:\(user[email])")}/// option 2: transform results using `map()`letmapRowIterator=try db.prepareRowIterator(users)letuserIds=try mapRowIterator.map{$0[id]}/// option 3: handle each row individually with `failableNext()`do{whilelet row=try rowIterator.failableNext(){        // Handle row}}catch{    // Handle error}

We can pluck the first row by passing a query to thepluck function on adatabase connection.

iflet user=try db.pluck(users){ /* ... */} // Row// SELECT * FROM "users" LIMIT 1

To collect all rows into an array, we can simply wrap the sequence (thoughthis is not always the most memory-efficient idea).

letall=Array(try db.prepare(users))// SELECT * FROM "users"

Queries have a number of chainable functions that can be used(withexpressions) to add and modifya number ofclauses to the underlyingstatement.

letquery= users.select(email)           // SELECT "email" FROM "users".filter(name!=nil)     // WHERE "name" IS NOT NULL.order(email.desc, name) // ORDER BY "email" DESC, "name".limit(5, offset:1)     // LIMIT 5 OFFSET 1

By default,queries select every column of the result set (usingSELECT *). We can use theselect function with a list ofexpressions to return specific columns instead.

foruserintry db.prepare(users.select(id, email)){print("id:\(user[id]), email:\(user[email])")    // id: 1, email: alice@mac.com}// SELECT "id", "email" FROM "users"

We can access the results of more complex expressions by holding onto areference of the expression itself.

letsentence= name+" is"+ cast(age)asSQLExpression<String?>+" years old!"foruserin users.select(sentence){print(user[sentence])    // Optional("Alice is 30 years old!")}// SELECT ((("name" || ' is ') || CAST ("age" AS TEXT)) || ' years old!') FROM "users"

We can join tables using aquery’sjoin function.

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

Thejoin function takes aquery object (for the table beingjoined on), a join condition (on), and is prefixed with an optional jointype (default:.inner). Join conditions can be built usingfilteroperators and functions, generally requirenamespacing, and sometimes requirealiasing.

When joining tables, column names can become ambiguous.E.g., both tablesmay have anid column.

letquery= users.join(posts, on: user_id== id)// assertion failure: ambiguous column 'id'

We can disambiguate by namespacingid.

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

Namespacing is achieved by subscripting aquery with acolumnexpression (e.g.,users[id] above becomesusers.id).

letquery= users.select(users[*])// SELECT "users".* FROM "users"

Occasionally, we need to join a table to itself, in which case we must aliasthe table with another name. We can achieve this using thequery’salias function.

letmanagers= users.alias("managers")letquery= users.join(managers, on:managers[id]==users[managerId])// SELECT * FROM "users"// INNER JOIN ("users") AS "managers" ON ("managers"."id" = "users"."manager_id")

If query results can have ambiguous column names, row values should beaccessed with namespacedcolumn expressions. In the abovecase,SELECT * immediately namespaces all columns of the result set.

letuser=try db.pluck(query)user[id]           // fatal error: ambiguous column 'id'                   // (please disambiguate: ["users"."id", "managers"."id"])user[users[id]]    // returns "users"."id"user[managers[id]] // returns "managers"."id"

SQLiteDB filters rows using aquery’sfilter function witha booleanexpression (SQLExpression<Bool>).

users.filter(id==1)// SELECT * FROM "users" WHERE ("id" = 1)users.filter([1,2,3,4,5].contains(id))// SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))users.filter(email.like("%@mac.com"))// SELECT * FROM "users" WHERE ("email" LIKE '%@mac.com')users.filter(verified && name.lowercaseString=="alice")// SELECT * FROM "users" WHERE ("verified" AND (lower("name") == 'alice'))users.filter(verified || balance>=10_000)// SELECT * FROM "users" WHERE ("verified" OR ("balance" >= 10000.0))

We can build our own boolean expressions by using one of the manyfilteroperators and functions.

Instead offilter we can also use thewhere function which is an alias:

users.where(id==1)// SELECT * FROM "users" WHERE ("id" = 1)

SQLiteDB defines a number of operators for building filtering predicates.Operators and functions work together in a type-safe manner, so attempting toequate or compare different types will prevent compilation.

• When comparing againstnil, SQLiteDB will useIS andIS NOTaccordingly.

We can pre-sort returned rows using thequery’sorder function.

E.g., to return users sorted byemail, thenname, in ascending order:

users.order(email, name)// SELECT * FROM "users" ORDER BY "email", "name"

Theorder function takes a list ofcolumn expressions.

Expression objects have two computed properties to assist sorting:ascanddesc. These properties append the expression withASC andDESC tomark ascending and descending order respectively.

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

We can limit and skip returned rows using aquery’slimitfunction (and its optionaloffset parameter).

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

We can perform a recursive or hierarchical query using aquery'sWITH function.

// Get the management chain for the manager with id == 8letchain=Table("chain")letid=SQLExpression<Int64>("id")letmanagerId=SQLExpression<Int64>("manager_id")letquery= managers.where(id==8).union(chain.join(managers, on:chain[managerId]==managers[id])chain.with(chain, recursive:true, as: query)// WITH RECURSIVE//   "chain" AS (//     SELECT * FROM "managers" WHERE "id" = 8//     UNION//     SELECT * from "chain"//     JOIN "managers" ON "chain"."manager_id" = "managers"."id"//   )// SELECT * FROM "chain"

Column names and a materialization hint can optionally be provided.

// Add a "level" column to the query representing manager's position in the chainletlevel=SQLExpression<Int64>("level")letqueryWithLevel=    managers.select(id, managerId,0).where(id==8).union(            chain.select(managers[id],managers[manager_id], level+1).join(managers, on:chain[managerId]==managers[id]))chain.with(chain,           columns:[id, managerId, level],           recursive:true,           hint:.materialize,           as: queryWithLevel)// WITH RECURSIVE//   "chain" ("id", "manager_id", "level") AS MATERIALIZED (//     SELECT ("id", "manager_id", 0) FROM "managers" WHERE "id" = 8//     UNION//     SELECT ("manager"."id", "manager"."manager_id", "level" + 1) FROM "chain"//     JOIN "managers" ON "chain"."manager_id" = "managers"."id"//   )// SELECT * FROM "chain"

Queries come with a number of functions that quickly returnaggregate scalar values from the table. These mirror thecore aggregatefunctions and are executed immediately againstthe query.

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

Filtered queries will appropriately filter aggregate values.

letcount=try db.scalar(users.filter(name!=nil).count)// SELECT count(*) FROM "users" WHERE "name" IS NOT NULL

• count as a computed property on a query (see examples above) returnsthe total number of rows matching the query.

  count as a computed property on a column expression returns the totalnumber of rows where that column is notNULL.

  letcount=try db.scalar(users.select(name.count)) // -> Int// SELECT count("name") FROM "users"

• max takes a comparable column expression and returns the largest valueif any exists.

  letmax=try db.scalar(users.select(id.max)) // -> Int64?// SELECT max("id") FROM "users"

• min takes a comparable column expression and returns the smallest valueif any exists.

  letmin=try db.scalar(users.select(id.min)) // -> Int64?// SELECT min("id") FROM "users"

• average takes a numeric column expression and returns the average rowvalue (as aDouble) if any exists.

  letaverage=try db.scalar(users.select(balance.average)) // -> Double?// SELECT avg("balance") FROM "users"

• sum takes a numeric column expression and returns the sum total of allrows if any exist.

  letsum=try db.scalar(users.select(balance.sum)) // -> Double?// SELECT sum("balance") FROM "users"

• total, likesum, takes a numeric column expression and returns thesum total of all rows, but in this case always returns aDouble, andreturns0.0 for an empty query.

  lettotal=try db.scalar(users.select(balance.total)) // -> Double// SELECT total("balance") FROM "users"

letcount=try db.scalar(users.select(name.distinct.count) // -> Int// SELECT count(DISTINCT "name") FROM "users"

We can upsert rows into a table by calling aquery’supsertfunction with a list ofsetters—typicallytyped columnexpressions and values (which can also be expressions)—eachjoined by the<- operator. Upserting is like inserting, except if there is aconflict on the specified column value, SQLite will perform an update on the row instead.

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

Theupsert function, when run successfully, returns anInt64 representingthe inserted row’sROWID.

do{letrowid=try db.run(users.upsert(email<-"alice@mac.com", name<-"Alice", onConflictOf: email))print("inserted id:\(rowid)")}catch{print("insertion failed:\(error)")}

Theinsert,update, anddelete functionsfollow similar patterns.

We can update a table’s rows by calling aquery’supdatefunction with a list ofsetters—typicallytyped columnexpressions and values (which can also be expressions)—eachjoined by the<- operator.

When an unscoped query callsupdate, it will updateevery row in thetable.

try db.run(users.update(email<-"alice@me.com"))// UPDATE "users" SET "email" = 'alice@me.com'

Be sure to scopeUPDATE statements beforehand usingthefilter function.

letalice= users.filter(id==1)try db.run(alice.update(email<-"alice@me.com"))// UPDATE "users" SET "email" = 'alice@me.com' WHERE ("id" = 1)

Theupdate function returns anInt representing the number of updatedrows.

do{iftry db.run(alice.update(email<-"alice@me.com"))>0{print("updated alice")}else{print("alice not found")}}catch{print("update failed:\(error)")}

We can delete rows from a table by calling aquery’sdeletefunction.

When an unscoped query callsdelete, it will deleteevery row in thetable.

try db.run(users.delete())// DELETE FROM "users"

Be sure to scopeDELETE statements beforehand usingthefilter function.

letalice= users.filter(id==1)try db.run(alice.delete())// DELETE FROM "users" WHERE ("id" = 1)

Thedelete function returns anInt representing the number of deletedrows.

do{iftry db.run(alice.delete())>0{print("deleted alice")}else{print("alice not found")}}catch{print("delete failed:\(error)")}

Using thetransaction andsavepoint functions, we can run a series ofstatements in a transaction. If a single statement fails or the block throwsan error, the changes will be rolled back.

try db.transaction{letrowid=try db.run(users.insert(email<-"betty@icloud.com"))try db.run(users.insert(email<-"cathy@icloud.com", managerId<- rowid))}// BEGIN DEFERRED TRANSACTION// INSERT INTO "users" ("email") VALUES ('betty@icloud.com')// INSERT INTO "users" ("email", "manager_id") VALUES ('cathy@icloud.com', 2)// COMMIT TRANSACTION

We can obtain generic information about objects in the current schema with aSchemaReader:

letschema= db.schema

To query the data:

letindexes=try schema.objectDefinitions(type:.index)lettables=try schema.objectDefinitions(type:.table)lettriggers=try schema.objectDefinitions(type:.trigger)

Specialized methods are available to get more detailed information:

letindexes=try schema.indexDefinitions("users")letcolumns=try schema.columnDefinitions("users")forindexin indexes{print("\(index.name) columns:\(index.columns))")}forcolumnin columns{print("\(column.name) pk:\(column.primaryKey) nullable:\(column.nullable)")}

SQLiteDB comes with several functions (in addition toTable.create) foraltering a database schema in a type-safe manner.

We can build anALTER TABLE … RENAME TO statement by calling therenamefunction on aTable orVirtualTable.

try db.run(users.rename(Table("users_old")))// ALTER TABLE "users" RENAME TO "users_old"

We can buildDROP TABLE statementsby calling thedropTable function on aSchemaType.

try db.run(users.drop())// DROP TABLE "users"

Thedrop function has one additional parameter,ifExists, which (whentrue) adds anIF EXISTS clause to the statement.

try db.run(users.drop(ifExists:true))// DROP TABLE IF EXISTS "users"

We can add columns to a table by callingaddColumn function on aTable.SQLiteDB enforcesthe same limited subset ofALTER TABLE that SQLite supports.

try db.run(users.addColumn(suffix))// ALTER TABLE "users" ADD COLUMN "suffix" TEXT

TheaddColumn function shares several of the samecolumn functionparameters used whencreatingtables.

• check attaches aCHECK constraint to a column definition in the formof a boolean expression (SQLExpression<Bool>). (See also thecheckfunction underTable Constraints.)

  try db.run(users.addColumn(suffix, check:["JR","SR"].contains(suffix)))// ALTER TABLE "users" ADD COLUMN "suffix" TEXT CHECK ("suffix" IN ('JR', 'SR'))

• defaultValue adds aDEFAULT clause to a column definition andonlyaccepts a value matching the column’s type. This value is used if none isexplicitly provided duringanINSERT.

  try db.run(users.addColumn(suffix, defaultValue:"SR"))// ALTER TABLE "users" ADD COLUMN "suffix" TEXT DEFAULT 'SR'

• collate adds aCOLLATE clause toSQLExpression<String> (andSQLExpression<String?>) column definitions witha collatingsequence defined in theCollation enumeration.

  try db.run(users.addColumn(email, collate:.nocase))// ALTER TABLE "users" ADD COLUMN "email" TEXT NOT NULL COLLATE "NOCASE"try db.run(users.addColumn(name, collate:.rtrim))// ALTER TABLE "users" ADD COLUMN "name" TEXT COLLATE "RTRIM"

• references adds aREFERENCES clause toInt64 (andInt64?) columndefinitions and accepts a table or namespaced column expression. (See theforeignKey function underTable Constraints fornon-integer foreign key support.)

  try db.run(posts.addColumn(userId, references: users, id)// ALTER TABLE "posts" ADD COLUMN "user_id" INTEGER REFERENCES "users" ("id")

TheSchemaChanger is an alternative API to perform more complexmigrations such as renaming columns. These operations work with all versions ofSQLite but use SQL statements such asALTER TABLE RENAME COLUMN when available.

letnewColumn=ColumnDefinition(    name:"new_text_column",    type:.TEXT,    nullable:true,    defaultValue:.stringLiteral("foo"))letschemaChanger=SchemaChanger(connection: db)try schemaChanger.alter(table:"users"){ tablein    table.add(column: newColumn)}

letschemaChanger=SchemaChanger(connection: db)try schemaChanger.alter(table:"users"){ tablein    table.rename(column:"old_name", to:"new_name")}

letschemaChanger=SchemaChanger(connection: db)try schemaChanger.alter(table:"users"){ tablein    table.drop(column:"email")}

letschemaChanger=SchemaChanger(connection: db)try schemaChanger.rename(table:"users", to:"users_new")try schemaChanger.drop(table:"emails", ifExists:false)

We can buildCREATE INDEX statementsby calling thecreateIndex function on aSchemaType.

try db.run(users.createIndex(email))// CREATE INDEX "index_users_on_email" ON "users" ("email")

The index name is generated automatically based on the table and columnnames.

ThecreateIndex function has a couple default parameters we can override.

• unique adds aUNIQUE constraint to the index. Default:false.

  try db.run(users.createIndex(email, unique:true))// CREATE UNIQUE INDEX "index_users_on_email" ON "users" ("email")

• ifNotExists adds anIF NOT EXISTS clause to theCREATE TABLEstatement (which will bail out gracefully if the table already exists).Default:false.

  try db.run(users.createIndex(email, ifNotExists:true))// CREATE INDEX IF NOT EXISTS "index_users_on_email" ON "users" ("email")

We can buildDROP INDEX statements bycalling thedropIndex function on aSchemaType.

try db.run(users.dropIndex(email))// DROP INDEX "index_users_on_email"

ThedropIndex function has one additional parameter,ifExists, which(whentrue) adds anIF EXISTS clause to the statement.

try db.run(users.dropIndex(email, ifExists:true))// DROP INDEX IF EXISTS "index_users_on_email"

You can use the convenience property onConnection to query and set thePRAGMA user_version.

This is a great way to manage your schema’s version over migrations.You can conditionally run your migrations along the lines of:

if db.userVersion==0{    // handle first migration    db.userVersion=1}if db.userVersion==1{    // handle second migration    db.userVersion=2}

For more complex migration requirements check out the schema managementsystemSQLiteMigrationManager.swift.

SQLiteDB supports serializing and deserializing any custom type as longas it conforms to theValue protocol.

protocolValue{typealias Datatype: BindingclassvardeclaredDatatype:String{get}classfunc fromDatatypeValue(datatypeValue:Datatype)->SelfvardatatypeValue:Datatype{get}}

TheDatatype must be one of the basic Swift types that values are bridgedthrough before serialization and deserialization (seeBuilding Type-Safe SQL for a list of types).

In SQLite,DATETIME columns can be treated as strings or numbers, so we cantransparently bridgeDate objects through Swift’sString types.

We can use these types directly in SQLite statements.

letpublished_at=SQLExpression<Date>("published_at")letpublished= posts.filter(published_at<=Date())// SELECT * FROM "posts" WHERE "published_at" <= '2014-11-18T12:45:30.000'letstartDate=Date(timeIntervalSince1970:0)letpublished= posts.filter(startDate...Date()~= published_at)// SELECT * FROM "posts" WHERE "published_at" BETWEEN '1970-01-01T00:00:00.000' AND '2014-11-18T12:45:30.000'

We can bridge any type that can be initialized from and encoded toData.

extensionUIImage:Value{publicclassvardeclaredDatatype:String{returnBlob.declaredDatatype}publicclassfunc fromDatatypeValue(blobValue:Blob)->UIImage{returnUIImage(data:Data.fromDatatypeValue(blobValue))!}publicvardatatypeValue:Blob{returnUIImagePNGRepresentation(self)!.datatypeValue}}

Codable types were introduced as a partof Swift 4 to allow serializing and deserializing types. SQLiteDB supportsthe insertion, updating, and retrieval of basic Codable types.

Queries have a method to allow inserting anEncodable type.

structUser:Encodable{letname:String}try db.run(users.insert(User(name:"test")))

There are two other parameters also available to this method:

• userInfo is a dictionary that is passed to the encoder and made availableto encodable types to allow customizing their behavior.

• otherSetters allows you to specify additional setters on top of thosethat are generated from the encodable types themselves.

Queries have a method to allow updating an Encodable type.

try db.run(users.filter(id== userId).update(user))

⚠ Unless filtered, using the update method on an instance of a Codabletype updates all table rows.

There are two other parameters also available to this method:

• userInfo is a dictionary that is passed to the encoder and made availableto encodable types to allow customizing their behavior.

• otherSetters allows you to specify additional setters on top of thosethat are generated from the encodable types themselves.

Rows have a method to decode aDecodable type.

letloadedUsers:[User]=try db.prepare(users).map{ rowinreturntry row.decode()}

You can also create a decoder to use manually yourself. This can be usefulfor example if you are using theFacade pattern to hidesubclasses behind a super class. For example, you may want to encode an Imagetype that can be multiple different formats such as PNGImage, JPGImage, orHEIFImage. You will need to determine the correct subclass before you knowwhich type to decode.

enumImageCodingKeys:String,CodingKey{case kind}enumImageKind:Int,Codable{case png, jpg, heif}letloadedImages:[Image]=try db.prepare(images).map{ rowinletdecoder= row.decoder()letcontainer=try decoder.container(keyedBy:ImageCodingKeys.self)switchtry container.decode(ImageKind.self, forKey:.kind){case.png:returntryPNGImage(from: decoder)case.jpg:returntryJPGImage(from: decoder)case.heif:returntryHEIFImage(from: decoder)}}

Both of the above methods also have the following optional parameter:

• userInfo is a dictionary that is passed to the decoder and made availableto decodable types to allow customizing their behavior.

There are a few restrictions on using Codable types:

• The encodable and decodable objects can only use the following types:
  • Int, Bool, Float, Double, String, Date
  • Nested Codable types that will be encoded as JSON to a single column
• These methods will not handle object relationships for you. You must writeyour own Codable and Decodable implementations if you wish to support this.
• The Codable types may not try to access nested containers or nested unkeyedcontainers
• The Codable types may not access single value containers or unkeyedcontainers
• The Codable types may not access super decoders or encoders

In addition tofilter operators, SQLiteDBdefines a number of operators that can modify expression values witharithmetic, bitwise operations, and concatenation.

Many of SQLite’score functionshave been surfaced in and type-audited for SQLiteDB.

name?? email // ifnull("name", "email")

Most of SQLite’saggregate functions have beensurfaced in and type-audited for SQLiteDB.

Most of SQLite'swindow functions have beensurfaced in and type-audited for SQLiteDB. Currently onlyOVER (ORDER BY ...) windowing is possible.

SQLite'sdate and timefunctions are available:

DateFunctions.date("now")// date('now')Date().date// date('2007-01-09T09:41:00.000')SQLExpression<Date>("date").date// date("date")

We can create custom SQL functions by callingcreateFunction on a databaseconnection.

For example, to give queries access toMobileCoreServices.UTTypeConformsTo, we canwrite the following:

import MobileCoreServiceslettypeConformsTo:(SQLExpression<String>,SQLExpression<String>)->SQLExpression<Bool>=(try db.createFunction("typeConformsTo", deterministic:true){ UTI, conformsToUTIinreturnUTTypeConformsTo(UTI, conformsToUTI)})

NotetypeConformsTo’s signature:

(SQLExpression<String>, SQLExpression<String>)-> SQLExpression<Bool>

Because of this,createFunction expects a block with the followingsignature:

(String, String)-> Bool

Once assigned, the closure can be called wherever boolean expressions areaccepted.

letattachments=Table("attachments")letUTI=SQLExpression<String>("UTI")letimages= attachments.filter(typeConformsTo(UTI, kUTTypeImage))// SELECT * FROM "attachments" WHERE "typeConformsTo"("UTI", 'public.image')

We can create loosely-typed functions by handling an array of raw arguments,instead.

db.createFunction("typeConformsTo", deterministic:true){ argsinguardlet UTI=args[0]as?String, conformsToUTI=args[1]as?Stringelse{returnnil}returnUTTypeConformsTo(UTI, conformsToUTI)}

Creating a loosely-typed function cannot return a closure and instead must bewrapped manually or executedusing raw SQL.

letstmt=try db.prepare("SELECT * FROM attachments WHERE typeConformsTo(UTI, ?)")forrowin stmt.bind(kUTTypeImage){ /* ... */}

someObj.statement=try db.prepare("SELECT * FROM attachments WHERE typeConformsTo(UTI, ?)")forrowin someObj.statement.bind(kUTTypeImage){ /* ... */}someObj.statement.reset()

We can create custom aggregation functions by callingcreateAggregation:

letreduce:(String,[Binding?])->String={(last, bindings)in    last+""+(bindings.firstas?String??"")}db.createAggregation("customConcat", initialValue:"", reduce: reduce, result:{ $0})letresult= db.prepare("SELECT customConcat(email) FROM users").scalar()as!String

We can create custom collating sequences by callingcreateCollation on adatabase connection.

try db.createCollation("NODIACRITIC"){ lhs, rhsinreturn lhs.compare(rhs, options:.diacriticInsensitiveSearch)}

We can reference a custom collation using theCustom member of theCollation enumeration.

restaurants.order(collate(.custom("NODIACRITIC"), name))// SELECT * FROM "restaurants" ORDER BY "name" COLLATE "NODIACRITIC"

We can create a virtual table using theFTS4module by callingcreate on aVirtualTable.

letemails=VirtualTable("emails")letsubject=SQLExpression<String>("subject")letbody=SQLExpression<String>("body")try db.run(emails.create(.FTS4(subject, body)))// CREATE VIRTUAL TABLE "emails" USING fts4("subject", "body")

We can specify atokenizer using thetokenize parameter.

try db.run(emails.create(.FTS4([subject, body], tokenize:.Porter)))// CREATE VIRTUAL TABLE "emails" USING fts4("subject", "body", tokenize=porter)

We can set the full range of parameters by creating aFTS4Config object.

letemails=VirtualTable("emails")letsubject=SQLExpression<String>("subject")letbody=SQLExpression<String>("body")letconfig=FTS4Config().column(subject).column(body,[.unindexed]).languageId("lid").order(.desc)try db.run(emails.create(.FTS4(config))// CREATE VIRTUAL TABLE "emails" USING fts4("subject", "body", notindexed="body", languageid="lid", order="desc")

Once we insert a few rows, we can search using thematch function, whichtakes a table or column as its first argument and a query string as itssecond.

try db.run(emails.insert(    subject<-"Just Checking In",    body<-"Hey, I was just wondering...did you get my last email?"))letwonderfulEmails:QueryType= emails.match("wonder*")// SELECT * FROM "emails" WHERE "emails" MATCH 'wonder*'letreplies= emails.filter(subject.match("Re:*"))// SELECT * FROM "emails" WHERE "subject" MATCH 'Re:*'

When linking against a version of SQLite withFTS5 enabled we can create the virtualtable in a similar fashion.

letemails=VirtualTable("emails")letsubject=SQLExpression<String>("subject")letbody=SQLExpression<String>("body")letconfig=FTS5Config().column(subject).column(body,[.unindexed])try db.run(emails.create(.FTS5(config)))// CREATE VIRTUAL TABLE "emails" USING fts5("subject", "body" UNINDEXED)// Note that FTS5 uses a different syntax to select columns, so we need to rewrite// the last FTS4 query above as:letreplies= emails.filter(emails.match("subject:\"Re:\"*"))// SELECT * FROM "emails" WHERE "emails" MATCH 'subject:"Re:"*'

Though we recommend you stick with SQLiteDB’stype-safe system whenever possible, it is possibleto simply and safely prepare and execute raw SQL statements via aDatabase connectionusing the following functions.

• execute runs an arbitrary number of SQL statements as a convenience.

  try db.execute("""    BEGIN TRANSACTION;    CREATE TABLE users (        id INTEGER PRIMARY KEY NOT NULL,        email TEXT UNIQUE NOT NULL,        name TEXT    );    CREATE TABLE posts (        id INTEGER PRIMARY KEY NOT NULL,        title TEXT NOT NULL,        body TEXT NOT NULL,        published_at DATETIME    );    PRAGMA user_version = 1;    COMMIT TRANSACTION;""")

• prepare prepares a singleStatement object from a SQL string,optionally binds values to it (using the statement’sbind function),and returns the statement for deferred execution.

  letstmt=try db.prepare("INSERT INTO users (email) VALUES (?)")

  Once prepared, statements may be executed usingrun, binding anyunbound parameters.

  try stmt.run("alice@mac.com")db.changes // -> {Some 1}

  Statements with results may be iterated over, using the columnNames ifuseful.

  letstmt=try db.prepare("SELECT id, email FROM users")forrowin stmt{for(index, name)in stmt.columnNames.enumerated(){print("\(name):\(row[index]!)")        // id: Optional(1), email: Optional("alice@mac.com")}}

• run prepares a singleStatement object from a SQL string, optionallybinds values to it (using the statement’sbind function), executes,and returns the statement.

  try db.run("INSERT INTO users (email) VALUES (?)","alice@mac.com")

• scalar prepares a singleStatement object from a SQL string,optionally binds values to it (using the statement’sbind function),executes, and returns the first value of the first row.

  letcount=try db.scalar("SELECT count(*) FROM users")as!Int64

  Statements also have ascalar function, which can optionally re-bindvalues at execution.

  letstmt=try db.prepare("SELECT count (*) FROM users")letcount=try stmt.scalar()as!Int64

To copy a database to another using theSQLite Online Backup API:

// creates an in-memory copy of db.sqliteletdb=tryConnection("db.sqlite")lettarget=tryConnection(.inMemory)letbackup=try db.backup(usingConnection: target)try backup.step()

We canATTACH andDETACHdatabases to an existing connection:

letdb=tryConnection("db.sqlite")try db.attach(.uri("external.sqlite", parameters:[.mode(.readOnly)]), as:"external")// ATTACH DATABASE 'file:external.sqlite?mode=ro' AS 'external'lettable=Table("table", database:"external")letcount=try db.scalar(table.count)// SELECT count(*) FROM 'external.table'try db.detach("external")// DETACH DATABASE 'external'

When compiled for SQLCipher, we can additionally pass akey parameter toattach:

try db.attach(.uri("encrypted.sqlite"), as:"encrypted", key:"secret")// ATTACH DATABASE 'encrypted.sqlite' AS 'encrypted' KEY 'secret'

We can log SQL using the database’strace function.

#if DEBUG    db.trace{print($0)}#endif

To run thevacuum command:

try db.vacuum()

If you have a Swift package that acts as database middleware, such as an ORMor other database access layer, that uses the SQLite3 C API directly, you can expose aSwiftPM 6.1 traitin yourPackage.swift to enable dependent packages to enable using SQLCipher rather than SQLite3.

// swift-tools-version:6.1import PackageDescriptionletpackage=Package(name:"your-middleware",    products:[.library(name:"NeatORM", targets:["NeatORM"])],    traits:[.trait(name:"SQLCipher", description:"Use the SQLCipher library rather than the vendored SQLite")],    depedencies:[.package(url:"https://github.com/skiptools/swift-sqlcipher.git", from:"1.4.0")],    targets:[.target(name:"NeatORM", dependencies:[            // target only depends on SQLCipher when the "SQLCipher" trait is activated by a dependent package            // otherwise it will default to using the system "SQLite3" framework.product(name:"SQLCipher",package:"swift-sqlcipher", condition:.when(traits:["SQLCipher"]))])])

Then throughout your package's code, wherever youimport SQLite3,you would change this to conditionallyimport SQLCipher if it is available(i.e., if the trait is activated by the client package):

#if canImport(SQLCipher)import SQLCipher#elseimport SQLite3#endif

Since the C API surface of the SQLCipher package is a superset of the SQLite3 framework,all the samesqlite3_* functions will behave identically. However, you may want to alsocreate additional conditionally-enabled functionality in your own package, such asfor key management or FTS5 search index handling. For example:

#if canImport(SQLCipher)import SQLCipherextensionConnection{func updateEncryptionKey(newKey:String)throws{trycheckError(sqlite3_rekey_v2(dbHandle, newKey,Int32(newKey.utf8.count)))}}

Clients of your package would then enable the SQLCipher trait in theirPackage.swift's dependencies:

dependencies:[    // turn on SQLCipher support for NeatORM.package(url:"https://github.com/your-middleware/NeatORM.git", from:"1.2.3", traits:["SQLCipher"])]

In this way, your package can be parameterized to work with either thebuilt-in vendored SQLite3 package or with the SQLCipher packagedepending on the needs of the developer.

For a full set of examples, see theExamples/PackageTraits folder.

• Browse discussions
• Open an issue
• Submit a pull request

• This swift-sqlcipher package uses the MIT license. Seethe LICENSE file for more information.
• The BSD-style sqlcipher license is available athttps://www.zetetic.net/sqlcipher/license/.
• SQLite3 itself is in the public domain. Its license is available athttps://sqlite.org/purchase/license.

Here are a number of other popular SQLite alternative packages:

• SQLite.swift: The root of this fork (non-SQLCipher SQLite3)
• SQLCipher.swift: Binary SQLCipher.xcframework (Darwin platforms only)
• GRDB
• FMDB
• swift-toolchain-sqlite