Database Connections

Database connections are established using the Connection class. A connection is initialized with a path to a database. SQLite will attempt to create the database file if it does not already exist.

Read-Write Databases

To create a database that your application can write to, you must provide a path in a writable directory.

On iOS, a common location is the app’s Documents directory:

let path = NSSearchPathForDirectoriesInDomains(
    .documentDirectory, .userDomainMask, true
).first!

let db = try Connection("\(path)/db.sqlite3")

On macOS, the app’s Application Support directory is a standard choice:

var path = NSSearchPathForDirectoriesInDomains(
    .applicationSupportDirectory, .userDomainMask, true
).first! + "/" + Bundle.main.bundleIdentifier!

try FileManager.default.createDirectory(
    atPath: path, withIntermediateDirectories: true, attributes: nil
)

let db = try Connection("\(path)/db.sqlite3")

Read-Only Databases

If you bundle a pre-populated database with your app, you can establish a read-only connection to it.

let path = Bundle.main.path(forResource: "db", ofType: "sqlite3")!

let db = try Connection(path, readonly: true)

!!! warning Signed applications cannot modify their bundle resources. If you bundle a database for bootstrapping purposes, you must first copy it to a writable location (like the Documents directory) before establishing a read-write connection.

In-Memory Databases

For temporary data that doesn't need to persist, you can create an in-memory database by omitting the path or using the .inMemory location type.

// These are equivalent
let db = try Connection() 
let db_mem = try Connection(.inMemory)

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

Temporary Databases

To create a temporary, disk-backed database, pass an empty file name or use the .temporary location type.

let db = try Connection(.temporary)

URI Parameters

You can pass .uri to the Connection initializer to control more aspects of the database connection using URIQueryParameters.

let db = try Connection(.uri("file.sqlite", parameters: [.cache(.private), .noLock(true)]))

For more details, see the official SQLite documentation on Uniform Resource Identifiers.

Thread-Safety

Every Connection object has its own serial queue for statement execution and can be safely accessed from multiple threads. When one thread opens a transaction or savepoint, it will block other threads from executing statements on that same connection until the transaction is complete.

If you are using multiple Connection objects for a single database file, you should configure a busy handler to prevent database is locked errors.

You can set a timeout (in seconds):

// Will retry for up to 5 seconds before throwing an error.
db.busyTimeout = 5 

Or provide a custom busy handler callback:

// Will retry up to 3 times before throwing an error.
db.busyHandler({ tries in
    return tries < 3
})

!!! note The default timeout is 0. If you encounter database is locked errors, it's a strong indication that you are trying to access the same database file from multiple connections simultaneously without a busy handler configured.