I wrote earlier about how to backup a small SQLite database.

That solution works well for small databases, but can become impractical for larger databases. In particular if the machine with the database has not enough disk space for another copy of it, and you want to backup to another machine.

One option is to use sqlite3_rsync, but I could not get that to work, it kept running out of memory. So I found another way, dump the database to text and gzip it. Combined with ssh, you can make a backup to a remote machine without storing anything extra on disk on the database machine. Run this on the machine where you want to have the backup:

As I mentioned earlier, it’s straightforward to use SQLite as the relational database in Go applications. Your data is stored in one single file with a well-defined format and there is no hassle with installing and configuring a separate database.

However, for any serious application you most likely want backup of your data. If your dataset is not very large, and it is OK to lose updates since the last backup, there is a very simple option. Use the sqlite3 command line tool:

I am using PostgreSQL from a Kotlin (JVM) application with the pgJDBC driver.

According to the documentation, you can get results from a query based on a cursor to avoid loading the whole result set into the application’s memory at once by calling the setFetchSize() method with a positive value on the Statement before issuing the query.

I had a non-trivial query which generated a lot of rows (several thousands), and I only needed the first hundred or so. So I hoped I could activate the cursor, iterate through the result set until I had got all the data I needed and then close it long before reaching the end.

I have written about using an embedded database like HSQLDB or H2 for testing Java applications. This can still be useful (although JavaEE is not so popular any longer). But even though H2 claims to emulate PostgreSQL and several other popular production SQL databases, sometimes your application uses very specific features of a particular database and you need to test against the real thing to be really sure your application works correctly.

This can conveniently be done using Testcontainers, which run an instance of the real database in a Docker container for your tests. The downside is that this can be significantly slower than an embedded in-memory database like HSQLDB or H2.

The MySQL database can store large chunks of binary data (up to 1 GB) using the BLOB data types.

However, this does not work well if you access the MySQL database from Java (or any other JVM based language) using the MySQL JDBC driver.

The JDBC API supports an efficient stream based way to handle BLOBs, but the MySQL JDBC driver does not implement this properly. It works, but it will buffer all data in memory in a way which is very inefficient and can make your JVM run out of memory if the BLOBs are large.