Update multiple databases

Hi,

I’ve got an application with current setup in a shared hosting for the moment:

Here is the current setup:

  1. Each tenant has it’s own database and the database structure is the same for each database
  2. There is just one set of application files.
  3. Each tenant also has it’s own subdomain.

At the moment I only need to manage 5 databases and therefore I’m implememnting all the database updates manually. I’m thinking in the future if the number of databases increase then it will not be feasible to run manual updates, therefore I’m starting to think what the best update strategy will be.

I’ve searched online and I came across some database migration libraries like https://phinx.org/.
Phinx seem to run via SSH commands but I don’t have access to SSH on my shared host unless there is an option to run it via pure PHP and also my idea is to check for new database and run the install (if exist) every time a visitor browse the subdomain, in this way it will not be me to update each database but it will be done automatically.

Do you think this could be a good idea?

1 Like

What you’re describing with database updates triggered by users is what’s also used for WordPress plugins.

The idea is that you give each schema a version number and write that version number in the database. Then when the code runs you see it it has any newer version than what is in the database now, and if so, apply it.

It seems to work for them. So I suppose it’s a fine idea. I personally like schema updates separate from the code itself during deploy, but if you can’t SSH into the host then that’s not op an option and updating at runtime is all you’ve got.

Hi @rpkamp thanks for your answer.

I suppose i need to run the script in the header and block user login until the update is compleated. Also what will be the right strategy in case something doesn’t go well with the update?
Are you aware of any PHP library I could use instead of writing the code myself?

many thanks

Yes that makes sense.

It’s hard to give a hard answer on this one, as it really depends on what the update was supposed to do and whether the application is still able to function without it.

What you could do is split the deployment of the updates from the code that actually needs those updates. So suppose you have a new feature that needs a new database table. Instead of deploying this all at once, the only thing you deploy is the code that updates the database. If this fails, nothing happens, because the feature that needs the database isn’t there yet. Then, once you know the update was successful, you deploy the actual code too.

There are several of these migration frameworks out there, but they all use the command line AFAIK. You’re probably better off writing something simple yourself.

In pseudo code it would look like this:

version = db.query('SELECT current_version FROM database_versions')
if (version < '1.1') {
   db.query( /* perform some update */ )
   db.query( /* perform another update */ )
   db.query('UPDATE database_versions SET current_version = "1.1"')
}
if (version < '1.2') {
   db.query( /* perform yet another update */ )
   db.query('UPDATE database_versions SET current_version = "1.2"')
}
// and so on and so forth

And be sure to do all the changes in one transaction, so you can rollback if you have an error. Otherwise you will end up in a half updated database in case of an error and you have a inconsistent status.

1 Like

Generally good advice, but please note that transactions don’t work for DDL queries (i.e. add/remove column, create table, add constraints, etc) in MySQL. It will say it started a transaction and after some statements if you say to roll back it will say it did, but it didn’t.

Related read: https://julien.danjou.info/why-you-should-care-that-your-sql-ddl-is-transactional/

1 Like

Is it a mysql database? If so, for each update, you can create an update sql file (such as update-2022-10-21.sql and write all your sql statements in it). Apart from that, you can maintain a simple php script which will run these sql statements using mysqli_multi_query on each one of your databases one by one. The end result might look something like this:

<?php
$sql = file_get_contents('/path/to/update_script.sql');
foreach($connections as $conn) { // assuming each $conn is a different database
	$result = mysqli_multi_query($conn, $sql);
	if (!$result) { //handle sql error}
	while(mysqli_more_results($this->db->conn_id)) {
		$result = mysqli_next_result($this->db->conn_id); // flush
		 if (!$result) {//handle sql error}
	}
}

One thing you may want to do is to fetch the update_script.sql through an argument instead of hard-coding it.

This is something you can do for every SQL database. This is not special for MySQL

That’s true but the “multi query” approach will be different for each database. For mysql, there is mysqli_multi_query() function, I’m not sure what it is for pgsql but for sqlite3, I recall that it’s a much simpler approach.

mysqli_multi_query() Is just a PHP replacement for doing one single MySQL call one after another. It has nothing to do with MySQL itself.

I would highly recommend you get off shared hosting and get a VPS. I have several and they are only about $5 a month each. If you don’t have the skills to manage a VPS, it would be worth learning. Easiest way to learn is to set up your own Virtual Machine on your local computer and install Linux.

1 Like

What is the advantage of VPS in the content of the threads owner question

The OP as as he stated, as well as anyone else is restricted on what they can do on a shared host. A mult-tenant application such as what the OP has is meant to scale. You cannot easily scale or do what you need to do when you have server restrictions.

The time to get a proper server setup is now, not in the future.

1 Like

Many thanks for the advice, I’ve read the article and found very interesting.
How could I manage rollback if an error occur?

Thanks everyone for helping me.

You could write a script that applies the change and one that rolls it back. If an error happens during the apply you run the roll back script. If that also fails though, you’re sole out of luck :grin:

@rpkamp will it work with a Simple PHP try catch?

Yup.

try {
   // apply changes
} catch (Throwable) {
   // roll back changes
}
1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.