mORMot Open Source

Mo0211

Member

Registered: 2018-12-30

Posts: 37

ORM-Wrapper for mysql/maria/sqlite in one

Dear all,

I'm currently working on one topic i don't find any solution.
I have a working solution connecting to a sqlite3 file with TSQLRestClientDB and also another one for mysql/mariadb with a zeos connection.
I thought TSQLRestClientDB is a generic model, so i only need to change the underlying provider (mysql/mariadb which i currently run with zeos) but I don't get this running.
It seems that this TSQLRestClientDB only works for sqlite and i still have to work with "connections" and pure sql-code for zeos.

Is my research correct or is this in general possible to access all three types only on connection level and work afterwards with the same code?
Which datatype do I need to use which suits for all of them?

Thanks for a quick guidance here.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,025

Website

Re: ORM-Wrapper for mysql/maria/sqlite in one

TSQLRestClientDB - or more appropriately TRestClientDB - owns its TRestServerDB instance.
To define external tables, just access the TRestServerDB instance.

And in most cases, you don't need TRestClientDB.
Just use a TRestServerDB instance, and its Orm: IRestOrm property.

Mo0211

Member

Registered: 2018-12-30

Posts: 37

Re: ORM-Wrapper for mysql/maria/sqlite in one

hello ab,

do you some working examples for them?
Where can i look in the code to find the best way?
I can't find the right way to connect a TRestServer to zeos db properties.

Thank you for y quick hint!

flydev

Member

From: France

Registered: 2020-11-27

Posts: 90

Website

Re: ORM-Wrapper for mysql/maria/sqlite in one

I dont really get what you are asking specifically, but did you took a look at /ex/extdb-bench or mongodb & techempower samples? also read test.orm.extdb.pas from test folder.

Basically to get a TRestServerDB virtually working with MariaDB, there is a generic connection definition:

  // 1. Define your external SQL connection
  Props := TSQLDBZEOSConnectionProperties.Create(
    FormatUtf8('zdbc:mysql://127.0.0.1:%/%?username=%;password=%',
      DB_PORT, DB_SOURCE, DB_USERNAME, DB_PASSWORD]),
      'aSqlite3.db', DB_USERNAME, DB_PASSWORD);
  Props.ThreadSafeConnection.Connect;
  // 2. Define your model and ORM mapping
  Model := TOrmModel.Create([TOrmSomething]);
  OrmMapExternal(Model, [TOrmSomething], Props);
  // 3. Create the REST server, mapped to the external DB
  Server := TRestServerDB.Create(Model);
  Server.Server.CreateMissingTables;

Last edited by flydev (2025-05-29 12:52:06)

Mo0211

Member

Registered: 2018-12-30

Posts: 37

Re: ORM-Wrapper for mysql/maria/sqlite in one

Hi Flydev,

thank you for your response!
That helped alot!
But maybe my main problem is i don't get the difference between TRestClient and TRestServer.

I have as said 3 use cases:

i want to have 3 possibly underlying connections and i want to handle the code after the connection is made in the same way to reduce code complexity.

To give you an example:

ORMDB:= TSQLRestClientDB.Create(aORMSQLModel, nil, ':memory:', TSQLRestServerDB, False, StringToUTF8(aPassword))

This is my current connection based on sqlite.
After this connection, i can easy access the database and make operations (retrive, CreateAndFillPrepare, delete and other things).

fSQLRec := TSQLRecord.CreateAndFillPrepare(ORMDB.Client, 'name = ?', [name], 'ID')
This is how i access the data based on the TSQLRestClientDB.

Now my goal is only to change the underlying connection from ORMDB to mysql e.g. and are able to use orm-functions to fetch and delete data in the same way with no need of changing code.

So, my goal is to connect to different databases and use the same orm-related access for all database types only with changing the connection.
Which datatype should i use?

I hope my question is more clear now!

flydev

Member

From: France

Registered: 2020-11-27

Posts: 90

Website

Re: ORM-Wrapper for mysql/maria/sqlite in one

TRestClientDB use an hidden TRestServerDB; you could map to an external db for example this should work:

Props := TSQLDBZEOSConnectionProperties.Create(...);
Props.ThreadSafeConnection.Connect;
OrmMapExternal(aORMSQLModel, [TYourOrmMapped], Props);
ORMDB:= TRestClientDB.Create(aORMSQLModel, nil, ':memory:', TRestServerDB, False, StringToUtf8(aPassword));
TRestServerDB(ORMDB.Server).Server.CreateMissingTables(0, [itoNoAutoCreateUsers]);

flydev

Member

From: France

Registered: 2020-11-27

Posts: 90

Website

Re: ORM-Wrapper for mysql/maria/sqlite in one

I published a gist with a working example (mormot2), assuming a TAuthUser is on external db, feel free to change the table name with one of your..

/flydev-fr/84f808d5ff184df6036e54ac03dd43ab

Last edited by flydev (2025-05-29 13:12:47)

Mo0211

Member

Registered: 2018-12-30

Posts: 37

Re: ORM-Wrapper for mysql/maria/sqlite in one

thank you!
this was really helpful!
code is already running for firsts tests

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,025

Website

Re: ORM-Wrapper for mysql/maria/sqlite in one

As I wrote above, do not mess with the TRestClientDB.
I am quite sure you don't need its overhead.

Just use a TRestServerDB, then use its Orm: IRest or Server: IRestServer properties in your code logic.
Don't even publish TRestServerDB to your business logic. Use it for initialization and DB connection, then rely on the abstraction of the IRest interface.