Good database library/ORM for cocoa development

Question

I am developing a cocoa application that will be making heavy use of both web services and a standard dbms (most likely MySQL) and I am wondering if anyone has a good option for a database library or ORM solution they have used. CoreData is not an option due to the need to support a standard DBMS and to be able to modify the data outside of the normal application operation.

I have found a number of possible options from new open source libraries: http://github.com/aptiva/activerecord/tree/master

To writing my own wrapper for the C MySQL api.

Any advice is welcome,

Thanks!

Paul

Answer 1

We faced a similar question when we first started work on Checkout, our solution was to code the entire app in Python, using PyObjC.  Checkout 1 had an sqlite backend, Checkout 2 has a postgres backend.

There are a couple of really mature and powerful ORMs on the Pyton side, such as SQLObject, which is pretty simple to work with (we used it for Checkout 1.0) and SQLAlchemy, which is more powerful but a bit harder to wrap your brain around (we used it for Checkout 2.0).

One approach you could evaluate, is building the application in Objective-C, but writing the data model and database connectivity/adminstration code in Python. You can use PyObjC to create a plugin bundle from this code, that you then load into your app  That's more or less the approach we took for Checkout Server, which uses a Foundation command-line tool to administer a postgres server and the databases in it, this CLI tool in turn loads in a Python plugin bundle that has all of the actual database code in it.  End-users mostly interact with the database through a System Preferences pane, that has no clue what the database looks like, but instead uses the command-line tool to interact with it.

Loading a plugin is simple:

NSBundle *pluginBundle = [NSBundle bundleWithPath:pluginPath];
[pluginBundle load];

You will probably need to create .h files for the classes in your bundle that you want to have access to from your Obj-C code.

Answer 2

You might also want to check out the BaseTen framework. It is a Core Data-like framework (in fact, it can import Core Data models), but works with PostgreSQL (though not MySQL, as far as I know). It includes some very nice features such as schema discovery at run time. It also includes an NSArrayController subclass that automatically handles locking and synchronizing across multiple users, so you can continue to make use of Apples Key-value Binding in your UI.

Answer 3

I have personal experience with this particular problem. I even started down the road of writing my own wrapper for the C MySQL API.

The eventual conclusion was: Don't!

The solution that worked in my case was to communicate with the MySQL server via PHP. If you are familiar with web services, chances are that you know about PHP, so I don't won't go into loads of detail about that.

To read from the database:

1. The cocoa app sends a request for a URL on the server: http://theserver.com/app/get_values.php
2. The get_values.php script handles the database query, and returns the data in xml format
3. The cocoa app loads and parses the xml

To write to the database:

1. The cocoa app sends a more complex request to the server: http://theserver.com/app/put_values.php?name="john doe"&age=21&address=...
2. The put_values.php script parses the input and writes to the database

The beauty of this solution is that PHP is great for working with MySQL, and cocoa has some handy built-in classes for working with XML data.

edit: one more thing:

One of the key things you have to figure out with this approach is how much processing should be done on the server, and how much should be done in the app itself. Let cocoa do the things that cocoa is good at, and let PHP and MySQL do the things that they are good at.

You could write a generic PHP script to handle all queries: perform_query.php?querystring="SELECT * FROM .....", but that is hardly an optimal solution. Your best bet is several smaller PHP scripts that handle individual datasets for you. In my case, there was one to get the list of users, one to get the list of transactions, etc. Again, it all depends on what your app is going to do.