PostgresNIOMacros: Create prepared statements from decorated SQL

Community Showcase
,
1

At ServerSideSwift @fabianfett pitched me the idea to use a Macro for Prepared Statements in PostgresNIO.

The @Statement macro can generate a prepared statement with columns and binds by providing a slightly modified SQL statement.

import PostgresNIO
import PostgresNIOMacros

@Statement("SELECT \("id", UUID.self), \("name", String.self), \("age", Int.self) FROM users WHERE \(bind: "age", Int.self) < age")
struct UsersStatement {}

let connection: PostgresConnection = ...
let stream = try await connection.execute(UsersStatement(age: 18), logger: ...)
for try await user in stream {
    print(user.id, user.name, user.age)
}

Under the hood, the macro generates a prepared statement, in this case, it creates the SQL string SELECT id, name, age FROM users WHERE $1 < age, a var age: Int bind and a struct Row.

struct Row {
    var id: UUID
    var name: String
    var age: Int
}

To achieve the same result without the macro one would need to write the following code:

struct UsersStatement: PostgresPreparedStatement {
    struct Row {
        var id: UUID
        var name: String
        var age: Int
    }

    static let sql = "SELECT id, name, age FROM users WHERE $1 < age"
    
    var age: Int

    func makeBindings() throws -> PostgresBindings {
        var bindings = PostgresBindings(capacity: 1)
        bindings.append(age)
        return bindings
    }

    func decodeRow(_ row: PostgresRow) throws -> Row {
        let (id, name, age) = try row.decode((UUID, String, Int).self)
        return Row(id: id, name: name, age: age)
    }
}

You can try it out here:

If proven useful it might be added to PostgresNIO in a future release.

Let us know what you think of it!

7 Likes
2

I'm not all that familiar with PostgresNIO, could you show what the statement would look like without the macro? (I.e. what a user would have to do to achieve the same result right now.)

3

Good point! I've added it to the post.

4

Very cool, I like how it provides the ultimate flexibility of SQL while retaining the benefits of type safety and input validation. :clap:

2 Likes
5

Thanks for adding the example, this is really neat and it definitely reduces boilerplate!

One aspect that doesn't sit so well with me is that I don't know how readable this is. The static let sql = ... is easy to read, but the statement macro is quite long thanks to the extra information required for the macro. It might be worth exploring different ways of spelling this – maybe it's multiple macros, or macros with multiple arguments.

6

It'd be amazing if this was pluggable. I.e. if we could use the same macros for e.g. SQLite etc.

If the macros generate the struct as you suggest, as well as some description of the Statement, different backends could produce an appropriate prepared statement for each use case.

7

I agree with @georgebarnett; the readability of the statement macro is off-putting. Something like this would be much better (just an example):

enum BindOperation : String {
    case lessThan = "<"
}
macro Statement<T: ExpressibleByStringLiteral>(_ query: T, variables: [(key: T, valueType: Any, bind: BindOperation?)], table: T)

so the dev would do something like:

@Statement("SELECT", variables: [("id", UUID.self, nil),("name", String.self, nil),("age", Int.self, .lessThan)], table: "users")
8

There is a lot of flexibility in keeping the macro as is, with the input being a string (with interpolation). For simple select statements, yes, this would make things cleaner, but at the risk of making more complex SQL statements impossible.

9

True. A better solution would be to have more macros for specific use cases (table expressions, combining, selecting, etc) and use the raw statement macro as a last resort.

An even better solution would be to rewrite the PostgresNIO (more specifically PostgresPreparedStatement) implementation, but I digress.

10

Thanks @lovetodream for playing around with this idea.

I'm not sure I agree here. Chances are very high that this leads to reimplementing SQL in a more Swift dialect. Embracing SQL as it is, offers the ultimate flexibility.

I think readability can be increased by just using line-breaks when defining the PreparedStatement:

@Statement("""
  SELECT 
    \("id", UUID.self), 
    \("name", String.self), 
   \("age", Int.self) 
  FROM users 
  WHERE 
    \(bind: "age", Int.self) < age
""")
struct UsersStatement {}

@Gregor_Milos Do you actually want pluggable macros or all database drivers to support the same pattern?

Either way I'm afraid this is a long way to go.

I'd really appreciate your feedback here in a PostgresNIO issue so that I can see what you have in mind.