Swiftpack.co - Package - vapor/sql-kit
SQLKit
Documentation Team Chat MIT License Continuous Integration Swift 5.2

Build SQL queries in Swift. Extensible, protocol-based design that supports DQL, DML, and DDL.

Major Releases

The table below shows a list of SQLKit major releases alongside their compatible NIO and Swift versions.

|Version|NIO|Swift|SPM| |-|-|-|-| |3.0|2.0+|5.2+|from: "3.0.0"| |2.0|1.0+|4.0+|from: "2.0.0"| |1.0|n/a|4.0+|from: "1.0.0"|

Use the SPM string to easily include the dependendency in your Package.swift file.

.package(url: "https://github.com/vapor/sql-kit.git", from: ...)

Supported Platforms

PostgresNIO supports the following platforms:

  • Ubuntu 16.04+
  • macOS 10.15+

Overview

SQLKit is an API for building and serializing SQL queries in Swift. SQLKit attempts to abstract away SQL dialect inconsistencies where possible allowing you to write queries that can run on multiple database flavors. Where abstraction is not possible, SQLKit provides powerful APIs for custom or dynamic behavior.

Supported Databases

These database packages are built on SQLKit:

Configuration

SQLKit does not deal with creating or managing database connections itself. This package is focused entirely around building and serializing SQL queries. To connect to your SQL database, refer to your specific database package's documentation. Once you are connected to your database and have an instance of SQLDatabase, you are ready to continue.

Database

Instances of SQLDatabase are capable of serializing and executing SQLExpression.

let db: SQLDatabase = ...
db.execute(sql: SQLExpression, onRow: (SQLRow) -> ())

SQLExpression is a protocol that represents a SQL query string and optional bind values. It can represent an entire SQL query or just a fragment.

SQLKit provides SQLExpressions for common queries like SELECT, UPDATE, INSERT, DELETE, CREATE TABLE, and more.

var select = SQLSelect()
select.columns = [...]
select.tables = [...]
select.predicate = ...

SQLDatabase can be used to create fluent query builders for most of these query types.

let planets = try db.select()
    .column("*")
    .from("planets")
    .where("name", .equal, "Earth")
    .all().wait()

You can execute a query builder by calling run().

Rows

For query builders that support returning results, like select(), there are additional methods for handling the database output.

  • all(): Returns an array of rows.
  • first(): Returns an optional row.
  • run(_:): Accepts a closure that handles rows as they are returned.

Each of these methods returns SQLRow which has methods for access column values.

let row: SQLRow
let name = try row.decode(column: "name", as: String.self)
print(name) // String

Codable

SQLRow also supports decoding Codable models directly from a row.

struct Planet: Codable {
    var name: String
}

let planet = try row.decode(model: Planet.self)

Query builders that support returning results have convenience methods for automatically decoding models.

let planets = try db.select()
    ...
    .all(decoding: Planet.self).wait()

Select

The select() method creates a SELECT query builder.

let planets = try db.select()
    .columns("id", "name")
    .from("planets")
    .where("name", .equal, "Earth")
    .all().wait()

This code would generate the following SQL:

SELECT id, name FROM planets WHERE name = ?

Notice that Encodable values are automatically bound as parameters instead of being serialized directly to the query.

The select builder has the following methods.

  • columns
  • from
  • where (orWhere)
  • limit
  • offset
  • groupBy
  • having (orHaving)
  • distinct
  • for (lockingClause)
  • join

By default, query components like where will be joined by AND. Methods prefixed with or exist for joining by OR.

builder.where("name", .equal, "Earth").orWhere("name", .equal, "Mars")

This code would generate the following SQL:

name = ? OR name = ?

where also supports creating grouped clauses.

builder.where("name", .notEqual, SQLLiteral.null).where {
    $0.where("name", .equal, SQLBind("Milky Way"))
        .orWhere("name", .equal, SQLBind("Andromeda"))
}

This code generates the following SQL:

name != NULL AND (name == ? OR name == ?)

Insert

The insert(into:) method creates an INSERT query builder.

try db.insert(into: "galaxies")
    .columns("id", "name")
    .values(SQLLiteral.default, SQLBind("Milky Way"))
    .values(SQLLiteral.default, SQLBind("Andromeda"))
    .run().wait()

This code would generate the following SQL:

INSERT INTO galaxies (id, name) VALUES (DEFAULT, ?) (DEFAULT, ?)

The insert builder also has a method for encoding a Codable type as a set of values.

struct Galaxy: Codable {
    var name: String
}

try builder.model(Galaxy(name: "Milky Way"))

Update

The update(_:) method creates an UPDATE query builder.

try db.update("planets")
    .where("name", .equal, "Jpuiter")
    .set("name", to: "Jupiter")
    .run().wait()

This code generates the following SQL:

UPDATE planets SET name = ? WHERE name = ?

The update builder supports the same where and orWhere methods as the select builder.

Delete

The delete(from:) method creates a DELETE query builder.

try db.delete(from: "planets")
    .where("name", .equal, "Jupiter")
    .run().wait()

This code generates the following SQL:

DELETE FROM planets WHERE name = ?

The delete builder supports the same where and orWhere methods as the select builder.

Raw

The raw(_:) method allows for passing custom SQL query strings with support for parameterized binds.

let table = "planets"
let planets = try db.raw("SELECT * FROM \(table) WHERE name = \(bind: planet)")
    .all().wait()

This code generates the following SQL:

SELECT * FROM planets WHERE name = ?

The \(bind:) interpolation should be used for any user input to avoid SQL injection.

Github

link
Stars: 91

Used By

Total: 0

Releases

Add support for RETURNING clause - 2020-06-24 20:25:46

This patch was authored by @grahamburgsma and released by @tanner0101.

Adds support for SQL RETURNING clauses on supported databases (#110).

  • Adds new SQLReturning expression to support returning columns on insert / update / delete queries.
var select = SQLSelect(...)
select.returning = ...
  • Adds SQLReturningBuilder for use of SQLReturning in query builders.
db.delete()
  .from("planets")
  .returning("*")
  .all()

The returning method supports multiple columns or identifiers.

builder.returning("id", "name")

It also supports qualified names using SQLColumn.

builder.returning(SQLColumn("name", table: "planets"))
  • Adds new SQLDialect option for checking if the current database supports returning.
if database.dialect.supportsReturning {
    builder.returning("*")
} else {
    // Fallback.
}

Add additional interpolations, constructors, and operations to SQLQueryString - 2020-06-22 20:23:58

This patch was authored and released by @gwynne.

Several new capabilities are available on SQLQueryString, as used by SQLRawBuilder:

  • Integer literal interpolation, e.g. \(literal: 1), for rendering correctly escaped numeric literals according to the database's SQLDialect
  • Boolean literal interpolation, e.g. \(literal: true), for rendering correctly escaped boolean literals according to the database's SQLDialect
  • String literal interpolation, e.g. \(literal: "hello"), for rendering strings as correctly escaped literal values in the database's SQLDialect
  • Arrays of string literals interpolation, e.g. \(literals: ["hello", "world", "how", "are", "you"], joinedBy: " ")
  • SQL identifier interpolation, e.g. \(ident: "created_at"), for rendering names as correctly escaped identifiers according to the database's SQLDialect - identifiers are usually table names, column names, alias names for tables and columns, and other similar items. PostgreSQL and SQLite enclose identifiers in " characters; MySQL uses backticks.
  • Arrays of SQL identifiers interpolation, e.g. \(idents: ["id", "created_at", "updated_at"], joinedBy: ", ") - great for generating column name lists for INSERT, for example.
  • A + operator for concatenating two SQLQueryStrings. Credit for this functionality goes to @t-ae in #111.
  • Array<SQLQueryString>.joined(separator:), similar to Array<String>.joined(separator:). Credit for this functionality goes to @t-ae in #111.
  • Improved tests for SQLQueryString. Partial credit for the improvements goes to @t-ae in #111.

Allow binding a list of values in SQLQueryString - 2020-06-20 04:08:09

This patch was authored by @grahamburgsma and released by @gwynne.

This addition allows binding a list of values. This is useful for IN statements for example WHERE column_name IN (value1, value2, ...).

Usage

let ids = [1, 2, 3...]

raw("...WHERE IN (\(binds: ids))")

Improve `SQLDropIndex`, add `SQLDropIndexBuilder` - 2020-06-18 19:47:11

This patch was authored and released by @gwynne.
  • SQLDropIndex now supports IF EXISTS if the underlying database dialect does.

  • SQLDropIndex now supports SQLDropBehavior (CASCADE and RESTRICT) if the underlying database dialect does.

  • Added SQLDropIndexBuilder for convenient creation and execution of SQLDropIndex queries.

  • Fix minor grammar typos in some comments.

ALTER TABLE constraints - 2020-05-18 18:18:34

This patch was authored and released by @tanner0101.

Adds support for adding and dropping constraints in ALTER TABLE queries.

SQLKit 3.0.0 - 2020-04-24 14:48:53

Docs: https://github.com/vapor/sql-kit/blob/master/README.md

More information on Vapor 4 official release: https://forums.swift.org/t/vapor-4-official-release-begins/34802

Adds a `normalizeSQLConstraint` method - 2020-04-13 23:33:00

This patch was authored and released by @mcdappdev.

Adds a normalizeSQLConstraint method to SQLDialect so that the drivers can truncate constraint identifiers that are too long.

Custom SQLDataType dialect support - 2020-03-04 23:06:50

This patch was authored and released by @tanner0101.

Adds a SQLDialect option for overriding SQLDataType's serialization.

Fix SQLRaw initializer - 2020-03-04 18:25:54

binds is now correctly set when using SQLRaw's initializer (#99, fixes #95).

This patch was authored and released by @tanner0101.

Release Candidate 1 - 2020-02-28 20:45:05

Updates to Swift 5.2 and macOS 10.15. Adds more CI testing.

Release candidates represent the final shift toward focusing on bug fixes and documentation. Breaking changes will only be accepted for critical issues. We expect a final release of this package shortly after Swift 5.2's release date.

Fix multi-column update - 2020-02-28 17:10:15

Fixes https://github.com/vapor/sql-kit/issues/96

There are two ways to go about this, I suppose. Either split this PR up so that allowsBatch can be introduced first and SQLiteKit updated to not support it, or just merge this with the broken SQLiteKit test (which is only breaking because I added a benchmark test that exercises batch column alterations in this PR anyway) and then follow quickly with a PR that turns allowsBatch off for SQLiteKit.

I'm partial to the easy route, but no strong opinion.

This patch was authored by @mattpolzin and released by @tanner0101.

Fix ALTER TABLE syntax error - 2020-02-21 00:02:19

Fixes a syntax error causing ALTER table to serialize column modifications even if they were empty.

Fix benchmarker test - 2020-02-20 23:55:20

Update benchmarker test to use latest alter column syntax.

Alter column data type support - 2020-02-20 23:26:26

Adds a new SQLAlterColumnDefinitionType expression for use in SQLAlterTable.modifyColumns.

This type is supported by a new SQLDialect.alterTableSyntax dialect option.

db.alter(table: "planets")
    .update(column: "type", dataType: .int)
    .run()

The above builder would result in the following queries:

PostgreSQL:

ALTER TABLE "planets" ALTER COLUMN "type" SET DATA TYPE BIGINT

MySQL:

ALTER TABLE `planets` MODIFY COLUMN "type" BIGINT

SQLite:

ALTER TABLE "planets"

Note: SQLite does not support altering existing columns.

Add optional autoincrement function expression to SQLDialect - 2020-02-13 16:50:08

Some databases doesn’t support the AUTOINCREMENT clause, but have the ability to implement autoincrementing primary keys using a function as a default instead.

Adds support for a drop table behavior clause - 2020-02-13 16:25:36

In SQL 92, the drop behavior, either RESTRICT or CASCADE is mandatory after the table name. Some databases requires either one of them to be present in a DROP TABLE statement, and there’s no way to specify that in sql-kit.

Intended to resolve issue #64.

Add support for creating and dropping triggers - 2020-02-12 16:38:14

Adds query structures and builders for creating a dropping triggers from the database (#86).

MySQL example:

try db.create(trigger: "foo", table: "planet", when: .before, event: .insert)
  .body(body)
  .order(precedence: .precedes, otherTriggerName: "other")

SQLite example:

try db.create(trigger: "foo", table: "planet", when: .before, event: .insert)
    .body(body)
    .condition("foo = bar")

PostgreSQL example:

try db.create(trigger: "foo", table: "planet", when: .after, event: .insert)
    .each(.row)
    .isConstraint()
    .timing(.initiallyDeferred)
    .condition("foo = bar")
    .procedure("qwer")
    .referencedTable(SQLIdentifier("galaxies"))

Trigger syntax can vary between SQL databases. SQLDialect.triggerSyntax has been added to check if certain features are supported before attempting to perform them.

Implement remaining binary operators - 2020-02-08 01:18:18

SQLBinaryOperator.add, .subtract, .multiply, .divide, and .modulo now work.

Unfortunately, .concatenate still does not work, because MySQL (being the odd one out, as usual) does not support it by default. However, we do now provide a much more helpful error message when encountering it.

Add SQLDistinct and isDistinct - 2020-02-08 01:14:40

Add many new helper methods (#84) - 2020-02-07 21:31:58

  • Add protocol extension method which enables generic type inference for SQLRow.decode(column:as:).
  • Add .columns(), alias-friendly .from(), and several forms of .join() to SQLSelectBuilder.
  • Add an initializer for creating SQLQueryStrings from types conforming to StringProtocol (String and Substring).
  • Add missing helpers taking arrays corresponding to variadic arguments on existing helpers.
  • Add helper for adding an array of column definitions to a create table builder.

Support the LIKE and NOT LIKE operators. - 2020-02-07 01:05:22

The .like. and .notLike binary operators now work.

Enum Support - 2020-01-22 02:44:38

Adds support for using SQL enums natively. There are three different levels of support:

  • typeName: Postgres-like, has CREATE TYPE ... AS ENUM syntax
  • inline: MySQL-like, has ENUM(...) data type
  • unsupported: SQLite-like, no enum support.

Supported enum syntax is available via SQLDialect.enumSyntax. This can be used to conditionally run code depending on database support.

A new test case SQLBenchmarker.testEnum has been added that tests the following on all three databases:

  • Create table with enum column
  • Add new value to enum
  • Drop value from enum

Note: Dropping enum values from Postgres-like DBs is not supported.

Here is the raw SQL output from the benchmark for each database:

SQLite

CREATE TABLE "planets"("id" BIGINT PRIMARY KEY, "name" TEXT NOT NULL, "type" TEXT NOT NULL) []
INSERT INTO "planets" ("name", "type") VALUES (?, 'smallRocky'), (?, 'gasGiant') ["Earth", "Jupiter"]
INSERT INTO "planets" ("name", "type") VALUES (?, 'dwarf') ["Pluto"]
DELETE FROM "planets" WHERE "type" = 'gasGiant' []
DROP TABLE "planets" []

Postgres

CREATE TYPE "planet_type" AS ENUM ('smallRocky', 'gasGiant') []
CREATE TABLE "planets"("id" BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "name" TEXT NOT NULL, "type" "planet_type" NOT NULL) []
INSERT INTO "planets" ("name", "type") VALUES ($1, 'smallRocky'), ($2, 'gasGiant') ["Earth", "Jupiter"]
ALTER TYPE "planet_type" ADD VALUE 'dwarf' []
INSERT INTO "planets" ("name", "type") VALUES ($1, 'dwarf') ["Pluto"]
DELETE FROM "planets" WHERE "type" = 'gasGiant' []
DROP TABLE "planets" []
DROP TYPE "planet_type" []

MySQL

Opening new connection to [IPv6]localhost/::1:3306
CREATE TABLE `planets`(`id` BIGINT PRIMARY KEY AUTO_INCREMENT, `name` TEXT NOT NULL, `type` ENUM('smallRocky', 'gasGiant') NOT NULL) []
INSERT INTO `planets` (`name`, `type`) VALUES (?, 'smallRocky'), (?, 'gasGiant') ["Earth", "Jupiter"]
ALTER TABLE `planets` MODIFY `type` ENUM('smallRocky', 'gasGiant', 'dwarf') []
INSERT INTO `planets` (`name`, `type`) VALUES (?, 'dwarf') ["Pluto"]
DELETE FROM `planets` WHERE `type` = 'gasGiant' []
ALTER TABLE `planets` MODIFY `type` ENUM('smallRocky', 'dwarf') []
DROP TABLE `planets` []

Moved https://github.com/vapor/sql-kit/pull/83 here.

SQLKit 3.0.0 Beta 3 - 2019-12-13 22:03:46

  • Added new SQLRow.decode(model:) method (#79)
  • SQLRow protocol expanded to support Codable (#79)
  • Added first(decoding:) all(decoding:) and run(decoding:) methods to SQLQueryFetcher (#79)
  • Added set(model:) method to SQLUpdateBuilder (#79)

SQLKit 3.0.0 Beta 2 - 2019-12-09 17:21:12

  • Adds support for passing custom Loggers (#73)
  • Adds general purpose table constraints (#62)
  • Changes to using SQL 92 standard not equal operator (#61)
  • SQLDialect protocol has been reworked to be non-mutating. SQLDatabase now carries a reference to its dialect. (#70)
  • Enabled test discovery on Linux (#78)

SQLKit 3.0.0 Beta 1 - 2019-10-24 20:41:43

  • SQLDialect can now suppress IF [NOT] EXISTS using the supportsIfExists option. (#68)
  • GROUP BY and HAVING are now supported. (#69)

SQLKit 3.0.0 Alpha 1.4 - 2019-10-10 17:27:13

  • Fixed a SQL syntax error in SELECT statements with multiple joins

SQLKit 3.0.0 Alpha 1.3 - 2019-08-14 17:53:05

New:

  • Added SQLDialect.literalDefault option for customizing default literal string

SQLKit 3.0.0 Alpha 1.2 - 2019-07-09 15:56:41

New:

  • Adds SQLInsertBuilder.model method.
  • Adds SQLQueryEncoder.

SQLKit 3.0.0 Alpha 1.1 - 2019-06-06 00:07:15

New:

  • Added SQLDataType.custom(SQLExpression)

SQLKit 3.0.0 Alpha 1 - 2019-06-04 01:08:06

More information on Vapor 4 alpha releases:

https://medium.com/@codevapor/vapor-4-alpha-1-releases-begin-94a4bc79dd9a

API Docs:

https://api.vapor.codes/sql-kit/master/SQLKit/index.html