# Overview - Vapor Docs

Skip to content


# Using SQL

The SQL library helps you build and serialize SQL queries in Swift. It has an extensible, protocol-based design and supports DQL, DML, and DDL:

- DQL: Data Query Language (`SELECT`)
- DML: Data Manpulation Language (`INSERT`, `DELETE`, etc)
- DDL: Data Definition Language (`CREATE`, `ALTER`, etc)

This library's goal is to help you build SQL queries in a type-safe, consistent way. It can be used by ORMs to serialize their data types into SQL. It can also be used to generate SQL in a more Swifty way. The rest of this guide will give you an overview into using the SQL library manually.

## Data Query

DQL (data query language) is used to fetch data from one or more tables. This is done via the `SELECT` statement. Let's take a look how we would serialize the following SQL query.

    SELECT * FROM users WHERE name = ?

This query selects all rows from the table `users` where the name is equal to a parameterized value. You can serialize literal values into data queries as well, but parameterization is highly recommended.

Let's build this query in Swift.

    // Create a new data query for the table.
    var users = DataQuery(table: "users")

    // Create the "name = ?" predicate.
    let name = DataPredicate(column: "name", comparison: .equal, value: .placeholder)

    // Add the predicate as a single item (not group) to the query.
    users.predicates.append(.predicate(name))

    // Serialize the query.
    let sql = GeneralSQLSerializer.shared.serialize(query: users)
    print(sql) // "SELECT * FROM `users` WHERE (`name` = ?)"

Here we are using the shared `GeneralSQLSerializer` to serialize the query. You can also implement custom serializers.

## Data Manipulation

DML (data manipulation language) is used to mutate data in a table. This is done via statements like `INSERT`, `UPDATE`, and `DELETE`. Let's take a look how we would serialize the following SQL query.

    INSERT INTO users (name) VALUES (?)

This query inserts a new row into the table `users` where the name is equal to a parameterized value. You can serialize literal values into data manipulation queries as well, but parameterization is highly recommended.

Let's build this query in Swift.

    // Create a new data manipulation query for the table.
    var users = DataManipulationQuery(statement: .insert, table: "users")

    // Create the column + value.
    let name = DataManipulationColumn(column: "name", value: .placeholder)

    // Add the column + value to the query.
    users.columns.append(name)

    // Serialize the query.
    let sql = GeneralSQLSerializer.shared.serialize(query: user)
    print(sql) // "INSERT INTO `users` (`name`) VALUES (?)"

That's all it takes to generate an `INSERT` query. Let's take a look at how this query would serialize if we use the `.update` statement instead.

    // Change the statement type
    users.statement = .update

    // Serialize the query.
    let sql = GeneralSQLSerializer.shared.serialize(query: user)
    print(sql) // "UPDATE `users` SET `name` = ?"

You can see that SQL has generated an equivalent `UPDATE` query with the appropriate syntax.

## Data Definition

DDL (data definition language) is used to create, update, and delete schemas in the database. This is done via statements like `CREATE TABLE`, `DROP TABLE`, etc. Let's take a look at how we would serialize the following SQL query.

    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)

This example is using SQLite-like syntax, but that is not required. Let's generate this query in Swift.

    // Create a new data definition query for the table.
    var users = DataDefinitionQuery(statement: .create, table: "users")

    // Create and append the id column.
    let id = DataDefinitionColumn(name: "id", dataType: "INTEGER", attributes: ["PRIMARY KEY"])
    users.addColumns.append(id)

    // Create and append the name column.
    let name = DataDefinitionColumn(name: "name", dataType: "TEXT")
    users.addColumns.append(name)

    // Serialize the query.
    let sql = GeneralSQLSerializer.shared.serialize(query: user)
    print(sql) // "CREATE TABLE `users` (`id` INTEGER PRIMARY KEY, `name` TEXT)"

That's all it takes to generate a `CREATE` query.

## Serializer

The default `GeneralSQLSerializer` that comes with this library generates a fairly "standard" SQL syntax. However, each flavor of SQL (SQLite, MySQL, PostgreSQL, etc) all have specific rules for their syntax.

To deal with this, all serializers are backed by the `SQLSerializer` protocol. All serialization methods are defined on this protocol and come with a default implementation. Custom serializers can conform to this protocol and implement only the methods they need to customize.

Let's take a look at how we would implement a `PostgreSQLSerializer` that uses `$x` placeholders instead of `?`.

    /// Postgres-flavor SQL serializer.
    final class PostgreSQLSerializer: SQLSerializer {
        /// Keeps track of the current placeholder count.
        var count: Int

        /// Creates a new `PostgreSQLSerializer`.
        init() {
            self.count = 1
        }

        /// See `SQLSerializer`.
        func makePlaceholder() -> String {
            defer { count += 1 }
            return "$\(count)"
        }
    }

Here we've implemented `PostgreSQLSerializer` and overriden one method from `SQLSerializer`, `makePlaceholder()`.

Now let's use this serializer to serialize the data query from a previous example.

    // Data query from previous example
    let users: DataQuery = ...

    // Serialize the query.
    let sql = PostgreSQLSerializer().serialize(query: users)
    print(sql) // "SELECT * FROM `users` WHERE (`name` = $1)"

That's it, congratulations on implementing a custom serializer.

## API Docs

Check out the API docs for more in-depth information about SQL's APIs.