Using SQL¶
The SQL library helps you build and serialize SQL queries in Swift. It has an extensible, protocol-based design that supports many standard SQL queries like:
SELECT
,INSERT
,UPDATE
,DELETE
CREATE TABLE
,ALTER TABLE
,DROP TABLE
CREATE INDEX
,DROP INDEX
This package also integrates deeply with Codable
and parameter binding to make working with your database fast and secure.
This guide assumes you have already chosen and configured a driver in SQL → Getting Started. In some cases, these SQL dialects will have different syntaxes or supported features. Be sure to check their API docs for additional functionality.
Connection¶
The first step to building a SQL query is getting access to a connection. Most often, you will use withPooledConnection(to:)
followed by your database's dbid
.
Note
Refer to the table in SQL → Getting Started for your database's default dbid
.
The dbid
allows you to use multiple databases per application.
router.get("sql") { req in return req.withPooledConnection(to: .<#dbid#>) { conn in return // use conn to perform a query } }
Check out Database Kit → Overview → Connections for more information. The rest of this guide will assume you have access to a SQL database connection.
Select¶
Use the select()
method on a connection to create a SQLSelectBuilder
. This builder helps you create SELECT
statements and supports:
*
, columns, and expressions like functionsFROM
JOIN
GROUP BY
ORDER BY
The select builder conforms to SQLPredicateBuilder
for building WHERE
predicates. It also conforms to SQLQueryFetcher
for decoding Codable
models from the result set.
Let's take a look at an example SELECT
query. Replace the Xcode placeholder with the name of the database you are using, i.e., SQLite
.
struct User: SQLTable, Codable { static let sqlTableIdentifierString = "users" let id: Int? let name: String } let users = conn.select() .all().from(User.self) .where(\User.name == "Vapor") .all(decoding: User.self) print(users) // Future<[User]>
The resulting SQL will look something like this:
SELECT * FROM "users" WHERE "users"."name" = ?
As you can see, the Swift code reads similarly to actual SQL. Be sure to visit the API docs for the various builder protocols to see all available methods.
API Docs¶
Check out the API docs for more in-depth information about SQL's APIs.