Advice on starting to work with databases

[Yaron 53]

I am a total novice to databases, I would appreciate any pointers you could give.

 

I am developing a back-end server for an app/game that requires storing user accounts with a few details about each user:

1. Name

2. Reputation

3. Score

4. Connection to a list of content (images+metadata) submitted by the user

 

And since the users are submitting content (images), I need to store this content (or path to the content) with some metadata:
1. Likes

2. Effect flags

 

The database will be hosted on a windows server 2008 machine I'm administrating using remote desktop.

It will run on the same hardware as the REST server I'm currently writing.

 

What is the quickest/cheapest route to learn how bring up a database so I can quickly and reliably store and manipulate the data from within Delphi (v10.2 Tokyo)?

 

[Markus Kinzler 174]

Do you want advise in choosing a DBMS or are your question about database design?

[Yaron 53]

To begin with, which database software should I install that would be the easiest (to install on windows) and connect to with Delphi.

 

I previously installed MySQL for an unrelated project and didn't write any code for it, but I'm not sure if MySQL is overkill for my needs.

Edited November 14, 2018 by Yaron

[Markus Kinzler 174]

No overkill but MySQL isn't only "free" for OS projects. MSSQL express or FireBird seem to be the better solutions in my opinion.

[Arnaud Bouchez 407]

As backend, consider SQlite3 as a local database on the server side. It is perfect for such service storage.
There are several SQLite3 libraries for Delphi, from FireDAC to Zeos/ZDBC or mORMot.

 

In your code, if you don't know anything about SQL, consider using an ORM like Aurelius or mORMot to work with objects, and not SQL.

[Yaron 53]

I'm not sure I understand your answer, the project I intend on writing is not open-source.

 

After reading a bit about both, I'm leaning toward FireBird.

 

Other than the FireBird site itself, is there any related resources you would recommend I review?

[Markus Kinzler 174]

http://ibphoenix.com/

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Connect_to_Firebird_(FireDAC)

 

Besides the dbms specific syntax (shouldn't be too much) there're basics about rdbms might be good to know about.

 

 

[Yaron 53]

@Arnaud Bouchez I've been looking at the mORMot samples for SQLite3:

https://synopse.info/fossil/dir?ci=deb75010c0736e31&name=SQLite3/Samples

 

Do you think I should investigate the first two samples?

[Hans J. Ellingsgaard 21]

Don't use SQLite in a multiuser enviroment, go for a real RDBMS like Firebird, Interbase etc.. 

[Rollo62 536]

9 hours ago, Hans J. Ellingsgaard said:

Don't use SQLite in a multiuser enviroment, go for a real RDBMS like Firebird, Interbase etc.. 

Exactly,I would think the same way.

But since Arnauld is recommending mORMOT, does this help to allow Multi User ?

Edited November 15, 2018 by Rollo62

[Markus Kinzler 174]

SQLite is great for single user or on mobile devices. But I prefer FireBird, because it's more powerful an scalable (embedded for single user; Server for multiuser; database file is interchangable).

 

Is this the project you talking about? Look at the MARS samples. You will also find examples for database access.

[Edwin Yip 154]

9 hours ago, Hans J. Ellingsgaard said:

Don't use SQLite in a multiuser enviroment, go for a real RDBMS like Firebird, Interbase etc.. 

Unless you use a framework like mORMot which turns SQLite into a real multi-user DBMS (I meant real, although it's achieved with threads and locking).

 

Advises for using an ORM - only use ORM (Object-Relation Mapping) for the simple CRUD part, but don't map your entire data model into a ORM. Doing the latter is the root cause of all complains about ORM from all the Java, C# and Delphi programmers.

 

In this regard let me tell you why I LOVE mORMot - it allows you to accomplish simple data operations such reading/saving/updating/deleting a piece of cake, while it does NOT limit you using any SQL features.

 

It's all about balance between a full-fledged ORM and using raw SQL, mORMot provides the best of both world.

 

There is no over-abstraction nor over-engineering in mORMot, but it provides a HUGH foundation for your application - ORM, database, http(web ) server and http client Rest, remote function call, encryption, crossplatform-ready code base (think FPC and Linux), and so on, and so on, wow, unbelievable, all my new programs are relying on it, including an archiving software and a POS (point of sales) software.

 

 

[Markus Kinzler 174]

Quote

turns SQLite into a real multi-user DBMS 

Then multible users can access the db at the same time. But I wouldn't call this real muti user.

Quote

Advises for using an ORM - only use ORM (Object-Relation Mapping) for the simple CRUD part, but don't map your entire data model into a ORM. Doing the latter is the root cause of all complains about ORM from all the Java, C# and Delphi programmers.

 

Especially when using a "simulated" multi user system. A DBMS server offers more than just the possibillity to access a db simultaneously. The overhead for the ORM depends on the ORM also.

[Lars Fosdal 1791]

Whichever DB you decide to use - don't expose the DB as remote storage for to your game clients, but use a REST interface or similar instead.

This isolates the game client from the storage technicalities, and you can change the databases without having to change the game client.

It also reduces the risk of someone meddling with the DB - at least as long as you handle the REST data properly and ward off SQL injection attempts.

[Markus Kinzler 174]

Quote

Whichever DB you decide to use - don't expose the DB as remote storage for to your game clients, but use a REST interface or similar instead.

He does this already. But he uses a Textfile for storage.

[Yaron 53]

@Markus Kinzler The project your linked to is my first try at writing a REST server, since the data-set it uses is so small, for simplicity I just used a flat DB file.

 

My upcoming project is more of a small scale social network, so there will be:

1. Accounts

2. Content upload (text/images) linked to the accounts.

3. Content interaction (likes, shares) linked to content, etc.

 

For this purpose I want a more solid and reliable DB back-end.

Like suggested earlier in the thread, the client isn't connecting directly to the DB, it's connecting through a REST server, so the only DB user is the REST server.

 

I don't think SQL syntax is that hard to learn and I have friends to consult with.

Based on this should I go the firebird route or mORMot+SQLite3 ?

 

 

[Arnaud Bouchez 407]

It is a perfect match for mORMot + SQLite3.

But I am biased for sure! 😄

 

You may even use easily one database per account, if you wish too... this ease maintenability and scaling a lot!

 

And eventually you would be able to use FPC to compile your server application for Linux, if you need to.

 

Performance will be much higher than with FireBird or other remote DB, and SQlite3 is perhaps the most well tested DB ever.  https://www.sqlite.org/testing.html
We use it on production with DB of dozen of GB, with no problem - and it usually uses much less disk space than FireBird for instance.

 

Edited November 15, 2018 by Arnaud Bouchez

[David Schwartz 426]

Since you're just learning, you should try out different options. What will work for a prototype is not likely to scale-up very well if you don't know what you're doing. You need to walk before you run, and while I applaud your questions, you need to just play around and see how everything works.

 

You've said you're using a REST service, then you ask about connecting to the DB with Delphi. The client side won't talk to the DB, the REST endpoint logic will do that.

 

And if you're using an integrated ORM, there's really very little to worry about regardless of what DB you choose -- it becomes almost invisible if it's supported by the ORM.

[Edwin Yip 154]

19 hours ago, Markus Kinzler said:

Then multible users can access the db at the same time. But I wouldn't call this real muti user.

It's real muti-user. When a SQLite db's accessed through mORMot, be it Delphi clients using the mORMot client classes, or any JS, C#, Java clients accessing through Rest API, tens of thousands of users can read/read the SQLite db just like what you do with other db system such as SQL Server.

What is a multi-user system if this is not.

[Hans J. Ellingsgaard 21]

Here is what SQLite says on it's own webpage sqlite.org:

Many concurrent writers? → choose client/server

If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

SQLite only supports one writer at a time per database file.

 

Why not choose a DBMS when there are lots of them to choose from for free? You also get extra features like live backup and replication for free. But as stated by someone else before, if you carefully design your interface between your database and restserver, it's not to much work switching database on a later stage. 

[Arnaud Bouchez 407]

@Hans J. Ellingsgaard You are making assumption, I am afraid.

 

On the server side, a single SQlite3 insert takes a few microseconds.
A batched SQlite3 from our ORM (i.e. several inserts per requests) writes at 200,000 inserts per second on a laptop.
See https://synopse.info/files/html/Synopse mORMot Framework SAD 1.18.html#TITL_59
No remote client/server DB I know is so fast, even running on localhost.

And from concurrent writers over HTTP, you got more than 6000 inserts per second on a laptop with 200 concurrent clients (one insert per request).
See https://synopse.info/files/html/Synopse mORMot Framework SAD 1.18.html#TITL_4

This is faster than regular client/server databases, and we tested it even with 50,000 clients - try to make it with a regular RBMS.

 

We setup backup and replication in our framework for SQLite3 backup.
As you stated, using an ORM like the one in mORMot would allow to switch to another DB, even a NoSQL one like MongoDB, with no code modification.
But from all our tests, SQLite3 was the fastest when building MicroServices - i.e. Services which require a local storage.

Edited November 16, 2018 by Arnaud Bouchez

[Hans J. Ellingsgaard 21]

it was not an assumtion. It's at copy/paste from the sqlite.org webpage.

[Arnaud Bouchez 407]

The SQLite3 documentation you quoted didn't take into account that a client/server layer like mORMot.
And for this use case (mORMot + SQlite3), it states the contrary in https://www.sqlite.org/whentouse.html

Quote

Server-side database

Systems designers report success using SQLite as a data store on server applications running in the datacenter, or in other words, using SQLite as the underlying storage engine for an application-specific database server.

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.

[Guest]

My .005, firebird. You will get experience on proper ACID and a SQL implementation that is very well thought through. IMHO these are skills that will help you to really understand what's going on even if you later decide to implement something ORMish or LINQish. Or even choose semi-ACID databases like MySQL or even NoSQL. Mind: full feature servers will never ever match MySQL or SQLite on inserts.

Edited November 16, 2018 by Guest

[Arnaud Bouchez 407]

@Dany Marmur SQLite3 is really fully ACID - see https://www.sqlite.org/lockingv3.html - and it is especially safe and efficient with its WAL mode https://www.sqlite.org/wal.html

 

I think MySQL is ACID if you use the InnoDB engine.