[Discussion] NIOPostgres: A NIO-based PostgreSQL Driver

(Tanner) #1

NIOPostgres: A NIO-based PostgreSQL Driver

Introduction

NIOPostgres is a client package for connecting to, authorizing, and querying a PostgreSQL server. At the heart of this module are channel handlers for parsing and serializing messages in PostgreSQL's proprietary wire protocol. These channel handlers are combined in a request / response style connection type that provides a convenient, client-like interface for performing queries. Support for both simple (text) and parameterized (binary) querying is provided out of the box alongside a PostgresData type that handles conversion between PostgreSQL's wire format and native Swift types.

Motiviation

Most Swift implementations of Postgres clients are based on the libpq C library which handles transport internally. Building a library directly on top of Postgres' wire protocol using SwiftNIO should yield a more reliable, maintainable, and performant interface for PostgreSQL databases.

Dependencies

This package has two dependencies:

  • swift-nio from 2.0.0
  • swift-nio-ssl from 2.0.0

This package has no additional system dependencies.

Proposed Solution

This section goes into detail on a few distinct types from this module to give an idea of how they work together and what using the package looks like.

PostgresConnection

The base connection type, PostgresConnection, is a wrapper around NIO's ClientBootstrap that initializes the pipeline to communicate via Postgres messages using a request / response pattern.

import NIOPostgres

// create a new event loop group
let elg = MultiThreadedEventLoopGroup(numberOfThreads: 1)
defer { try! elg.syncShutdownGracefully() }

// create a new connection
let address = try SocketAddress(ipAddress: "127.0.0.1", port: 5432)
let conn = try PostgresConnection.connect(to: address, on: elg.eventLoop).wait()
defer { try! conn.close().wait() }

// optionally configure TLS
try conn.requestTLS(using: .forClient(certificateVerification: .none), serverHostname: "127.0.0.1")

// authenticate the connection using credentials
try conn.authenticate(username: "username", database: "database", password: "password").wait()

// ready to query
print(conn) // PostgresConnection

Closing

A connection must be closed before it deinitializes. PostgresConnection ensures this by asserting that it has been closed in its deinit handler. This is meant to help developers implement proper graceful shutdown early and avoid leaking memory or sockets.

Simple Query

Assuming we have an active, authenticated PostgresConnection, we can query the connected server using PostgreSQL's simple, text format.

import NIOPostgres

let conn: PostgresConnection ...

// select the current version
let rows = try conn.simpleQuery("SELECT version()").wait()
print(rows) // [PostgresRow]

// fetch the version column from the first row casting it to a Swift string
let version = rows[0].column("version")?.string
print(version) // String?

This format does not support parameterizing input and returns all data in string format. To bind values, insert them into the string:

try conn.simpleQuery("SELECT * FROM planets WHERE name = 'Earth'")

Query

We can also perform parameterized queries with an active PostgresConnection. These queries support binding input parameters and return data in a more compact binary format.

Input parameters are passed as an array of PostgresData following the SQL string. In the query string, input parameters are referenced by incrementing placeholders, starting with $1.

import NIOPostgres

let conn: PostgresConnection ...

// selects all planets where name is equal to the first bound parameter
let rows = try conn.query("SELECT * FROM planets WHERE name = $1", ["Earth"]).wait()

// fetch the "name" column from the first row, casting it to a string
let foo = rows[0].column("name")?.string
print(foo) // "Earth"

PostgresData

PostgresData represents data both going to and coming from Postgres.

Input

An array of PostgresData is supplied alongside parameterized queries, one for each parameter. There are many initializers for creating PostgresData from Swift's standard types. For example:

import NIOPostgres

let string = PostgresData(string: "Hello")
let double = PostgresData(double: 3.14)
let date = PostgresData(date: Date(timeIntervalSince1970: 42))

PostgresData also conforms to Swift's Expressible... protocols, allowing for conversion between Swift literals.

import NIOPostgres

let inputs: [PostgresData] = ["hello", 3.14]

Output

Likewise, PostgresData can be converted back to Swift types. This is useful for converting data returned by Postgres queries into meaningful types. There are many methods for Swift's standard types, for example:

import NIOPostgres

let data: PostgresData
print(data.string) // String?

Here is a full list of types supported currently:

  • Swift.String
  • Swift.Int
  • Swift.Int64
  • Swift.Int32
  • Swift.Int16
  • Swift.Int8
  • Swift.UInt
  • Swift.UInt64
  • Swift.UInt32
  • Swift.UInt16
  • Swift.UInt8
  • Swift.Float
  • Swift.Double
  • Foundation.Date
  • Foundation.Data
  • Foundation.UUID

PostgresRow

Both simpleQuery and query return an array of PostgresRow. Each row can be thought of as a dictionary with column names as the key and data as the value. While the actual storage implementation is private, PostgresRow gives the following methods for accessing column data:

struct PostgresRow {
	func column(_ column: String) -> PostgresData?
	func column(_ column: String, tableOID: UInt32) -> PostgresData?
}

If no column with that name is contained by the row, nil is returned. Additionally, if no tableOID is supplied, matching columns from any table will be returned on a first match basis.

PostgresError

The PostgresError type represents errors thrown from both the Postgres package itself (during parsing, for example) and errors returned by the server:

public enum PostgresError: Error {
    case proto(String)
    case server(PostgresMessage.Error)
    case connectionClosed

    var code: Code { ... }
}

The PostgresError.Code type is a large enum-like struct containing all recognized Postgres error codes. This is useful for error handling:

let conn: PostgresConnection ...

do {
    _ = try conn.simpleQuery("SELECT &").wait()
} catch let error as PostgresError {
	switch error.code {
	case .syntax_error: ...
	default: ...
	}
}

PostgresClient

While PostgresConnection is the main type to use for connecting, authorizing, and requesting TLS, the PostgresClient protocol is sufficient for performing both text and parameterized queries.

protocol PostgresClient {
    var eventLoop: EventLoop { get }
    func send(_ request: PostgresRequestHandler) -> EventLoopFuture<Void>
}

PostgresConnection is the only conformer that NIOPostgres provides, but it is expected that dependencies will add additional conformers. For example, a consumer of this package might add conformance to a pool of connections, allowing for automatic recycling as needed, a crucial feature for long-running applications.

Note on usage

Since most of NIOPostgres's convenience methods are added to PostgresClient instead of PostgresConnection directly, any additional conformers should feel exactly the same to use. Because of this, it is expected that PostgresClient should be used any place where you need to make queries. For example, in a theoretical controller:

final class UserController: Controller {
    let db: PostgresClient
    init(db: PostgresClient) { 
        self.db = db
    }

    func names(_ req: HTTPRequest) -> EventLoopFuture<[String]> {
        return self.db.query("SELECT name FROM users").map { rows in
            return rows.map { $0.column("name")?.string! }
        }
    }
}

Because this controller relies on PostgresClient, any of the following could be supplied to it:

  • Connected PostgresConnection
  • Pool of PostgresConnections
  • Dummy conformer for testing

PostgresRequestHandler

Postgres' wire protocol uses a request / response pattern, but unlike HTTP or Redis, one request can yield one or more responses. Because of this, a PostgresRequestHandler is used instead of PostgresRequest / PostgresResponse.

protocol PostgresRequestHandler {
    func respond(to message: PostgresMessage) throws -> [PostgresMessage]?
    func start() throws -> [PostgresMessage]
}

PostgresRequestHandler is responsible for sending zero or more initial messages and handling the server's responses. When the request is complete, nil is returned by respond, causing the client's send future to complete.

CMD5

MD5 hashing is required for PostgreSQL's authentication flow. This module follows NIO's approach and embeds a private C-based implementation rather than relying on external Swift crypto libraries.

Todo / Discussion

Here are some things that are still a work in progress:

  • Prepared Statement API: Postgres allows for parameterized queries to be re-used multiple times with different inputs. An API for doing this in NIO Postgres would be useful.
  • PostgresRequestHandler edge cases: Finer grain input / output from this protocol would be useful in assisting with protocol edge cases. For example, sometimes a Postgres error message can signal request completion depending on state.
  • PostgresRow column lookup: The exact algorithm used for looking up PostgresData for a given column name should be determined by performance testing.
  • Warnings: There are several #warning messages indicating small, remaining todos, like fixing some force trys and unwraps. All warnings will be removed before tagging pre-release versions.

How to use

To try this package out at home, add the following dependency to your Package.swift file:

.package(url: "https://github.com/vapor/nio-postgres.git", .branch("master")),

Then add "NIOPostgres" to your module target's dependencies array.

Seeking Feedback

  • If anything, what does this proposal not cover that you will definitely need?
  • If anything, what could we remove from this and still be happy?
  • API-wise: what do you like, what don't you like?

Feel free to post feedback as response to this post and/or GitHub issues on vapor/nio-postgres.

12 Likes
[Feedback] NIOPostgres: A NIO-Based PostgreSQL Driver
(Thomas Krajacic) #2

Fantastic work as usual!

I think that the proposal should at least include the NUMERIC type we discussed offsite though, since it is part of the SQL standard even, and not Postgres specific. We'd also get more feedback on it here.

2 Likes
(Johannes Weiss) #3

+1! Great stuff @tanner0101, IMHO this should definitely move forward and get a tagged version.

I think this is a great start!

Don't think there's anything.

I think instead of making the TLS configuration after you get your connection as in your proposal:

// optionally configure TLS
try conn.requestTLS(using: .forClient(certificateVerification: .none), serverHostname: "127.0.0.1")

I think we should state that when making the connection, for example:

let conn = try PostgresConnection.connect(to: SocketAddress,
                                          tlsConfiguration: TLSConfiguration?,
                                          on: EventLoop).wait()

Why: I think this is unnecessary flexibility (that needs to be tested) because it doesn't make sense to change your mind in the middle of a connection. Also whilst NIO supports enabling/disabling TLS whenever, Network.framework does not. So if you plan to use this with swift-nio-transport-services without swift-nio-ssl, then you would need to state the TLS configuration at connection creation time and not thereafter.

2 Likes
(Jari (LotU)) #4

Overall, looks amazing!

One thing that got me thinking, is the parameter binding:

// selects all planets where name is equal to the first bound parameter
let rows = try conn.query("SELECT * FROM planets WHERE name = $1", ["Earth"]).wait()

why do we all of a sudden start counting at 1? :thinking: Seems a bit off to me seeing as how:

  1. Arrays start counting at 0
  2. Native swift closures use $0 for the first argument

Also, the answers to your questions:

Nothing I can see right now.

Nothing I can think of.

See my point above :smiley: other than that, no comments!


Thanks for your time put into this, it looks like a great proposal!

2 Likes
(Tanner) #5

That's a Postgres thing unfortunately, so not much we can do about it. I think a cool feature we could add in the future would be a Swift interpolation API for parameterized queries, so that you could do something like the following:

let rows = try conn.query("SELECT * FROM planets WHERE name = \("Earth")").wait()

The above would get converted internally to the $1 placeholder and bound values = ["Earth"].

Thanks!

2 Likes
(Jari (LotU)) #6

Ah, that's a pity. I seemed to recall postgres using ?'s. But if it's a postgres default, defenetally keep it.

That'd be amazing! And a great feature to Swift in general too I think!

(David Hart) #7

I'd be really nice to camelCase the error codes: syntax_error does feel very weird in Swift.

2 Likes
(Johannes Weiss) #8

IIRC there was a similar discussion about the unicode character properties which then also got added in a more Swift-like form. So Changes_When_Casefolded became changesCaseWhenFolded.

(Francois Green) #9

I've been trying out NIOPostgres with Kitura: This is a far better abstraction than Swift-Kuery! That said, a story for NUMERIC conversion would be nice as it keeps popping up:

/* This leads to a fatal numeric conversion error */
select avg(length(content)) as average_length from message

My workaround is to cast the NUMERIC to something readable:

select cast(avg(length(content)) as double precision) as average_length from message

This is a great library and will hopefully serve as a model for other database drivers.

1 Like
(Gwendal Roué) #10

FWIW, and I can't speak for NIOPostgres, but GRDB (a Swift SQLite client) transparently decodes doubles to ints, and ints to doubles. Fatal errors only happen for overflow (such as decoding 128 into Int8, or a huge double into Int64). This allows the application code to get what it generally expects, even with a limited knowledge of the type conversions performed by the database engine, while preventing the worst data loss scenarios. This has proven an excellent ergonomic decision, so far. Maybe NIOPostgres could consider such an approach?

(Caleb Kleveter) #11

Since ExpressibleByStringInterpolation has been re-implemented in Swift 5, it should be possible to do that.

(Tanner) #12

The next phase of this proposal, Feedback, is here:

@tomerd can you please lock this thread?

closed #13