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:
- 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.
- 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.
- 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
-- suspends the procedure from returning and waits for next line
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 🙂