Servoy Forum

Hello Servoyians!

For the following situation I like to know your input, ideas and how to's:

Most of you probably have a dedicated customer database (with only the data from 1 customer).
Then in other cases there are databases that contain data of multiple customers (saas).

Suppose you need to migrate data from the dedicated customer database to the saas database.. how would you do that?
(Please mention your tools, approach, etc. Also note that you cannot use the same primary keys when migrating your data into the saas database)

Looking forward to whatever you come up with

Hi Karel,

why using 1 database?
you can have multiple database connections and do a switchServer at login.

mboegem wrote:Hi Karel,

why using 1 database?
you can have multiple database connections and do a switchServer at login.

Eeey Marc! These are the specs and that's the challenge we have to deal with

What PK type are you using in these databases?

Hi Karel,

why using 1 database?
you can have multiple database connections and do a switchServer at login.

Answer : Multi-tennant with shared tables...

Do you want this in a running SaaS server?
Suppose the SaaS has two databaseservers connected: user_data which contains the SaaS-database containing data from different customers and user_data_source which contains the data of the customer to be migrated to the SaaS-database.
Server user_data_source will only be accessed using rawSQL, so we can restore a complete database int it without having to flush anything or run update db sequences. Server user_data will be accessed using Servoy foundsets.
For every table and for every record in user_data_source: Keep creating (and deleting if not succesful) new records in user_data until you find an id that is also available in user_data_source. In user_data_source change id to this newly found id, not only in the record itself but also in all referenced tables where this id is used as a foreign key.
After this we have a user_data with a lot of empty placeholder records and a user_data_source that can be bulk copied to user_data using postgresql COPY FROM and COPY TO. Just before the COPY TO delete all the placeholder records.
Most difficult part is "not only in the table itself but also in all referenced tables".

karel wrote:(Please mention your tools, approach, etc. Also note that you cannot use the same primary keys when migrating your data into the saas database)

Why can't you use the same primary keys?

We converted our DB from a single "customer" to a SaaS model rather easily by adding a "tenant_id" column to all our tables and putting addTableFilterParam in our solution open method. Since you are starting out from a single customer DB, all you would need to do is add the column and do a bulk update on all the tables setting the new tenant_id column to the same number, like tenant_id = 1.

Code: Select all

databaseManager.addTableFilterParam('cruz', null, 'id_tenant', '=', globals.core_g_tenant_id, 'tenant_filter');


The only right way todo this is to convert your solution to UUID PK's and FK's,

than you can migrate easily from a dedicated customer to a SAAS environment, without conflicting PK's (sequences)

you than also need to take care that every foundset and relation, will have a default sort!
because now Servoy defaults sorts on the PK, (so in practise this is the default creation-order)
with UUID, everything get's mixed up because of the sort on UUID. So you need to sort than on creationdate for example..

Harjo wrote:The only right way todo this is to convert your solution to UUID PK's and FK's

True, but what is the impact on performance? A normal id is an integer field in the database, a uuid id is a text field. And we have to generate new id's ourselves when creating a new record (admit it, small problem). On the other hand, we never have to run update db sequences again...

m.vanklink wrote:

Harjo wrote:The only right way todo this is to convert your solution to UUID PK's and FK's

True, but what is the impact on performance? A normal id is an integer field in the database, a uuid id is a text field. And we have to generate new id's ourselves when creating a new record (admit it, small problem). On the other hand, we never have to run update db sequences again...

Performance: we know several companies, running it this way, and there the performance is minimal, maybe others can collaborate on that or have exact figures
generate id's: Servoy has it's own UUD generator! so you don't have to think about it.

Nice, but still have some caveats. How could we get our current tables transformed to tables with a uuid field as primary key? And all the relations we have defined? And how do we get this deployed to our current customers?

Doing great Servoyians! Besides the fact that a couple of very interresting approaches are posted here, I'm definetly looking forward for more idea's, tools and suggestions!
And... who knows.. at the end of this year.. we could demo and share some community stuff live.. (but keep this a secret!)

karel wrote:And... who knows.. at the end of this year.. we could demo and share some community stuff live.. (but keep this a secret!)