The skip-sql framework is available at https://github.com/skiptools/skip-sql.git, which can be checked out and tested with skip test once Skip is installed.

• Usage
  • Connection example
  • In-memory databases
  • Transactions
  • Bound parameters
  • Schema Migration
  • Concurrency
• Implementation
• SQLite Versions
  • Vendored SQLite Versions
  • SQLPlus
    • Using SQLPlus
    • JSON
    • Encryption
• Building
• Testing

The SkipSQL module is a dual-platform Skip framework that provides access to sqlite database in Darwin and Android systems.

Usage

Connection example

To connect

let dbpath = URL.documentsDirectoryURL.appendingPathComponent("db.sqlite")

let ctx = try SQLContext(path: dbpath, flags: [.create, .readWrite])
defer { ctx.close() }

try sqlite.exec(sql: "CREATE TABLE IF NOT EXISTS SOME_TABLE (STRING TEXT)")

try sqlite.exec(sql: "INSERT INTO SOME_TABLE (STRING) VALUES ('ABC')")

let rows: [[SQLValue]] = ctx.query(sql: "SELECT STRING FROM SOME_TABLE")
assert(rows[0][0] == SQLValue.string("ABC"))

In-memory databases

When passing nil as the path, the SQLContext will reside entirely in memory, and will not persist once the context is closed. This can be useful for unit testing and performing in-memory calculations, or as a temporary engine for calculations and sorting.

let ctx = try SQLContext(path: nil)
defer { ctx.close() }

let rows: [[SQLValue]] = ctx.query(sql: "SELECT 1, 1.1+2.2, 'AB'||'C'")

assert(rows[0][0] == SQLValue.integer(1))
assert(rows[0][1] == SQLValue.float(3.3))
assert(rows[0][2] == SQLValue.text("ABC"))

Transactions

Performing multiple operations in the context of a transaction will ensure that either all the operations succeed (COMMIT) or fail (ROLLBACK) together.

try ctx.transaction {
    try ctx.exec(sql: "INSERT INTO TABLE_NAME VALUES(1)")
    try ctx.exec(sql: "INSERT INTO TABLE_NAME VALUES(2)")
}

The default transaction type is .deferred, but it can be specified as a parameter to transaction to override the default, or nil to perform the operation without a transaction.

Bound parameters

Retaining a prepared SQLStatement will mean that the SQL doesn’t need to be re-parsed each time a query or insert/update statement is performed.

SQL statements with a ? symbol will expect those parameters to be applied with the bind function before the statement is executed.

let insert = try sqlite.prepare(sql: "INSERT INTO TABLE_NAME (NUM, STR) VALUES (?, ?)")
defer { insert.close() }

// insert 1,000 rows in a single transaction, re-using the insert statement
try sqlite.transaction {
    for i in 1...1_000 {
        let params: [SQLValue] = [
            SQLValue.integer(Int64(i)),
            SQLValue.text("Row #\(i)")
        ]
        try insert.update(parameters: params)
    }
}

Schema Migration

There is no built-in support for schema migrations. Following is a part of a sample of how you might perform migrations in your own app.

// track the version of the schema in the database, which can be used for schema migration
try ctx.exec(sql: "CREATE TABLE IF NOT EXISTS DB_SCHEMA_VERSION (id INTEGER PRIMARY KEY, version INTEGER)")
try ctx.exec(sql: "INSERT OR IGNORE INTO DB_SCHEMA_VERSION (id, version) VALUES (0, 0)")
var currentVersion = try ctx.query(sql: "SELECT version FROM DB_SCHEMA_VERSION").first?.first?.integerValue ?? 0

func migrateSchema(v version: Int64, ddl: String) throws {
    if currentVersion < version {
        let startTime = Date.now
        try ctx.transaction {
            try ctx.exec(sql: ddl) // perform the DDL operation
            // then update the schema version
            try ctx.exec(sql: "UPDATE DB_SCHEMA_VERSION SET version = ?", parameters: [SQLValue.integer(version)])
        }
        currentVersion = version
        logger.log("updated database schema to \(version) in \(startTime.durationToNow)")
    }
}

// the initial creation script for a new database
try migrateSchema(v: 1, ddl: """
CREATE TABLE DATA_ITEM (ID INTEGER PRIMARY KEY AUTOINCREMENT)
""")
// migrate records to have new description column
try migrateSchema(v: 2, ddl: """
ALTER TABLE DATA_ITEM ADD COLUMN DESCRIPTION TEXT
""")

Concurrency

As a thin layer over a SQLite connection, SkipSQL itself performs no locking or manages threads in any way. It is up to the application layer to set up reader/writer locks, or else just perform all the operations in an isolated context (e.g., using an actor).

The SQLite guide on Locking And Concurrency can provide additional guidance.

Implementation

SkipSQL speaks directly to the low-level SQLite3 C library that is included with all Darwin/iOS/Android operating systems. On Darwin/iOS, it communicates directly through Swift’s C bridging support. On Android, it uses the SkipFFI module to interact directly with the underlying sqlite installation on Android. (For performance and a consistent API, SkipSQL eschews Android’s android.database.sqlite Java wrapper, and uses JNA to directly access the SQLite C API.)

SQLite Versions

Vendored SQLite Versions

Because SkipSQL uses the version of SQLite that is shipped with the platform, care should be taken when using recent SQLite features, such as the json function, which is new in SQLite 3.38. This would raise an error on Android versions below 14.0 (API 34) and iOS versions below 16.0.

Be aware that some very useful SQL features may only have been added to more recent versions of SQLite, such as strict tables (added in 3.37). This may impact the Android API version you can deply back to, so be sure to test your code on the oldest available Android emulator and iOS simulator for your project.

Also be aware that the availability of some SQL features are contingent on the compile flags used to build the vendored sqlite implementation provided as part of the OS, such as SQLITE_ENABLE_JSON1 enabling the various json_ operations. In the case of Android, be aware that local Robolectric testing will be insufficient to identify any limitations resulting from sqlite compile flags, since local testing will use the local (i.e., macOS-vendored) version of SQLite. Testing against an Android emulator (or device) should be performed when developing new SQL operations.

SQLPlus

The skip-sql framework includes an additional SQLPlus module, which creates a local build with the following extensions enabled:

• Full Text Search (FTS)
• Encryption (sqlcipher)

The SQLPlus module uses sqlite version 3.44.2, which means that it will be safe to use newer sqlite features like the json function, regardless of the Android API and iOS versions of the deployment platform.

This comes at the cost of additional build time for the native libraries, as well as a larger artifact size (around 1MB on iOS and 4MB on Android).

Using SQLPlus

The SQLPlus extensions can be used by importing the SkipSQLPlus module and passing configuration: .plus to the SQLContext constructor, like so:

import SkipSQL
import SkipSQLPlus

let dbpath = URL.documentsDirectoryURL.appendingPathComponent("db.sqlite")
let db = try SQLContext(path: dbpath.path, flags: [.create, .readWrite], configuration: .plus)
// do something with the database
db.close()

JSON

SQLPlus enables the json extensions:

let sqlplus = SQLContext(configuration: .plus)
try sqlplus.exec(sql: #"CREATE TABLE users (id INTEGER PRIMARY KEY, profile JSON)"#)

try sqlplus.exec(sql: #"INSERT INTO users (id, profile) VALUES (1, '{"name": "Alice", "age": 30}')"#)
try sqlplus.exec(sql: #"INSERT INTO users (id, profile) VALUES (2, '{"name": "Bob", "age": 25}')"#)

let j1 = try sqlplus.query(sql: #"SELECT json_extract(profile, '$.name') as name FROM users WHERE id = ?"#, parameters: [.integer(1)]).first
assert j1 == [.text("Alice")]

let j2 = try sqlplus.query(sql: #"SELECT json_extract(profile, '$.name') as name, json_extract(profile, '$.age') as age FROM users WHERE id = ?"#, parameters: [.integer(2)]).first
assert j2 == [.text("Bob"), .integer(25)]

Encryption

SQLPlus contains the SQLCipher extension, which adds 256 bit AES encryption of database files and other security features like:

• On-the-fly encryption
• Tamper detection
• Memory sanitization
• Strong key derivation

SQLCipher is based on SQLite and stable upstream release features are periodically integrated. The extension is documented at the official SQLCipher site. It is used by many mobile applications like the Signal iOS and Android app to secure local database files. Cryptographic algorithms are provided by the LibTomCrypt C library, which is included alongside the sqlcipher sources.

An example of creating an encryped database:

import SkipSQL
import SkipSQLPlus

let dbpath = URL.documentsDirectoryURL.appendingPathComponent("encrypted.sqlite")
let db = try SQLContext(path: dbpath.path, flags: [.create, .readWrite], configuration: .plus)
_ = try db.query(sql: "PRAGMA key = 'password'")
try db.exec(sql: #"CREATE TABLE SOME_TABLE(col)"#)
try db.exec(sql: #"INSERT INTO SOME_TABLE(col) VALUES(?)"#, parameters: [.text("SOME SECRET STRING")])
try db.close()

Building

This project is a Swift Package Manager module that uses the Skip plugin to transpile Swift into Kotlin.

Building the module requires that Skip be installed using Homebrew with brew install skiptools/skip/skip. This will also install the necessary build prerequisites: Kotlin, Gradle, and the Android build tools.

Testing

The module can be tested using the standard swift test command or by running the test target for the macOS destination in Xcode, which will run the Swift tests as well as the transpiled Kotlin JUnit tests in the Robolectric Android simulation environment.

Parity testing can be performed with skip test, which will output a table of the test results for both platforms.