How can I get some more RDBMS features but still use SQLite?

Question

I've decided to use SQLite for my personal development project, because it just sprung up out of my computer seemingly of its own volition and I wanted to learn more about it. The problem is, I'm starting to really miss a lot of the features I'm accustomed to with heavyweight RDBMS's: stored programs, constraints, DRI, complex datatypes like DateTime, etc.

I really like the "database as a file" concept, but I'm finding myself implementing a lot of integrity features from scratch as I go along.

So, as the title says, I'm curious: are there more features built into the product that I just don't know about? Are there tools or libraries (preferably for .Net) that add in features as-needed? Is there another version out there that's a little more robust?

I'm using Mono 2.4 and Mono.Data.SQLite.

Answer 1

I think the clue is in the name, SQ*Lite*!

It's designed to have just enough functionality for embedded use, but little else. But it's fine to layer on additional functionality if your application needs it.

There are alternative databases you could embed though.

Answer 2

SQLite unfortunately just doesn't support some of the nice features you're used to. You just have to get used to working without them, if you want to use it.

However, there are other single-file DBs that work this way. For example, VistaDB supports Mono (it's 100% managed), and provides stored procedures, constraints, etc. It shares most of the same feature set as MS SQL, including having a very similar interface (although there are subtle differences).

Answer 3

There's at least a few ways to enforce foreign key constraints. That page explains how to do it using triggers, and it links to at least two sites that will convert your normal foreign key constraint to a trigger, programmatically, for you.

Because SQLite uses manifest typing, you can store any type in any column. You don't precisely need "complex datatypes like DateTime," because you can store a blob (serialized data) anywhere, or you can store whatever you want as a string. Note that there are a number of datetime functions in the SQLite core as well -- they just operate on numbers or strings (or both, due to automatic casting), depending on the function. There's not a real need for a date "type" when a string suffices here. That's one of the ways SQLite remains so small and portable.

Answer 4

Some of the features you mention are there mostly because of the usual client/server separation. with an embedded DB library like SQLite, it's no longer client/server, it's a layered architecture.

For example, stored procedures. What difference does it make if SQLite manages them or if your program does it? they're both on the same process and the same memory space! it's just a layer on top of the storage. Even better, you can use your own language instead of some 'proceduralized SQL'. Likewise for complex types and constraints.

just a few caveats:

• You have to write the missing layers, SQLite doesn't provide them. (OTOH, they'll be specific to your taste and needs).
• To really handle constraints in your own layer you have to be sure to do all accesses through your layer.