SQLite vs Firebird

Question

The scenario I'm looking at is "This program uses Postgres. Oh, you want to just use it single-user for the moment, and put off having to deal with installing a database server? Okay, in the meantime you can use it with the embedded single-user database." The question is then which embedded database is best.

As I understand it, the two main contenders are SQLite and Firebird; so which is better? Criteria:

• Full SQL support, or as close as reasonably possible.
• Full text search.
• Easy to call from C#
• Locks, or allows you to lock, the database file to make sure nobody tries to run it multiuser and ends up six months down the road with intermittent data corruption in all their backups.
• Last but far from least, reliability.

As I understand it, the disadvantages of SQLite are,

• No right outer join. Workaround: use left outer join instead.
• Not much integrity checking. Workaround: be really careful in the application code.
• No decimal numbers. Workaround: lots of aspirin.

None of the above are showstoppers. Are there any others I'm missing? (I know it doesn't support some administrative and code-within-database SQL features, that aren't relevant for this kind of use case.)

I don't know anything much about Firebird. What are its disadvantages?

Answer 1

SQLite also has the distinguishing feature (good for some apps, shouldn't affect many others) of a cell-based rather than column-based typing discipline. Each cell in a table can have a different type; the column's type merely sets that column's preference (affinity) for a particular type. If you're aware of that, it shouldn't cause a problem.

I, too, am not very familiar with Firebird. I'd probably go with SQLite by virtue of the fact that it's well-known and widely-used (thus there are lots of eyes out for bugs, and community support should be good).

Answer 2

Firebird very bad choice at this time. Very poor internal gbd structure, inplace transaction log drive database file to corruption, missed important DDL features, Interbase legacy and many, many other misfeatures. SQLite seems to very reduntnant for me, fast and feturefull. My choice is SQLite.

Answer 3

If you use .Net, you should also look at Sql Server Compact.

Answer 4

Firebird have big advantage to be use in embedded version like SQLite or server like PostgreSQL. So you can use for both.

Although it is transactional, SQLite does not support concurrent transactions, so if your embedded application needs two or more connections, they must be serialized. An embedded Firebird database is simple to upgrade to a fully shared database - just change the shared library.

Ann

Firebird have very good dotnet driver

You can know more about Firebird here

Answer 5

I'll vote for Firebird anyway. FB have everything what you want except full text search. I've some question for Alexey - can you be more specific? I don't see in FB anything you've wrote so please explain me what you exactly mean.

Answer 6

Sql Server Express really isn't that painful an install and unlike SqlLite or SqlCE everything works just as it does when you are working with SQL Server!

From a programming perspective this is your best choice. From an installation-only perspective SqlLite and SQL CE are both OK.

Answer 7

Firebird, yes. No stored procs in SQLLite. No idea what the previous poster was saying about corruption. I have dozens of FB installs and never had one hitch yet.

Answer 8

Firebird would/is my choice.

Using it since version 1.5, currently on 2.1 and will soon upgrade to 2.5, for a shareware application, never had any database engine related issues.

I use the server version on Windows 7 for my development machine, which allows access from other tools while running the application, when I deploy I distribute the embedded version - no source code change needed for this!

FB is also cross platform, i.e. you can develop on Win$ and distribute on Linux or Mac or ...

FB enforces referential integrity - a show stopper as far as I am concerned with SQLite.

Werner

Answer 9

Why not simply keep Postgres and start it as an "application" process in the background (no install or service needed)

Answer 10

As a Rails shop, we use SQLite a lot in development. This makes apps easy to install on new boxes, etc. Then we switch to MySql or Postres on our review and production servers.

This way of working has failed us miserably. If you want to develop on a standalone database for some actual reason -- e.g., you need to distribute your app this way -- SQLite kicks major butt and is robust and can do many things. But using a different kind of database to ease your development process -- and not as an intentional part of your development -- will actually make development harder. At some point (later) you'll note certain expectations and code that work on one database and don't work on the other.

This is not a question of "good" or not: even between non-standalone servers like MySql and Postres there are some basic differences in terms of what works and what doesn't. But if you can run the target database server as your development server, you should. Otherwise, you're easing development now and potentially complicating it later.

Note: Your list of showstoppers is probably right, but the implications of that list on your code are hard to guess. In the end, you'll only be able to suss most of that stuff out empirically (by trying it out), which is more work. Which is what you're trying to avoid, I think.