SqlDelight/SQLite not executing joins properly?

Question

I have added SqlDelight to my Multiplatform project and have created a few basic tables and queries.

When I try to use more complex queries though, like JOINS the query still executes and the generated interfaces contain all the correct fields, but the actual objects do not contain the generated data.

GithubRepository.sq:

CREATE TABLE GithubRepository (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

Foobar.sq:

CREATE TABLE foobar (
    id INTEGER NOT NULL PRIMARY KEY,
    foobar TEXT NOT NULL,
    repoId INTEGER NOT NULL,
    FOREIGN KEY(repoId) REFERENCES GithubRepository(id)
);

findFoobars:
SELECT *
FROM foobar
LEFT JOIN GithubRepository ON GithubRepository.id = foobar.repoId;

insert:
INSERT INTO foobar VALUES ?;

Now, I verify that this is working with a couple of tests, like so:

    @Test
    fun `Joins working?`() {
        assertEquals(0, queries.findAll().executeAsList().size)

        queries.insert(GithubRepository.Impl(2, "bar"))
        foobarQueries.insert(Foobar.Impl(1, "foo", 2))

        assertEquals("bar", foobarQueries.findFoobars().executeAsList()[0].name)
    }

All individual queries succeed and I can write to and read from all the tables as expected.

It just happens that when accessing the query with a JOIN the joined property stays empty.

In a more complex setup I have also tested this on an Android emulator, where I can read from all the individual tables, but not from the joined fields.

Can anyone spot where I missed something?

Answer 1

After some more experimenting, I realized that I was using an in memory database on Android. After switching that over to a proper file based SQLite database by passing a database name to the AndroidSqliteDriver constructor, the JOINS are executed correctly and the expected fields are filled correctly.

This seems to be a limitation of the the in memory database driver. I expect that switching the tests over to a file based one will solve the test failures as well.