JDBC SQLite Tips

Intro

SQLite is probably my favorite “flat-file” format to work with for storing data. I mean, you would hope so, right? It is a database after all. Anyways, as much of a pleasure as it is to work with, it does come with plenty of its own pitfalls, especially working with it in highly dynamic systems of any noticeable scale. Below I intend to document some tips/advice/wisdom I have garnered over time from working with it. But first, I want to address a couple of common questions I hear from the less experienced.

FAQ

Why Not Just Use JSON?

Hey! Funny one. I used to think like this too, and in fact quite a lot of 2009scape’s data is still stored in JSON. The problems with JSON rear their head when you start working with frequent modifications to that data where making sure that data persists properly is key. This means you would need to save often, and serializing your data to JSON and then writing it to a file is expensive, and each of these operations starts to add up. You also lose out on the benefits of transactions. There are definitely some usecases where JSON would work, but in many of my cases, it simply doesn’t.

Why Not Use A “Real” Database?

Another funny one. Real databases have more overhead and are harder to justify, not to mention being less portable. The only time you can really justify using a full fledged database server over SQLite is when you either a) have a LOT of data to store, or b) need to acces that data from multiple services concurrently. This obviously has its usecases, and frequently! There are simply many equally common scenarios where SQLite is more than adequate with significantly less overhead.

The Actual Tips

Clean Up Connections And Statements.

The JDBC isn’t super great. I mean it’s very convenient and very helpful, but it can lead to lots of memory leaks if you don’t keep an eye on what’s happening. If you notice your JDBC-enabled program using up significantly more memory after you begin using SQL queries more often, then the problem is in the connection. The JDBC connection internally caches statements and their result sets, as well as the query strings themselves, until either the statement is manually cleaned up (with statement.close()) or the connection is cleaned up (also with .close()).

If you are using Kotlin (which, in my opinion, you should be), there’s a really nice way of making sure your active connection/statement is closed automatically. Java has this as well of course, it’s just more verbose and less wieldy.

In Kotlin, simply create your resource (i.e. val myConn = connection.prepareStatement) and then execute all your code that actually uses that resource in a use block, like so:

val conn = MyDatabaseManager.getConnection()
conn.use { 
  it.prepareStatement(blah blah blah)
}

This will ensure that the connection is automatically cleaned up.

Re-use Connections When Possible

This might seem like it contradicts the previous tip, but it really doesn’t. Connections are expensive to create, both for proper SQL servers and for SQLite databases. You do need to clean them up, but you shouldn’t be cleaning them up and then creating new ones every time you need a connection. This is also bad practice.

The best thing to do, from my experience, is to wrap all your SQLite query calls inside of a helper lambda that reference-counts your connection, and then cleans it up when no one else is using it anymore:

class MyAwesomeDatabaseManager {
  private var activeConnection: Connection? = null
  private var connectionRefs = 0

  private fun getConnection(): Connection {
    if (activeConnection == null || activeConnection.isClosed) {
      activeConnection = //get a new connection here
    }
    return activeConnection
  }

  fun runQuery(logic: (conn: Connection) -> Unit) {
    connectionRefs++
    
    val conn = getConnection()
    logic(conn)
    
    if (--connectionRefs <= 0) conn.close()
  }
}

You would then run your SQL queries like this:

fun doesALotOfSQLStuff() {
  MyAwesomeDatabaseManager.runQuery { conn -> 
    for (thin in aMillionThings) {
      val stmt = conn.prepareStatement(MyDefinitelyGreatQuery)
      stmt.use {
        //do things
      }
    }
  }
}

This would ensure that when you’re done with that connection that it automatically gets cleaned up and a new one is able to be created automatically the next time it is needed. The benefit this has over a simple use statement is that you can nest multiple methods that request a connection without fears of locking up the database. This will re-use the same connection for all of those methods and then clean it up when you’re done.

Don’t Create Too Many Connections At Once

One of the noticeable drawbacks of SQLite over other SQL solutions is that it has a much lower default concurrent connection limit, and it should. It’s a flat file, after all. Using my solution above actually really helps with this issue, if not remove it as a concern entirely.

Be Mindful Of Threads

This one became particularly relevant to me as I needed to find a thread-safe and efficient way to handle my SQLite operations for the asynchronous market backend for 2009scape.

If you have a lot of other non-sql things that happen on your main thread and you have to stick within a time limit, do not put your SQL logic on the main thread. Instead, offload it to another dedicated thread if possible. File I/O is expensive, and you should almost always avoid doing it on your main thread if possible. One really convenient solution to this problem is to use something like a LinkedBlockingDequeue to queue up some “tasks” for your SQL thread to take on when it gets time.

The big benefit of LinkedBlockingDequeue over other options is that, yes, it blocks the thread until something is available in the queue to process, meaning you aren’t using CPU time running an infinite loop of NOOPs. It is also reasonably thread-safe, due to it being backed by a LinkedList rather than an array.

If you don’t feel like you do SQLite operations often enough to justify a dedicated thread, consider using a Kotlin Coroutine alongside a dispatcher instead.

Below, I offer an example implementation that should give you the right idea:

object SQLiteTaskScheduler {
  private val taskQueue = LinkedBlockingDeque<SQLiteTask>()

  fun init() {
    Thread {
      Thread.currentThread.name = "SQLiteTaskScheduler"
      while (true) {
        val task = taskQueue.takeFirst() //blocks here until we have a task in the queue ready to execute. The thread is "sleeping" right now.
        task.logic()
      }
    }.start()
  }

  fun pushTask(logic: () -> Unit) {
    taskQueue.addLast(SQLiteTask(logic))
  }
}

data class SQLiteTask(logic: () -> Unit)

This moves all the actual SQLite-related logic off to its own dedicated thread provided you wrap it in

SQLiteTaskScheduler.pushTask {
  //do stuff
}

Postamble

I intend to revisit this post in the future with any new tips (or adjustments to old tips) that I happen to accumulate in my time working with SQLite. I really hope some of my advice here was able to help you out of a bind! I wish someone had been there to give it to me.