mORMot Open Source

sms

Member

Registered: 2014-06-04

Posts: 22

SQLite3 DB Verses Firebird

Hi ,

I am planning to build a retail POS application from scratch which was using Firebird db. I am going to use mORMot for my server app. The old application was very slow in some ares where the table/query has too many records. Do you think it will be good to move to SQLite3 Db instead of Firebird ? Or SQLite3 is just for small purpose ?

I thought to go with mORMot's ORM once I decide the db :-)

Thanks
sms

martin.suer

Member

Registered: 2013-12-15

Posts: 76

Re: SQLite3 DB Verses Firebird

I am a long time Delphi and Firebird user and started lurking at mORMot 6 month ago. Your question has no simple single right answer.
I liked Firebird very much for its stability, easy administration and full fledged Enterprise features (transactions, mvcc, stored procedures and trigger) and it has been quite performant. If one is looking for an RDBMS I would always vote for firebird if it’s able to scale up to your (customers) need (compared to e.g. Oracle) and you’re looking for a cheap solution.

Using an ORM like mORMot however raises a lot of totally other questions. Suddenly the DB will be used more or less as a simple storage  engine and you won’t need most of the features that firebird delivers. You probably won’t make a lot of use of stored procedures and triggers anymore, instead you write your code in Delphi and do most stuff outside the db.

Also the data model you are dealing with will be slightly different. The hardest point to me has been changing my mind from Relations to Objects and DDD.

Imagine you build a relational database schema. Theoretically you go and normalize your data to 1) keep your model consistent and 2) have the ability to perform all kind of possible queries. Practically in a real world project exactly that will quickly lead to unperformant databases because you have to do too much joins to get your query results. As a consequence you make compromises with your normalization and do the math and think about which queries are the ones you want to be answered in your application often and performant and then carefully adjust the model of your tables according to your needs.

Using an ORM you use an opposite aproach. You build your Objects according to the needs of your Business Processes or Domain. Unfortunately, since these Objects in the end are stored in a relational database you most likely or often also have to tweak your Object Model a little because you want to have the values that you use in query selection predicates (known as your sql where clause) to be mapped to single columns (in most cases). Speaking in DDD Terms, your aggregate objects content is usually not fully exposed to SQL and if you want your database to perform the selection instead of your Delphi code, you need to take that into account while designing your aggregate objects.

So, mORMot gives you the tools to use everything you need but of course can’t magically solve all data design questions. You have to do that on your own and if you come from a relational world and Delphi RAD that’s going to take some time (at least that is my experience).
But as far as I can tell it’s worth it. You get much cleaner code, your code is more maintainable and it’s more performant (if you build your object model right ;-) )

Finally coming back to your question: If you use mORMot, you should start using SQLite. It’s built in. 0-administration. You won’t need any firebird specific things. Also SQLite has one big advantage and that is TEXT-colums have no significant maximum length whereas firebirds varchar field has. And the operations that are used by mORMot are reasonably faster with SQLite3. This is not saying SQLite3 is better than firebird. It’s just better in conjunction with mORMot (or any other orm). However mORMot allows you also to use firebird for some or all your data tables. So you can do that if you have a good reason for it and need some data stored in firebird instead of SQLite.

Last edited by martin.suer (2014-06-20 10:45:00)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SQLite3 DB Verses Firebird

@Martin
I'm quite impressed.
IMHO your remarks are all relevant, and match our own experiment with several projects, and highlight mORMot's design.

Data modeling is in fact the hardest part, and probably the most important part of software design.
As Linus stated:

I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships.
http://lwn.net/Articles/193245

SQLite3 is indeed the best engine to start with, with a project using mORMot, and built from scratch.
And, as you wrote, you could still access other databases, even legacy ones, just by tuning the table/column mapping with an external database.
BTW, PostgreSQL also features TEXT columns, which allows efficient text storage without explicit maximum column size (which is IMHO a deprecated inheritance in the latest SQL standard from computing prehistory, and do not make any sense today).

Thanks a lot for sharing!

@sms
SQlite3 is able to create huge databases, with lower disk and CPU use than alternatives.
For a POS, it sounds a perfect fit.
Just tune your table indexes, and set the ORM cache as appropriate.

Remember you can switch from SQLite3 into an external database later, if needed.
But today, I would perhaps favor PostgreSQL in comparison to FireBird, if an external professional database is needed (e.g. for extended data backup features).
The fact that PostgreSQL features variable unlimited length TEXT columns (just like SQLite3), may help working with RawUTF8 published properties in TSQLRecord, without any "index ##" specifier.

Do not forget to download and read the first part of the SAD 1.18 pdf, before starting any serious project with mORMot.
You would have not only to learn some new units and classes, but also let your software design evolve, and change your way of thinking.
mORMot gives its full power when switched from pure RAD approach to SOA + ORM, and RAD for the UI.

sms

Member

Registered: 2014-06-04

Posts: 22

Re: SQLite3 DB Verses Firebird

Thank you Martin and Arnaud. I am quite impressed by the detailed replies. Thanks a lot for that.

Last 5 years I was working with Firebird. I am quite used to it now. That's why I am bit confused whether I need to change DB engine.

I need 2 databases, one for main POS and another for online licensing. I think for online licensing I can use SQLite3 since it will be quite small db with less data and i can have a small designing without stored procedures.

But for POS I can think of either FB or PostgreSQL. But I never used PostgreSQL. Have to do little bit research on that before I start with it.
With FB, I am quite comfortable.

I can use ORM concept for my Licensing app and use normal way of programming using Delphi for POS.

If I didn't use ORM, I can still use THttpAPIServer just to make a fully independent server, right ? Using the normal Delphi application?

Actually I designed both DBs in firebird already and thought of Delphi XE6 and Datasnap for my server. When I did some research
on the performance of Datasnap I came to know about mORMot :-). So I am completely confused. Let me do some more study and I'll come back with more questions .

Thanks again
sms