SQLite - ALMOST a great embedded database solution for .NET applications - Jon Galloway

SQLite - ALMOST a great embedded database solution for .NET applications

A comment on my post about the SQL/e announcement said it sounds a lot like SQLite. The subject warrents a separate post. SQLite is a great embedded database. It's free, open source, small, fast, ACID  compliant, and frequently updated. 

SQLite is an ideal embedded database provided you don't care about concurrency. That's because any write operation locks the entire database . Even Microsoft Access beats that; it's had record level locking for a while.

That's not a complete show stopper, even for multi-threaded applications, if your data provider handles SQLITE_BUSY errors correctly. By "handling the error", I mean repeatedly trying the database operation until it succeeds or the connection or command timeout is reached, which is kind of sad but works. I submitted a bug report for the ADO.NET Data Provider for SQLite project almost a year ago about this exact issue; the fix was made quickly, but the result hasn't helped anyone since this project hasn't produced a new release since August 2005. Unless you want to mess with building from the SourceForge CVS repository, you're out of luck under .NET 1.1.

However, there's a new project for a .NET 2.0 data provider for SQLite which appears to handle database locks correctly. So that's good.

Still, before SQLite can compete with Firebird or SQL/e, it needs to clean up its act when it comes to multi-threading. That means moving beyond just publishing best practices on how to work around the quirks of a database engine which has two lock states (locked / not locked) to table, page, and row level locks. I think Shailesh has a pretty good suggestion for adding a real locking strategy to SQLite. Might be a good idea if they integrate this before Mozilla integrates SQLite as their new Unified Storage system.

And a general recommendation for using any embedded database engine - use an abstration layer. I learned this the hard way; an OR/M like NHibernate would have saved me a lot of time and frustration.

Published Wednesday, April 12, 2006 4:43 AM by Jon Galloway

Comments

# re: SQLite - ALMOST a great embedded database solution for .NET applications

Right, within the Apple dev community the rule of thumb has been "don't use SQLite with Core Data if you think different threads will have to access the data.". I hadn't heard anything about the .NET provider, for the most part it seems a lot of people either just serialize to xml/binary or use MSDE. It's good to know that work is being done on the provider. It's not good to know that the current workaround for threading includes temporary tables. I'd think that kind of stuff would make E.F. Codd roll over in his grave.

Wednesday, April 12, 2006 10:04 AM by Scott

# re: SQLite - ALMOST a great embedded database solution for .NET applications

Scott, I think an embedded database is a much better solution than serialization and MSDE in many cases. Serialization doesn't scale at all - try running RSS Bandit with over 1000 feeds. MSDE installs a database service that runs 24/7, which is way too heavy weight for an application that is run occasionally.

Wednesday, April 12, 2006 12:30 PM by Jon Galloway

# re: SQLite - ALMOST a great embedded database solution for .NET applications

Right, but up until SQL/e, what choices were there for a .NET app? SQlite of course, which has it's own problems. Maybe write your own BTree or BerkeleyDB class. I can't think of any others offhand.

Wednesday, April 12, 2006 12:54 PM by Scott

# re: SQLite - ALMOST a great embedded database solution for .NET applications

Firebird, interesting. I thought that was the OSS SAP database, but it's based on Interbase. MaxDB is the old OSS SAP DB. I'll have to check that out.

Wednesday, April 12, 2006 3:26 PM by Scott

# re: SQLite - ALMOST a great embedded database solution for .NET applications

So what's wrong with Access / Jet ? Scott asks what other choices there are... well distributing your app with a Jet database isn't all that bad at all if you ask me, the limitations aren't really that considerable for your typical desktop application.

Wednesday, April 12, 2006 10:40 PM by John Wood

# re: SQLite - ALMOST a great embedded database solution for .NET applications

SQLite has always been designed around an embedded platform, so I guess in an effort to save some space, a robust locking mechanism was tossed. Personally, I'd love to see SQLite graduate to better locking, perhaps with a switchable locking module so the embedded developers can keep their tiny engine sizes.

As for John Wood's comment on Access:
There's a lot of reasons people avoid Access as a database back-end. Here's just a few:
- No ACID compliance
- SQL dialect is non-standard
- No named parameter support
- No longer supported on WinCE as of 5.0
- Database size limited to 2gb
- Databases are rather bloated
- Windows-only
- No native 64-bit support

Friday, April 14, 2006 5:40 PM by Robert Simpson

# re: SQLite - ALMOST a great embedded database solution for .NET applications

I agree with what you say for reasons to avoid Access as a back-end solution, but I was talking specifically about 'typical desktop application', and I don't think any of your points really apply there.

Friday, April 21, 2006 12:40 PM by John Wood

# re: SQLite - ALMOST a great embedded database solution for .NET applications

SQLite has NO support for data types! What good is that?

Saturday, April 22, 2006 3:06 AM by huh

# re: SQLite - ALMOST a great embedded database solution for .NET applications

When I was distributing apps using Jet, I ended up having to distribute MDAC with my app, and/or roll the latest JET SP into my app. There were plenty of JET bugs that cropped up and were fixed in SP's that my app depended on. It was a pain in the butt. I also couldn't run Jet in an isolated (side-by-side) install. Jet depends heavily on registry keys and settings that are global, and which could drastically affect the ability of my apps to perform bulk ops and other queries efficiently -- especially over a network.

As for the other poster regarding SQLite's lack of type-enforcement:

SQLite supports Int64's, doubles, blobs and text natively. SQLite doesn't enforce type-ness except for a column declared as "integer primary key". I realize this is contrary to how other databases work, and the SQLite .NET 2.0 provider actually does some enforcement work that the SQLite engine itself doesn't do ... but really -- how often do you catch yourself inserting a blob into a text column, or a double into an integer column? This is really not a mistake most of us make out in the field.

If you start thinking of lack of enforcement as a feature, you open yourself to a lot of new possibilities that other databases would have a difficult time providing.

Monday, April 24, 2006 4:48 PM by Robert Simpson