Got a project underway and doing a bit of brainstorming about how best to architect the database connection pooling aspects. So please bear with me, trying to present a concise explanation is not always so easy.
Got an XDataServer that will have hundreds of endpoints grouped together in a bunch of different services, with each service corresponding to a particular part of a database schema. Think of an ERP system. One service deals with workers, one service deals with inventory, etc.
On the database side, there are a handful of DB2 servers and a handful of MySQL servers. Lets call them DB2-A, DB2-B, DB2-C, and MYSQL-01 through MySQL-10. Each DB2 database can have a max of 25 concurrent things going on, and each MySQL database is more like 5 things. So from a database pooling perspective, those would be the numbers I'd start with.
All the databases have the same schemas (more or less). I've got a separate DataModule defined fore each part of the schema as mentioned (Workers vs. Inventory for example). And each of these defines an FDConnection and a pile of SQL statements. Let's assume FireDAC and FDQuery everywhere for now.
(As an aside, I really, really do appreciate all that is Aurelius, but I also really, really like SQL and the thought of rewriting thousands of queries into Aurelius is, well, something I'll worry about after this is up and running).
So. Connection pooling. I'm assuming I'll need to create, in the XDataServer, a connection pool for each of the 13 databases, as outlined in the very last example in the XDataServer User Guide's Other Tasks section. I'm assuming that for each of these 13 connection pools, they'll instantiate a copy of the same DataModule, just setting the FDConnection parameters specifically for each different connection pool. If I've got 5 different DataModules, then I'll be looking at (5x13) 65 distinct connection pools.
When writing the code for the service endpoints, anything that requires a database connection (well, all of them most likely) will then be able to use a value in the JWT to determine which database is being used. All the connections in the same service will use the same DataModule, so it will then grab a connection from the appropriate pool (one of the 60). Once the work of the endpoint is done, the connection would be released back to the pool. I think there are examples of this "grab a connection" and "release a connection" here in the support center, so all good so far.
So am I on the right track here? Should I instead setup 13 FDManager components in the XDataServer itself and use its connection pooling mechanism, in addition to the pooling system for the DataModules? I kinda like the idea that the DataModules contain the connections and thus abstract away that kind of complexity in this IDBConnectionFactory business.
Finally, I'm assuming this is all transparent to the client side - clients can fire off all the async requests to endpoints they like and the XDataServer will happily chug away at them all, keeping the connection pool limits in mind. Kind of like doing client multithreading the hard way. Or easy way depending on your point of view.