TL;DR : The easiest way I found to test my database service is to mimick Supabase using Docker Compose and Test Containers. Here's the code

In case you don't know it, I'm a big fan of Supabase. I love that they're a viable alternative to Firebase. I love that they're built on top of Open-Source pieces. I love how innovative they are, and how much they give back to the community. And as you already know it, I love Kotlin as well.

Lately, I've been building a side-project which consists of a Ktor webapp, and uses Supabase-Kt to communicate with the database. I've been looking into ways to test my Kotlin component that interacts with the database, and it's been harder than expected.

In this article, I'll dive into several methods I've been looking into and why I finally decided to go for a Docker Compose / Test Containers solution. You can check the example repository here AND THE FINAL CODE HERE.

What I want to achieve

Let's imagine a minimal code example that contains a Person data class, and wants to save/fetch persons via a SupabaseClient. It can look like this:

@Serializable
data class Person (val name: String, val age: Int)

@Serializable
data class ResultPerson (
    val id: Int,
    val name: String,
    val age: Int,
    val timestamp: String
)

fun main() {
    val supabaseClient = createSupabaseClient(
        supabaseUrl = "",
        supabaseKey = ""
    ) {install(Postgrest)}

    runBlocking {
        savePerson(listOf(Person("Jan", 30), Person("Jane", 42)), supabaseClient)
    }
}

suspend fun getPerson(client: SupabaseClient): List<ResultPerson> {
    return client
        .postgrest["person"]
        .select().decodeList<ResultPerson>()
        .filter { it.age > 18 }
}


suspend fun savePerson(persons: List<Person>, client: SupabaseClient): List<ResultPerson> {
    val adults = persons.filter { it.age > 18 }

    return client
        .postgrest["person"]
        .insert(adults)
        .decodeList<ResultPerson>()
}

The SQL definition of our table looks like this

create table
    public.person (
                    id bigint generated by default as identity not null,
                    timestamp timestamp with time zone null default now(),
                    name character varying null,
                    age bigint null
) tablespace pg_default;

We want to be able to test that our functions behave properly. For the sake of this minimal example, I've decided to filter all non adults, but you can imagine any other use case where the functions contain some business logic.

First attempt : Mock Supabase

When unit testing code using third parties that I don't have control over, my first reflex is to try and mock it.

It stopped being fun really quickly. The Supabase-Kt library is making use of a lot of inline function and I ended up having to mock more and more parts of the library and never managed to get a functional tests.

The short version is that because they are as the name indicates, inlined, inline functions cannot be mocked in Kotlin. So that was the end of that experiment

The MainKtTestMock file of my example repository reflects that attempt.

class MainKtTestMock {

    private lateinit var supabaseClient : SupabaseClient

    @BeforeTest
    fun setUp() {

        supabaseClient = mockk<SupabaseClient>()
        val postgrest = mockk<Postgrest>()
        val postgrestBuilder = mockk<PostgrestBuilder>()
        val postgrestResult = PostgrestResult(body = null, headers = Headers.Empty)

        every { supabaseClient.postgrest } returns postgrest
        every { postgrest["path"] } returns postgrestBuilder
        coEvery { postgrestBuilder.insert(values = any<List<Path>>()) } returns postgrestResult
    }

    @Test
    fun testSavePerson(){
        val randomPersons = listOf(Person("Jan", 30), Person("Jane", 42))

        runBlocking {
            val result = savePerson(randomPersons, supabaseClient)
            assertEquals(2, result.size)
            assertEquals(randomPersons, result.map { it.toPerson() })
        }
    }
}

Here's the final error I encountered :

java.lang.IllegalStateException: Plugin rest not installed or not of type Postgrest. Consider installing Postgrest within your supabase client builder
    at io.github.jan.supabase.postgrest.PostgrestKt.getPostgrest(Postgrest.kt:172)
    at MainKtTestMock$setUp$1.invoke(MainKtTestMock.kt:34)
    at MainKtTestMock$setUp$1.invoke(MainKtTestMock.kt:34)

Second attempt: Encapsulate the Supabase Client

My second attempt was to get around the problem by encapsulating the problematic client inside a class of mine that I can then control.

It can be as simple as this :

class DatabaseClient(private val client: SupabaseClient){
    suspend fun savePerson(persons: List<Person>): List<ResultPerson> {
        val adults = persons.filter { it.age > 18 }

        return client
            .postgrest["person"]
            .insert(adults)
            .decodeList<ResultPerson>()
    }
}

And my test can then look like this (see MainKtTestSubclass):

class MainKtTestSubclass {

    private lateinit var client : DatabaseClient

    @BeforeTest
    fun setUp() {
        client = mockk<DatabaseClient>()
        coEvery { client.savePerson(any<List<Person>>()) } returns listOf(ResultPerson(2, "name_2", 2, "timestamp_2"))
    }

    @Test
    fun testSavePerson(){
        val fakePersons = listOf(Person("name_1", 1), Person("name_2", 2))

        runBlocking {
            val result = client.savePerson(fakePersons)
            assertEquals(2, result.size)
        }
    }
}

My main issue now is that because I have to indicate every single time what my output should be. It also just displaces the problem, because I don't really have any nice and clean way to check that my business logic works as intended, since I'm mocking it.

Third attempt : Ktor mock

The main contributor of the project gave another possible workaround in the GitHub issue I created : mock the internal Ktor engine of the Supabase client.

See the MainKtTestMockEngine :

class MainKtTestMockEngine {

    private val supabaseClient : SupabaseClient = createSupabaseClient("", "",) {
        httpEngine = MockEngine { _ ->
            respond(Json.encodeToString(Person.serializer(), Person("name_1", 16)))
        }
    }

    @Test
    fun testSavePerson(){
        val randomPersons = listOf(Person("Jan", 30), Person("Jane", 42))

        runBlocking {
            val result = savePerson(randomPersons, supabaseClient)
            assertEquals(2, result.size)
            assertEquals(randomPersons, result.map { it.toPerson() })
        }
    }
}

This is actually not a bad idea, it's light and it gets the job done is a clear and readable way. Those tests are also fast to run.

My main issue with this method would be that to test my business logic I'd have to dive into the received requests of the mock engine every time, which is a little cumbersome and prone to lots of maintenance.

I do want to investigate it further though.

Proposed solution : Test Supabase db

Now, one semi obvious solution would be to fire up a test database in supabase itself, and test there!

That'd work. I even do it to test my release deployments!

It has some obvious downsides though:

We couldn't be further away from unit tests, since we're testing on the cloud
Tests run slower, require internet, and also require a setup database. Cleanup can also be a mess
I'd be terrified to run that against the wrong database
It uses my bandwidth and projects, that either are limited, or I have to pay for!

Final solution : Docker Compose and Test Containers

I had one last idea, and that's the one I've decided to stick with for now. It leverages the fact that at its core, Supabase is built on a lot of Open-Source. And when we're using the Supabase client, we're essentially interacting with a glorified PostgreSQL / postgrest combo!

I've decided to create a Docker Compose setup that would mimick the actual Supabase production setup and connect to this instead.

A few things had to be taken into account for this to work :

I had to redirect all my postgrest calls to /rest/v1, which is the path that Supabase expects. So a GET on /persons should actually be on /rest/v1/persons.
postgrest uses JSON Web Tokens for authentication, so we have to set that up as part of the test class.

One last thing to note is that I would have to do MORE work in case I start using any of the other services of Supabase (say auth for example).

The Docker Compose setup looks like this :

version: '3'

services:
  ################
  # postgrest-db #
  ################
  postgrest-db:
    image: postgres:16-alpine
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=${POSTGRES_DB}
      - DB_SCHEMA=${DB_SCHEMA}
    volumes:
      - "./initdb:/docker-entrypoint-initdb.d"
    networks:
      - postgrest-backend
    restart: always

  #############
  # postgrest #
  #############
  postgrest:
    image: postgrest/postgrest:latest
    ports:
      - "3000:3000"
    environment:
      - PGRST_DB_URI=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgrest-db:5432/${POSTGRES_DB}
      - PGRST_DB_SCHEMA=${DB_SCHEMA}
      - PGRST_DB_ANON_ROLE=${DB_ANON_ROLE}
      - PGRST_JWT_SECRET=${PGRST_JWT_SECRET}
    networks:
      - postgrest-backend
    restart: always

  #############
  # Nginx     #
  #############
  nginx:
    image: nginx:alpine
    restart: always
    tty: true
    volumes:
      - ./nginx.conf:/etc/nginx/conf.d/default.conf
    ports:
      - "80:80"
      - "443:443"
    networks:
      - postgrest-backend

networks:
  postgrest-backend:
    driver: bridge

Note that a few auxiliary files are needed for this to work. You can find everything in the test/resources folder of the example GitHub repository.

There is :

A short nginx.conf file.
An SQL file to setup the database (note that in my actual repo, this guy already exists since I need it to setup production :)).
a .env file to list all my environment variables

Once that is done, it is already possible to run $ docker-compose up -d and to run your application against localhost like if you were interacting with the real Supabase. (don't forget to call $docker-compose down --remove-orphans -v to kill and delete all containers once you're done).

To make the magic complete, we're gonna use the power of TestContainers to run this as unit/integration tests. My final MainKtTestTestContainers test class looks like this :

@Testcontainers
class MainKtTestTestContainers {

    // The jwt token is calculated manually (https://jwt.io/) based on the private key in the docker-compose.yml file, and a payload of {"role":"postgres"} to match the user in the database
    private val jwtToken = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoicG9zdGdyZXMifQ.88jCdmcEuy2McbdwKPmuazNRD-dyD65WYeKIONDXlxg"

    private lateinit var supabaseClient: SupabaseClient

    @Container
    var environment: ComposeContainer =
        ComposeContainer(File("src/test/resources/docker-compose.yml"))
            .withExposedService("postgrest-db", 5432)
            .withExposedService("postgrest", 3000)
            .withExposedService("nginx", 80)

    @BeforeEach
    fun setUp() {
        val fakeSupabaseUrl = environment.getServiceHost("nginx", 80) +
                ":" + environment.getServicePort("nginx", 80)

        supabaseClient = createSupabaseClient(
            supabaseUrl = "http://$fakeSupabaseUrl",
            supabaseKey = jwtToken
        ) {
            install(Postgrest)
        }
    }

    @Test
    fun testEmptyPersonTable(){
        runBlocking {
            val result = getPerson(supabaseClient)
            assertEquals(0, result.size)
        }
    }

    @Test
    fun testSavePersonAndRetrieve(){
        val randomPersons = listOf(Person("Jan", 30), Person("Jane", 42))

        runBlocking {
            val result = savePerson(randomPersons, supabaseClient)
            assertEquals(2, result.size)
            assertEquals(randomPersons, result.map { it.toPerson() })

            val fetchResult = getPerson(supabaseClient)
            assertEquals(2, fetchResult.size)
            assertEquals(randomPersons, fetchResult.map { it.toPerson() })
        }
    }
}

All of the magic happens at the beginning, to setup a fake Supabase URL and connect to it. Once that is done, we can write our tests as easily as ever, since we're actually interacting with an actual light Supabase clone!

A word of conclusion

It took me a little while to get all these tests running, but I'm very happy about the final result. It might not be the best solution for production grade apps, but the trade off of running test containers for my side project definitely makes up for the fact that I literally have no boilerplate to run and can avoid using mocks.

I'll check in the future how much I can extend the docker compose image as I get to use more Supabase services. Maybe it would be nice of Supabase to offer that image themselves so we can test easily and avoid using the cloud where not necessary :).

[Unit] Testing Supabase in Kotlin using Test Containers