sqlite3 connection and holding onto it during the lifetime of the app

Question

I am following the 'SQLiteBooks' sample to understand how to work with sqlite3 for the iPhone and am wondering if the connection to the database should be closed immediately after you do a query and reopened each time you need to talk to the db or should the connection be kept open at the start of the app and close on 'appWillTerminate'? What are the costs associated with reopening the db connection multiple times within an app?

Also, the sample shows the reference to sqlite3 object (aka 'database') being passed around but without any reference counting -- is this a good pattern to follow?

Answer 1

The answer primarily depends on the type of application you are building. If you are only going to occasionally be accessing the database, then it's fine to open/close on an as needed basis, but if the app will be frequently using the DB, say because your application content is completely database driven, then you really should open the DB connection when the app launches and close it when it terminates.

In my last application, I started by passing the DB connection as a pointer when necessary and later changed my architecture so that the class that originally opened the DB, a composite of AppDelegate, was also the class to handle all queries to and responses from the DB. Worked nicely and is easy to maintain, to me, that equals win.