The right database for client-server web applications? (OT)

[stegemma]

I'm developing a web application system in c++/sockets (server side) and html/css/js (client side). The first application I'll develop using this system will be for less than 10 users but I plan to develop other applications (even chess engine related) for more users. Of course the web application system would be the same for any application (think as it were Apache, for sample).

At present, I'm using SQL-server Express but I would like to know what could be the best choice, for a scalable and cheap solution. I use ODBC to connect to DB but even a direct native API would be good. The server could be Windows, Linux or OS X (that's why SQL-Server isn't the optimal choice).

I ask here because I know that there are very good and experienced programmers and I trust your suggestions. I don't want to know what is the absolute best choice but what is your experience and comment about your try with different DB.

Thanks.

[uaf]

MariaDB (a fork of MySQL) or PostgreSQL.

If you are developing a commercial software I would go with with PostgreSQL, because of its BSD-like license, to avoid any possible license hassle.

[smatovic]

if it is really only for 10 or less users i would consider using sqlite,
MySQL may be the de facto standard for web applications,
but has some overhead for adminstration.

--
Srdja

[Dann Corbit]

The real answer to your question is:
It depends.

If single threaded access is OK, then SQLite as an embedded solution will be hard to beat. Any real world multi-user SQL system is going to need a DBA to maintain it.

SQL Stores:
MonetDB is very fast for some kinds of applications, but the ODBC interface has a few small quirks.

PostgreSQL is very effective and we use it for lots of things.

Alternatives to SQLite and relational SQL stores:
There are some other simple key value stores that might solve your need, depending on what you are really trying to do. Redis is a really quick data structure store, for example.

Another interesting project is lmdb: https://github.com/LMDB/lmdb
It is a simple key value store.

We have used FastDB and Gigabase with success, but these tools are REALLY fiddly if you need multiple writers.

[jdart]

Second that.

Most Web developers are using mySql or Postgres. Since Oracle acquired mySQL they have let it languish a bit, at least the free version (there is a paid version with different licensing). MariaDB is a fork that includes many of the original mySQL committers.

But Postgres is a somewhat better choice I think. More open license, better standards support.

If you expect high update rates or very large db sizes then there are other dbs you should look at. But most websites are moderate size, read-mostly.

--Jon

[Rémi Coulom]

Hi,

I take the opportunity of this thread to advertise my joedb:
http://www.remi-coulom.fr/joedb/intro.html

I have found it frustrating to use an SQL API inside C++. It always produces ugly code. So I decided to develop my own database. It is based on an original journal-centered architecture. I believe it has a lot of potential. It is considerably more pleasant to use in C++, and it can be an order of magnitude faster than traditional SQL databases for many usual scenarios.

I am also developing a web application in C++. In the beginning, I also considered writing my own server code. But I decided to be a bit reasonable, so I use fastcgi with nginx. I have also started to develop C++ tools for web development.

Joedb is not yet mature, and probably not a reasonable advice. But if you are not into reasonable programming, you might like it. Its current features are a bit limited, but it is usable. I believe in the potential of joedb, so I am working on developing the missing features.

Any feedback is most welcome.

Rémi

1. Joedb, the Journal-Only Embedded Database

Joedb is a minimalist embedded relational database, where data is manipulated directly in the target programming language, without using SQL: a compiler produces native data-manipulation code from the database schema.

In joedb, the journal of all modifications is stored to disk. This way, the whole data history is remembered, and it is possible to re-create any past state of the database. It is also a way to make the system extremely simple, and fast.

1.1. Context and Motivation

The usual approach to writing programs with persistent data stored in a relational database consists in using SQL queries. Most database management systems provide an API that takes SQL strings as parameters.

The problem of using an SQL API is that the program has to produce SQL strings at run time. It has a cost in terms of performance, because the SQL string has to be generated by the program, and parsed by the database. Another problem with such SQL string manipulation is the security risk of SQL injection. Also, type and identifier errors will be detected at run time instead of compile-time, which does not fit the philosophy of statically-typed programming languages.

Some weaknesses of SQL APIs can be corrected by encapsulating the SQL string manipulations into some higher-level interfaces such as object-relational mapping systems or data-access objects. These systems improve safety by providing static typing and identifier lookup.

These higher-level interfaces might look much cleaner from the programmer’s point of view, but the additional layer of abstraction is costly. For example, it might become necessary to use a loop over objects to update them one by one. One single complex SQL query might have done the job efficiently, but the abstraction forces the programmer to inefficiently generate several queries instead.

The idea of joedb is to overcome these problems by dropping SQL, and all the abstraction layers. Using SQL makes sense as a protocol to connect to a remote database, but is not necessary for an embedded database. All the operations over the relational data can be directly implemented in the target programming language. This produces an architecture that is cleaner, simpler, and offers great opportunities for performance optimization.

Joedb stores data in permanent storage with a journal. Writing a journal is necessary for crash recovery and transactions. And because the journal can contain all the data, it is not necessary to make anything else permanent. In addition to its extreme simplicity, this approach also makes it possible to annotate the history with time stamps and tags, and to re-create any past state of the database.

A journal is a simple representation of the data, but, for many typical operations, it is not a convenient representation. In practice, an application that uses joedb will build in-memory or on-disk tabular structures and indexes to manipulate the data conveniently. The joedb compiler can generate some ready-made data structures for the most usual situations. But the programmer is free to control the data-management code completely, and can implement any data structure or file format that fits a particular usage pattern.

[stegemma]

I have my own c++ db, that can operates with direct c++ calls or sql commands, it can computes formulas, executing scripts and so on but:

- it is an in-memory DB stored in a single file on disk
- it is not multi-user

The latter is partially false, because I have a program that let multiple users connect to a single DB but changes are not committed one by one, only writing the full DB to disk.

A reference can be found here:

http://www.linformatica.com/index-minosse.php

It is not sold, I keep it private now.

For my web-application server I need a system that handles multiple users and possibly multiple applications in multiple DBs. The first app would be for condominium administration. The administrator can have up to 10 employee (maybe more but it is rare), that's why my first user limit. Any administrator must have its own DB that contains multiple condominiums.

I plan to use the same system for other applications, for sample for machine configurators for mechanical industry, where users could potentially be some hundreds or I could enjoy in chess related stuffs, where you can have thousands of users.

Anyway, your project is very very interesting. Having write my own DB, I know how complex would be this kind of work and your approach is very original.

It seems to me that for my needs MySQL or PostgreSQL are the best choice.

I've wrote my web-server (I'm a little insane, I know!) and I'm testing web-sockets, to have a bidirectional asynchronous interface with users. en passant, this would be great for a simple engine tourneys visualizer...

[Rémi Coulom]

Thanks a lot for your feedback, Stefano.

I have just started to develop a joedb server, but it is not functional yet. The journal-only approach makes it relatively simple to do. I hope I'll finish it during the summer. I am quite busy with other projects, so progress is a bit slow.

My server will not handle user authentification or rights. User authentification is the job of the web application.

Anyway, I did not really expect that joedb would fit your requirements. MySQL or PostgreSQL seem to be good choices for your situation, indeed.

[stegemma]

Thanks a lot for your feedback, Stefano.

I have just started to develop a joedb server, but it is not functional yet. The journal-only approach makes it relatively simple to do. I hope I'll finish it during the summer. I am quite busy with other projects, so progress is a bit slow.

My server will not handle user authentification or rights. User authentification is the job of the web application.

Anyway, I did not really expect that joedb would fit your requirements. MySQL or PostgreSQL seem to be good choices for your situation, indeed.

Journaling could be good but you must think about a "consolidation" of the file, because in a context of multiple updates the DB could be bigger than expected.

For a production use, someone needs transactions too (I'd never used transactions but I know that I should do) but this could be a nightmare for the structure of your DB, in a multi-user environment (in a single user you can just roll-back from the starting point of the transaction in the journal).

[Rémi Coulom]

Yes, I already have the consolidation operation. It is really very slow. But for my applications, it is not necessary, so I don't worry to much about this.

Transactions are not so difficult with a journal. I don't wish to implement transactions that can be rolled back. I don't think they are necessary, and they are too complicated to implement, whatever the data structure. I will only implement simple transactions that can only be commited. In the multi-user case, they will be like a mutex. So they will have to be short in time. This simplified form of transactions is very easy to implement, and should cover all practical needs. I only have to implement a "start transaction" and "end transaction" operation in the journal.

This kind of transaction is really required, for crash safety and multi-user access. Transactions that can be rolled back may be convenient, but I never used them, and their implementation is complex. I could do it by storing undo data in the journal, but it is too costly for my taste, and not useful in my practice.