mORMot Open Source

okoba

Member

Registered: 2019-09-29

Posts: 88

Routing requests to multiple database

Hello,

I have multiple databases for each customer (need to be independent databases for legal reasons), with the same structure or model. And I like to use interface methods to access them. The API should have independent methods called by user, so no direct access to database tables.
What is the best way to rout incoming requests to multiple databases?

To be clear, what is the best way to rout like:
User1 calls /api/logger/add?data=XYZ&key=user1_key
Server checks user1_key, finds that the data need to be added to user1.db and like to insert to that file.

Perhaps, in the future, the Server need to call another server (User1_Server) to do the actual insert. I think this part can be done by the master/slave architecture described in the documentation, but I appreciate any needed info.

JD

Member

Registered: 2015-08-20

Posts: 98

Re: Routing requests to multiple database

I have a similar Use Case and I manage it using PostgresSQL and its schemas. Each schema houses a different database belonging to different companies.

The REST call contains the name of the schema and that way a different select/insert/delete can be accessed depending on the schema in the REST call.

For example, I use this REST call to find out if my mORMot server is up and running:

http://localhost:8088/service/myapi/sayhello?schema=company_xyz

This will call the SayHello resource in the Company_XYZ schema.

I hope this helps a little.

Cheers,

JD

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 13,763

Website

Re: Routing requests to multiple database

I would not put the data scope as parameter. It is unsafe, and verbose.

What I have done is to have the database be derivated from the User, e.g. from a simple field in the User table, or - even better - by using a bits map to the User ID (the highest bits of the UserID are in fact a DataID or AccountID, which is used to pickup the right database).

Or I could store the DataID into a JWT, if I use a JWT for authentication.

About multiple databases, I usually use SQLite3, because they are easy to manage (even move the data), efficient, and small.

okoba

Member

Registered: 2019-09-29

Posts: 88

Re: Routing requests to multiple database

@JD thank you, although I need to have independent file for databases.
@ab thank for the notes. I want to do it with SQLite if possible. Can you elaborate on how can I route the class to different DBs while using interface based?
I was thinking about a virtual table but maybe it is over engineering? I guessed maybe there is something better in mORMot.
PS, I’m using mORMot 2 for this new project.

okoba

Member

Registered: 2019-09-29

Posts: 88

Re: Routing requests to multiple database

@ab can you help on this?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 13,763

Website

Re: Routing requests to multiple database

Just create a TRestServerDB per client (or group of clients). For mORMot 2, you could use TRestOrmServerDB instances.
For efficiency, cache them in a list, with a timeout, so that you can reuse one instance from a previous call, without opening it.
Then let your interface based service use this cache list and retrieve the proper TRestServerDB/TRestOrmServerDB instance when needed.

I will add such a cache in mORMot 2, if you prefer.

okoba

Member

Registered: 2019-09-29

Posts: 88

Re: Routing requests to multiple database

Thank you very much. I need to test your suggestion and will let you know if the cache is needed.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 13,763

Website

Re: Routing requests to multiple database

Opening a SQLite3 database is fast, a lot of applications (e.g. Fossil) do this, with no real penalty.

But to prepare the future, and leverage your server, a simple cache of opened SQlite3 instances is a good idea - even if not mandatory at first.
You can just make a method which returns the instance, creating it every time, and enabling cache later on if needed for your project... A naive cache of "last used instance" may be enough for most use cases, in practice.