SQLite Core

SQLite (vapor/sqlite) is a wrapper around the libsqlite C-library.

!!! seealso The higher-level, Fluent SQLite ORM guide is located at SQLite → Fluent

Using just the SQLite package for your project may be a good idea if any of the following are true.

  • You have an existing DB with non-standard structure.
  • You rely heavily on custom or complex SQL queries.
  • You just plain don’t like ORMs.

SQLite core is built on top of DatabaseKit which provides some conveniences like connection pooling and integrations with Vapor’s Services architecture.

!!! tip Even if you do choose to use Fluent SQLite, all of the features of SQLite core will be available to you.

Getting Started

Let’s take a look at how you can get started using SQLite core.

Package

The first step to using SQLite core is adding it as a dependency to your project in your SPM package manifest file.

  1. // swift-tools-version:4.0
  2. import PackageDescription
  4. let package = Package(
  5.     name: "MyApp",
  6.     dependencies: [
  7.         /// Any other dependencies ...
  9.         // 🔵 SQLite 3 wrapper for Swift.
  10.         .package(url: "https://github.com/vapor/sqlite.git", from: "3.0.0-rc"),
  11.     ],
  12.     targets: [
  13.         .target(name: "App", dependencies: ["SQLite", ...]),
  14.         .target(name: "Run", dependencies: ["App"]),
  15.         .testTarget(name: "AppTests", dependencies: ["App"]),
  16.     ]
  17. )

Don’t forget to add the module as a dependency in the targets array. Once you have added the dependency, regenerate your Xcode project with the following command:

  1. vapor xcode

Config

The next step is to configure the database in your configure.swift file.

  1. import SQLite
  3. /// ...
  5. /// Register providers first
  6. try services.register(SQLiteProvider())

Registering the provider will add all of the services required for SQLite to work properly. It also includes a default database config struct that uses an in-memory DB.

You can of course override this config struct if you have non-standard credentials.

  1. /// Register custom SQLite Config
  2. let sqliteConfig = SQLiteDatabaseConfig(storage: .memory)
  3. services.register(sqliteConfig)

Query

Now that the database is configured, you can make your first query.

  1. router.get("sqlite-version") { req -> Future<String> in
  2.     return req.withPooledConnection(to: .sqlite) { conn in
  3.         return try conn.query("select sqlite_version() as v;").map(to: String.self) { rows in
  4.             return try rows[0].firstValue(forColumn: "v")?.decode(String.self) ?? "n/a"
  5.         }
  6.     }
  7. }

Visiting this route should display your SQLite version.

Connection

A SQLiteConnection is normally created using the Request container and can perform two different types of queries.

Create

There are two methods for creating a SQLiteConnection.

  1. return req.withPooledConnection(to: .sqlite) { conn in
  2.     /// ...
  3. }
  4. return req.withConnection(to: .sqlite) { conn in
  5.     /// ...
  6. }

As the names imply, withPooledConnection(to:) utilizes a connection pool. withConnection(to:) does not. Connection pooling is a great way to ensure your application does not exceed the limits of your database, even under peak load.

Simply Query

Use .simpleQuery(_:) to perform a query on your SQLite database that does not bind any parameters. Some queries you send to SQLite may actually require that you use the simpleQuery(_:) method instead of the parameterized method.

!!! note This method sends and receives data as text-encoded, meaning it is not optimal for transmitting things like integers.

  1. let rows = req.withPooledConnection(to: .sqlite) { conn in
  2.     return conn.simpleQuery("SELECT * FROM users;")
  3. }
  4. print(rows) // Future<[[SQLiteColumn: SQLiteData]]>

You can also choose to receive each row in a callback, which is great for conserving memory for large queries.

  1. let done = req.withPooledConnection(to: .sqlite) { conn in
  2.     return conn.simpleQuery("SELECT * FROM users;") { row in
  3.         print(row) // [SQLiteColumn: SQLiteData]
  4.     }
  5. }
  6. print(done) // Future<Void>

Parameterized Query

SQLite also supports sending parameterized queries (sometimes called prepared statements). This method allows you to insert data placeholders into the SQL string and send the values separately.

Data sent via parameterized queries is binary encoded, making it more efficient for sending some data types. In general, you should use parameterized queries where ever possible.

  1. let users = req.withPooledConnection(to: .sqlite) { conn in
  2.     return try conn.query("SELECT *  users WHERE name = $1;", ["Vapor"])
  3. }
  4. print(users) // Future<[[SQLiteColumn: SQLiteData]]>

You can also provide a callback, similar to simple queries, for handling each row individually.