Choosing SQLite as a database (1/3)

This is the first part of the new series – continuation next week!

Selecting the best data storage engine for your application can be a non-trivial task. There are various databases available, relational or non-relational, simple or sophisticated, specialized or more generic, open-source or commercial, client-server architecture or built-in library, easy or difficult to use, and integrate with your app. The requirements for application data storage can also be very different – from small app needing to store a couple of lines of data to large app storing megabytes of data each hour.

For apps that don’t need specialized data storage, there is one database engine that could fit the needs of most of them. It is named SQLite and meets the following:

– it is very easy to integrate, especially in Qt framework, where it is integrated natively,
– it is lightweight, used as a built-in library, without the need to configure the external server,
– it runs on most combinations of hardware (PCs, phones, Linux based prototype boards), architecture (x86, x86_64, ARM), and OS (Windows, Linux, Android, iOS). We have not tested it on refrigerator yet, but who knows… ;),
– it is best suited for small to medium databases, but large databases can work too when designed properly,
– it has good enough subset of SQL to use it as an almost-drop-in replacement with other DBs,
– it is fast enough for most use-cases,
– it stores data in normal files, easing tasks such as automated backup or sending large portions of data (by sending whole database file).

Obviously, such advantages come in line with their own limitations, which sometimes can make it unusable in some applications. This question will be the main focus of this series – when SQLite would be the perfect choice for our app and when the stronger DB engine would be needed.

One of the apps we’ve created as an example of such a difficult choice between different databases. SQLite’s easiness of use and lack of need to configure external servers would really speed up the creation of the app and deploy processes, and that was a really strong incentive for us. Our app’s data storage requirements weren’t big enough to justify using another database engine, yet there were many considerations if SQLite will be really good enough. The app needed storage for two types of data: the first was a small chunk of configuration data for worker processes, which needed a reliable database, and the second was lots of statistical data, generated by worker processes, which needed a fast database. The problem is that in SQLite “reliable” and “fast” adjectives do not necessarily describe the same database. When choosing the final solution we compared how SQLite and MariaDB operate in our use-case scenarios, did some tests, and created some workarounds. Finally, SQLite has been chosen!

Where SQLite really shines

The strongest point of SQLite is its portability and easiness to deployment. It is a true multiplatform solution. It can be built for most processors (x86, x86_64, ARM) and for most modern OS-es – for most of them, there are ready-to-use prebuilt libraries available. SQLite can also be used where other DB engines would be problematic to deploy:

– on some hardware that could lack the power to run stronger DB engine,
– on some operating systems that don’t allow to install database servers (especially Android and iOS),
– on systems where we don’t have access to privileged user account to deploy database as a service.

This is where SQLite really comes to help. It is deployed with your app as a shared library and can be used in all of these circumstances. It doesn’t need any more privileges than your app besides the access to a filesystem where the database would be stored. It is arguably the most versatile database engine. From our experience, we created many multiplatform solutions with SQLite as a database, and these apps were built from a single source code and are being used on phones, PCs, laptops, and prototype boards. Single source means lower overall costs of creating and maintaining application and less troubleshooting. SQLite database also doesn’t need maintenance – it is simply a file on a filesystem, not an external process that can crash, or stop working properly in any other way.

SQLite and Qt

Thanks to its shared-library architecture, SQLite is often integrated into larger frameworks, such as Qt. It makes use and deployment of SQLite even easier, because it is deployed with standard Qt libraries and tightly integrated with Qt classes. Opening (or creating) database is as simple as creating a QSqlDatabase object, setting filename as database name, and calling method open(). There is no need to install and configure an external server, there is no need for any external dependencies (such as libmysqlclient library for MySQL or MariaDB). There is completely no deployment needed related to the database.

Qt integration is also beneficial when it comes to type handling. SQLite doesn’t impose strict typing. It allows us, for example, to insert the string into the integer-type column. Therefore, we cannot always be sure what datatype we will get as a result of a particular query. This is where Qt’s QVariant and its type-checking and converting facilities are perfect match. Qt classes abstract data type for us allowing us to completely separate database handling from data classes – that is, class which makes database queries can be completely agnostic of classes which store this data – and vice versa, they can communicate using QSqlQuery, QSqlRecord and QVariant.

Standard SQLite use cases

SQLite has established its presence when it comes to designing:
– apps for mobile phones and for embedded devices,
– desktop apps with small to medium data storage requirements,
– websites having not too large traffic,
– apps needing to exchange considerate amount of data – they can exchange data by sending SQLite database file,
– apps needing temporary databases.

These uses are the most commonly found ones, but this list is far from being a complete list of what you can do with SQLite, there are many other possible uses.

Single worst weak points of SQLite: concurrency and time of insert operation

Of course, SQLite has also its deficiencies. As said before, our app in question had one supervising process, and some worker processes (could be tens of them), and each of these processes generated between 1 to 50 rows of statistics every 30 seconds. Each of these rows had to be written to a single table in our database, containing all unaggregated statistics. But here comes SQLite worst limitation: it supports only one writing process at any point in time! Trying to read concurrently is not a problem, but trying to write concurrently results in application slowing down, sometimes almost to a standstill, and it may cause crashes and/or database corruption in some instances. It’s easy to go as far as to say that high concurrency is to be avoided at all costs in SQLite, even at the cost of changing the database engine!

Looking at the architecture of our solution (multiple worker processes) the best choice would be a database working in the client-server model. Stronger database engines (MariaDB for example) handle concurrency just fine. They are multithreaded by design and they have row-level locks along with other mechanisms to cope with high concurrency. But they need to have an external database server. Our app’s deployment process would be greatly complicated should it need to install and configure the MariaDB server, especially considering that our app could be used on machines that already had MariaDB server installed. Because of this, we spent a considerable amount of time developing workaround allowing our processes to write to a database without the need for concurrent writes. Our supervisor app manages the whole database. Worker processes don’t write directly to a database, instead they pass stats to supervisors via a WebSocket. Then, the supervisor queues them and writes them to the database sequentially. This model allowed us to bypass the worst SQLite limitation, but it created another problem – slow insert operations in a normal working mode of SQLite.

By properly setting database parameters along with configuring WAL mechanism we managed to overcome this new problem obtaining, finally, a solution that satisfied all of our needs. In the two following posts in the series, we will describe in detail practical aspects of optimizing write and read operations in SQLite.
Check those posts next week on our tech blog!

Our tests have shown that SQLite, given some care when designing app and databases, can be successfully used in many applications, especially when the database is small to medium, or when we have to use it in an embedded system. Easiness of use, especially in the Qt framework, is also a big advantage – it is tightly integrated into Qt and allows anyone to create applications efficiently. It has lots of potential uses, especially if you can overcome its main limitations. On the other side – it does not scale, and for each growing app, there will be a moment when it will be necessary to change it to another engine.

Use SQLite:

– when writing an app for mobile or embedded systems,
– when there would be a problem to use another database,
– when application data requirements are small to medium,
– when there is a need to exchange a large amounts of data between applications.

Don’t use SQLite:

– when there is a need for concurrent writes,
– when application needs to store lots of data,
– when the database should be scalable.

Let us know, what do you think or share with us your knowledge: