Raw Query In Vapor 4

Question

In Vapor 3 I was able to get a newConnection to the database and call a raw sql query using this:

return request.withNewConnection(to: .mysql) { (con) -> EventLoopFuture<T> in  
    return con.raw(sql)...
}

Now that I'm migrating my code to Vapor 4 the function raw or withNewConnection is gone, what can I use to make a raw query.

Steps to reproduce

return request.withNewConnection(to: .mysql) { (con) -> EventLoopFuture<T> in  
...
}

Error: Cannot infer contextual base in reference to member 'mysql' Value of type 'Request' has no member 'withNewConnection'

Expected behavior

Have a function inside request.db that let me get a new connection or run a raw query.

Environment

• Vapor Framework version: 4.7.1
• Vapor Toolbox version: vapor-beta
• OS version: macOS Catalina

Answer 1

Try this:

import SQLKit
struct Row:Content
{
    // fields go here
}
struct Context:Decodable
{
    let rows:[Row]
}
func rawDemo(_ request:Request) throws -> EventLoopFuture<View>
{
    return (request.db as! SQLDatabase).raw("SELECT * FROM MyTable").all(decoding:Row.self).flatMap
    {
        results in
        let context = Context(rows:results)
        return request.view.render("rawDemo", context)
    }
}

You must import the SQLKit module and cast request.db to SQLDatabase as shown in order to execute raw queries. Once you've done that, it becomes much like the Vapor 3 method. However, with the new functionality available in Vapor 4, I have now managed to get rid of all my raw queries and replace with sleek Fluent ones!

Answer 2

Error: Cannot infer contextual base in reference to member 'mysql' Value of type 'Request' has no member 'withNewConnection'

With Fluent 4 database identifier .mysql is not generic anymore unfortunately. So you have to cast it like this request.db as! SQLDatabase all the time to get methods like raw(...) to execute your raw query. Also connections are deep inside under the hood now.

I'd suggest to take a look at SwifQL + Bridges with Vapor 4

import Vapor
import MySQLBridge

struct MyResult: Codable {
    let column1: String
    let column2: Int
}
func someHandler(_ req: Request) throws -> EventLoopFuture<[MyResult]> {
    req.mysql.connection(to: .myDatabase) { conn in
        SwifQL
            .select(\SomeTable.$column1, \SomeTable.$column2)
            .from(SomeTable.table)
            .execute(on: conn)
            .all(decoding: MyResult.self)
    }
}

SwifQL is a DSL that gives you an ability to build any raw query safely, and Bridges is a helper for database drivers like mysql-nio and postgres-nio so it gives just convenience methods for them.