Hi all. I have a question about sqldelight on iOS ...

# multiplatform

[Bohdan]

Hi all.
I have a question about sqldelight on iOS side. I can add, store and display data on UI on both(Android and iOS) platforms.
But it is more convenient (especially if you have more than 20 tables) to open a database file on any sqlite tool - to test queries, compare some data, visually see data etc.
It’s quite easy to get and open db file from Android. I aslo would like to do same with file from ios. As i understood on ios it is generated 3 files :
appDb-wal
appDb-shm
appDb
I am able to find and get these files on emulator. While adding new rows only “appDb-wal” size changed - so my guess that data are stored in this file ))
My issue that i couldn’t open this file - its requires password as db encrypted .
How can i open it? or where i could find this password ? What I am doing wrong ?
I am mostly working with android and don’t have much experience with iOS.
Thanks in advance.

[kpgalligan]

(Context, I maintain the driver under the sqldelight driver). Grab those 3 files. In theory you only need appDb and appDb-wal, though. When modifying the db, the *-wal file is updated (“wal” stands for “write ahead log”). Any tool you’re using to read the db file should be using the sqlite process under the hood, and understand how to look at the core db file and the wal.

[kpgalligan]

The way it works is, the core file appDb is where the db data lives, but db data mods are written to the appDb-wal file until a threshold is reached and they are copied into the main file. This happens for reasons related to transactions, etc. https://sqlite.org/wal.html

[kpgalligan]

I assume you’re not using sqlcipher. If you are, I assume it’s the same situation, but you need to provide the key to decrypt the data in the file.

[kpgalligan]

Yada yada, open appDb

[Bohdan]

@kpgalligan appDb is empty in my case even without schema of tables (but data present on ui). I will try to open them with some other tool that could look at both files. Thanks.

[kpgalligan]

That’s weird. I guess I haven’t directly opened the sqlite file in a while. With a simulator you should be able to see the files directly on your dev machine and just point the sqlite cli at them. Let me see if I can do that quick…

[Bohdan]

i was able to open files with some tool - after closing all data was moved into “appDb” - but on simulator it is not working.
could be that connection to db was not properly closed ?

[kpgalligan]

They never really are “closed”, generally speaking, unless you do so explicitly.

[Bohdan]

also data was not transferred automatically from “wal” - So i need to close connection to db after transaction ? that’s also weird and not convenient. Will it be closed if user kill app on real device ? On simulator it didn’t help

[kpgalligan]

So i need to close connection to db after transaction ?

Definitely not

[kpgalligan]

Data stays in wal until sqlite decides it gets moved over.

[kpgalligan]

To be clear, it is not necessarily abnormal to have data in wal. It doesn’t get copied over when the transaction ends.

[kpgalligan]

From my terminal:

kgalligan@Kevins-MacBook-Pro databases % ls -lA
total 136
-rw-r--r--  1 kgalligan  staff   4096 Feb  3 08:50 KampkitDb
-rw-r--r--  1 kgalligan  staff  32768 Feb  3 08:50 KampkitDb-shm
-rw-r--r--  1 kgalligan  staff  28872 Feb  3 08:50 KampkitDb-wal
kgalligan@Kevins-MacBook-Pro databases % sqlite3 KampkitDb
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite> select * from Breed limit 2;
1|affenpinscher|0
2|african|0
sqlite> .exit
kgalligan@Kevins-MacBook-Pro databases % ls -lA           
total 96
-rw-r--r--  1 kgalligan  staff  16384 Feb  3 08:51 KampkitDb
-rw-r--r--  1 kgalligan  staff  32768 Feb  3 08:51 KampkitDb-shm
-rw-r--r--  1 kgalligan  staff      0 Feb  3 08:51 KampkitDb-wal
kgalligan@Kevins-MacBook-Pro databases %

[kpgalligan]

This was a fresh run of “KaMPKit”. It loads data into a table on first run. You’ll notice the wal file is much larger than the db file when I do ls first (that’s after I killed the app).

[kpgalligan]

Open it with sqlite3 KampkitDb. It sees all of the data in the wal as well. It’s not weird that the data is in the wal, but you open the main db file.

[Bohdan]

i can’t get same behaviour in my project - i will look once more at “KaMPKit”

[kpgalligan]

The sqlite3 tool does trigger the data copy from wal into the main db file at some point, but that had nothing to do with the app itself. It was the cli tool.

[Bohdan]

Does it depend on amount of data ?

[kpgalligan]

Copying wal? I believe so. However, it should work regardless, so if something is not working in your situation I probably wouldn’t worry about wal too much. Any tool you use to open sqlite should be able to deal with wal. You can tell ios not to use wal, but I really wouldn’t.

[kpgalligan]

You can pass in a different journal mode. WAL is default. “Delete” is what should be default on android, but you would need to delete your app as I assume trying to open a wal db with a different journal mode would not work. https://github.com/touchlab/SQLiter/blob/main/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/DatabaseConfiguration.kt#L28

[kpgalligan]

I don’t know anybody that is using different journal modes (Room on Android also defaults to WAL because it’s considered the better option in all cases I can think of)

[Bohdan]

@kpgalligan I will play around more with it. In any case thanks a lot. In fact, I started with kmm based on “KaMPKit” - thank you for your work! 🙂

[kpgalligan]

I would try the sqlite3 cli tool and verify that that doesn’t work on your db first, then maybe its the tool you’re using.

[kpgalligan]

Glad KaMP Kit helped! It’s getting a little old so we’re working on an update. Everything takes longer than you plan, though.