Single-Tenant Databases - creating and updating new tenants via PHP

Question

We've built a single-tenant webapp in PHP and as it stands each hosted 'site' has its own database.

However we need to be able to run a script to create their database, assign a mysql user, and insert the default sql.

Now, we've got the database and user stuff working fine, its just the importing of the SQL. From what I can tell, PHP quite simply sucks when it comes to handling sql files, so storing a 'template' sql file seems to be out of the question.

My next thought was maybe storing a live database which is then cloned, giving the site its pre-populated database, however I cant see how this would work.

At the same time, I'd rather not 'hard code' the table creations into PHP as if we perform any updates we want to be able to update the SQL direct from our backend control panel.

Surely there has to be a logical way of handling creations and updates on a multi-tenant SaaS?

Answer 1

You can execute an SQL file from the command line using MySQL's client. So you always have the option of doing:

mysql -u <etc> -p <etc> <database> < master_definition.sql

right from a command line or shell script. Or exec() from within PHP. Once the structure is created, then insert users from within a PHP script.

Answer 2

I think I would connect to the database you want to take this from, and query the information_schema database in you MySQL server that holds info on all databases. Then loop it, then use what ever is in it to make a new database by running querys. If you change some fields or table names you don't need to worry about anything beeing hardcoded, because all names are taken from the information_schema and not an existing database that you might change:

select * from SCHEMATA  where SCHEMA_NAME = 'dateabase_to_clone'

You can pass in the name with a variable, so when you start this cloning script nothing at all is hard coded. This gets you the specifics on the database, caracterset, colarion, path. You can now create an identical database dynamically.

select * from TABLES where TABLE_SCHEMA = 'database_to_clone' 

gives you a list off all the tables in this database. You loop it and get the rest and so on.

Answer 3

We ended up going with a rather dirty method.

The sql file has a comment below each query like so:

-- split --

When the sql file is loaded up by the php script, it simply explodes the sql data, using the split. It then just loops through the exploded array data and runs each query.

I dont like it, but its the best option out of a bad bunch really.