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?

Algophobia

Actually, SQLite allows you to implement foreign keys with check constraints. – Klaus

SQLite 3.6.19 (released in November 2009) added support for foreign key constraints. – Stripteaser

Answer 1

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.

Linchpin

SQLite also supports joins and unions: sqlite.org/lang_select.html. Maybe you meant that it does not enforce the integrity of foreign keys? – Assignable

A db that doesn't enforce foreign-key constraints is not really a database in any real sense. – Moslemism

@David: it may not matter in some cases and if you're using an ORM, it will generally handle these for you. There is a place for all of these databases, you just need to pick the one that makes the most sense for your project. – Linchpin

Foreign-key constraints MUST be enforced in the database engine itself, not at some level above it (either at the application level or in a tier between the app and the database). I am shocked that at this late date anybody would attempt to justify the enforcement of relational integrity anywhere else than in the database engine itself. It just makes no sense to me at all. – Moslemism

Open Office lets you open Jet DB so its cross platform too. – Agosto

SQLite has supported enforcing Foreign Key constraints since version 3.6.19 released in October 2009: sqlite.org/foreignkeys.html – Ambit

Answer 2

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. See "SQLite Is Transactional" and "Atomic Commit In SQLite" for more details.

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.

Columbium

That's not a downside of SQLite. Concurrent/network file writing on Windows is inherently unreliable (sooner or later you'll find intermittent data corruption has been occurring for the last six months so it's in all your backups too), so multiuser systems need a proper database server. SQLite and Jet are for single user scenarios. – Skipton

What is the basis for the claim that Jet is not ACID-compliant? Jet by default uses implicit transactions for everything (unless you explicitly tweak the database engine to not use them), and you have full access to transactions for commit/rollback via DAO. So, I think the assertion is completely baseless. – Moslemism

The issue of "nothing needs to be installed" applies to Jet, as well, at least for all versions of Windows starting with Windows 2000, where Jet 4 is installed as part of the OS. – Moslemism

@Moslemism The basis for the claim is this article: msdn.microsoft.com/en-us/library/aa190103.aspx, direct from MSDN itself. Note the "Important" section near the bottom that quite clearly states that the JET engine does not support durable transactions, something that is required for ACID-compliance (indeed, durability is the "D" in ACID). – Columbium

In 16 years of professional Access development I've never enountered a problem with the "D" in ACID. This is one of those false tests, seems to me, a theoretical construct that doesn't necessarily reflect real-world requirements. – Moslemism

@CraigTP: If you are going to quote from MSDN, at least provide the whole quote in context: "File-server databases, such as the Jet database engine, can't guarantee durable transactions." The point is that no file-server database can guarantee durability. You know, file-server databases like SQLite. – Hexamerous

@Hexamerous - Not true. Although SQLite is a file-based database, it does support durable transactions. See here: sqlite.org/transactional.html – Columbium

@CraigTP: I stand corrected. -1 to +1. I edited your answer to add the links that support your main argument (and because SO wouldn't let me change my vote until the answer was edited). – Hexamerous

Answer 3

This is still a question that comes up from time to time. I'm considering all the pros and cons for both right now for a new project. I write a lot of financial applications that deal with money values so one of the most important "pros" for Access/JET/ACE/whatever-they're-calling-it-today (I just call it Access) is its strong types. Don't underestimate the power of having strong types when you're dealing with money - Access is the only single-file database I've seen with support for a money/decimal type that can store REAL money values.

One of my main retail products uses SQLite as a backend and I can tell you that I've had virtually no problems with it deployed even in the craziest situations. SQLite is definitely designed to be single-user but I have a LOT of customers using it over SMB. You do have to write checks into your software to check for return values of SQLITE_BUSY when running queries but if you wrap that up with an auto-retry it "just works".

There are only a few reasons I'd choose Access over SQLite - one is data types. If I'm ever writing software that has to do math on money values (tax, etc), I'll use Access. The only other compelling reason to use Access is an upgrade path to SQL Server. Since I've never used SQL server in my life (and don't plan to), it's not a big deal.

In the end, both are extremely robust databases - I wouldn't hesitate to use either in a production environment. Just remember to use the right tool for the job and sometimes that means a database server (PostgreSQL has treated me right over the last 13 years, that's for sure!).

Imagism

Again, SQLite's real weakness is loose typing. But that's only a weakness if you need strong types, and most folks probably don't. MS-Access/Jet/Whatever is the only single-file zero-admin database that I've found with support for one data type that is exceptionally important in most of the software I write -- the money/decimal type! When you need it, nothing else will do! – Imagism

No -- its proper handling of fixed decimal values that's important for financial applications. You can only bill, pay tax etc. using whole cents. Its not valid to bill someone $.0033333333 since they cannot pay this without resorting to tinshears. Since you can normally return individual items on an order or invoice they must be rounded to a value in cents. Floating point, or plain integers dont hack it in this space. – Leadbelly

Uh, isn't that an application-layer issue (rounding), rather than a data storage issue? Jet/ACE's Currency type is limited to 4 decimal places, but you're perfectly free to round to whole cents before storing the numbers in it. – Moslemism

Answer 4

We used Jet for a long time and recently switched over to SQLite. Why?

1: When a database gets anywhere near 2 GB or with frequent use, it becomes corrupted in Jet eventually. This has caused us a lot of grief! This has not been fixed in Jet or ACE, though Microsoft has a separate tool that can supposedly fix the database files.

2: Microsoft deprecated Jet years ago, in favor of ACE, but if you read the details, Microsoft itself says that ACE is NOT a replacement for jet, and really wants you to use SQL Server instead.

3: Jet is no longer a standard part of Windows, but part of Microsoft Office, though you can download and install the distributable. However, you can not have both the 32 and 64-bit engines installed at the same time. If you have Office 2007 32-bit installed, and you try and install the 64-bit ACE engine, it tells you need to uninstall Office 2007 first.

So for these reasons we just decided enough is enough. Installing SQL Server is not a solution because it is a big complex invasive install and not very portable.

Our C++ software directly supports SQLite via the sqlite3.c file, and it works very well. I have implemented antive interfaces for OCILIB, Oracle, SQL Server, MySQL etc and this was one of the easiest. It is also much faster than Jet and the resulting files maybe a third of the Jet size. We do have some VB6 and VBA and .NET code that also need to use our database files and for that we use the SQLite ODBC driver (just Google it). Works well.

SQLite works fine in both 32 and 64-bits. And if you read up on it you will see it is seriously tested and amazingly stable. It also supports more standard SQL and is closer to Oracle/SQL Server than Jet is.

Nonreturnable

Your #3 is WRONG. Jet 4 is still a part of Windows, and has been since Windows 2000 -- it's used by Active Directory. I do expect that to change soon, though, given the 64-bit issue (i.e., Jet 4 will never be 64-bit, which means any compopnent depending on it have to be 32-bit). Jet 4 IS NOT managed by the Office development team, but by the Windows team. It is the ACE, the fork from Jet 4 (could have been called Jet 4.5 or Jet 5) that is owned by the Access development team (not the Office team), and will continue to be developed on its own path. – Moslemism

Jet 4 is not seeing any further development, so is a stable target. It is also fully supported and updated by Windows Update (which means it gets security updates, if necessary). But I'm not sure I'd assume that it will continue to be part of the OS for much longer. – Moslemism

The 2GB limit applies to ACE as well as Jet 4, and is unlikely to ever change, seems to me. I have never seen it as an issue, since from my point of view, the applications for which a Jet/ACE data store is appropriate are never going to get that large. If I had an app where the data store started approaching even 1GB I'd be upsizing immediately. If you're running into the 2GB limit in production use, it suggests two things to me: 1. you're not doing any db maintenance (regular compact), and 2. you chose the wrong data store in the first place. – Moslemism

@Moslemism the game Onlink simulates a (very downscaled) ipv4 internet, about 150 companies, 3 ISPs, 2000 computers, 7 banks, 250 humans, 50 computer hackers, academic records, social security records, criminal records, bank transfer records, stock exchange network, internet connection logs, and it uses SQLite. i have seen the game savefiles become over 1GB in the past after extended periods of gameplay – Beadruby

Answer 5

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

Centiare

The Wikipedia article is filled with bad information. Jet was removed from the MDAC because it was PART OF WINDOWS (from Win2K on). The ACE is just a new version of Jet, fully backwardly compatible with Jet 4 and earlier files. The ACE has a long future, as long as Access exists. – Moslemism

This from Microsoft: "Microsoft has deprecated the Microsoft Jet Engine, and plans no new releases or service packs for this component." (msdn.microsoft.com/en-us/library/ms692882.aspx). – Georgeanngeorgeanna

"The ACE has a long future" -- the last release was a disappointment, unless SharePoint integration is your thing, or if multi-valued data types as an engine-supported means of violating 1NF had been on your wishlist for the near decade it took for the Access team to get around to releasing ACE ;-) – Georgeanngeorgeanna

"The ACE has a long future" -- IMO the engine lost more than it gained in going from Jet to ACE i.e. user level security and replication. The best thing about ACE is they finally fixed the DECIMAL data type negative values sort bug :) – Georgeanngeorgeanna

Microsoft again confirming that Jet is deprecated: "Access 2007 provides a new engine based on JET, called the Microsoft Access Engine (ACE Engine), rather than using the deprecated Microsoft JET engine." (msdn.microsoft.com/en-us/library/cc811599.aspx). – Georgeanngeorgeanna

ACE is Jet, just under a different name. You've claimed elsewhere that it's not, but it really is, and everyone knows that. If you mean "Jet 4" is deprecated, well, congratulations -- have a cigar. Better if you say what you mean. – Moslemism

No thanks for the cancer stick: I'd prefer if you didn't refer to ACE as 'being Jet'. Jet is deprecated, ACE has a future. Agreed? – Georgeanngeorgeanna

Jet 4.0 is included as part of Windows 2000, XP, VIsta and 7. YOu don't need to install it. So how can SQLLite be easier to install? Granted copying a DLL isn't a difficult install. – Contrapositive

"Jet is deprecated" does not mean "Jet is not supported". Jet is part of Windows, it is supported just as much as any other part of Windows. "Deprecated" does mean Microsoft discourage its use and they would rather not enhance it. – Rampant

Here's the page on the Access official blog where they confirm that ACE is Jet. It also says Jet is a Windows system component: that means it is supported. blogs.msdn.com/b/access/archive/2005/10/13/480870.aspx – Rampant

Answer 6

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.

Kookaburra

Given that MS Jet 4.0 is included as part of Windows 2000, XP, Vista and 7 you don't actually need to include the drivers with your install. – Contrapositive

Answer 7

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.

Stripteaser

Jet does not have "strange corruption issues." When improperly managed, files can corrupt. The causes are quite predictable, in fact, and easily avoided. – Moslemism

"When improperly managed, files can corrupt. The causes are quite predictable, in fact, and easily avoided" -- I think the point is that more sophisticated engines manage things properly so that you don't have to. – Georgeanngeorgeanna

Any corruptions issues on a database, manageable or not, is reason enough not to consider it. – Stripteaser

None of my clients experience corruption with their Jet databases. It's been literally years since any of them had any problems. – Moslemism

@Moslemism I have experienced many MS Access data corruption situations in multiuser mode, especially over SMB. Now, this is not relevant in a single user environment. As far as the 2GB limit is concerned, it is ennoying. I had a situation where the database grew and hit this limit quickly when bulk inserting data. Compacting would cut the size to a third or less but that was not practical. What does 2GB represent today? – Vocalize

Answer 8

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

Suture

Answer 9

If you program well in Python, Pearl, Lua or many other languages, SQLite would be the natural choice.

Portsalut