NIOPostgres: Client for PostgreSQL server built on NIO.
This module, NIOPostgres, would include a client for connecting to, authorizing, and querying a PostgreSQL server. This includes channel handlers for encoding / decoding PostgreSQL packets, a handler for enabling request / response interaction, and a Codable-based support for parsing / serializing PostgreSQL's binary data format.
Motivation
Most Swift implementations of a PostgreSQL client are based on the libpq C library which handles transport internally. Building a library directly on top of PostgreSQL's wire protocol using SwiftNIO should yield a more reliable, maintainable, and performant interface for PostgreSQL databases.
Propsed solution
A proof of concept implementation is available at vapor-community/nio-postgres. This package has the following features implemented:
- Connection & Authorization
- Optional TLS negotation
- Simple Query
- Extended Query (parameterized binds)
- Encoder for converting single-value
Encodabletypes to query paramters - Decoder for converting PostgreSQL data to single-value
Decodabletypes - Decoder for converting PostgreSQL rows to keyed
Decodabletypes - Unit and performance tests
This package is based on the core implementation of vapor/postgresql. Basically, just the part that builds directly on SwiftNIO. As such, NIOPostgres's only dependencies are swift-nio and swift-nio-ssl.
Detailed solution
This section goes into some of the details of this solution and decisions that were made.
Connection-based Request / Response
This module centers around a PostgresConnection type that enables a request / response interaction with the connected PostgreSQL database.
Simple Query
Below is working example of connecting to a remote server with TLS, and making a simple query.
let elg = MultiThreadedEventLoopGroup(numberOfThreads: 1)
// open a new connection, adding PostgreSQL packet handlers
let conn = try PostgresConnection.connect(
to: SocketAddress.newAddressResolving(host: "my.postgresql.domain", port: 5432),
on: elg.next()
).wait()
print(conn) // PostgresConnection
// ask server if it supports TLS, upgrading if so
let upgraded = try conn.requestTLS(using: .forClient(certificateVerification: .none)).wait()
guard upgraded else {
fatalError("Remote server does not support TLS")
}
// authenticate, fetching startup information
try conn.authenticate(username: "vapor", database: "vapor", password: "secret").wait()
// perform a simple (text) query
let rows = try conn.simpleQuery("SELECT version()").wait()
print(rows) // [PostgresRow]
// decode a single-value `Decodable` from the row
let version = try rows[0].decode(String.self, at: "version")
print(version) // PostgreSQL 9.1 ...
// cleanup
try conn.close().wait()
try elg.syncShutdownGracefully()
Extended Query
Extended queries are similar to simple queries, except they allow for values to be parameterized.
let conn: PostgresConnection ...
// performs an extended query
let binds: PostgresBinds = ["hello"]
let rows = try conn.query("SELECT $1::TEXT as foo", binds).wait()
// decode a single-value `Decodable` from the row
let foo = try rows[0].decode(String.self, at: "foo")
print(foo) // hello
PostgresMessage
This module's PostgresMessage enum is the basis of its communication with the connected server. It represents all possible packets that could be sent to or received from the client.
An inbound ByteToMessageDecoder and outbound MessageToByteEncoder are used to translate raw data from SwiftNIO into these messages.
PostgresConnection
This class uses an inbound ChannelHandler to send and receive PostgresMessage via a request / response interface. This handler supports pipelining multiple requests by storing an array of promises that will be fulfilled later.
Table Names
PostgreSQL does not include table names in its wire protocol data. Only table OIDs are provided. Without string table identifiers, it is difficult to disambiguate between duplicate column names in joined queries.
To help remedy this issue, this package provides a method loadTableNames() on PostgresConnection. This method executes and stores the result of the following query:
SELECT oid, relname FROM pg_class
Once loaded, this information can be used to translate table name to table OID using a hash map. This enables PostgresRow to decode columns / entities from a specific table.
// SELECT * FROM user, orders
// this query results in two columns named "id"
// a row from the previous query
let row: PostgresRow ...
let user = row.decode(User.self, table: "users")
let id = row.decode(UUID.self, at: "id", table: "users")
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.
Error codes
A Swifty interface to PostgreSQL's supported error codes is provided. This makes it possible to detect errors like syntax errors.
let conn: PostgresConnection ...
do {
_ = try conn.simpleQuery("SELECT &").wait()
} catch let error as PostgresError {
switch error.code {
case .syntax_error: ...
default: ...
}
}
