Vapor 3: Using multiple databases

Question

Using Vapor 3, is there an easy way to switch databases, while the server is running?

For example, a user logs in using the 'login' db. I then set the db for that user in their cookie. Any subsequent requests from that user then use the db identified in the cookie (the 'user' in this scenario would really be a company).

All db's would be from the same db family (eg MySQL). This would keep every companies data in their own db, and limit the size of each db (and hopefully, overall, db operations would be faster). Also, any need to restore a db would only impact one company, and backups would be simpler.

1. How to achieve this?
2. Would this be very inefficient?

Are there other better ways to achieve this?

Answer 1

As far as I understand you could create some different database identifiers like:

extension DatabaseIdentifier {
    static var db1: DatabaseIdentifier<MySQLDatabase> {
        return .init("db1")
    }
    static var db2: DatabaseIdentifier< MySQLDatabase > {
        return .init("db2")
    }
}

and then register them in configure.swift like this

let db1 = MySQLDatabase(config: MySQLDatabaseConfig(hostname: "localhost", username: "root", database: "db1"))
let db2 = MySQLDatabase(config: MySQLDatabaseConfig(hostname: "localhost", username: "root", database: "db2"))
var databaseConfig = DatabasesConfig()
databaseConfig.add(database: db1, as: .db1)
databaseConfig.add(database: db2, as: .db2)
services.register(databaseConfig)

after that don't forget to use .db1 and .db2 identifiers everywhere instead of default .mysql (for MySQL), e.g. in migrations

migrations.add(model: User.self, database: .db1)

with pooled connections

return req.requestPooledConnection(to: . db1).flatMap { conn in
    defer { try? req.releasePooledConnection(conn, to: . db1) }
    return User.query(on: conn).all()
}

and in transactions

return req.transaction(on: .db1) { conn in
    return User.query(on: conn).all()
}

Sorry if I haven't answered your questions. I understand that it'd be great if Fluent could support passing database name for each query, but I haven't found that in it. (or it's not obvious how to pass database name on query)

But btw from my point of view having separate databases for each client may give you a real headache on migrations... maybe it'd be better to store them all in one database but with partitioning? e.g. for PostgreSQL like described here