Select SQL query for the fluent-postgres-driver with bindings

📚 Official Books Server Side Swift with Vapor
1

Hello everyone!
I do not understand how to put bindings properly in SQL query in my Vapor project.
Always I just get 500 error.
I add them as ‘PostgresData’, then put after query SQL.
It looks like this:

// PostgreSQL
        var returnValue: [Song] = []
        
        guard let postgres = req.db as? PostgresDatabase else {
            throw Abort(.internalServerError)
        }
        
        let id = PostgresData(int32: 289) // add binding
        
        do {
            let results = try await postgres.query("SELECT songs FROM bangs WHERE songDuration = $1", [id]).get() // add binding here as an array, '$1' inside the array
            
            for result in results {
                returnValue.append(try result.sql().decode(model: Song.self))
            }
        } catch {
            throw Abort(.accepted) // error here!
        }
        return returnValue

Could you please help me, I am struggling with this for one week looking for any documentation.

2

Looks like you are manually querying the database and then decoding a model. The easy way to do it, is using Fluent: Vapor: Fluent → Overview

let planets = try await Planet.query(on: database)
    .filter(\.$type == .gasGiant)
    .sort(\.$name)
    .with(\.$star)
    .all()

But if you are set on doing things manually, try something like:

import FluentSQL

if let sql = req.db as? SQLDatabase {
    // The underlying database driver is SQL.
    let planets = try await sql.raw("SELECT * FROM planets").all(decoding: Planet.self)
} else {
    // The underlying database driver is _not_ SQL.
}

See the following link: Vapor: Fluent → Advanced

1 Like
3

This should be written as

let results = try await postgres.query("SELECT songs FROM bangs WHERE songDuration = \(id)").get()

SQLQueryString statements translate string interpolation to bindings automatically for you. If you need a raw insert you can do "SELECT songs FROM bangs WHERE songDuration = \(raw: id)" but obviously only do this when you control the string or are completely sure you’ve sanitised the input as that’s how you get hacked

2 Likes
4

Thank you very much!

I also drilled the internet and used local pgAdmin with selects directly to local PostgresDB and it turned out that you need to put column names under “” (if the type is ‘text’ e.g.).

In this case is would be like:

if let sql = req.db as? SQLDatabase {
    // The underlying database driver is SQL.
    let planets = try await sql.raw("SELECT * FROM planets WHERE \"name\" = \(id)").all(decoding: Planet.self)
} else {
    // The underlying database driver is _not_ SQL.
}