SQLDelight in Android: Getting Started

Transcript: 05. Understand SQLDelight's Type System

Let’s take a closer look at the Kotlin code that SQLDelight generated from the script files we have created previously.

Here is the Bug table - SQL creation statement on the left, the generated Kotlin code on the right. Notice how the SQL types are transformed on the Kotlin side: The bugId column uses the INTEGER type in the database, which is represented as a Long in the Kotlin class.

As for the name and description columns, SQLDelight turns these from TEXT to String. You can also see how column nullability is recognized: The NOT NULL constraint causes the generated type to be String for the name column, but nullable String for the description column, which doesn’t have the same constraint.

For every SQL type, there is a corresponding mapping to Kotlin built into SQLDelight out of the box. This table shows all of them!

There is a curiosity near the bottom of this table, where we can see the same SQL type being mapped to a different type in Kotlin. This is not standard SQL syntax, but an extension introduced by SQLDelight itself.

Consider it a custom constraint for certain columns - it doesn’t have an official name, so let’s call it the “type cast constraint”. For example, it allows us to create an INTEGER column in the database that is not treated as a Long in Kotlin, but actually an Int!

This works for other numerical primitves as well: check out this conversion from REAL to Float, for example. I really like this one for boolean values, too: it’s an INTEGER column with either 0 or 1, depending on the state of the boolean.

The Kotlin code doesn’t have to care about how this information is stored in the database, though! Let’s apply some type cast constraints to our own tables!

We want to express the fighting skills of our bugs through the BugAttributes class, which has a corresponding table in the database. The attack & defense stats should be integer values, as there is no need for them to be Long.

Let’s open the script file for bugAttributes and add the type cast constraint to the attack and defense columns. The IDE plugin will detect the change immediately and re-generate the affected class.

CREATE TABLE bugAttributes (
  attributesId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  bugId INTEGER NOT NULL,
  size TEXT NOT NULL,
  weight TEXT NOT NULL,
- attack INTEGER NOT NULL,
- defense INTEGER NOT NULL,
+ attack INTEGER AS Int NOT NULL,
+ defense INTEGER AS Int NOT NULL,

  FOREIGN KEY(bugId)
    REFERENCES bug(bugId)
    ON DELETE CASCADE
);

Look at this: The Kotlin has already updated the fields from Long to Int! Really cool. Let’s do the same thing for the inCollection table. This one has a quantity column, which can become an Int as well.

CREATE TABLE inCollection (
  collectionId INTEGER NOT NULL,
  bugId INTEGER NOT NULL,
- quantity INTEGER NOT NULL,
+ quantity INTEGER AS Int NOT NULL,

  FOREIGN KEY(collectionId)
    REFERENCES collection(collectionId)
    ON DELETE CASCADE,

  FOREIGN KEY(bugId)
    REFERENCES bug(bugId)
    ON DELETE CASCADE
);

I would like to update one more column: The creation timestamp of the Collection table. Timestamps like this are usually stored in seconds since 1970, to avoid problems with timezones and things like that.

The thing is, I would love to use an actual object to represent this instance in time in the Kotlin code instead of having to deal with the raw seconds. For instance, there is the Java Time API with a class called ZonedDateTime, which would be perfect for this purpose.

Unfortunately, SQLDelight doesn’t understand this type… unless we tell it how it works! That’s right: It’s possible to extend the type cast system of SQLDelight so that a database column can be mapped to anything on the Kotlin side.

It’s possible to add import statements at the top, just like the ones in Java or Kotlin code! We can import the ZonedDateTime class here and use this type in the column definition. By the way, the SQLDelight plugin is smart enough to propose the import on its own. How awesome is that?

+import java.time.ZonedDateTime;

CREATE TABLE collection (
  collectionId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  creationTime INTEGER NOT NULL,
+  creationTime INTEGER AS ZonedDateTime NOT NULL,
  name TEXT NOT NULL
);

When creating a custom column type, we have to make sure to teach SQLDelight how to convert between SQL and Kotlin, so we need to do a little manual work to finish this.

It’s a similar mechanism to JSON libraries, where the developer needs to teach the program how to convert between two domains: The SQLDelight version of this concept is called ‘column adapters’.

Let’s open up the DatabaseRepository class - it’s the place where the Database object is created. Immediately, we notice the error flagged by the IDE: A parameter is missing.

The code generator realized that it requires more knowledge about the Collection table, so we need to provide this information to him in the form of a column adapter. Let’s add names to the parameters of the Database constructor, then create a value for the collectionAdapter parameter.

class DatabaseRepository(driver: SqlDriver) {

-    private val database = Database(driver)
+    private val database = Database(
+        driver = driver,
+        collectionAdapter = 
+    )
}

Make sure to use the correct Collection class here - we don’t want the standard Kotlin class, we want our own! SQLDelight generated an empty adapter class, which will be provided with the custom adapters for each of the columns that it cannot figure out on its own.

Here, it’s only the creationTime column, which will we will now connect to the column adapter for the ZonedDateTime. Leave this statement unfinished for now, we’re going to come back to it.

class DatabaseRepository(driver: SqlDriver) {

    private val database = Database(
        driver = driver,
+        collectionAdapter = Collection.Adapter(
+            creationTimeAdapter = 
+        )
    )
}

Scroll down to the end of the DatabaseRepository file and create a new private val named ‘zonedDateTimeAdapter’. Initialize it with an anonymous implementation of the ColumnAdapter interface, like so.

private val zonedDateTimeAdapter = object : ColumnAdapter<ZonedDateTime, Long> {
}

This interface has two type parameters: The first one is the Kotlin type, which is ZonedDateTime in our case. The second one is the raw SQL type, which is Long as the database uses a numerical column for the timestamp. Hover over the error here to let the IDE fill in the missing methods that we need to implement.

private val zonedDateTimeAdapter = object : ColumnAdapter<ZonedDateTime, Long> {
    override fun decode(databaseValue: Long): ZonedDateTime {
        TODO()
    }

    override fun encode(value: ZonedDateTime): Long {
        TODO()
    }
}

If you have ever written custom code for libraries like Gson or Moshi, this will look familiar to you. There are two methods, one for each direction of the conversion. The decode() method goes from database to Kotlin, and encode() is the other way around.

Let’s start with the decoding: so we have a timestamp in the database, the number of seconds since 1970, and want to transform this into an object. It depends on your use case how to do this conversion, but here is the solution for ZonedDateTime in the Java Time API.

+private val UTC = ZoneId.of("UTC")

private val zonedDateTimeAdapter = object : ColumnAdapter<ZonedDateTime, Long> {
    override fun decode(databaseValue: Long): ZonedDateTime {
+        return ZonedDateTime.ofInstant(Instant.ofEpochSecond(databaseValue), UTC)
    }

    override fun encode(value: ZonedDateTime): Long {
        TODO()
    }
}

Use the factory method ‘ofInstant’, create an Instant from the number of seconds and associate this number with the UTC timezone, just like this. We can even move out the timezone declaration into its own private variable for reusability.

Decoding is done! Next up: The other way around, from object to database value. This one is very straightforward in our case, since ZonedDateTime has a convenient method to convert itself into a numerical UTC value.

private val UTC = ZoneId.of("UTC")

private val zonedDateTimeAdapter = object : ColumnAdapter<ZonedDateTime, Long> {
    override fun decode(databaseValue: Long): ZonedDateTime {
        return ZonedDateTime.ofInstant(Instant.ofEpochSecond(databaseValue), UTC)
    }

    override fun encode(value: ZonedDateTime): Long {
+        return value.toEpochSecond()
    }
}

This is all the code we need to make SQLDelight understand the new type. Scroll back up to the Database creation and pop in a reference to the adapter variable from below. Done and done!

The generated Collection class has a field of type ZonedDateTime, which maps to an integer column in the database below.

class DatabaseRepository(driver: SqlDriver) {

    private val database = Database(
        driver = driver,
        collectionAdapter = Collection.Adapter(
+            creationTimeAdapter = zonedDateTimeAdapter
        )
    )
}

One thing we can now change in the code itself is how the timestamp is presented to users. Open the CollectionListAdapter class by searching for it in the UI package, then scroll down to the bindViewHolder method.

Change line 93 so that it doesn’t call toString() on the creation timestamp, but use a proper method instead. To pretty-print the timestamp for this TextView, use the format() method and pass in the formatter, which is a local variable that already exists up here.

This will make it so the timestamps look nice and tidy once we created some content for the database.

-holder.creationTextView.text = item.creationTime.toString()
+holder.creationTextView.text = item.creationTime.format(formatter)

I think it’s time to create some queries, y’all!