database - H2 vs SQLite for java swing, which one should I use?

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post .

Closed 8 months ago .

I'm working on a client's project with an in memory database, I used H2, but after googling the size limit for each one i got 4TB for H2 and 281TB for SQLite, and after more research I found that SQLite is not a replacement for PostgreSQL and that H2 is better in my case since it is a free SQL database written in Java. both H2 and SQLite databases worked fine with me, my only concern is the database size limit witch one should I use?

H2 and SQLite are both embedded databases. The database engine runs in the same process as your application. They are made for low traffic, small to midsize databases. If you're concerned about size limit, then you need to run your own tests. Create a database with the expected size + a safety buffer and run your queries and inserts, etc. Then you will see, if it works for you, If not then you need a real database server like PostgeSQL or MariaDB. – vanje Apr 6 at 11:12 Also keep in mind for privacy. SQLite does not support username/password when you write in a file. At least back then when I used it. While H2 gives this choice. If the data of your client is in a .db file, this file might be better if it was somehow locked and only read by your application (again, this was my case few years back). – George Z. Apr 6 at 12:52 @tquadrat Actually, 4 TB, not GB, is the size limit of H2 . The OP is incorrect in stating 4 GB. So, “more than sufficient” indeed. – Basil Bourque Apr 6 at 17:53 @BasilBourque – Thanks for the heads-up, I have not checked the figures, because for my understanding, an in memory database is one that lives only in memory and will not be persisted to hard drive. And for such a DB, 4 GB should be sufficient in most cases – who runs machines with terabytes of memory and executes Java programs on them, allocating terabytes of memory through the JVM?? – tquadrat Apr 7 at 8:25

The creator of SQLite , D. Richard Hipp , has explained the product was not meant to be a big serious database server but rather an alternative to applications inventing their own file formats. To quote :

“SQLite is not a replacement for PostgreSQL. SQLite is a replacement for fopen() ”

That said, SQLite is a proven, well-worn, and incredibly successful product.

You said:

a lot of people decided to switch to SQLite because of data corruption in H2

That is an audacious statement to make, and an irresponsible one as well if you cannot back it up with some evidence. I suggest you edit your Question to be avoid being a gossip-monger, and possibly libelous.

H2 Database Engine is one of the most popular pure-Java database products.

Regarding comparisons of SQLite and H2, the most obvious consideration is that SQLite is native code and H2 is pure Java. If you have control over the deployment machine(s), that may not be a problem. But if you want to ship a Java app without worrying about which native implementation of your database engine to bundle, then H2 will be more convenient.

Other points of comparison would include the fact that H2 is a more complete implementation of SQL, with many more database features than SQLite. The most basic of those is that H2 has a wealth of data types while SQLite has very few types . H2 offers many more relational features. But then if you have no need for those features, SQLite might do.

You said:

the size limit … 4GB for H2

You are incorrect: 4 TB ( terabyte ), not 4 GB ( gigabyte ).

To quote the Limits and Limitations section of H2 documentation:

Database file size limit: 4 TB (using the default page size of 2 KB) or higher (when using a larger page size). This limit is including CLOB and BLOB data.

The documentation mentions an exception for the obsolete FAT or FAT32 file systems because those systems are constrained to 4 GB files.

Again, I suggest you edit your Question to eliminate such falsehoods.

As for the purpose of your Question, choosing an in-memory database product, H2 will suffice if you are deploying to a machine with under 4 TB of RAM.