iOS – FMDB is THE End to Your SQLite Nightmares

When you’re writing apps for iPad or iPhone, you’ve got a couple of options for you database solutions.  By far the best options (if it fits your app’s data needs) is Apple’s own Core Data which can use SQLite as it’s database store.  Core Data is fast, efficient, an object store, and provides some great integration with Table Views.  It does have a learning curve…I used it in my PhotoSmash app, so it’s not too crazy.

So, why would you ever want to use SQLite?  Well, my current app project is a data tracking app, which will rely on lots of aggregations and counts, etc.  Stuff that is best handled using raw SQL statements.  Core Data is an object store, strongly typed and all that.  But it is a bit cumbersome doing SUM, AVG, and those types of Column operations.   It might also be less efficient as those aggregations since I think it will have to load the full objects into memory just to get at the columns you need.

So, if you’re doing data intensive calculations, you might want to look at pure SQLite.  Don’t be afraid of it.  It’s a pretty powerful little db.  It is a single file database that is embedded right with your application, unlike an Oracle or SQL Server database which is run from a db server.

The bad part of SQLite is the C API.  It is slap yourself painful.  Someone on SO says “only a masochist would use it without a wrapper”, and they are right!

I wrote half of my app using the API, but finally, I couldn’t take it anymore.

Do yourself a favor and go get FMDB (Flying Meat Database – that does have some imagery that I could do without).  After a little bit of the SQLite API, you will think you’ve entered the pearly gates.  To see how to use FMDB, download the package from GIT here, then open up the fmdb.m file in the src directory.  It is a demo file that demonstrates lots of the functionality, just read through the comments.

Thank you FMDB!  You made SQL fun again.  Here  are some links to other stories of people who opted for SQLite + FMDB over Core Data.  And, here is a great article that talks about other cases where straight up SQLite + FMDB might make sense over CD.  Gus is the man behind the Flying Meat.

Cheers,

Byron

, ,

4 Responses to iOS – FMDB is THE End to Your SQLite Nightmares

  1. Matthew November 26, 2011 at 4:22 am #

    Although FMDB has been around for a while, newer libraries have now been written to manage SQLite databases that provide more functionality. For instance, checkout https://github.com/ziminji/objective-c-sql-query-builder which is more than just a wrapper project. It provides a set of SQL query builder classes and an ORM. In general, it does every FMDB can do and more. This is my preference for working with Objective-C and SQLite on iOS devices.

    • byron November 26, 2011 at 11:03 am #

      Hi Matthew,

      Thanks for the link! Like you said, there are a number of libraries that do a lot more than FMDB. I liked FMDB because it was so lightweight and easy to use. In truth, I could have used an ORM because I found myself populating NSMutableArray’s for a lot of my objects. FMDB made that fairly simple, but it would have been even more simple with an ORM.

      That said, FMDB has been solid, fast, and simple. So, depending on your need, it might be all you want. Of course, good old CoreData is excellent for 99% of the cases.

      Cheers,
      Byron

  2. Gabri January 23, 2012 at 3:00 am #

    Another thing to consider is the fact that one could prefer SQLite over CoreData because you have an Android or Windows or server app that uses the same SQLite db (so CoreData would mean other work for only one platform).

    • byron January 23, 2012 at 8:27 am #

      Good point. I don’t know what the FMDB equivalent would be for Android, but I’d bet there are several good options.

      BB

Powered by WordPress. Designed by Woo Themes