I’ve used Cashapp’s SQLDelight for years in my Android apps. Unlike other ORM libraries, which create an abstraction that allows you to access databases in native code and generates the SQL to access the data for you, SQLDelight embraces SQL as a first class language, recognizing it’s optimizations for defining and querying data, and allows you to write queries and data definitions and generates consuming code based on those.

Over the last few years SQLDelight has been transformed from a nice Android ORM that integrates nicely with AutoValue into a mutliplatform option to access and manage your data without having to learn another query abstraction.

One of the most valuable changes that was made was to add support for database dialects other than SQLite. Currently, SQLDelight supports MySQL (or MariaDB), PostgreSQL and H2. This makes SQLDelight a great choice for server code in addition to mobile apps.

Gringotts⌗

A few weeks ago I spent an evening building a simple currency conversion API that I call Gringotts. It allows you to get daily exchange rates for several currencies based on data from the European Central Bank, and also includes an endpoint to convert values between arbitrary currencies.

» curl https://gringotts.dev/api/convert?from=cad&to=usd&amount=44.93
{
  "date": "2020-08-21",
  "result": 33.97681488144959
}

This simple service allows us to convert purchase prices to US Dollars for analytics events at Pixite. It’s fairly low traffic, and doesn’t require a high SLA since it doesn’t power core app functionality, so this made a good candidate for an evening micro-service project.

SQLDelight for the Server⌗

Though SQLDelight was originally designed to work with SQLite, that embedded database isn’t well suited to server environments. It’s biggest limitation is that it’s designed to be used by a single consumer (i.e. one app), so doesn’t support many features like distribution, sharding and threading (technically it does support that last one, but it’s a pain to use and doesn’t appear to be in common circulation).

As mentioned above, SQLDelight now lets you choose from a number of server-grade database dialects. For Gringotts I chose to go with MySQL because that’s the server I’m most familiar with. Once you’ve selected a server dialect, all you need to do differently from the standard SQLDelight configuration is to add a dialect property to the database configuration in your gradle build file.

plugins {
  ...
  id("com.squareup.sqldelight")
}

sqldelight {
  database("Database") {
    packageName = "gringotts.db"
    dialect = "mysql"
  }
}

dependencies {

  val sqldelightVersion = "1.4.2"
  implementation("com.squareup.sqldelight:runtime-jvm:$sqldelightVersion")
  implementation("com.squareup.sqldelight:jdbc-driver:$sqldelightVersion")
  implementation("com.squareup.sqldelight:coroutines-extensions:$sqldelightVersion")

  // Hikari JDBC connection pool
  implementation("com.zaxxer:HikariCP:3.4.5")

  // MySQL drivers
  implementation("com.h2database:h2:1.4.200")
  implementation("org.mariadb.jdbc:mariadb-java-client:2.1.2")
  implementation("mysql:mysql-connector-java:8.0.21")
  implementation("com.google.cloud.sql:mysql-socket-factory-connector-j-8:1.0.16")
}

As you can see from my build.gradle.kts snippet above, I also include several different JDBC drivers. This allows for ultimate flexibility in hosting choices, and I chose include these so that I could host Gringotts on my own private server using Docker Compose and either MariaDB or MySQL, on Google Cloud Run connected to a Cloud SQL instance, or using a local file based H2 database for development and tests.

An Embedded Development Database⌗

One of the biggest pain points of backend development, at least for me, is interfacing with databases. Ruby on Rails provides a nice ability to use a simple SQLite database for local development and testing, and swap in a full featured database server like PostgreSQL or MySQL in production environments.

While SQLDelight doesn’t offer query translation that would support multiple types of databases, we can have an equally convenient experience by using an embedded H2 database in compatibility mode.

For Gringotts, I decided to use SQLDelight’s MySQL dialect, since it’s a ubiquitous database option with which I have experience, and set up my Ktor application config file to default to a local H2 database file in MySQL compatibility mode.

database {
  connection = "jdbc:h2:file:./.database/gringotts;MODE=MySQL"
  connection = ${?DATABASE_URL}
  username = ${?DATABASE_USER}
  password = ${?DATABASE_PASS}
}

This makes development far easier, since there is no configuration required to start working with a development database.

From application.conf to SQLDelight Database⌗

As you can see above, the database configuration is provided via Environment variables to the application’s application.conf file. This is the standard configuration mechanism for Ktor, and allows the user to easily configure portions of the default config, or they can choose to override the entire file at the command line (or in your docker-compose.yml file) using the -config switch.

While SQLDelight generates Kotlin classes and interfaces from your SQL files, it has no knowledge of Ktor configuration files, so it’s up to us to parse the configuration and set up the database.

Following a common convention in Kotlin, I use a capitalized top-level function to parse the application config and instantiate my database. While this is truly a factory function, it feels like a constructor at the use site. This seemingly irrelevant fact makes for some nice ergonomics, since it allows me to create a platform-specific factory for my platform-agnostic database without the need to remember which standard-lacking factory naming scheme was chosen for this project.

fun Database(app: Application): Database {
  val dbConfig = app.environment.config.config("database")
  var connectionUrl = dbConfig.property("connection").getString()

  // If this is a local h2 database, ensure the directories exist
  if (connectionUrl.startsWith("jdbc:h2:file:")) {
    val dbFile = File(connectionUrl.removePrefix("jdbc:h2:file:")).absoluteFile
    if (!dbFile.parentFile.exists()) {
      dbFile.parentFile.mkdirs()
    }
    connectionUrl = "jdbc:h2:file:${dbFile.absolutePath}"
  }

  val datasourceConfig = HikariConfig().apply {
    jdbcUrl = connectionUrl
    dbConfig.propertyOrNull("username")?.getString()?.let(this::setUsername)
    dbConfig.propertyOrNull("password")?.getString()?.let(this::setPassword)
    dbConfig.propertyOrNull("poolSize")?.getString()?.toInt()?.let(this::setMaximumPoolSize)
  }
  val dataSource = HikariDataSource(config)
  val driver = dataSource.asJdbcDriver()
  val db = Database(driver)
  app.environment.monitor.subscribe(ApplicationStopped) { driver.close() }

  return db
}

This method allows our generated database to feel as though it was made for Ktor at the call site.

fun Application.main() {
  val db = Database(this)
  ...
}

What About Migrations⌗

When using SQLDelight in a mobile app in which there is a single user (at a time, at least) it makes sense to perform migrations in the app code since you don’t have to worry about conflicts or inconsistencies between the version of the app in use and the version of the database on the device. This doesn’t translate as well to the server, unfortunately.

For Gringotts, which requires a single database and a single application server with short lived sessions this isn’t a big deal, so I currently perform migrations in my runtime code, but I know that won’t scale. When I deploy a new version of the app code, I can’t let it start migrating data while user’s might still have active session on the old version of the app which expects old data structures.

I’ll admit that this isn’t something I’ve figured out yet, but I have some ideas for seemingly reasonable approaches. I’m thinking that migrations will need to be a manual process in which I point app servers at a database that has the correct schema version for that server via the configuration above. For a short time, while rolling out a new version of the app, I’ll have two versions of the database schema active until all traffic has been migrated to the new version, at which point I can delete the old databases.

Again, I haven’t solved this part yet, but I’m confortable that it lies outside of the scope of SQLDelight and is a solved problem, so I’m happy to cross that bridge when I get there.

And That’s All⌗

Other than these points above, my usage of SQLDelight is almost exactly the same as it would be in an Android app. I create .sq files that contains my data definitions and queries and use the generated interfaces and classes to access and mutate my data.

I’ve really grown to enjoy SQLDelight’s approach to letting you write your queries and generating sane code from them, instead of requiring you to learn their abstract for the query language, and constantly translate in your head to understand what’s going on. Being able to bring SQLDelight with me to the server is a really nice step, and I’m excited to continue down this road.