Describe PostgreSQL table from command line

You may be familiar with annotate gem(GitHub, Rubygems). Neat library that adds comments summarizing the current DB schema to the top of each of your model/factory/spec. First time I’ve used it in production project(5+ yrs ago) it felt convenient, especially in big apps with lots of models and fields. I no longer needed to keep in my mind specific field names neither needed I to look them up in DB administration tools. That was big productivity boost.

After using this whole model annotation concept in plenty of projects and thinking about it I would prefer not to use it in future projects. Here’s why:

  • It increases the length of your models, the first thing that you do when you open a file is to scroll down to actual code that you care about. A bit annoying and slightly slows down your workflow when you think about how many times you do it every day.
  • It needs to be updated and kept in sync with you code updates. Yes, you can automate it as part of your overcommit setup but it also takes time and can be a bit annoying when you need to switch between branches.
  • It makes commits/pull requests a bit more verbose and repetitive then they could to be e.g., one modified line of code could come with 3+ modified annotation lines.
  • In case of automated annotation, it becomes really annoying when you need to switch between various gems with different database schema versions.

So what I prefer to use these days? Just open up a separate tab and used psql’s CLI interface for the model I would like to describe:

psql foo_development -c  '\x' -c '\d+ books'

This command could be easily found in your bash/zsh history using reverse search or you can extract it into a shell function like so:

function describe_table() {
  psql eth_farm_development -c  "\x" -c "\d+ $1"

And call it like: describe_table books

Trying to recall how many time I need to look up a model schema and it looks like just a fraction of time spent on all other activities. So for me it’s

Desire to look up table schema is such a rare occasion in comparison with all the other tasks that you need to perform that it doesn’t make sense to pay such a “high price” for the low benefit if there is a simple workaround.

One of the main reasons I find this approach more desirable is because seeing this kind of info is such a rare activity in comparison with all other tasks that you need to do.