Why should I use SQLite over a Jet database

Question

Someone asked me this the other day, and I couldn't think of a good answer. Platform portability is completely irrelevant to the project.

In fact, Jet has some features that SQLite does not, namely foreign keys.

So can anyone think why SQLite should be used instead of a Jet database?

Answer 1

Jet is no longer supported. SQLite is also easier to install since it's one dll that can easily be packaged with your app. Using SQLite also can prevent vender lockin, just because language or cross platform portibility isn't a concern now doesn't mean it won't become one later. For more on Jet's retirement see http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

Answer 2

Off the top of my head, it's free and cross platform; but more important... do you think it is more stable and scalable that Jet/MS Access/.mdb? Will it be longer lived that its successor (ACE/.accdb)

If it is being used by more than just a couple people, I don't bother with Jet. I go straight to MS-SQL (even the free version of it). It's just not worth the pain of a corrupt DB (which Jet is known for - although maybe they fixed it - I don't want to be their test case though).

Answer 3

Cost is not an issue. If your frontend is built in something other than MS-Access, users of the application do not have to pay any fees to have the Jet drivers installed. Visual Studio would include those drivers during your build (At least the pre .NET versions did.).

I'm guessing you have no personal preference and are equally skilled in development in either environment. If your users have already MS-Access licenses and they would like to be able to write their own reports (Oh, God forbid any non-hacker attempting such a tremendous feat!), use Jet.

Answer 4

SQLite is the new Jet. Even if cross-platform is irrelevant to you, it may not be to your customers. Using Jet locks them into Windows and to a no longer supported DB, neither of which are good things. And SQLite works with just about any development environment out there.

Jet is known for having strange corruption issues, so I tend to stay away from it in general.

You can certainly create foreign keys in SQLite, and as of SQLite 3.6.19 foreign key constraints have also been added.

Answer 5

SQLite is superior to Jet for the major reason that SQLite is ACID-compliant whereas Jet, unfortunately, isn't. If data integrity is an issue, SQLite offers a much more "robust" platform for your data storage requirements.

SQLite does indeed lack a few features (such as foreign keys), however, these are primarily due to SQLite being specifically developed as being an extremely small and lightweight database that is also serverless.

The serverless aspect of SQLite is also a major benefit over Jet in that nothing needs to be installed on the machine that will run your database. For example, I have used SQLite in an ASP.NET web application and all I needed was the SQLite DLL (in this case is was the excellent System.Data.SQLite drop-in replacement) in my application's "bin" folder, and my database in the application's "App_Data" folder. I could then upload these files to my webhost, and it all "just worked". This is without having to actually install or register anything on the target machine.

A small dowside of SQLite is due to the database being file-based. Database writes will lock the entire database file rather than a specific row or table, whereas Jet will offer you a more granular level of locking. Another small issue, based on the same file-based reasoning, is concurrency, however Jet itself does not offer a high level of concurrency either.

Answer 6

Contrary to what other people are saying, Jet is not dead and far from it: ACE is the new version of Jet and it's pretty robust and backward compatible.

Both SQLite and Jet/ACE have their strengths and weaknesses and you need to get more information about the specific points that are important to you and your application.

• In either case you can redistribute the engine.
• Jet/ACE is a bit more integrated and supported out of the box in MS tools and Visual Studio.
• Jet/ACE has more granular locking, which may be important if your app allows multi-users or needs multi-threaded access to the database.
• Jet/ACE has more features in terms of what you would expect from a database (joins, unions and complex queries).
• Jet/ACE has a simple migration path to SQL Server, so if your database needs become big, you could move to SQL Server fairly easily.
• SQLite is cross-platform, so if your app needs to be ported to Linux/Mac under Mono then SQLite is a better choice.
• the SQLite engine is tighter so redistributing may be easier.
• datatypes are quite loose in SQLite.
• SQLite has more liberal redistribution rights (since you can basically do whatever you want with it).

People who say that Jet corrupts databases are stuck in 1995.

In the end, unless your application has some very specific requirements that are pushing the boundaries of either database engines, then it probably doesn't matter which one you chose.
Just use the one that easiest for you to include in your project.

Answer 7

How about a regfree implmentation Checkout the StarSchema-OLAP demo. Hard to believe the performance.

http://www.thecommon.net/3.html