SQLite3 and JSONARRAY

carlosig03 · 2022-03-14 16:22:11

Hello Everyone!

I'm starting with mormot.

Since now is amazing.

I created a server responding 'hello world', using http.sys and interface, put it under stress with JMeter and got 0 errors and amazing performace.

The next step:
- I have a hudge firbird database
- 200 simultaneous connections
- My strategy is create a sqlite database (DONE), create the only table the clients need (DONE), pump all data (DONE), periodically do updates (DONE) and use this bd and not the firebird produciton database.
I now mormot use internally sqlite3, so I whant to use this new sqlite database locally with de new mormot server.

How can I use mormot to execute sql for each http request (must be threadsafe) and deliver JSONARRAY for clients view the results on dbgrid?
To create my basic server I used the sample "C:\JoseCarlosCosta\instalacoes\Delphi\Componentes\JSON\mORMot\SQLite3\Samples\ThirdPartyDemos\George\REST-tester".

Does any one can help?

Thanks

Best Regards

ab · 2022-03-15 07:42:25

Thanks for the kind words, and interest!

If you use our SQlite3 engine, its access is protected by a mutex, so it is thread safe by design.

Let your interface method return a RawJson parameter, and fill it from IRestOrm.RetrieveListJson or directly from TSqlDataBase.ExecuteJson.

But instead of using SQL from the client side, which may be unsafe, I would rather define several dedicated methods, depending on the use case.

For a new project, I would consider using mORMot 2 instead of mORMot 1, because it will be better maintained and optimized in the long term.

carlosig03 · 2022-03-15 11:24:33

Thanks ab for your attention.

The challenge is to convert Datasnap to mORMot. With Datasnap it was quick to develop but has many performance problems and stability issues.

"But instead of using SQL from the client side, which may be unsafe, I would rather define several dedicated methods, depending on the use case."

I agree, I want to execute all sql on the server side from local sqlite bd (or load the bd into memory ram).

To my clients, I just want to deliver JsonArray (clients are already rest clients, but I can change for mORMot).

So I have a local SQLite database (maybe load it into memory RAM, need advice about best practice and how to do it), then I will have an interface service with 3 functions with 2 parameters each (start and end date),
this functions will execute sql on that database and retrieve results to clients as jsonarray/binary/other (what is the faster?).

Thanks,

Best Regards.

carlosig03 · 2022-03-15 11:32:33

This server only returns data. Do not insert/update/delete. Just Select.

igors233 · 2022-03-15 13:04:30

Are your clients Delphi/FPC or under your control? If you want to make them mORMot clients then a look at 04-InterfacedBasedServices demo as a starting point.
If your clients are done in other languages then probably 03-MethodBasedServices demo could be a good start.

ab · 2022-03-15 14:27:38

There are a lot of advantages to put the SQL on the server side.

1. More secure, against DoS attacks from malformated SQL: clients can't inject huge queries which may put the server or bandwidth down.
2. More secure, against retrieving information which the user doesn't have right to access (e.g. the data of another user).
3. More separation, since you can change the SQL on the server side with no update of the client needed (you could make your server compatible with old clients if needed).
4. Tune the interfaces and methods to each client.
5. Better unit testing, because you can call, therefore validate the logic on the server side.
6. Better maintainability, because you don't mix UI and SQL/logic.

carlosig03 · 2022-03-15 14:52:37

igors233 wrote:

Are your clients Delphi/FPC or under your control? If you want to make them mORMot clients, then a look at 04-InterfacedBasedServices demo as a starting point.
If your clients are done in other languages, then probably 03-MethodBasedServices demo could be a good start.

Hello igors233,

Thank you very much for your attention.

I'm a Delphi developer. My clients are developed in Delphi also by me, so if needed I can also adapt to mormot.

I started with the sample "..mORMot\SQLite3\Samples\ThirdPartyDemos\George\REST-tester", this is an interface-based-services, right?

Since my firebird database is hudge, I created an local sqlite database with the only table needed, I pumped all data from firebird to sqlite and periodically I update that local bd.

Now, in mormot I don't now wath to do :

Put my local db in memory RAM (how?), create class ORM for my table? My interface must have dedicated connection to my bd and run sql to deliver json to client? or use only one connection for bd?
There will be 300+ simultaneous connections, this will be thread safe? Concurrency safe?

The functions on service must receive parameters (start and end date), how to do?

I want the best performance, so i need to do the right way, but since i'm starting and there are multiple choices, i need your help.

Thanks,

Best regards.

carlosig03 · 2022-03-15 14:56:54

ab wrote:

There are a lot of advantages to put the SQL on the server side.
1. More secure, against DoS attacks from malformated SQL: clients can't inject huge queries which may put the server or bandwidth down.
2. More secure, against retrieving information which the user doesn't have right to access (e.g. the data of another user).
3. More separation, since you can change the SQL on the server side with no update of the client needed (you could make your server compatible with old clients if needed).
4. Tune the interfaces and methods to each client.
5. Better unit testing, because you can call, therefore validate the logic on the server side.
6. Better maintainability, because you don't mix UI and SQL/logic.

Thanks ab for your answer.

I totally agree with you

but how to do it on the right way, i'm lost because there are multiple choices.

I already made some decisions :

- I will use Interfaced Based Services
- I created an local sqlite bd
- I will run sql on the server

How to do it?

ab · 2022-03-15 16:25:56

Use a single TSqlDatabase for the SQLite3 DB. This single instance will be thread-safe, and protect all requests using a critical section.
Call TSqlDataBase.ExecuteJson with the proper SELECT SQL statement, and return a RawJson parameter on the interface method.

Keep the data on disk, because it is not needed to put everything in ram: it will use efficient memory cache if needed.

For the best performance (and security), if you can, cut your data into several SQlite3 DB files, e.g. one per client account.
Then maintain a pool of TSqlDatabase instances, one per file.

Look at the mORMot 1 documentation for how to define your interfaces.
It is (almost) the same as with mORMot 2.
Take a look at other resources, e.g. https://tamingthemormot.wordpress.com/2 … databases/

carlosig03 · 2022-03-16 09:37:41

Thanks ab!!!

You mean one connection to sqlite for all client requests, right?

Best Regards

Last edited by carlosig03 (2022-03-16 09:38:29)

ab · 2022-03-16 10:05:06

Yes

carlosig03 · 2022-03-16 10:18:31

ab wrote:

Yes

ok,
So I must create that connection at RestServer level, when I create it, right?

ab · 2022-03-16 10:25:37

yes and reuse it everywhere

carlosig03 · 2022-03-16 10:32:35

ab wrote:

yes and reuse it everywhere

Ab,

is DONE ;-).

Is it right to use RestDebugger to test my Server Methods results?

The results I receive are not the expected.

O I'm doing something wrong, or I cannot use it and I must create an tiny client to test with my interfaces?

{
"result": [
[
{
"DATA": "2019-10-11",
"HORA": "15:24:57.000"
},
{
"DATA": "2019-10-11",
"HORA": "15:27:29.000"
},
{
"DATA": "2019-10-11",
"HORA": "15:29:34.000"
}
]
],
"id": 10916
}

Why is that "result" and the "id"?

ab · 2022-03-16 10:39:32

This is as expected.

Please read the mORMot 1 documentation about interface based services. It also applies to mORMot 1 in this case.
https://synopse.info/files/html/Synopse … DE_TITL_63
All this is properly documented.

carlosig03 · 2022-03-16 11:25:44

ab wrote:

This is as expected.
Please read the mORMot 1 documentation about interface based services. It also applies to mORMot 1 in this case.
https://synopse.info/files/html/Synopse … DE_TITL_63
All this is properly documented.

Ok Ab.

I'm reading the manual "Synopse mORMot Framework SAD 1.18" and using the examples of mormot 1.

The problem is that I must give a solution in a very short period of time.

Thanks again for your help.

Best Regards

carlosig03 · 2022-03-17 12:01:59

ab wrote:

This is as expected.
Please read the mORMot 1 documentation about interface based services. It also applies to mORMot 1 in this case.
https://synopse.info/files/html/Synopse … DE_TITL_63
All this is properly documented.

Hello Ab!

Good news!

It's working, tested with 50 threads/100k requests, got zero errors, even with the server under stress, tested my client app and tested via browser URI with amazing performance.

I was testing with RestDebugger witch gives full response format.

I changed my client to use the interface (with parameters now ;-)) and WORKS!! The response is the expected.

On my client, I'm loading RawJson into memtable with "LoadFromJSON()" from DataSet.Serialize.

Mormot have deserialization with the RawJson I receive on my client, produced by "ExecuteJSON"?

Best regards.

ab · 2022-03-17 16:24:53

Yes, try to use unit mormot.ui.rad.json.pas and its JsonToDataSet() function.

carlosig03 · 2022-03-21 15:31:24

ab wrote:

Yes, try to use unit mormot.ui.rad.json.pas and its JsonToDataSet() function.

Hello Ab!

My client with direct access (ibx) to production database is faster than my mORMot server with local sqlite db.

Do you have any tips for performance ?

I'm searching example about compression SynLZ, my server send RawJSON 70k records with 30 fields, maybe this can optimize?
Can't find sample about how use SynLZ, only find a configuration on client side :
TSQLHttpClientWinHTTP(fClient).Compression := [hcSynLZ, hcDeflate];

And on the server side?

Do you have any other tips?

Best regards.

Last edited by carlosig03 (2022-03-22 09:12:40)

ab · 2022-03-21 20:51:33

1. Please follow the forum rules and don't post such code directly in forum messages.

2. You should set DB.Synchronous := smOff; and DB.LockingMode := lmExclusive;
as stated by https://synopse.info/files/html/Synopse … ml#TITL_60

3. Create your fDatabase instance before the HTTP server for safety.

4. From my tests, the sockets client class is faster than the WinHttp version.

5. I don't get what you mean by "faster".
Faster reads? writes? single row? multiple row? ... Reading 70KB of JSON should be very fast. And the SynLZ compression is enabled by default on the server and the client IIRC.
From our tests, accessing a SQLite3 database via HTTP and JSON is usually (much) faster than direct DB access.
Check https://synopse.info/files/html/Synopse … ml#TITL_59

6. You didn't show how you actually access the fDatabase instance, i.e. how the IRestMethods interface is implemented.

7. You didn't put any security to your server, which is not a good idea.

carlosig03 · 2022-03-22 09:48:39

ab wrote:

1. Please follow the forum rules and don't post such code directly in forum messages.
2. You should set DB.Synchronous := smOff; and DB.LockingMode := lmExclusive;
as stated by https://synopse.info/files/html/Synopse … ml#TITL_60
3. Create your fDatabase instance before the HTTP server for safety.
4. From my tests, the sockets client class is faster than the WinHttp version.
5. I don't get what you mean by "faster".
Faster reads? writes? single row? multiple row? ... Reading 70KB of JSON should be very fast. And the SynLZ compression is enabled by default on the server and the client IIRC.
From our tests, accessing a SQLite3 database via HTTP and JSON is usually (much) faster than direct DB access.
Check https://synopse.info/files/html/Synopse … ml#TITL_59
6. You didn't show how you actually access the fDatabase instance, i.e. how the IRestMethods interface is implemented.
7. You didn't put any security to your server, which is not a good idea.

Hello Ab!

1. Sorry, it won't happen again

2. Done

3. Done

4. Do you mean to use another protocol (I'm using :RestServerSettings.Protocol := HTTPsys;)

5. Yes, reads, my clients only receive JSONRaw (most clients just view data from current day, but some analyze few months and in this case I copied the result json into file to get the size and got 33Mb, I will talk with them,
maybe they don't need all data but just summary)

6. Do you mean the code (I show just part because of forum rules)
function TRestMethods.GetData(DateStart, DateEnd : RawUTF8): RawJSON;
begin
Result := RestServer.fDatabase.ExecuteJSON('..',True);
end;

7. For now, I didn't put because it is for use only on our intranet, but it is also a future requirement, what the best way to do it ?

Ab, thanks for all your help and the time helping me.

Best regards.

ab · 2022-03-22 10:04:36

4. Use TSQLHttpClientWinSock instead of TSQLHttpClientWinHTTP for the client.

5. 33MB seems way too big for a regular query: they should use proper paging, or refine the fields used (e.g. if there are some BLOBs, only retrieve then when needed).
For blobs, don't use RawJson (and base-64 encoding), but an interface method returning a single RawbyteString parameter would use binary transmission - see https://synopse.info/files/html/Synopse … l#TITL_197

6. This sounds just fine and should be very fast.

7. The easiest is to use mORMot built-in security, even with fixed user/password pairs hardcoded in code.
At least, it would avoid direct access with no forensic.
And of course, encryption may be good: either put a HTTPS proxy (like nginx), or just switch to our binary Websockets prototol, which allows simple encryption.

carlosig03 · 2022-03-28 08:47:26

ab wrote:

4. Use TSQLHttpClientWinSock instead of TSQLHttpClientWinHTTP for the client.
5. 33MB seems way too big for a regular query: they should use proper paging, or refine the fields used (e.g. if there are some BLOBs, only retrieve then when needed).
For blobs, don't use RawJson (and base-64 encoding), but an interface method returning a single RawbyteString parameter would use binary transmission - see https://synopse.info/files/html/Synopse … l#TITL_197
6. This sounds just fine and should be very fast.
7. The easiest is to use mORMot built-in security, even with fixed user/password pairs hardcoded in code.
At least, it would avoid direct access with no forensic.
And of course, encryption may be good: either put a HTTPS proxy (like nginx), or just switch to our binary Websockets prototol, which allows simple encryption.

Hello Ab,

I've been working on your suggestions and the feedback is very good:

4. Done and the difference is big

5. Done (only show the last n records, very fast)

6. Done

Now I have another problem.

I only have 2 connections to the sqlite local database:

a - external app to pump new records

b - New mORMot server

After 24h working, mormot server don't show new data, closing and opening de mormot server it works, Does he need some "refresh" periodically (restart database connection and mormot server)

Best regards.

carlosig03 · 2022-03-28 08:54:22

ab wrote:

4. Use TSQLHttpClientWinSock instead of TSQLHttpClientWinHTTP for the client.
5. 33MB seems way too big for a regular query: they should use proper paging, or refine the fields used (e.g. if there are some BLOBs, only retrieve then when needed).
For blobs, don't use RawJson (and base-64 encoding), but an interface method returning a single RawbyteString parameter would use binary transmission - see https://synopse.info/files/html/Synopse … l#TITL_197
6. This sounds just fine and should be very fast.
7. The easiest is to use mORMot built-in security, even with fixed user/password pairs hardcoded in code.
At least, it would avoid direct access with no forensic.
And of course, encryption may be good: either put a HTTPS proxy (like nginx), or just switch to our binary Websockets prototol, which allows simple encryption.

Another question,

I was using "UseCache=True" on server side (mormot) wich gives amazing performance, however when I pump new data, the server don't show it becuase it's using cache.

Is it possible to update the cache? Tried to reset cache, but didn't work.

I have to disable it and lost some performance.

ab · 2022-03-28 14:07:22

Resetting the cache does work.

You did not reset the proper cache, I suspect.
There are several caches in mORMot:
https://synopse.info/files/html/Synopse … ml#TITL_38

carlosig03 · 2022-03-29 15:31:36

ab wrote:

Resetting the cache does work.
You did not reset the proper cache, I suspect.
There are several caches in mORMot:
https://synopse.info/files/html/Synopse … ml#TITL_38

Hello Ab,

I'm using fDatabase.CacheFlush, however now I disabled it "fDatabase.UseCache := False;".

After some hours working, my clients stop receiving new data, I need to close and reopen them and then new data appears.

The server still working with no problem.

Any suggestion ?

Best regards.

mORMot Open Source

#1 2022-03-14 16:22:11

SQLite3 and JSONARRAY

#2 2022-03-15 07:42:25

Re: SQLite3 and JSONARRAY

#3 2022-03-15 11:24:33

Re: SQLite3 and JSONARRAY

#4 2022-03-15 11:32:33

Re: SQLite3 and JSONARRAY

#5 2022-03-15 13:04:30

Re: SQLite3 and JSONARRAY

#6 2022-03-15 14:27:38

Re: SQLite3 and JSONARRAY

#7 2022-03-15 14:52:37

Re: SQLite3 and JSONARRAY

#8 2022-03-15 14:56:54

Re: SQLite3 and JSONARRAY

#9 2022-03-15 16:25:56

Re: SQLite3 and JSONARRAY

#10 2022-03-16 09:37:41

Re: SQLite3 and JSONARRAY

#11 2022-03-16 10:05:06

Re: SQLite3 and JSONARRAY

#12 2022-03-16 10:18:31

Re: SQLite3 and JSONARRAY

#13 2022-03-16 10:25:37

Re: SQLite3 and JSONARRAY

#14 2022-03-16 10:32:35

Re: SQLite3 and JSONARRAY

#15 2022-03-16 10:39:32

Re: SQLite3 and JSONARRAY

#16 2022-03-16 11:25:44

Re: SQLite3 and JSONARRAY

#17 2022-03-17 12:01:59

Re: SQLite3 and JSONARRAY

#18 2022-03-17 16:24:53

Re: SQLite3 and JSONARRAY

#19 2022-03-21 15:31:24

Re: SQLite3 and JSONARRAY

#20 2022-03-21 20:51:33

Re: SQLite3 and JSONARRAY

#21 2022-03-22 09:48:39

Re: SQLite3 and JSONARRAY

#22 2022-03-22 10:04:36

Re: SQLite3 and JSONARRAY

#23 2022-03-28 08:47:26

Re: SQLite3 and JSONARRAY

#24 2022-03-28 08:54:22

Re: SQLite3 and JSONARRAY

#25 2022-03-28 14:07:22

Re: SQLite3 and JSONARRAY

#26 2022-03-29 15:31:36

Re: SQLite3 and JSONARRAY

Board footer