Using Xcode to Develop a RDBMS

Ever since Apple stopped developing WebObjects, I haven't found a lightweight replacement for graphically designing relational databases like I used to do with EOModeler. Specifically, I wanted a graphical way to create a database by simply clicking and dragging between tables to join them as I demoed at WWDC in 2001.

RDBMS Design from Scratch

Earlier this year, I began a new software project, from scratch, that required a modest size relation database with about two dozen tables. I was surprised to discover that there was no simple RDBMS tool available, today, that could do what EOModeler did back in the mid-1990s.

After putting some thought into the problem, I realized that EOModeler and EOs (Enterprise Objects) were repurposed into Xcode's Core Data framework and renamed Managed Objects. Xcode's xcdatamodeld is nearly an identical implementation of EOModeler tied to a SQLite database. (SQLite is a lightweight RDBMS designed to be embedded in end-user applications instead of the traditional client-server architecture.)

Once I made the connection between EOModeler and Xcode, I reasoned that it might work very nicely for graphically designing the entity relationship model along with the SQL to created the schema. It turns out that I was right and it worked nicely. Here's how I did it...

1. I created an Xcode project that used Core Data.

2. I designed my database schema, graphically, in XCode by dragging between entities to create one-to-many, one-to-one, and many-to-many joins. (Note: Every time I was about to create a many-to-many join, I gave that relationship a long, hard look to make sure I was modeling it properly.)

3. Each time I built my Xcode project (which only contained the database model, no code needed), a new SQLite database was created in ~/Library/Containers/Project Name/Data/Library/Application Support/Project Name. In this folder are typically three files: the SQLite database (.sqlite), the write-ahead log (.sqlite-wal), and a temporary file for shared memory access (sqlite-shm).

4. I downloaded and installed DB Browser for SQLite to access the SQLite database that Xcode created. From there, I exported the database as a SQL file which was then used to create the same database schema in a Postgres database running on Amazon's RDS.

The only gotcha I encountered with Xcode was that sometimes, when I made changes to the database schema that conflicted with a previous database version, I'd have to delete the target folder in Step 3, above, so Xcode would recreate the database from scratch.

It's hard to beat Xcode's ease of use for RDBMS design, even though that's not its primary intent. Please let me know if you're aware of a database client that allows the simple graphical design that EOModeler used to have.