views:

1277

answers:

10
+6  Q: 

SQLite vs Firebird

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?

+5  A: 

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).

Michael E
A: 

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.

Alexey Sviridov
Why is Firebird a bad choice?
Lasse V. Karlsen
@Lasse the answer explains that; it corrupts data (critical) and is missing features. Designs that increase the probability of database corruption should automatically disqualify data storage systems from consideration.
Michael E
missed important DDL features : which one ?
Hugues Van Landeghem
The internal gdb structure is only important for the engine, not the application. Inplace transaction log (which is not really a log, things work differntly for multigenerational databases) are a good thing and in many years of heavy usage I have not had any corruptions. Most important: Switching to a multiuser database is as easy as using another connect string. Only problem: No full text search. (Use a good engine as lucine..)
Steffen Heil
2HuguesOf course i'm about alter view. Now, to alter view, you need drop view and all their depedensies. Sometimes this come to nightmare, when you change some fundamental view of your shema. Yes, yes i'm know abou 2.5, but it's still in RC stage now, and will be stable somwwhere about 2.5.1 - 2.5.2 as usual.
Alexey Sviridov
@Alexey : Here we are comparing SQLite and Firebird. If you find Alter View in SQLite http://www.sqlite.org/lang.html you can say this but alter viev don't exist in SQLite too !
Hugues Van Landeghem
-1, because your answer is very colored and seems almost political.
Ritsaert Hornstra
Not political, but practical. I'm was firebird fan since Interbase 5. But now it's really bad chice, sad but true.
Alexey Sviridov
-1, it's political: it's simply your opinion and I doubt you can support your claims. It's your choice to not like Firebird, but I don't agree so I'm down-voting. All my database applications are using Firebird, so every day there are at least 20000 firebird databases used in production, running my code. If ANY one of those gets corrupted I know about it, and it very rarely happens! And when it happens it's ALWAYS related to power failures on computers with no UPS-es. Power failures can corrupt anything so that's not a problem, and database servers should at a minimum have an UPS.
Cosmin Prund
+3  A: 

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

GvS
OP said it should support full SQL. Compact doesn't support full SQL unless something's changed in the last 6 mos. And I don't think it supports full text search. It supports all your basic CRUD, but there's a number of missing parts of SQL that have periodically driven me bonkers. Once I reminded myself that it was intended to be run on the same machine as the app, I realized it just meant to do those things in the app. Not knocking Compact, just saying it's not meeting the OP's requirement. Sounds like OP wants to take it to a server at some point. Not a scenario for Compact from my exp.
Jim Leonardo
+3  A: 

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

Hugues Van Landeghem
+6  A: 

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.

Milan Tomes
A: 

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.

Hightechrider
+3  A: 

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.

VladTheImpaled
What is dozen in your case? I'm have only about 2000 installation of firebird 2.1 and have at least once database corruption every day. Will be migrate to mysql as soon as i can.
Alexey Sviridov
@Alexey: I have same number of MySQL and Firebird installation (about 1000 each). And database corruption is allmost the same (very few in fact). The big difference is that Firebird have more features and is Free and it is more easy to switch to a newer version of Firebird.
Hugues Van Landeghem
+2  A: 

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

Werner
A: 

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

a_horse_with_no_name
I never heard of any capability of running Postgres without an install process, and with a quick Google search just now I still can't find any mention of such. Can you point me to the relevant instructions?
rwallace
Not much instructions necessary. Download the ZIP file archive, unzip it, and run initdb to initialize the database, then pg_ctl to start the server. That's it
a_horse_with_no_name
Check out this post:http://www.dbforums.com/postgresql/1646331-install-postgre-service-manually.html
a_horse_with_no_name
A: 

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.

Yar
Oh yes of course, supporting two database engines is necessarily more work for the programmer, especially compared to the almost nonexistent effort of installing Postgres on one's own workstation (which I've already done). The party I'm trying to make things easier for is the low-end user, not myself.
rwallace
@rwallace, yeah, we thought that too. The problem is that your time is worth so much that unless you have MANY of these users, you should just go over and install Potsgres yourself :)
Yar