Ayende @ Rahien

It's a girl

In search of an embedded DB

I need to use an embedded DB in a project. Naturally, I turned to my old friend, SQLite. It has served me well in the past, and I am well versed in all its tricks. Except... SQLite really fall apart when you are talking about even moderately multi threaded applications. By that I mean, it works just as advertised, for sure. It just lock the entire DB when used, which tend to kill other threads who want to access the poor DB.

I then turned to FireBird, but a short spike told me that it is... inadequate. I managed to get the database into inconsistent state, but I am not sure how or why. There seem to be very little information about it as well, so at the moment it is out of the game.

Sql CE is worse than SQLite when it comes to handling multiple threads, so that wasn't even in the running.

Vista DB is commercial, and I plan to make this an OSS project, so that is out as well.

I want to use Berkeley DB, but is seems like the .Net story is hard. There are CLR Binding, but I run into several blocking issues with them. There is no 1:1 mapping to the C API or the Java API, the error messages are... terse. And there aren't any tests at all! Even worse, the code is interfacing with native code, so it is C# after a rail road has hit it at 200 kph and having spent three days crying in a desert.

I put some time into trying to get this to work, and once the initial hump is over (understanding the association between environments, databases and transactions), it makes a lot of sense. Of course, there are a lot of features that I wouldn't even try to figure out.

First order of business, create a .Netified wrapper that will abstract the C like API away from me.

Okay, I am being ungracious here, the API make a lot of sense and the code is good for the purpose it was built, it was just that I had a moment of existential uncertainty when I looked at the code and it looked like C++#.

Comments

Tommaso Caldarola
07/03/2008 08:27 AM by
Tommaso Caldarola

I'm using Berkeley DB to store diagnostic data (several Gb at month), the way to C# is C -> C++/CLI (managed API wrapper) -> C# (client).

Jimmy
07/03/2008 11:13 AM by
Jimmy

Have you looked at SharpHSQL?

http://www.codeplex.com/sharphsql

davo
07/03/2008 11:21 AM by
davo

Have you tried H2? It's a rewrite of the Java Hypersonic DB that SharpHSQL is based on by the original author. It works under .Net via IKVM. There is also an ADO.Net wrapper for it called H2Sharp.

http://www.h2database.com/

http://www.h2database.com/html/frame.html?advanced.html%23microsoftdotnet&main

http://code.google.com/p/h2sharp/

Reshef Mann
07/03/2008 11:24 AM by
Reshef Mann

Maybe db4o will be good for u in case u don't mind OO db.

Ryan Roberts
07/03/2008 11:43 AM by
Ryan Roberts

Db4o? It plays with OSI compliant licenses and is optimistically concurrent by default.

Paco
07/03/2008 11:59 AM by
Paco

I used Firebird for a .net app that supported embedded and on server database. The tools and documentation suck, but it's not very hard to use

Davy Landman
07/03/2008 12:03 PM by
Davy Landman

I've used Db4O in a few projects as well. And like it allot, the new version also has LINQ support.

Ngoc Van
07/03/2008 01:44 PM by
Ngoc Van

I'm using embed Firebird for a commercial application. It's really nice, easy to use and ease to upgrade to server database, except:

No encryption for embed database. You event don't need to provide a password to connect to the database even though you've set the password when you create it.

Application hang after exit. You have to explicitly call FbConnection.ClearAllPools. Put that in a destructor doesn't seem to always do the job.

Anyway, I"m using NHibernate, so change of database engine will not be a hell.

Louis
07/03/2008 02:55 PM by
Louis

We've been sadly coming to the same realization with some recent experiences as well. Looking forward to hearing what you conclude.

Does "data source=.\sqlexpress;AttachDbFileName=path\file.mdf;Integrated Security=True;User Instance=True;" land too far from your requirements? Out of process, but it runs on a named db file, and with calling app credentials. Installation overhead -1.

Ayende Rahien
07/03/2008 03:17 PM by
Ayende Rahien

What is the managed wrapper that you use?

Is it available?

Ayende Rahien
07/03/2008 03:28 PM by
Ayende Rahien

Louis,

You need to install SQL Server Express for that, so there is installation overhead

Scott
07/03/2008 03:43 PM by
Scott

I've never used it, but what's wrong with compact edition?

http://www.microsoft.com/sql/editions/compact/default.mspx

Ken Egozi
07/03/2008 03:44 PM by
Ken Egozi

@Ayende:

is the need to bundle SQL Express is that bad?

You'd gain an excelent tool for free.

as for Firebird - it doesn't sound right. I had some experience with firebird, and apart from the fact that back then handling BLOBs was a PITA, it performed very well. Is it possible that you were using a dodgy .NET FB driver?

Rob
07/03/2008 03:59 PM by
Rob

Perst.NET:

http://www.mcobject.com/perst/

josh
07/03/2008 04:03 PM by
josh

I used firebird with a toy app I wrote (auto parsing of job posting and sending emails with resume attached). It was well behaved and performance was fine for what I was doing... until I decided to make it work with Mono too. I'm not sure if it was switching the db driver or some change I made, but I started getting weird results. I got a job and didn't really look at solving it.

firebird is still my embedded db choice, but I'll be looking into suggestions from other commentors.

Yitzchok
07/03/2008 04:18 PM by
Yitzchok

Whats with VistaDB "Express"

"You can now use all the great power of VistaDB on your personal websites and other non commercial projects for FREE."

Necromantici
07/03/2008 04:36 PM by
Necromantici

You should really check out db4o (DB For Objects, www.db4o.com). They had really done a good job in the data persistance area. And there are many tools for it that run on .NET and Java.

I have used it in commercial applications for Windows Desktop, ASP.NET Websites and Windows Mobile applications and never had a problem and they all got good performance in final users criteria.

Ayende Rahien
07/03/2008 07:25 PM by
Ayende Rahien

Scott,

Utterly not thread safe.

Can leave the database locked and require a process restart to continue working.

Ayende Rahien
07/03/2008 07:26 PM by
Ayende Rahien

Ken,

I am using FirebirdClient, which is pretty decent, as far as I understand.

The issue is that a) no tools, b) no docs for even the SQL dialects, c) I managed to corrupt the state very easily.

In general, it is a bad beginning, and I feel that while I could probably overcome it, there isn't a good reason to try,

Kevin Teague
07/04/2008 11:20 PM by
Kevin Teague

ZODB works very well as an embedded persistent Python object store (handles mutli-threaded concurrency issues). Of course, it only works w/ Python, so ...

John L
07/05/2008 12:49 AM by
John L

Firebird is definitely adequate: I've used it for many years in large, heavily stressed, enterprise applications. I think its simplicity, stability, and performance are difficult to match.

Victor
07/05/2008 01:26 AM by
Victor

Derby

Jon Galloway
07/15/2008 05:36 AM by
Jon Galloway

I bumped into this problem a few years ago; it just doesn't seem to go away. http://weblogs.asp.net/jgalloway/archive/2005/07/08/418474.aspx

Time for AyendeDB!

Comments have been closed on this topic.