How to choose between Libgda and SQLite

Libgda has been added as a new external dependency for GNOME 2.26, with now two database libraries: Libgda and SQLite.

This page offers some hints about how to make the choice between the two when writing an application which needs to store/access some data in a database.

Comparison hints

Of course if you want to either be able to access different databases types or you think you may need to in the future, Libgda is a better choice because with SQLite you can only access SQLite files. The comparison makes sense only if you want to create an SQLite database and have to decide between Libgda and SQLite to do it. Note that Libgda uses SQLite internally anyway (it's not a reimplementation) to access SQLite files.

• Dependencies point of view: if you want very few dependencies, SQLite is a better choice as it has no dependency whereas Libgda depends on GLib and LibXML2.
• Data access point of view: SQLite only allows reading data using a forward moving cursor, and Libgda allows you to choose between that mode and a random access data access mode (some caching is done). This point is important if you need to run a SELECT statement once and use the results several times.
• Low level control point of view: if you need some very specific SQLite features, then it's better to use SQLite as Libgda does not allow you to access SQLite's API direclty.
• Additionnal features point of view: Libgda offers much more than just a wrapper around SQLite (or other database's APIs), if you need them, it's better to use Libgda than to reimplement them yourself.
• Raw performances point of view: by design SQLite is better (but not by much).
• Data types point of view: Libgda (as most if not all databases engines) impose that for each column in each table the data be of the same type. SQLite does not impose this restriction (this means that for each row, the data in a column can be of a different type and it's up to the programmer to handle that).

One important thing to keep in mind is that changing from using SQLite to using Libgda (or the other way around) can be a long an painfull experience, so you need to make sure you choose the right one right from the beginning.

Common points

Here are some (more or less) common points between Libgda and SQLite:

• both work on all major OSes (Linux/Unix, Windows, MacOS)
• both are easy to use in the compilation process thanks to pkg-config
• both are well maintained

Libgda's features SQLite does not have

SQLite is a database engine implementation, and as such it's not meant to compete feature for feature with database abstraction libraries like Libgda, ODBC or JDBC. However it's important to know what features are implemented in Libgda which you may need to implement yourself in your application if you use SQLite directly.

Those features are:

• meta data retreival: obtaining information about database objects such as the tables and their columns, the triggers,...
• multi-threaded access to connections
• SQL parser
• loading and saving XML or CSV files
• UI interface to the data: even though it's not (yet) part of Libgda, Libgnomedb implements some data bound widgets