So I'm attempting to write a middleware method that can provide the main application with a database connection declared using the requesting user's credentials that are pulled from a top-level database. I'm building out a multi-tennant application that needs to handle data that is stored in a different db for each user. This is my current code (with some things unfinished) using some PostgresNIO methods, probably in the wrong way:
import Vapor
import FluentKit
import PostgresNIO
import FluentPostgresDriver
extension DatabaseID {
static let sample_db = DatabaseID(string: "sample_db")
}
struct DatabaseMiddleware: Middleware {
func respond(to request: Request, chainingTo next: Responder) -> EventLoopFuture<Response> {
// Extract the user information from the request header
let user = request.headers.first(name: "user_id")
// Fetch the database configuration for the user from the other database
return request.db.withConnection({ conn in
return conn.query("SELECT database_config FROM users WHERE username = $1", [user]).map { rows in
guard let databaseConfigString = rows.first?.first else {
// Return a default configuration if no configuration is found for the user
request.database = .init(...)
return next.respond(to: request)
}
// Parse the database configuration string and set the configuration for the request
let databaseConfig = self.parseDatabaseConfig(databaseConfigString)
request.database = databaseConfig
// Pass the request on to the next handler
return next.respond(to: request)
}
})
}
private func parseDatabaseConfig(_ configString: String) -> PostgresConfiguration {
let configValues = configString.split(separator: ",")
let host = configValues[0]
let username = configValues[1]
let password = configValues[2]
let database = configValues[3]
return .init(hostname: String(host), username: String(username), password: String(password), database: String(database))
}
}
As you can see I've been experimenting with different libraries of which I cannot seem to find the right method to create a PostgresConnection to return to my main application so that it can then use that database to perform all of the respective User's DB queries on.
I understand that I am missing some fundamental understanding of how I should be going about creating this PostgresConnection using data from a database in a Vapor/Swift way. I'm looking for some insight into how I might modify my methodology here to achieve the desired results.
I appreciate any advice on this. I've been going through the Kodeco books and I bought a hard copy of the Vapor book because I keep referencing it, but I can't find the information I'm looking for.
I should add that xcode lets me know that using request.db.withConnection({...}) this way is ambiguous without more context and I don't really know what context I need to be providing so that it can be used in this way or what alternative, and less ambiguous, method there is available to perform the same task.
I recently asked in the Discord channel about your doing what you are looking to do. The answer was that it was a bad idea. Instead, use your configure.swift to set up and establish your database connection. Middleware should not be used to accomplish this. The Vapor app should re-establish the connection if the database were to disappear and then later return, which is why I suspect that your string does this in middleware. By the time your App is actually executing the middleware, the database should already be configured and available in the middleware.
Thank you for your reply!! And thank you for taking the time to go and ask about my problem here. I'm on the discord too but sometimes my questions get missed in the shuffle of messages on there, nevertheless it's a great place to get info.
Your suggestion makes sense, I was at first a bit apprehensive to agree that this would work for what I'm trying to accomplish but after some more thinking, I'll bet there's a way to pre-load all the user database information and run the db setups, and then use the middleware to then select the correct one.
Now my only question (assuming this will all work as I anticipate) is: How will I re-run the configure.swift when I create a new user that then needs to have an entirely new set of tables created by a migration? Or maybe creating a new user, which then in-turn requires creating a new table for said user, will require a server restart to run the migration.
After doing some more thinking and research, I think it's a bad idea to try and create tables during runtime and instead I should look into either using a global set of tables that all user data can be stored and filter by some sort of user_id to get that user's data OR I should look into doing something like creating a bunch of sets of empty tables for use and then adopt them as new users are created. This isn't super efficient but maybe there's a more efficient way of accomplishing a similar idea with Postgres.
So then I wonder: Is it appropriate to still use middleware to check the packet header and pass the correct db reference to the next handler to then be used or should that be done in the main application?
As I see that it looks like you are using a unique connection to the database for each user. I am not quite sure why? The typical flow I would see is to have a standard DB connection used by the application. Why are you trying to use a distinct and different connection per user to the database? Is there a particular security requirement that requires isolation of the user accounts or are you trying to use the business logic of the DB engine per user to manage permissions and access to parts of the database?
Your individual login process is a very unusual backend design pattern. Though very common on a frontend design pattern. Usually, you would have a common DB connection used by the server and then the server-side business logic would handle permissions. Where the front end would present a user identity, the backend business logic would handle the Access control for an individual user connected at the front end.
The downside to individual DB connections is that a slight misconfiguration can cause the DB connection to blow up or fail spectacularly or in an infuriating single account series of errors based on permissions.
I wonder if you trained as a front-end developer or app developer who is now starting to get into the backend. There is nothing wrong with this. it's just switching mindsets when switching perspectives of the target, in the backend you typically do not produce tables for specific users. Instead, you define rows in the tables that exist for the users.
Personally, I have always had to be a full-stack developer which gives me an insight into some of the perspectives of both front and back-end development Not out of desire but more of necessity, and that the profession was not divided like that back in the 90s.
In my case what I do for database connections are use environmental variables on the server to handle the DB connection and leave it at that. this way the environmental variables are not in your version tracking source code, but setting up your DB connection can easily be done by passing in a few environmental variables and calling it a day.
Thanks again for the detailed reply. This conversation I super helpful.
Iāll admit thereās a lot I donāt know, but my application is a little outside the norm, hence the assumed need for separation of each userās data (not separation of actual āuserā info e.g. username).
The plan originally was to have a single user table, and the front end will manage user sessions like youāve outlined, but each userās data was to be stored separately for customer security concerns. Some customers have a requirement that they warehouse their own data which puts a single table approach into question.
However Iām fairly certain it would be acceptable to require those customers to pay for their own dedicated app instance so that we can use a more stable DB architecture. But thatās not an engineering problem to solve but rather a business problem.
Thanks again for the explanation and background on why itās better to stick to a single table approach.
If there is a specific business requirement for maintained separation of data you could accomplish this in other ways but it would also require a larger overhead and expense to the client agency. In those instances I would spin up a separate version of the app in a Kubernetes cluster. Client1.my domain.com, client2.my domain.com etc. then setup a fully separate pgsql instance of the app for each client. While setting up a db2 in each app for logging and auditing information. This kind of requirement is very rare as long as effective security and privacy design factors are considered and implemented.
For instance if you built an investment account backend your data in the backend would not wind up in separate database instances. But the business logic in the backend would certainly maintain an audit trail and maintain separation of interests. This would likely be implemented in a logic unit above the db protecting interests.
Do t think of it as a quickbooks hosted locally, think of it as a quickbooks online.
FWIW (and aware Iām a little late to the party) you may be able to implement something like this by using some kind of custom session logic and custom database client objects, along with a cache (but be generous in your use of NIOLocks) of those database clients and their connections. This way you could retain the connections between requests and also prune entire database clients which you no longer need to retain at the end of a userās session. We use a somewhat similar concept to provision and destroy individual HTTP clients for use with dynamic mutual TLS configurations within both our OpenID Connect provider and Open Banking services.
These sorts of things are likely more applicable in multi-tenancy contexts like ours (multiple customers with their own groups of users) but I can vouch for cost and deployment ops savings in avoiding the āapp instance per customerā approach that would first come to mind.
Feel free to reach out if you want to have a dig through some examples.
