Cocoa SQLite when to close the database?

Question

I am writing my first MacOS application that uses SQLite (https://github.com/ccgus/fmdb).

I could either open / close the database connection for each transaction (CRUD), or in init / dealloc. What is the best way?

Answer 1

I'm not sure that I have a definitive answer, but having studied this a bit, I have seen numerous people who say that it is normal to leave the database open.

Also, if you look at the Sqlite website, you will see that they have done a great job of ensuring that the database is not damaged in the event of a power outage, power outage, etc.

http://www.sqlite.org/testing.html

http://www.sqlite.org/atomiccommit.html

My experience of using Sqlite and FMDB is to open the connection normally and just leave it open. Remember that this is a “connection” to a file that is on the local file system, which is in flash memory. This is a completely different situation than connecting to a network. I think the probability of a failure is very subtle, since it is clearly designed to handle failures, power failures, etc. Even if they occur during the actual operation with the database - therefore, outside the operation with the database they are not a problem.

You could, of course, argue that it’s a bad practice to open a database connection when it is not in use, and I would not recommend it in a typical client-server setup, but on iPhone / iPad I find the question. Keeping it open seems to be working fine, and least of all to worry about.

Answer 2

You do not want your application to keep the database open from start to finish, unless everything that it does does not start, does not do database stuff, and then shuts down. The reason for this is that in rare cases, the application may be interrupted by a system problem, loss of power, etc .; since SqLite is file-based, this can lead to a closed file or other out-of-sync state. Open the database when you need to open it, do your work and close it when you no longer need to open it. You cannot protect against failure while you are actually doing db ops, but you see that db was stable and closed when you started your last set of db operations. As an aside, SqLite opens and closes very quickly. Ok, let me fix this: SqLite3, which I compiled into my application. I do not know about other versions.