Choosing a free embedded database

Recently, I started to develop several applications, which require a database to store their data. One is a little accounting program, that needs to keep track of purchases and sales of various products, calculate remainders automatically and generate nice reports. Second is a bigger program, that uses an online API to download information which is then put into a database for later use.

Since these programs are supposed be only used by one single person, I don’t need a complete server, like SQL Server or MySql – it’s too much for a simple program. I’ve used MS Access before, but it’s quite slow. My search for a free embedded database began.

The first database that popped into my mind was SQLite. I’ve heard many things about it, so I though I would try. SQLite has a really really small library file size, is cross-platform and used widely. It seemed like a good choice, but there are few disadvantages I found out about later:

  1. SQLite does not have foreign key constraints. You have to use an ugly (in my opinion) workaroud – create triggers to enforce the constraint. Why is it ugly? Because you might just forget to create one, when you have more than a couple tables in your database. This might lead to having invalid data in your database.
  2. The SQL dialect it uses doesn’t have an IF statement. Ok, they say that in SQL standard IF statement doesn’t exist, but for me, as a programmer, it would quite useful and I can’t live without it.
  3. Lack of Stored Procedures. SPs are convenient. They let you keep all the SQL code in the database itself. After using them quite a lot in SQL Server, I simply miss them.

The second product I took a look at was SQL Server CE. The RDMS I’ve used most is SQL Server , so I thought I found what I need. CE stands for compact edition and well yeah, it’s too compact. It doesn’t have Stored Procedures either and also lacks triggers and views. It also has a slightly different SQL dialect than the server editions. So this also wan not my choice.

What I’ve chosen to use is Firebird. I’ve never ever heard the name before I accidentally found it on Google. Not talking much about it’s story – which lasts over 20 years already – I’ll just say that it supports everything I need: Stored Procedures, Triggers, Views, etc. It even has my beloved IF statement πŸ˜› In other words, Firebird embedded is almost the same as the server versions. Developers admit, that it’s enought to only change the connection string and voila – you’ve changed from local embedded database to server database.

Of course, it has it’s own tricks. I need some time to get used to the SQL dialect it uses in Stored Procedures. To return a table of data, I have to define every single column that the table contains as an output parameter and use a query like that:

FOR SELECT "field1", "field2" FROM tblTable t
INTO :"output1", :"output2" DO
BEGIN
-- suspends the procedure from returning and waits for next line
SUSPEND;
END

Strange to get used to at first. Also, the libraries needed to use Firebird embedded are ~2Mb in total.

So for the timebeing, I’m staying with Firebird as my #1 choice for embedded database. I’d probably switch immediately if MS released a new enhanced SQL Server CE, that would be more like the Express version though. πŸ˜› I’ll report any other problems I face with these databases in future πŸ™‚

P.S. This is my first blog entry ever on the internet, so please be lenient πŸ™‚

Advertisements

3 thoughts on “Choosing a free embedded database

  1. Thanks for the comment. It looks quite good and I may try it out in future. πŸ™‚
    It is not as mature as Firebird yet though. I would like to see some performance comparisons with other embedded databases too πŸ™‚

  2. Try EffiProz Database http://www.EffiProz.com.
    EffiProz is a database written entirely in C#. comprehensive SQL support including Stored Procedures, Triggers and Functions. Ideal for embed in .Net applications. Support Silverlight 3 and .Net compact framework as well
    Include Visual Studio ad-in, ADO.Net provider, Entity Framework provider, etc.

  3. I tried the latest firebird with visual studio 2015 and could not get it to work. It used to work fine with 32 bit Delphi. Having to edit a text conf file to make it work in 2016 really annoys me, especially when it still does not work. I uninstalled it and will use something else for my C# version of StandardDiet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s