2

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

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

Nathan H
  • 48,033
  • 60
  • 165
  • 247
  • only one answer, no comments and no votes? – Nathan H May 23 '11 at 11:22
  • check this [post](http://stackoverflow.com/questions/15720272/when-to-close-sqlite-database-using-fmdb?lq=1) and @ccgus advised to keep it open. – Gon Dec 31 '13 at 07:53

2 Answers2

0

I'm not sure I have the definitive answer, but having looked into this a bit myself, I've seen numerous people who say it's ok to leave the database open.

Also, if you look at the Sqlite site you'll see they've done a lot of work on ensuring a database will not get corrupted from crashes, power failures etc.

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

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

My experience using Sqlite and FMDB is it seems to be fine to open a connection and just leave it open. Remember, this is a "connection" to a file, that's on a local file system that's on Flash memory. That's a very different situation than a connection over the network. I think the chances of failure are extremely slim, as it's clearly designed to handle crashes, power failures etc. even if they occur during an actual database operation - so outside of a database operation they are not an issue.

You could of course argue that it's bad practice to keep a database connection open when not in use, and I wouldn't recommend it in a typical client-server setup, but on the iPhone/iPad I think it's a non-issue. Keeping it open seems to work fine and is one less thing to worry about.

Nigel Flack
  • 108
  • 1
  • 4
-1

You don't want your app to keep the DB open from start to finish, unless all it does is start, do DB stuff, then quit. The reason for this is that on rare occasions, the app may be terminated by a system problem, loss of power, etc.; since SqLite is file-based, this may result in an unclosed file or some other out-of-sync condition. Open the DB when you need it open, do your thing, and close it when you no longer need it open. You can't protect against a crash while you're actually doing db ops, but you see to it that the db was stable and closed when your last set of db ops ran. Just as an aside, SqLite opens and closes very quickly. Well, let me amend that: the SqLite3 I have compiled into my app does. I don't actually know about other versions.

fyngyrz
  • 2,458
  • 2
  • 36
  • 43