When to Close your DB connection?

Hi all I have a quick question regarding when to close your DB connection.

I have a profile window that I have multiple methods for as you would expect. SQLConnect, SQLSelect, SQLUpdate, SQLInsert, SQLRemove, SQLSchemaSetup etc. This is the normal flow right now (very high level omitting error checks etc).

  1. I open my DB connection
  2. I check for Schema Setup / Create if not there
  3. Wait for User to do something. Ie. add new row (SQLInsert), modify a row (SQLUpdate), remove a row (SQL remove) etc.
  4. Run method which performs SQL transaction
  5. Keep DB connection open for future transactions

Since my user will possibly be making many modifications at this Window at once my question is when do you Close the Database Connection? Does it put a huge load on the SQLite DB opening/closing the connection after every transaction? Is that best practice?

My db size forecasting is about 7 columns and probably max 2k-3k entries.

Any advice would be appreciated. Thank you.

Keeping the connection open to your sqlite database is okay. For apps where the db is local we open the connection at app start and close it when then app is quit.

Thanks Bob that is what I was hoping.

If this is an app that might be open a lot, you might want to check the connection before using it again. I’m not sure what happens to the connection if the computer goes to sleep. In one of our older apps (Mac carbon) it closes the connection on you so we have to make sure it opens it again. I don’t remember what it did in Windows.

Great point Bob as this app may be open for days and/or weeks at a time.