Architecture & Software Documentation, Part II – The Database

[Reading Time: 15mn]


I have introduced the topic of documenting software artefacts in my previous blog post. As I said, the purpose of a good documentation is to communicate – to your technical peers, to your boss, to your sponsors.

But a good documentation set is hard to achieve and then, to maintain. It should live as close to the actual code as possible. A good deal can be obtained through reverse engineering to / from UML when your project is object-oriented. But let’s face it, we live in a polyglot programming world. At least some layers in our software design won’t be amenable to UML because they don’t use object-orientation design. The canonical example being: how would you go about documenting your SQL-based data model?

ER to the rescue

Yes, of course, there’s a standard for that: Entity-Relationship diagrams. Boxes representing entities, and arrows representing relationships. Like this.


But again, how do you ensure that this kind of diagram is as close as possible to your actual database schema? Because you need to: each time you change a field in a table, or add a new entity, your ERD must be updated to reflect the current state.

So, why not do the same as you do with regular OO development: code the data model, and reverse-engineer it to produce diagrams.

So many gems

To do this, we will use the Rails web development framework (more formally, Ruby on Rails) and its ORM (ActiveRecord / AREL). This means that will actually code the database and, as we will see, this approach as several advantages. To begin with, the database definition and migration are part of the code artefacts and as such, they are source controlled, distributed and versioned. Not only that, but database schema interaction, testing, migration are part of the benefits. ActiveRecord also supports multiple environments (so that your dev can be using SQLite whereas UAT and Prod are on PostgreSQL, as they should).

Oh, I almost forgot: you will get the database schema documentation as well…


I am going to assume that you are familiar with the concept of a database, as well as the following: SQLDDLDMLORM, and MVC. After all, these are but a tiny part of any good developer’s tool belt.

The rest, I’m gonna explain to you guys.

Some background

What’s Ruby?

Ruby is a highly-dynamic OO scripting language. It does sound like a strange mix, although the concept is anything but new (Python anyone?). Ruby is highly dynamic: Sweet as arabic pastries, powerful as Hulk and cool as Tony Stark. But slow as dog. And very dangerous too (you should not use monkey-patching to fix production bugs shouldn’t you?)

What’s Rails?

Rails is an MVC-based web development framework running on Ruby. Its solution domain is table-driven web applications. Back in the day when the first version of Rails was released (circa 2005 / 2006), Java EE kind of ruled and its enterprisey / corporate orientation actually failed quite miserably at addressing the burgeoning Internet startup movement. Cool kids needed something quick and productive, and Rails is all about that.

Rails is server-based, although it heavily promoted using Javascript on the client-side. It is also very opinionated, and favours convention over configuration (Java EE is rather the reverse of this, letting you manage a ton and a half obnoxious XML configuration files). Rails put back the standards where they belong: at the forefront. Coding in Rails means coding the HTTP and HTML concepts. You cannot build a Rails application if you don’t want to embrace HTTP verbs or REST.

Ruby gems

Rails is a gem. No I mean, really, it is a Ruby “gem”. Dunno what a Ruby gem is? Ruby has a package manager (like Linux or Node.js or OS X – wait, no, OS X doesn’t have an official one, which is why everyone uses homebrew right?) called RubyGems. Ruby extensions or “gems” can be installed, updated, and uninstalled using gem.

Rails is a modular web framework, even though, nowadays, it depends on a gazillion of other Ruby “gems”. Gems are a fantastic way of enhancing Rails capabilities, even though inter-dependent versioning is very close to what some of us might call, sadly, a nightmare.

Scaffolding your data model

So, now that we have refreshed our minds about Rails, let’s start using it to create, interact and document our data model.

Remember we said that Rails solution domain is table-driven web applications. These are defined by models (or entities, which may or may not crystallise in database tables). The scaffolding in Rails lets you define a new model, and it will take care of creating the backend database table, the HTML views to manage CRUD operations on the model, the controller (the code associating the model and the views) and of course, the HTTP routes (connecting the HTTP verbs to the controller actions). And all of this with a simple command line. Wow.

Here’s an example for a model “Person” with 3 attributes: name, date of birth and email.

$ rails generate scaffold Person name:string dob:date email:string
invoke  active_record
create    db/migrate/20150610151404_create_people.rb
create    app/models/person.rb
invoke    test_unit
create      test/models/person_test.rb
create      test/fixtures/people.yml
invoke  resource_route
 route    resources :people
invoke  scaffold_controller
create    app/controllers/people_controller.rb
invoke    erb
create      app/views/people
create      app/views/people/index.html.erb
create      app/views/people/edit.html.erb
create      app/views/people/show.html.erb
create      app/views/people/new.html.erb
create      app/views/people/_form.html.erb
invoke    test_unit
create      test/controllers/people_controller_test.rb
invoke    helper
create      app/helpers/people_helper.rb
invoke      test_unit
invoke    jbuilder
create      app/views/people/index.json.jbuilder
create      app/views/people/show.json.jbuilder
invoke  assets
invoke    coffee
create      app/assets/javascripts/
invoke    scss
create      app/assets/stylesheets/people.scss
invoke  scss
create    app/assets/stylesheets/scaffolds.scss

Now, you can generate the database in DEV like so:

$ rake db:migrate RAILS_ENV=development

And run the application:

$ rails server


Developers love the CLI

You can interact with your data model using the scaffolded HTML pages if you like. These are bare-bones but they do work. However, it’s actually more fun to interact with your data using Ruby at the command line. Some examples:

$ rails console —sandbox
$ Person.create(name: "David", dob: "01/01/2000", email: "")
$ Person.count #=> 1
$ Person.create(name: "Ramon", dob: "01/01/2000", email: "")
$ Person.all.each {|it| puts}
$ twins = Person.where(:dob => '2000-01-01')
$ twins.all.each {|it| puts}

Your imagination is the limit.

Gimme ERD

So now we have some code to generate the database schema (a.k.a. migrations), we actually have a database to play around with and some HTML forms if we want to start coding the GUI. Nice. But we came thus far, and we still don’t have our ER diagram?

Fear no more, it is just a gem away. As everything’s Ruby, there’s indeed a gem for that: rails-erd. Once installed and invoked, it will read your migration files and generate a diagram based on graphviz, the essential diagram visualisation and construction language.

So first, install graphviz:

$ brew install graphviz

You also need to install the rails-erd gem, which you can do by editing your project’s gem file and add:

group :development
  gem “rails-erd”

before issuing a bundle install in your project:

$ bundle install

Of course, you only need to do it once in your Rails project. Now, you can invoke the gem and build your ER diagram:

$ rake erd

And hey, presto, the following is generated:

Screen Shot 2015-07-03 at 17.52.58

Anything fancier?

Well of course, the more the merrier the fancier. So let’s create some more models and link them using ActiveRecord:

$ rails generate model AccountType short_name:string description:string
$ rails generate model Bank short_name:string description:string contact_name:string
$ rails generate model Account short_name:string description:string number:string bank:references account_type:references
$ rails generate model AccountHolder account:references person:references

This will produce the following ER diagram:

Screen Shot 2015-07-03 at 17.54.33

Not bad, considering that it is all dynamically generated.


Using rails and rails-erd in your project to document your data model, you get:

  • A real data model w/ the DDL scripts (migrations)
  • A real data model that you can interact with using ActiveRecord at the command-line or a complete scaffolded CRUD web application
  • A nice ERD which is easily updated (just run rake erd) each time you change your model
  • A feeling of accomplishment!

I will very probably discuss the topic some more in a future post, so do not hesitate to give some feedback in the comments.

Thank you for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *