[RESOLVED] Tips for 2 different database connections in the same application

Bonesnap

Hi everyone!

I'm currently building an application for a client where there are 2 database connections. The database used is SQL Server 2014 and we're using the SqlSrvPDO extension wrapped in a DAL that I have created. So far there have been no issues as my research discovered that as long as the 2 databases are on the same server and the user has the correct permissions for both, you can explicitly specify the database in your queries for the second database, even though the DB object was created with the first.

This was fine up until about 10 minutes ago when the client revealed that in production the databases my be on different servers (they didn't tell me this prior, and up to this point their production environment has always mirrored their QA environment). So now I have to review my code where the database is concerned which is no cakewalk. What are the tips and best practices for this sort of scenario? Should I simply be passing two database objects to my classes as a safety measure instead of just the one?

The first database is being used to simply select data. This is the one that is being used to created the database object and passed to PDO. The second is where some data will be inserted and selected, but only in certain scenarios.

Thanks for any help!

pbismad

Your main application code should be responsible for creating any database connection(s) that it needs to accomplish a task, then inject the correct connection into any/each instance of a class that's dependent on having a database connection, using dependency injection. This will result in general purpose code that can make use of any number of different database connections.

NogDog

Unless they're talking about some sort of master/slave replication setup where inserts/updates go to the master while selects go to the slave (replica), in which case you could potentially use middleware that your database connection points to, and which then routes each request based on its type. In that case, your connection would go to whatever server:port that middleware listens on (which might also handle load-balancing to multiple read replicas if the system scales up that way).

But otherwise, yeah, I've just created separate DB objects (normally PDO objects), and passed in whichever one is needed to applicable functions, methods, or constructors.

dalecosp

NogDog;11056851 wrote:
But otherwise, yeah, I've just created separate DB objects (normally PDO objects), and passed in whichever one is needed to applicable functions, methods, or constructors.

Or assigned them as properties of $this ... ?

dalecosp

Bonesnap;11056843 wrote:
Should I simply be passing two database objects to my classes as a safety measure instead of just the one?

The first database is being used to simply select data. This is the one that is being used to created the database object and passed to PDO. The second is where some data will be inserted and selected, but only in certain scenarios.

Thanks for any help!

This seems good to me; a system here originally ran on one server. When we needed to add a second, we needed some queries to go back to the first as it is kind of the "master" for certain parts of the application. Having a $this->local_db and $this->remote_db made this pretty much a breeze.

My $.02 (discounted for inflation and a low COLA here 😉 )

NogDog

dalecosp;11056855 wrote:
Or assigned them as properties of $this ... ?

That's [FONT=Courier New]__construct()[/FONT]'s job. 😉

dalecosp

NogDog;11056859 wrote:
That's [FONT=Courier New]__construct()[/FONT]'s job. 😉

Of course! Who writes your constructors for you? :p 😃 😉

Bonesnap

pbismad;11056845 wrote:
Your main application code should be responsible for creating any database connection(s) that it needs to accomplish a task, then inject the correct connection into any/each instance of a class that's dependent on having a database connection, using dependency injection. This will result in general purpose code that can make use of any number of different database connections.

I am doing this. I was asking if there were any tips or best practices for doing more than one connection. Typically an application has a single database connection.

In any case, after reviewing the code it was segregated enough that passing a secondary object to select methods or constructors wasn't much of a burden. I am only writing to the secondary database in 2 places.

NogDog;11056851 wrote:
But otherwise, yeah, I've just created separate DB objects (normally PDO objects), and passed in whichever one is needed to applicable functions, methods, or constructors.

Yup, ended up doing just this.

Thanks for the responses, guys! 🙂