sqldelight: How to ensure that `create` is only called once

Question

How can I make sure that Database.Schema.create() is only called once when using the application? You could add ...IF NOT EXISTS... to the table definition, then the SQL instructions are executed during every startup but no tables are created after the first run, however, I'm not sure if this is the correct way to do this.

Alternatively, I could query the tables and check if they are already present.

What I'm looking for is something like:

if (<first run>) {
    Database.Schema.create()
}

I thought I could use migrations for this, but migrations seem to work differently for sqldelight then I expected (I tried to create the tables inside a migration but this does not work).

What platform are you writing this for? Android, iOS, JVM? – Tenfour04 Feb 17 '20 at 16:44 — Tenfour04, Feb 17 '20 at 16:44
JVM (this is just extra text to have at least 12 chars...) – matthjes Feb 17 '20 at 16:47 — matthjes, Feb 17 '20 at 16:47

score 3 · Answer 1 · answered Feb 22 '20 at 10:57

3

There has been a very helpful reply posted here: https://github.com/cashapp/sqldelight/issues/1605

It basically boils down to storing the current version inside PRAGMA user_version (which is initially 0) and performing a migration when this version differs from the latest migration version.

This has the advantage that the version is stored inside the database. I've adopted this solution for my Kotlin project.

answered Feb 22 '20 at 10:57

matthjes

671
7
20

Thanks for the link. I wonder why there is no official documentation on this. – MrStahlfelge Nov 14 '21 at 12:44
Is there a non-SQLite way of doing this (e.g. I use SQLite on my local for development but use MySQL on a server)? – masterwok Dec 21 '22 at 09:02

sqldelight: How to ensure that `create` is only called once

1 Answers1