SQLite WAL mode

[calippo]

Hi! I'm trying to use my sqlite database in WAL mode. It should be doable just by passing a property to DriverManager as in:

Properties config = new Properties();
config.put("journal_mode", "WAL");
Connection con = DriverManager.getConnection("jdbc:sqlite:db.sqlite", config);

My code looks like:

val dbUrl = "jdbc:sqlite:db.sqlite"
val configCP = new com.zaxxer.hikari.HikariConfig()
configCP.setJdbcUrl(dbUrl)

configCP.setMinimumIdle(1)
configCP.setMaximumPoolSize(3)
configCP.setConnectionTestQuery("SELECT 1")
configCP.addDataSourceProperty("journal_mode", "WAL")

I am getting this error: Connection attempt to database HikariPool-0 failed: batch entry 0: query returns results (as if setConnectionTestQuery was not there). Is this supported by Hikari?

[calippo]

I thought I solved just using:

configCP.addDataSourceProperty("dataSource.journal_mode", "WAL")

It doesn't return an error, but the database is still in delete mode

[mmuruganandam]

@calippo The datasource property should have helped.

Try the below to see if that works.

val dbUrl = "jdbc:sqlite:db.sqlite?journal_mode=WAL"

[calippo]

@mmuruganandam It creates a database called db.sqlite?journal_mode=WAL

I solved my problem using slick typesafe (scala) configuration and loading journal_mode = "wal" property from configuration. Still I don't understand why the other way is not working.

[brettwooldridge]

@calippo If journal_mode = "wal" worked, but configCP.addDataSourceProperty("journal_mode", "WAL") did not, it is probably a simple issue of "WAL" needing to be lower-case ("wal").

[calippo]

It looks like it's case insensitive. @brettwooldridge with lowecase wal and property journal_mode I get Connection attempt to database HikariPool-0 failed: batch entry 0: query returns results error.

With lower case wal and property dataSource.journal_mode the database is still in delete mode.

[brettwooldridge]

@calippo Ok, I'll have to investigate further. As far as I know, Slick is simply calling addDataSourceProperty(), just as you have, so it is hard to imagine what the difference could be.

[calippo]

@brettwooldridge in case you need it https://github.com/calippo/hikari-sqlite-wal

[brettwooldridge]

Any further investigation on this?

[calippo]

Moved to MySQL :)

[juskrey]

To switch sqlite database to WAL mode forever, you should issue PRAGMA journal_mode= WAL; SQL command once, that's it. No need to tweak connection parameters.