Multi tenancy options

dev support
1

My long, slow road to being a modern dev…

I have used MySQL for decades now. And I have previously developed a large application with CF where the multi tenant data was in one database, using specific identifiers for each client. Even then, the database was never a challenge to manage. Maybe I was just lucky.

But I know this is not “the way” to do things now. So, I am wondering if you have any advise for a “beginner” looking to implement a modern version of multi tenancy. Is there code or application that applies changes to each database? Is it done with MySQL views? What is an easy way to start?

Don’t forget to tell us about your stack!

OS: ???
Java Version: ???
Tomcat Version: ???
Lucee Version: ???

2

Not sure if this is the right way, but I would go with a security first model.

MySQL / MariaDB can have separate users and permissions per user per database.

As We have something like this, you would create a master database blueprint, from there you can export and import the schema to a new mysql instance, add your own permissions and point that lucee instance at that database.

You can control all of this via the Application.cfm/c

ie
application.dsn1 = customer1.database.or.server
application.dsn2 = customer2.database.or.server
application.dsn3 = customer3.database.or.server

So on and so forth

3

Thanks Terry! I get what you are saying. But even before that step, what do you use to manage the multiple databases? For instance, if I want to add a column to a table called “dayOfWeek”, and that column has to be added in 20 identical schemas (20 databases), what do you use to do that instead of manually adding (even through a DB manager) to each database. Aren’t there tools for doing this kind of replication and management?

4

You could use a database modeling software (e.g. erwin Data Modeler | Industry-Leading Data Modeling Tool | erwin, Inc.)
or you could use a migration plan (e.g. https://guides.cfwheels.org/docs/database-migrations-getting-started)

1 Like
5

one option is using partitioned tables

1 Like
6

Use Transactions for this.

The code would go something like this

START TRANSACTION;
  UPDATE  'db_customer1'.'somethingthatneedsdayofweek' SET `visible`=0 
    WHERE 'name' IN ("table1","dayofweek");
  UPDATE  'db_customer2'.'somethingthatneedsdayofweek' SET `visible`=0 
    WHERE 'name` IN ("table1","dayofweek");
  UPDATE  'db_customer3'.'somethingthatneedsdayofweek' SET `visible`=0 
    WHERE 'name' IN ("table1","dayofweek");
COMMIT;

of you could use ColdFusion to do something like that.

<cfloop customer.dsns.listx >


<cfquery>
update customer.#customerX#.dsn
where somethingthatneedsdayofweek
 'name' IN ("table1","dayofweek")
</cfquery>
</cfloop>
7

That’s what I was thinking you were getting at. I’ve been out of practice long enough, and heard people talk about this that I just assumed there was some kind of extension or open source tool that rolled changes across a series of databases as needed. I can see CF doing this, but it would seem best to create a tight application that does this and only this - unless someone already has?