• Stars
    star
    141
  • Rank 259,917 (Top 6 %)
  • Language
    Swift
  • License
    Other
  • Created about 7 years ago
  • Updated 10 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Powerful Swift wrapper for SQLite

SQift

Build Status CocoaPods Compatible Carthage Compatible Platform

SQift is a lightweight Swift wrapper for SQLite.

Features

  • On-Disk, In-Memory and Temporary Database Connections
  • SQL Statement Execution
  • Generic Parameter Binding and Value Extraction
  • Codable and Codable Collection Bindings
  • Simple Query APIs for Values, Rows, and Collections
  • Transactions and Savepoints
  • Tracing and Trace Event Support
  • Scalar and Aggregate Functions
  • Commit, Rollback, Update, Authorizer Hooks
  • WAL Checkpointing
  • ConnectionQueue for Serial Execution per Database Connection
  • ConnectionPool for Parallel Execution of Read-Only Connections
  • Top-Level Database to Simplify Thread-Safe Reads and Writes
  • Database Migration Support
  • Database Backups
  • Comprehensive Unit Test Coverage
  • Complete Documentation

Requirements

  • iOS 10.0+, macOS 10.12+, tvOS 10.0+, watchOS 3.0+
  • Xcode 10.2+
  • Swift 5.0+

Migration Guides

Communication

  • Need help? Open an issue.
  • Have a feature request? Open an issue.
  • Find a bug? Open an issue.
  • Want to contribute? Fork the repo and submit a pull request.

Installation

CocoaPods

CocoaPods is a dependency manager for Cocoa projects. You can install it with the following command:

$ gem install cocoapods

CocoaPods 1.3+ is required to build SQift.

To integrate SQift into your Xcode project using CocoaPods, specify it in your Podfile:

platform :ios, '11.0'
use_frameworks!

target '<Your Target Name>' do
    pod 'SQift', '~> 4.0'
end

Then, run the following command:

$ pod install

Carthage

Carthage is a decentralized dependency manager that builds your dependencies and provides you with binary frameworks.

You can install Carthage with Homebrew using the following command:

$ brew update
$ brew install carthage

To integrate SQift into your Xcode project using Carthage, specify it in your Cartfile:

github "Nike-Inc/SQift" ~> 4.0

Run carthage update to build the framework and drag the built SQift.framework into your Xcode project.


Usage

SQift is designed to make it as easy as possible to work with SQLite from Swift. It does not, however, eliminate the need to understand how SQLite actually works. Before diving into SQift, it is recommended to first have a firm grasp on what SQLite is, how it works and how to use it.

SQift heavily leverages the new error handling model released with Swift 2.0. It was designed from the ground up to throw in all applicable cases. This makes it easy to wrap all your SQift calls in the do-catch paradigm.

Creating a Database Connection

Creating a database connection is simple.

let onDiskConnection = try Connection(storageLocation: .onDisk("path_to_db"))
let inMemoryConnection = try Connection(storageLocation: .inMemory)
let tempConnection = try Connection(storageLocation: .temporary)

There are also convenience parameters to make it easy to customize the flags when initializing the database connection:

let connection = try Connection(
    storageLocation: .onDisk("path_to_db"),
    readOnly: true,
    multiThreaded: false,
    sharedCache: false
)

In most cases, the default values are preferred. For more details about creating a database connection, please refer to the SQLite documentation.

Executing Statements

To execute a SQL statement on the Connection, you need to first create a Connection, then call execute.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))

try connection.execute("PRAGMA foreign_keys = true")
try connection.execute("PRAGMA journal_mode = WAL")

try connection.execute("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")

try connection.execute("INSERT INTO cars VALUES(1, 'Audi', 52642)")
try connection.execute("INSERT INTO cars VALUES(2, 'Mercedes', 57127)")

try connection.execute("UPDATE cars SET name = 'Honda' where id = 1")
try connection.execute("UPDATE cars SET price = 61_999 where name = 'Mercedes'")

try connection.execute("DELETE FROM cars where name = 'Mercedes'")
try connection.execute("DROP TABLE cars")

Bindings

Most Swift data types cannot be directly stored inside the database. They need to be converted to a data type supported by SQLite. In order to support moving Swift data types into the database and back out again, SQift leverage three powerful protocols: Bindable, Extractable, and Binding.

Bindable Protocol

The Bindable protocol handles converting Swift data types into a BindingValue enumeration type which can be stored in the database.

public protocol Bindable {
    var bindingValue: BindingValue { get }
}

Extractable Protocol

While the Bindable protocol helps move Swift data types into the database, the Extractable protocol allows SQift to extract the values from the database Connection and convert them back to the requested Swift data type.

public protocol Extractable {
    typealias BindingType
    typealias DataType = Self
    static func fromBindingValue(_ value: Any) -> DataType?
}

Binding Protocol

To extend Swift data types to be able to be inserted into the database and also be extracted safely, the Binding protocol forces the data type to conform to both the Bindable and Extractable protocols.

public protocol Binding: Bindable, Extractable {}

In order to make it as easy as possible to use SQift, SQift extends the following Swift data types to conform to the Binding protocol:

  • NULL: NSNull
  • INTEGER: Bool, Int8, Int16, Int32, Int64, Int, UInt8, UInt16, UInt32, UInt64, UInt
  • REAL: Float, Double
  • TEXT: String, URL, Date
  • BLOB: Data

Additional Swift data types can easily add Bindable protocol conformance if necessary.

Binding Parameters to a Statement

Safely binding parameters to a Statement is easy thanks to the Binding protocol. First you need to prepare a Statement object, then bind the parameters and run it using method chaining.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))

try connection.prepare("INSERT INTO cars VALUES(?, ?, ?)").bind(1, "Audi", 52_642).run()
try connection.prepare("INSERT INTO cars VALUES(:id, :name, :price)").bind([":id": 1, ":name": "Audi", ":price": 52_642]).run()

There are also convenience methods on the Connection for preparing a Statement, binding parameters and running it all in a single method named run.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))

try connection.run("INSERT INTO cars VALUES(?, ?, ?)", 1, "Audi", 52_642)
try connection.run("INSERT INTO cars VALUES(:id, :name, :price)", parameters: [":id": 1, ":name": "Audi", ":price": 52_642])

It is very important to properly escape all parameter values provided in a SQL statement. When in doubt, always use the provided bind functionality.

Querying Data

Querying data from the database makes extensive use of the Binding protocol. It extracts the original values from the database, then uses the Binding protocol along with generics to convert the final Swift type.

Single Values

Extracting a single value from the database can be done using the query API.

let synchronous: Int? = try db.query("PRAGMA synchronous")
let minPrice: UInt? = try db.query("SELECT avg(price) FROM cars WHERE price > ?", 40_000)

Multiple Values

You can also use the query API to extract multiple values through the Row type.

if let row = try db.query("SELECT name, type, price FROM cars WHERE type = ? LIMIT 1", "Sedan") {
    let name: String = row[0]
    let type: String = row[1]
    let price: UInt = row[2]
}

The values can be accessed by index or by name.

if let row = try db.query("SELECT name, type, price FROM cars WHERE type = ? LIMIT 1", "Sedan") {
    let name: String = row["name"]
    let type: String = row["type"]
    let price: UInt = row["price"]
}

The Row type supports both optional and non-optional value extraction through index and name subscripts. The non-optional subscripts are certainly the safest, but not always the most convenient. It is up to you to decide which makes more sense to use in each situation. Generally, try to use the optional types where the SQL statement is decoupled from the row value extraction. In these cases, the ExpressibleByRow type can be useful to help handle optionality.

ExpressibleByRow Types

In many cases, you want to construct model objects from a row in a result set. The ExpressibleByRow type was designed for this use case.

protocol ExpressibleByRow {
    init(row: Row) throws
}

To make use of the ExpressibleByRow protocol, first create your model object and conform to the protocol.

struct Car {
    let name: String
    let type: String
    let price: UInt
}

extension Car: ExpressibleByRow {
    init(row: Row) throws {
        guard
            let name: String = row[0],
            let type: String = row[1],
            let price: UInt = row[2]
        else {
            throw ExpressibleByRowError(type: Car.self, row: row)
        }

        self.name = name
        self.type = type
        self.price = price
    }
}

Then, you can use the query API to automatically convert the result set into a Car.

let car: Car? = try db.query("SELECT name, type, price FROM cars WHERE type = ? LIMIT 1", "Sedan")

Multiple Rows

The query APIs also support querying for result sets with multiple rows.

let names: [String] = try db.query("SELECT name FROM cars")
let cars: [Car] = try db.query("SELECT * FROM cars WHERE price > ?", [20_000])

In addition to array result sets, you can also create dictionary result sets.

let sql = "SELECT name, price FROM cars WHERE price > ?"
let prices: [String: UInt] = try db.query(sql, 20_000) { ($0[0], $0[1]) }

Dates

SQift has full binding support for Date objects which allows you to easily leverage the builtin date functionality in SQLite. You can insert dates easily into the database, run queries against them, and then extract them back out. SQift handles this through the Date binding which leverages the bindingDateFormatter. By default, all Date types are stored in the database as TEXT, so make sure you map your column types accordingly.

let date1975: Date!
let date1983: Date!
let date1992: Date!
let date2001: Date!

try connection.execute(
	"CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, release_date TEXT)"
)

try connection.execute("INSERT INTO cars(?, ?)", "70s car", date1975)
try connection.execute("INSERT INTO cars(?, ?)", "80s car", date1983)
try connection.execute("INSERT INTO cars(?, ?)", "90s car", date1992)
try connection.execute("INSERT INTO cars(?, ?)", "00s car", date2001)

Once you have your dates stored in the database, you can run date range queries to narrow down your data.

let date1980: Date!
let date2000: Date!

let carCount: Int = try connection.query(
    "SELECT count(*) FROM cars WHERE release_date >= date(?) AND release_date <= date(?)",
    date1980, 
    date2000
)

print("Total Cars from the 80s and 90s: \(carCount)") // should equal 2

You can swap the default date formatting, but be careful when doing so. You need to make sure the new date format complies with the SQLite requirements so date range queries will continue to work as expected.

You can also extract dates out of each row as an Date.

let sql = "SELECT release_date WHERE name = ? LIMIT 1"
let releaseDate: Date = try connection.query(sql, "80s car")

Custom Bindings

SQift has support for many common primitive types, but what about when you want to store a custom type in the database? This is where custom bindings come into play. All you need to do to store your own custom types in the database is conform to the Binding protocol.

enum DownloadState: Int {
    case pending, downloading, downloaded, failed
}

extension DownloadState: Binding {
    typealias BindingType = Int64

    var bindingValue: BindingValue { return .integer(Int64(rawValue)) }

    static func fromBindingValue(_ value: Any) -> AssetType? {
        guard let value = value as? Int64, let rawValue = Int(exactly: value) else { return nil }
        return DownloadState(rawValue: rawValue)
    }
}

try connection.execute("CREATE TABLE downloads(name TEXT PRIMARY KEY, state INTEGER NOT NULL)")
try connection.run("INSERT INTO downloads VALUES(?, ?)", "image1", DownloadState.pending)

if let state: DownloadState? = try connection.query("SELECT state FROM downloads WHERE name = 'image1') {
    print(state)
}

// Output
// DownloadState.pending

Codable Bindings

SQift also supports Codable bindings out-of-the-box. For example, let's say we have a Person object that is Codable. We can store Person instances directly in the database without having to create a custom binding.

struct Employee {
    let id: Int64
    let firstName: String
    let lastName: String
    let age: UInt
}

let phil = Person(id: 1, firstName: "Phil", lastName: "Knight", age: 79)

try connection.execute("CREATE TABLE employees(id INTEGER PRIMARY KEY, employee BLOB NOT NULL)")
try connection.run("INSERT INTO employees(employee) VALUES(?)", phil)

if let employee1: Employee? = try connection.query("SELECT employee FROM employees WHERE id = 1) {
    print(employee.firstName)
}

// Output
// "phil"

You need to think through whether it makes sense for you to use Codable bindings or not. While they are very convenient, the information inside them cannot be queried. In the above example, you could not run a query such as SELECT count(1) FROM employees where firstName = 'Phil'. If your use case does not require you to run such a search, then Codable bindings may be a useful choice.

Codable Collections

SQift also supports Codable collections through the ArrayBinding, SetBinding, and DictionaryBinding types.

let points: ArrayBinding = [
    CGPoint(x: 1.0, y: 2.0),
    CGPoint(x: 3.0, y: 4.0),
    CGPoint(x: 5.0, y: 6.0),
    CGPoint(x: 7.0, y: 8.0),
    CGPoint(x: 9.0, y: 10.0)
]

try connection.execute("CREATE TABLE stream(id INTEGER PRIMARY KEY, data BLOB NOT NULL)")
try connection.run("INSERT INTO stream(data) VALUES(?)", points)

let pointsQueried: ArrayBinding<CGPoint>? = try connection.query("SELECT data FROM stream WHERE id = 1")

pointsQueried?.elements.forEach { print("(\($0.x), \($0.y))") }

// Output
// (1.0, 2.0)
// (3.0, 4.0)
// (5.0, 6.0)
// (7.0, 8.0)
// (9.0, 10.0)

Codable collections can be useful in situations where you are writing large streams of data that are never partially queried. If you only write the data in one pass, and only query the data as the entire set, codable collections might be a good option.

Transactions

Changes cannot be made to the database except within a transaction. By default, any command that changes the database will automatically start a transaction if one is not already in effect. Transactions can also be started manually in SQift when multiple operations need to be run inside a single transaction.

try connection.execute("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")

try connection.transaction {
    try connection.prepare("INSERT INTO cars VALUES(?, ?, ?)").bind(1, "Audi", 52642).run()
    try connection.prepare("INSERT IN cars VALUES(?, ?, ?)").bind(2, "Mercedes", 57127).run()
}

If any error occurs within the transaction, all the changes are automatically rolled back by SQift.

Tracing

When debugging SQL statements, sometimes it can be helpful to be able to print out what is actually being executed by SQLite. SQift allows you to do this through the traceEvent API by registering a closure to run for each statement execution.

let connection = try Connection(storageLocation: storageLocation)

connection.traceEvent { event in
    if case .statement(_, let sql) = sql {
        print(sql)
    }
}

try connection.execute("CREATE TABLE employees(id INTEGER PRIMARY KEY, name TEXT)")
try connection.prepare("INSERT INTO employees VALUES(?, ?)").bind(1, "Bill Bowerman").run()
try connection.prepare("INSERT INTO employees VALUES(?, ?)").bind(2, "Phil Knight").run()
let employees: [Employee] = try connection.query("SELECT * FROM employees")

// Output
// "CREATE TABLE employees(id INTEGER PRIMARY KEY, name TEXT)"
// "INSERT INTO employees VALUES(1, 'Bill Bowerman')"
// "INSERT INTO employees VALUES(2, 'Phil Knight')"
// "SELECT * FROM employees"

The traceEvent API allows you to be more selective about the types of statements you want to trace. You can select which types of statements you want by using the trace event masks.

Collations

SQift supports custom collation functions for cases where the three built-in collating functions are not sufficient. A couple real-world examples of custom cases might include: diacritic aware sorting and numerical sorting.

let connection = try Connection(storageLocation: storageLocation)

connection.createCollation(named: "NUMERIC") { lhs, rhs in
    return lhs.compare(rhs, options: .numeric, locale: .autoupdatingCurrent)
}

try connection.execute("CREATE TABLE values(text TEXT COLLATE 'NUMERIC' NOT NULL)")

let values = ["string 1", "string 21", "string 12", "string 11", "string 02"]

try values.forEach { try connection.run("INSERT INTO values(text) VALUES(?)", $0) }
let extractedValues: [String] = try connection.query("SELECT * FROM values ORDER BY text")

extractedValues.forEach { print($0) }

// Output
// "string 1"
// "string 02"
// "string 11"
// "string 12"
// "string 21"

Functions

While SQLite is a very robust library, sometimes you will run into cases where you need to extend the functionality of SQLite where it is limited. For example, you may need to create a custom function to determine what month of a calendar year a particular date falls within. SQLite cannot do this directly since it lacks calendar support.

SQift supports custom scalar and aggregate functions. The following is a simple example of how you could extend SQLite to support a strip_unicode function.

try connection.addScalarFunction(named: "strip_unicode", argumentCount: 1) { _, values in
    guard
        let value = values.first, value.isText,
        let valueData = value.text.data(using: .ascii, allowLossyConversion: true),
        let asciiValue = String(data: valueData, encoding: .ascii)
    else { return .null }

    return .text(asciiValue)
}

let sql = "SELECT strip_unicode(?)"

let result1: String? = try connection.prepare(sql, "รฅ").query()
let result2: String? = try connection.prepare(sql, "ฤ‡").query()
let result3: String? = try connection.prepare(sql, "รกฤ").query()

// result1 = "a"
// result2 = "c"
// result3 = "ac"

For more advanced examples of scalar and aggregate functions, please refer to the test suite.


Advanced

Hooks

SQift has support built in for commit, rollback, update, and authorizer hooks. The commit hook is used to determine whether a commit should be executed or rolled back. The rollback hook is called when a commit is rolled back.

var shouldCancelCommit = false

connection.commitHook { return shouldCancelCommit }
connection.rollbackHook { print("rollback occurred") }

Update hooks can be used to react to .insert, .update, or .delete operations.

connection.updateHook { type, databaseName, tableName, rowID in
    var message = "\(type) row \(rowID)"

    if let databaseName = databaseName { message += " on \(databaseName)" }
    if let tableName = tableName { message += ".\(tableName)" }

    print(message)
    
    // Could update the file system, invalidate a cache, send notifications, etc.
}

try connection.execute("""
    INSERT INTO employee(name) VALUES('Phil Knight');
    UPDATE person SET name = 'Bill Bowerman' WHERE id = 1;
    DELETE FROM person WHERE id = 1
    """
)

// Output
// "insert row 1 on main.person"
// "update row 1 on main.person"
// "delete row 1 on main.person"

The authorizer hook is the most complex of the four. It allows you to control what statements are allowed to run on a connection. For example, you could disable all actions on a particular connection other than select statements.

try connection.authorizer { action, p1, p2, p3, p4 in
    guard action == .select else { return .deny }
    return .ok
}

Checkpoints

Databases with a WAL journal mode use checkpoint operations to move updates from the WAL file into the database. SQift supports checkpoints and busy timeouts and handlers which can be useful in certain situations. For example, you may want to use a WAL database for performance reasons, then transfer it to a remote server or different device. Before doing this, it is wise to checkpoint the database and also vacuum it.

try connection.busyHandler(.timeout(1.0)) // 1 second
let checkpointResult = try connection.checkpoint(mode: .truncate)

try connection.execute("VACUUM")

Checkpointing is a very complex process. Before using the checkpoint APIs, make sure to read through the SQLite documentation.

Thread Safety

Thread-safety is a complex topic when it comes to SQLite. As a general rule, it is NEVER safe to access a database Connection from multiple threads simultaneously. Each connection should be accessed serially to guarantee safety.

If you wish to access the database in parallel, there are a few things you need to know. First off, you'll need to use Write-Ahead Logging by setting the journal mode to WAL. By changing the database to a WAL journal mode, the database can be read during a write and written during a read in parallel using multiple connections.

try connection.execute("PRAGMA journal_mode = WAL")

Another important note is that SQLite can only perform write operations serially, no matter how many connections you create. Therefore, you should only ever create a single connection for writing if possible. You can use as many reader connections as you wish. For more information about thread-safety and WAL journal modes, please refer to the following:

Connection Queue

The ConnectionQueue class in SQift was designed to help guarantee thread-safety for a database Connection that could be accessed from multiple threads. It executes all operations on an internal serial dispatch queue. This ensures all operations on the connection operation in a serial fashion. The ConnectionQueue also supports executing logic inside a transaction and savepoint.

let queue = try ConnectionQueue(connection: Connection(storageLocation: .onDisk("path_to_db")))

try queue.execute { connection in
    try connection.execute("PRAGMA foreign_keys = true")
    try connection.execute("PRAGMA journal_mode = WAL")
    try connection.execute("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")
}

try queue.executeInTransaction { connection in
    try connection.execute("INSERT INTO cars VALUES(1, 'Audi', 52642)")
    try connection.execute("INSERT INTO cars VALUES(2, 'Mercedes', 57127)")
}

try queue.executeInSavepoint("drop_cars_table") { connection in
	try connection.execute("DROP TABLE cars")
}

Connection Pool

The ConnectionPool class allows multiple read-only connections to access a database simultaneously in a thread-safe manner. Internally, the pool manages two different sets of connections, ones that are available and ones that are currently busy executing SQL logic. The pool will reuse available connections when they are available, and initializes new connections when all available connections are busy until the max connection count is reached.

let pool = try ConnectionPool(storageLocation: .onDisk("path_to_db"))

try pool.execute { connection in
    let count: Int = try connection.query("SELECT count(*) FROM cars")
}

Since SQLite has no limit on the maximum number of open connections to a single database, the ConnectionPool will initialize as many connections as needed within a small amount of time. Each time a connection is executed, the internal drain delay timer starts up. When the drain delay timer fires, it will drain the available connections if there are no more busy connections. If there are still busy connections, the timer is restarted. This allows the ConnectionPool to spin up as many connections as necessary for very small amounts of time.

The thread-safety is guaranteed by the connection pool by always executing the SQL closure inside a connection queue. This ensures all SQL closures executed on the connection are done so in a serial fashion, thus guaranteeing the thread-safety of each connection.

Database

The Database class is a lightweight way to create a single writable connection queue and connection pool for all read statements. The read and write APIs are designed to make it simple to execute SQL statements on the appropriate type of Connection in a thread-safe manner.

let database = try Database(storageLocation: .onDisk("path_to_db"))

try database.executeWrite { connection in
    try connection.execute("PRAGMA foreign_keys = true")
    try connection.execute("PRAGMA journal_mode = WAL")
    try connection.execute("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")
}

try database.executeRead { connection in
    let count: Int = try connection.query("SELECT count(*) FROM cars")
}

This is the easiest way to operate in a 100% thread-safe manner without having to deal with the underlying complexities of the ConnectionQueue and ConnectionPool classes.

Another important consideration when using the Database type is whether or not to use the shared cache. If you are using a WAL journal mode, it is best to set the sharedCache parameter to true. This allows the reader pool to have access to the recent changes made by the writer connection at all times. If you don't use the shared cache, the readers will not always have access to the latest written changes. This can happen when other long lived read operations are running while changes are being written by a different connection.

We would like to encourage everyone to use a Database object rather than working directly with connection queues or connection pools.

Table Lock Policy

Table lock errors are SQLITE_ERROR types thrown by execute, prepare, and step operations. These errors can occur when the database is configured with a WAL journal mode as well as a shared cache. When one connection has obtained a lock on a table, another connection running on a different thread will receive table lock errors until the previous lock is released. In these situations, there are a couple of ways to proceed. The error can either be immediately thrown and handled by the client, or the calling thread can poll the operation until the lock is released.

The TableLockPolicy defines two different ways to handle table lock errors. The first option is to poll on the calling thread at a specified interval until the lock is released. The other option is to immediately fast fail by throwing the table lock error as soon as it is encountered. Connection, ConnectionPool, and Database types are set to .fastFail by default.

In order to enable polling for table lock errors, all that needs to be done is to set the policy in the Connection or Database initializer.

let connection = try Connection(storageLocation: storageLocation, tableLockPolicy: .poll(0.01))
let database = try Database(storageLocation: .onDisk("path_to_db"), tableLockPolicy: .poll(0.01))

When using a WAL journal mode and a shared cache, it is recommended to use a .poll table lock policy with a poll interval of 10 ms.

Migrations

Production applications generally need to migrate the database schema from time-to-time. Whether it requires some new tables or possibly alterations to a table, you need to have a way to manage the migration logic. SQift has migration support already built-in for you through the Migrator class. All you need to do is create the Migrator instance and tell it to run. Everything else is handled internally by SQift.

let connection = try Connection(storageLocation: .onDisk("path_to_db"))
let migrator = Migrator(connection: connection, desiredSchemaVersion: 2)

try migrator.runMigrationsIfNecessary(
    migrationSQLForSchemaVersion: { version in
        var SQL: String = ""

        switch version {
        case 1:
            return "CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)"

        case 2:
            return "CREATE TABLE person(id INTEGER PRIMARY KEY, name TEXT, address TEXT)"

        default:
            break
        }

        return SQL
    },
    willMigrateToSchemaVersion: { version in
        print("Will migrate to schema version: \(version)")
    },
    didMigrateToSchemaVersion: { version in
        print("Did migrate to schema version: \(version)")
    }
)

All migrations must start at 1 and increment by 1 with each iteration. For example, the first time you create a Migrator, you want to set the desiredSchemaVersion to 1 and implement the migrationSQLForSchemaVersion closure to return your initial database schema SQL. Then, each time you need to migrate your database, bump the desiredSchemaVersion by 1 and add the new case to your migrationSQLForSchemaVersion schema closure. In a production application, it would be easiest to write actual SQL files, add them to your bundle and load the SQL string from the file for the required version.

Backups

It can often be wise to run scheduled backups of your database allowing users to restore from a backup if corruption is detected. SQift makes it extremely to backup a database safely using the SQLite backup APIs.

let sourceConnection = try Connection(storageLocation: sourceLocation)
let destinationConnection = try Connection(storageLocation: destinationLocation)

let progress = try sourceConnection.backup(to: destinationConnection) { result in
    print(result)
}

The progress instance vended by the backup API can be used to monitor progress as well as cancel the backup operation.

The backup operation, by default, happens as an iterative process. It backs up the specified page size in each operation until it completes. Passing a pageSize of -1 causes the backup to be performed in a single operation. It is recommended to use the default pageSize of 100 and allow the operation to iterate until complete.


FAQ

Why not use CoreData?

There are many trade-offs between CoreData and SQift. SQift was certainly not created as a replacement for CoreData. It was created to make working with SQLite from Swift as easy and painless as possible. Anyone trying to decide between using CoreData and using SQift needs to consider the pros and cons carefully before making a decision. Both have significant learning curves and require significant amounts of forethought and architectural design before being integrated to an application or framework.

Should I use SQift in my project?

Maybe. The most important question to ask first is whether you really need a database. There are many other ways to store data which are much less complicated. If you do have a large amount of data that needs to be indexed for queries, then a database is probably the best option.

Once you know you need a database, then you need to decide whether you need a key-value store, or full relational query power. If you only need a key-value store, there are other libraries out there that are less complex and more tailored to your needs. If you need the full power of SQLite, then SQift is going to be a good option.

Any plans to add a Swift DSL on top of SQL in SQift?

This is something that we've considered multiple times and haven't dove into yet. Currently, we do not have any plans to build a DSL, but it's not off the table. If we do decide to try to add a DSL to SQift, we'll need to make sure we do not remove users too far from SQL.

The main goal of SQift is to make it as easy and convenient as possible to use SQLite with Swift. Convenience, however, does not mean abstraction. SQLite is very complicated, and the goal of SQift is not to simplify it, but enable it. Anyone looking to use SQift in their project needs to have a firm understanding of SQLite and how it works. This is absolutely by design.


License

SQift is released under the New BSD License. See LICENSE for details.

Creators

More Repositories

1

Willow

Willow is a powerful, yet lightweight logging library written in Swift.
Swift
1,334
star
2

gimme-aws-creds

A CLI that utilizes Okta IdP via SAML to acquire temporary AWS credentials
Python
902
star
3

Elevate

Elevate is a JSON parsing framework that leverages Swift to make parsing simple, reliable and composable.
Swift
612
star
4

koheesio

Python framework for building efficient data pipelines. It promotes modularity and collaboration, enabling the creation of complex pipelines from simple, reusable components.
Python
595
star
5

burnside

Fast and Reliable E2E Web Testing with only Javascript
JavaScript
381
star
6

wingtips

Wingtips is a distributed tracing solution for Java based on the Google Dapper paper.
Java
326
star
7

hal

hal provides an AWS Lambda Custom Runtime environment for your Haskell applications.
Haskell
235
star
8

brickflow

Pythonic Programming Framework to orchestrate jobs in Databricks Workflow
Python
187
star
9

spark-expectations

A Python Library to support running data quality rules while the spark job is runningโšก
Python
161
star
10

riposte

Riposte is a Netty-based microservice framework for rapid development of production-ready HTTP APIs.
Java
122
star
11

timeseries-generator

A library to generate synthetic time series data by easy-to-use factors and generator
Python
122
star
12

bartlett

A simple Jenkins command line client to serve your needs.
Haskell
81
star
13

cerberus-doc-site

Secure Property Store for Cloud Applications
CSS
81
star
14

aws-greengrass-core-sdk-rust

Provides an idiomatic Rust wrapper around the AWS Greengrass Core C SDK to more easily enable Greengrass native lambda functions in Rust.
Rust
71
star
15

cerberus

The Cerberus micro-service, a secure property store for cloud applications. It includes a REST API, authentication and encryption features, as well as a self-service web UI for users.
Java
62
star
16

referee

Referee is a UI for using Spinnaker Kayenta as a standalone service.
TypeScript
59
star
17

moirai

Libraries that can be used to determine if a feature should be exposed to a user.
Java
53
star
18

riposte-microservice-template

An example template for quickly creating a new Riposte microservice project.
Java
51
star
19

harbormaster

Harbormaster is a webhook handler for the Kubernetes API.
Go
42
star
20

fastbreak

Fastbreak is a simple Java 8 native circuit breaker supporting async future, blocking, and callback/manual modes.
Java
40
star
21

signal_analog

A troposphere-inspired library for programmatic, declarative definition and management of SignalFx Charts, Dashboards, and Detectors.
Python
39
star
22

backstopper

Backstopper is a framework-agnostic API error handling and (optional) model validation solution for Java 7 and up.
Java
38
star
23

react-virtualized-item-grid

React component for efficiently rendering a large, scrollable list of items in a series of wrapping rows
JavaScript
38
star
24

knockoff-factory

A library for generating fake data and populating database tables.
Python
34
star
25

pterradactyl

Pterradactyl is a library developed to abstract Terraform configuration from the Terraform environment setup.
Python
32
star
26

lambda-logger-node

A middleware logger that implements the MDC logging pattern for use in AWS NodeJS Lambdas.
TypeScript
29
star
27

lambda-router

JavaScript
23
star
28

bokor

Bokor is a simple, Record and Playback Mock Server written in Node.js, utilized for Service Virtualization.
JavaScript
23
star
29

piggyback

This tool allows you to tunnel SSH (using ProxyCommand) via HTTPS (with Squid Proxy). It is a python implementation of corkscrew, but over https (TLS) instead of http (plaintext).
Python
17
star
30

cerberus-node-client

Node client for interacting with a Cerberus backend. It can be used in Amazon EC2 instances and Amazon Lambdas.
JavaScript
16
star
31

cerberus-java-client

Java Client for Cerberus
Java
14
star
32

cerberus-lifecycle-cli

Command Line Interface for managing a Cerberus environment in AWS
Java
14
star
33

cerberus-python-client

Python Client for Cerberus
Python
13
star
34

cerberus-management-dashboard

A single page react app that is the self service web UI for administration of Safe Deposit Boxes, access control, and data.
HTML
13
star
35

tdd-training-cube

Papercraft cube used as training aid for Outside-In Test Driven Development
11
star
36

cerberus-serverless-components

A collection of AWS Serverless components for Cerberus
Java
11
star
37

cerberus-go-client

A Golang client for interacting with Cerberus, a secure property store for cloud applications.
Go
11
star
38

gradle-localstack

Gradle plugin for working with mock AWS endpoints using LocalStack.
Java
11
star
39

aws-thin-dynamo-node

A small, fast re-implementation of the AWS Dynamo DocumentClient
JavaScript
10
star
40

cerberus-archaius-client

An Archaius property provider implementation backed by Cerberus.
Java
9
star
41

epc-standards

Implementation of decoding GS1 EPC tags
Java
9
star
42

lambda-zipper

Zip up your node lambda code and production dependencies without pruning node_modules
JavaScript
9
star
43

java-vault-client

This is a java based Vault client library for communicating with Vault via HTTP.
Java
8
star
44

cerberus-cli

A CLI for the Cerberus API.
Go
8
star
45

cerberus-integration-tests

Groovy
8
star
46

cerberus-gateway-puppet-module

Puppet Module for installing Nginx and config downloader scripts
Python
8
star
47

Fleam

Scala
7
star
48

cerberus-consul-puppet-module

A Puppet module for installing Hashicorp's Consul as a service with customized start up scripts for Cerberus.
HTML
7
star
49

bluegreen-manager

Java
6
star
50

aws-thin-s3-node

A super-thin AWS S3 client
JavaScript
5
star
51

homebrew-nike

Homebrew formulas provided by Nike, Inc.
Ruby
5
star
52

sagerender

A library for configuring SageMaker pipelines using hierarchical configuration pattern.
Python
5
star
53

dynamo-arc

TypeScript
5
star
54

metrics-new-relic-insights

Reporter to send Dropwizard Metrics to New Relic Insights.
Java
5
star
55

cerberus-vault-puppet-module

A Puppet module for installing Hashicorp's Vault as a service with customized start up scripts for Cerberus.
HTML
5
star
56

cerberus-spring-boot-client

Spring Boot client for interacting with a Cerberus backend.
Java
4
star
57

aws-thin-ses-node

A super-thin AWS Simple Email Service client
JavaScript
3
star
58

dabber

Dabber is a Node CLI tool and AWS Lambda that helps you work with Dynamo.
JavaScript
3
star
59

actions-cerberus-secrets

Read secrets from Cerberus and make it as environment variables in GitHub Actions job so that it can be used in CICD process.
TypeScript
3
star
60

nike-inc.github.io

HTML
3
star
61

phiera

Python
2
star
62

Fawcett

A collection of Monocle lenses for navigating Amazon's API models.
Scala
2
star
63

cerberus-ruby-client

Ruby Client for Cerberus
Ruby
2
star
64

aws-scale

AWS Scaling Made Simple
JavaScript
2
star
65

gimme-a-cli

Gimme a CLI is a Java library for creating quick and easy command line interfaces (CLIs) using JCommander and Spring dependency injection.
Java
2
star
66

gradle-localdynamodb-plugin

XSLT
1
star
67

dynamo-butter

JavaScript
1
star
68

redwiggler

The composting worm. Composts your contract specification and tests and confirms that the contract specification is being followed.
Scala
1
star
69

gimme-a-cli-starter-project

Clone and modify this project to quickly create your own CLI based on the Gimme a CLI library.
Java
1
star