That must be something peculiar to SQLite because the H2 in-memory DB is used throughout the next.jdbc tests and it doesn't vanish between operations...?

Yes, H2 have this behavior - you can have multiple "in-memory" databases with specific names. I remember I had the opposite "problem" on H2 (I was expecting to work as SQLite works, and the tables were persisting between connections)

Ah, interesting.

(actually, now that you mention, maybe H2 is actually better for my use-case :rolling_on_the_floor_laughing:)

For SQLite, the database lives in a single connection only. Once you've closed it, the db is gone. So you spawn a connection using get-connection and pass it everywhere.

The same applies to a file-driven sqlite: without get-connection, you'll be opening and closing the file on each query. So you need a persistent connection.

@U1WAUKQ3E You could use a connection pool like HikariCP with SQLite for file-based access, right? The same is true of all DBs: if you pass a spec hash map or even a plain datasource around, it'll create a new connection for every operation which is very wasteful/slow.

I haven't tried HikariCP with SQLite yet as it might lead to weird behaviour, I believe. Although the pool reuses the connections, there is still a chance that you get a new connection with a new in-memory db.

I want suggesting for in memory, only for file based