PostgreSQL Core

PostgreSQL (vapor/postgresql) is a pure-Swift (no libpq dependency), event-driven, non-blocking driver for PostgreSQL. It’s built on top of the Swift NIO networking library.

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

Using just the PostgreSQL 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.

PostgreSQL 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 PostgreSQL, all of the features of PostgreSQL core will be available to you.

Getting Started

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

Package

The first step to using PostgreSQL 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.         // 🐘 Non-blocking, event-driven Swift client for PostgreSQL.
  10.         .package(url: "https://github.com/vapor/postgresql.git", from: "1.0.0-rc"),
  11.     ],
  12.     targets: [
  13.         .target(name: "App", dependencies: ["PostgreSQL", ...]),
  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 PostgreSQL
  3. /// ...
  5. /// Register providers first
  6. try services.register(PostgreSQLProvider())

Registering the provider will add all of the services required for PostgreSQL to work properly. It also includes a default database config struct that uses typical development environment credentials.

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

  1. /// Register custom PostgreSQL Config
  2. let psqlConfig = PostgreSQLDatabaseConfig(hostname: "localhost", port: 5432, username: "vapor")
  3. services.register(psqlConfig)

Query

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

  1. router.get("psql-version") { req -> Future<String> in
  2.     return req.withPooledConnection(to: .psql) { conn in
  3.         return try conn.query("select 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 PostgreSQL version.

Connection

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

Create

There are two methods for creating a PostgreSQLConnection.

  1. return req.withPooledConnection(to: .psql) { conn in
  2.     /// ...
  3. }
  4. return req.withConnection(to: .psql) { 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 PostgreSQL database that does not bind any parameters. Some queries you send to PostgreSQL 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: .psql) { conn in
  2.     return conn.simpleQuery("SELECT * FROM users;")
  3. }
  4. print(rows) // Future<[[PostgreSQLColumn: PostgreSQLData]]>

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: .psql) { conn in
  2.     return conn.simpleQuery("SELECT * FROM users;") { row in
  3.         print(row) // [PostgreSQLColumn: PostgreSQLData]
  4.     }
  5. }
  6. print(done) // Future<Void>

Parameterized Query

PostgreSQL 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: .psql) { conn in
  2.     return try conn.query("SELECT *  users WHERE name = $1;", ["Vapor"])
  3. }
  4. print(users) // Future<[[PostgreSQLColumn: PostgreSQLData]]>

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