Multi-Tenant App -- Duplicating DB structure

[mikeallenbrown]

Web app using 7295.

I've built a multi-tenant app where each user has their own database. I'm wanting to automate the creation of new databases via a sign up screen. I'm trying to write a stored procedure (that resides in a "control" database) that will create the new database, it's tables, functions, triggers, stored procedures, etc. Getting the syntax correct and dealing with all of the escaping of single quotes has become a nightmare.

Any one here running a multi-tenant app with multi databases? How are you handing the creation of these databases when a new user signs up?

In the mean time I have a script that SSMS created for me that runs fine. I just rather have this automated.

[peteconway]

multi-tenant app uses just one database or it's not multi-tenanted, or am I misunderstanding what you are saying?

Pete

[mikeallenbrown]

multi-tenant app uses just one database or it's not multi-tenanted, or am I misunderstanding what you are saying?

Pete

From Alpha: https://documentation.alphasoftware....%20Strings.xml

A multi-tenant application is an application that is shared by many different customers. Salesforce.com is a good example of a multi-tenant application. There are typically two different approaches to building a multi-tenant application. These are:

• All tenants share the same database, but each table in the database has a 'tenantId' field which identifies the tenant. Every query that is executed includes the tenantId in the WHERE clause. The tentantId is set when the customer logs into the application.
• Each tenant has its own database. -This is my approach

The second approach is much easier to code because you don't have worry about adding the 'tenantId' field into every query and insert statement. However, it is less scalable because you need to provision a new database for every tenant.

[peteconway]

Can't agree with that at all, I'd also say that would have been the view prior the 2018 DEVCON where I demonstrated a Mutl-tennant and Alpha the released a new MT Connection string to do the auto persistence of the TenantID. - Much easier without duplication.
Just sayin. Pete

[lgrupido]

In a multi-tenant app with multiple databases, any change to the database structure is going to have to be copied between multiple databases. You do NOT want to deal with that once enough different databases are created.

Go with the idea of a tenant ID, whether you use Alpha's connection string method or just remember to include it yourself in your selections. (It may not apply to EVERY table, for instance a phone type table would not need different selections for every client.)

Otherwise, if you insist on doing multiple databases, I would put the bulk of the create/copy code into a stored procedure on the master database. The wonderful thing about SQL is that it is ALL SQL. So all of the creation commands are also SQL and can be run in SQL.

If a customer wants some truly custom code that would not translate well to any other client, you can hide that functionality from the other clients by tenant ID or you could create a secondary database for them that holds just their custom tables.

[madtowng]

I would suggest that as long as we have strtran and your dbs are accessible under one connections, making the changes should be a minimal effort.
The code below demonstrates a simple script that creates 3 tables, then alters each table to add a column as well as primary key.
Knowing there is potential for many more than 3 tables, this could easily be adjusted to make tableList dynamc.
This script does assume mySQL or mariaDB as the backend. It also assumes a connection named mylab.

dim namedCN as c = "::name::mylab"
dim tableList as c
dim sqlCode as p
dim mtCN as sql::Connection
dim mtRS as sql::ResultSet
dim mtARGS as sql::Arguments
dim itsOpen as l
dim itWorked as l

tableList = <<%txt%
AALab.alphaa
AALab.alphab
AALab.alphac
%txt%

sqlCode.createTables = <<%txt%
create table :tbl(
firstname varchar(15),
lastname varchar(30)
);
%txt%
sqlCode.alterTables = <<%txt%
alter table <tablename> add column seq int not null auto_increment primary key first;
%txt%
debug(1)
itsOpen = mtCN.Open(namedCN)
for each tbl in tableList
sqlCode.use=strtran(sqlCode.createTables,":tbl",tbl.value)
itWorked = mtCN.Execute(sqlCode.use,mtARGS)

next 'tbl
debug(1) ' check tables
for each tbl in tableList
sqlCode.use = strtran(sqlCode.alterTables,"<tablename>",tbl.value)
itWorked = mtCN.Execute(sqlCode.use)
next 'tbl
mtCN.Close()

debug(1)

[Pat Bremkamp]

If you are determined to use multiple databases, then I suggest you create a "master database" with all the tables, triggers, stored procedures, referential integrity, etc. Then when a new person signs up, you use a stored procedure to duplicate the master, not create it table by table.. I've used stored procedures to build tables and they are fast and accurate, but and entire database will be very hard to update and maintain.

[nxlcomputers]

Hi Mike, our clients require we have a separate database for their information so we have an automation routine we can share if you need it. Big picture, we run a stored procedure to create the new empty database and then another stored procedure that restores a backup of a master database to the new database. The backup of the master also has some pre-populated data to act as a 'sandbox' project for the new clients to review and get use to the system. The whole process which restores 632 tables and 747 views runs in under 10 seconds and is initiated from a ux button push. We have it set up so we can enter a short name for the new clients database and the database is built using that name by passing in an argumet to the stored procedures. You probably will not need this but we also automatically create and ODBC connection between mariadb and sql and then automatically create a linked server connection between the two using the odbc conneciton just created.

[mikeallenbrown]

Hi Mike, our clients require we have a separate database for their information so we have an automation routine we can share if you need it. Big picture, we run a stored procedure to create the new empty database and then another stored procedure that restores a backup of a master database to the new database. The backup of the master also has some pre-populated data to act as a 'sandbox' project for the new clients to review and get use to the system. The whole process which restores 632 tables and 747 views runs in under 10 seconds and is initiated from a ux button push. We have it set up so we can enter a short name for the new clients database and the database is built using that name by passing in an argumet to the stored procedures. You probably will not need this but we also automatically create and ODBC connection between mariadb and sql and then automatically create a linked server connection between the two using the odbc conneciton just created.

Thanks, I appreciate that. I came up with a more complicated process that I think will work but I'd be interested in your solution. If you don't mind you can email me at: <personal info removed>

Thank you.

[WindForce]

Hi Mike
But in your scenario, doesn't every new tenant need a different copy of your alpha anywhere project? Otherwise assuming it's just a matter of different connection strings for different tenants, how do you point each one to the correct connection string?

Jaime

[Ted Giles]

I've worked extensively on Multi Tenant stuff, and would suggest that a separate Db for each client will end up driving you nuts.
If a user has specific requirements for an application - away from your mainstream offering - they really need a separate application just for them.
If the aim is to provide a universal app which replicates the functionality and feature upgrades for everyone to enjoy, you are much better off using GUID's - captured at login - to select and display the data for that tenant.
In your current scenario, you have to select the Db from a login or IP address or a similar process. Selecting data based on the login is no more difficult and as has been said, you don't need to update multiple Db's when you need another field or process.

I understand why you have done the development the way you have Mike, but in the long run, it is likely to be problematic and possibly unsustainable.
But that's just my opinion.
It may well work for you as you are deeply into it, but I would suggest a bit more research into MT Apps design.

[nxlcomputers]

We use the same workspace for all the clients but the publishing profile is unique and that's where the connection strings get updated. The negatives are that you have to publish changes to each IIS/Alpha site individually and like everyone is saying, you have to keep your databases in synch (structure). We use NaviCat to manipulate the databases and it has a 'structure synch' tool that we use as a backup method for monthly database compares. Our primary method is to route our database changes through our database admin and she uses scripts to push out the changes to each database simultaneously. We have a 'to-do' item this year to look for a tool to help with Alpha workspace production release automation. I remember a presentation at an Alpha DevCon a few years back that addressed this issue but for the life of me cannot remember the tool they used.

[mikeallenbrown]

Hi Mike
But in your scenario, doesn't every new tenant need a different copy of your alpha anywhere project? Otherwise assuming it's just a matter of different connection strings for different tenants, how do you point each one to the correct connection string?

Jaime

The application is the same for all tenants, however, unlike most apps each tenant gets their own database. Alpha's dynamic connection string method points the user to the correct database after login.

https://documentation.alphasoftware....%20Strings.xml

[mikeallenbrown]

I've worked extensively on Multi Tenant stuff, and would suggest that a separate Db for each client will end up driving you nuts.
If a user has specific requirements for an application - away from your mainstream offering - they really need a separate application just for them.
If the aim is to provide a universal app which replicates the functionality and feature upgrades for everyone to enjoy, you are much better off using GUID's - captured at login - to select and display the data for that tenant.
In your current scenario, you have to select the Db from a login or IP address or a similar process. Selecting data based on the login is no more difficult and as has been said, you don't need to update multiple Db's when you need another field or process.

I understand why you have done the development the way you have Mike, but in the long run, it is likely to be problematic and possibly unsustainable.
But that's just my opinion.
It may well work for you as you are deeply into it, but I would suggest a bit more research into MT Apps design.

Thanks Ted, I appreciate the advice. Alpha's dynamic connection string method handles everything for me. That part is working well. As far as keeping the databases in sync I plan on writing an automation routine, in Alpha and SQL, to handle that part.

[Ted Giles]

Good stuff Mike.
Can I ask you to humour me on the design.
There is no "Right" wayto design an app, so just in case you are trailblazing a new approach, can you share the rationale for your design?