Firebird

While poking around with a play project, I was thinking, "Wouldn't it be nice if SQLite had a server mode?". It'd be nice to have a database that could give us the best of both worlds-- the simplicity of SQLite and the multi-client scalability of Postgres. You could build small projects with it, and if any of them ever needed to scale, no problemo.

Well, Firebird might be that database. It's an old database from the days of Pascal. It can be embedded, can run in embedded environments, and can run as a standalone server on a high-core, high-RAM beast of a machine.

Let's take a look.

Installation

Installation is pretty straightfoward. You download, unzip, and run an install script:

bash

curl -O "https://github.com/FirebirdSQL/firebird/releases/download/v5.0.1/Firebird-5.0.1.1469-0-linux-x64.tar.gz"

tar -xvzf "Firebird-5.0.1.1469-0-linux-x64.tar.gz"

./Firebird-5.0.1.1469-0-linux-x64/install.sh

The instlal script prompts you to create a password for the sysdba user.

Everything is now installed in /opt/firebird. The server can be configured by editing /opt/firebird/firebird.conf. All binaries are in /opt/firebird/bin which you should add to your path.

To start Firebird in server mode, all you have to do is run:

bash

firebird

Firebird comes with an example database: /opt/firebird/examples/empbuild/employee.fdb. Let's connect to that using the Firebird interactive tool isql.

bash

# Replace "secret" with whatever password you set up previously
isql -user sysdba -password secret /opt/firebird/examples/empbuild/employee.fdb

Here, we can list the tables by running an isql-specific command show tables; or with a bit of standard SQL:

sql

SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG = 0 AND RDB$RELATION_TYPE = 0;

We see the following:

RDB$RELATION_NAME
=============================================================== 
COUNTRY
JOB
DEPARTMENT
EMPLOYEE
PROJECT
EMPLOYEE_PROJECT
PROJ_DEPT_BUDGET
SALARY_HISTORY
CUSTOMER
SALES

Let's have a look at the first employee record:

sql

SELECT FIRST 1 * FROM employee;

If your terminal isn't wide, it might be hard to read the output due to text wrapping. You can run SET LIST ON; to change how the rows are printed.

Transactions

An interesting thing to note is that everything in Firebird happens in a transaction, and by default, the isolation level is snapshot. This caught me off-guard, as I've never used snapshot isolation in any other database.

To illustrate this, fire up two separate isql instances.

In one instance, modify the database:

sql

UPDATE employee SET first_name='Bobby' WHERE emp_no=2;

Then, in both isql instances, retrieve that record:

sql

SELECT first_name FROM employee WHERE emp_no=2;

One will show Bobby and one with show Robert.

That's because we haven't committed our transaction. In the isql where you ran the update, go ahead and run COMMIT;, then re-run the select queries.

There's no change! Bobby and Robert still show up.

That's because the "Robert" isql instance is viewing a snapshot of the database as it was when the database connection was established. To view the latest data, you have to COMMIT; there, too, and now you're looking at the latest version of the database.

As I said, this surprised me, since it's not the default behavior of any database I've ever used. The transaction levels can be controlled explicitly. See the docs and this blog post for details of how Firebird transactions work.