Jump to content
Yaron

Advice on starting to work with databases

Recommended Posts

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)?

 

  • Like 1

Share this post


Link to post

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 by Yaron

Share this post


Link to post

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

Share this post


Link to post

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.

Share this post


Link to post

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?

Share this post


Link to post
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 by Rollo62

Share this post


Link to post

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.

Share this post


Link to post
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.

 

 

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post

@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 ?

 

 

Share this post


Link to post

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 by Arnaud Bouchez

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post

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. 

Share this post


Link to post

@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 by Arnaud Bouchez

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post

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 by Dany Marmur

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×