What is Reddit's opinion of H2 Database Engine?

From 3.5 billion Reddit comments

sbt is running your tests in multiple threads, you're sharing the connection (db) across threads, and SQLite's JDBC driver sucks at this. Three problems, three solutions:

1. parallelExecution in Test := false
2. Make db a var and set it in before, i.e. use a connection per test.
3. Use a better database/JDBC driver. I suggest H2.

Hope this helps!

Update: Since you presumably won't use SQLite in production but will use threads, including opening new database connections in a multi-threaded environment, I recommend option 3), and, of course, deeper testing with your production DB (MariaDB, PostgreSQL...) and its JDBC driver.

The database you want is H2, not Derby, and it will make it easy to do this. There are clear tutorial instructions on their website for much of this.

It's got clear and easy instructions for running in embedded mode, and a helpful fast web console with quickstart instructions. You can launch the DB and web console just by double-clicking the right file and start playing with a DB right away (or if you want to explore it separately from your catalog app).

As far as the file: the DB can be stored in a single file with a couple small marker files in the same folder. The location of the database can be configured easily, and the JDBC url given the the driver sets the storage location and mode. If the DB location is set to point to a Dropbox folder shared between computers (and configured on both), then you can see the same database on both -- though you will have problems if you try to use it with both at the same time.

If you want to turn this into a full web app, then you can run the application in AWS on a t2.micro and store the DB files on the server.

Why H2 and not Derby? Derby lacks a lot of normal RDBMS features and many of the useful H2 features for fast setup. It is also slow, larger to download, and has terrible documentation. I tried to use it for a similar project some years ago, and eventually gave up when I stumbled on H2 and fell in love. Liked H2 enough to contribute to the community & project, and even have a few small contributions in the codebase.

I try to avoid stored procedures like the plague, if I can. The second time I spent 3< hours chasing a bug, only to find out someone had stored some business logic in a stored procedure, I knew I was done with those. If you already have the work done to shove data in, have you considered using an in-memory option like H2 (http://www.h2database.com/html/main.html)? I use it for exactly that purpose. Then, you can run all of your tests in isolation. Once the test is done, H2 goes away with your dummy data. There are options for NoSql db's too, such as https://github.com/flapdoodle-oss/de.flapdoodle.embed.mongo

Most people aren't aware of how powerful embedded SQL DBs are for the initial deployment of a minimum viable product on a single server.

• H2 (Java) in embedded mode has phenomenal performance, includes all the ACID guarantees the mainline RDBMSes do, and has better SQL compliance. It scales rather well, and is quite full-featured. (Yes, it has a server mode, but the original author is on record as not recommending H2 for large-scale production cases.)
• SQLite (C-based, bindings available for many languages) is also extremely fast, and has a test harness that is widely considered the best of any project. There are some feature limitations and all writes are single-threaded, but many small-to-medium web applications do well to start out with SQLite despite these restrictions.

Absolutely, 100% do DB & web server on the same system (using an embedded DB) for your first deployment on small-to-medium scale apps, where you don't have existing systems in place.

If you're outgrowing this, availability, security, data integrity, and performance all dictate moving to a dedicated DB server (which is vertically scaled) with hot standby. This is paired with a fleet of horizontally-scaled web servers behind a load balancer. Going to NoSQL is not a general purpose solution, because you lose all the data integrity guarantees a relational DB offers. Additionally you run smack against the CAP theorem if you try to share the persistent state among hosts. Hot standby suffers from CAP too, but in practice it is far more reliable than multi-master solutions. With only two servers you can always take one out of rotation manually for troubleshooting and run on the standby.

H2 is the most commonly used in-process database in the Java ecosystem. It's pure Java (so no native libraries), very feature complete, can emulate other databases like Postgres. It can keep data in memory (so it's gone when the program exists, used when testing) and on disk.

The site I linked has examples. You'd still use JDBC too, so you could use the same code (mostly) if you'd ever move to a different database.

/u/bobberton made the great suggestion ofH2, which is a great Java based in-memory SQL server. In addition to being platform agnostic, it would remove all the install/configuration requirements. In short, it's ideally suited. (I did edit my original several times and it does include mention of H2 now).

There's no query writing involved, I'll agree that setting up a mySQL server is a definite impediment - even if you don't hit any firewall issues with it.

Looks like dbeaver is using jdbc csv driver which is using the H2 engine

> H2 database as an SQL engine and supports the most part of ANSI/ISO SQL grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.

https://sesamesoftware.com/jdbc-database-drivers-csv/jdbc-database-drivers-csv-doc/#Supported%20SQL%20Syntax

http://www.h2database.com/html/main.html

• H2 DB
• Apache Derby
• SQLite and no, it's not only for Android. It exists for most programming languages and most operating systems

Alternatively:

• Java's serialization
• Files in XML, JSON, CSV, etc. formats

An embedded SQL database is what you need. I'm partial to H2, but there's also Derby and HSQLDB. You get the power of SQL without having to run a large SQL server like MySQL or Postgres.

Performance can suffer using an embedded DB, but for a single user desktop application that's not likely to be a problem.

Sort the array by date. (Also your probably better of using ArrayList rather than a plain old Array)

OR

Try storing the data in something like H2 so you can use SQL to query/sort/group it without having to write too much code to do it. http://www.h2database.com/html/main.html

>Well, thing is, it's a desktop application that needs to save a lot of data... what better alternative than SQLite for such requirements?

OK. In that case, I still recommend H2, or avoiding threads like the plague.

Oh, it is a great tool for learning SQL for java coders - and for creating databases embedded into your java application - and for testing.

Simply download it here: http://www.h2database.com/html/main.html

Or from Maven repo.

There is h2...jar file.

Run it with java -jar h2...jar command.

It starts its database server and simultaneously opens its web-interface in your browser, so you can connect to in-memory database, run queries like CREATE TABLE... and INSERT INTO... etc.

Note quickstart guide here:

http://www.h2database.com/html/quickstart.html

Main problem with Postgres is that it could look not as handy as H2 or MySQL at first glance and it could be bit tougher to start with unless you have some experience with databases. However if you do not use some specific functions it is easy to write your SQL so that migrating between these databases needs only changing url link of the server.

Check out the try-with-resources pattern, it's a lot cleaner and automatically closes resources like output streams for you.

Regarding writing user input to a text file, that'll certainly work, but it's rather dependent on your specific use case and requirements. Other options include using a database (I like H2) or writing structured data, like a single JSON file with all your user's input and settings, and reading it back with a library like Gson.

The use case is that if you have data that is not stored in firebase, and need to transform data coming from the client, and also the data not stored in firebase, then you cannot do that on the client. The spring api in this case would serve as a middle man between the client and firebase, for the purposes of obtaining this extra piece of data required.

Also, the main databse in this example is h2, an in memory database. The firebase thing was just an example of how to do it if you wanted to.

The way this api is set up, firebase, and h2 are implementation details, and the client is completely encapsulated from both of them. The storage mechanisms can be swapped out/in depending on the use case

When I need unit testing for a db I usually opt for an in-memory mock. For no-sql databases a hashmap is often sufficient. For SQL databases I start with H2DB using whatever compatibility mode is required. It's not integrated with any particular testing framework, which I happen to like.

Pull the table into an in-memory H2 database at startup, and inject the H2 data source into your DAO. (If you do the queries right, and don't use any non-standard SQL, then you'll be able to use the same DAO with both data sources.)