Nov 13 2012

If you're a developer using MongoDB, you'll be familiar with the problem of kludging around in the console trying to read what's in your collections concisely. Take this collection, for example:

dev:PRIMARY> db.question.find()
{ "_cls" : "Question", "_id" : ObjectId("503edc84fe75bd2a96000001"), "_types" : [ "Question" ], "created" : ISODate("2012-08-30T03:22:44.194Z"), "image1" : DBRef("image", ObjectId("503edc5efe75bd2a0c000005")), "image2" : DBRef("image", ObjectId("503edc5ffe75bd2a0c00000a")), "report_count" : 0, "s" : [ 154.55235184576958, 18.9734399140749 ], "title" : "Freetext", "vote1" : 1, "vote2" : 0 }
{ "_cls" : "Question", "_id" : ObjectId("503ee119fe75bd345c000010"), "_types" : [ "Question" ], "category" : DBRef("category", ObjectId("503ed335fe75bd141e000002")), "created" : ISODate("2012-08-30T03:42:17.545Z"), "image1" : DBRef("image", ObjectId("503ee113fe75bd345c000009")), "image2" : DBRef("image", ObjectId("503ee116fe75bd345c00000e")), "report_count" : 0, "s" : [ 132.59225429162245, 18.162035529637897 ], "vote1" : 1, "vote2" : 1 }
{ "_id" : ObjectId("503eeac0fe75bd50b200000d"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000004")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:23:28.483Z"), "_types" : [ "Question" ], "title" : "Who is hotter? (Female)", "report_count" : 0, "s" : [ 23.47876653187288, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eeabbfe75bd50b200000b")), "image1" : DBRef("image", ObjectId("503eeab8fe75bd50b2000006")) }
{ "_cls" : "Question", "_id" : ObjectId("503eee34fe75bd58c400001e"), "_types" : [ "Question" ], "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "created" : ISODate("2012-08-30T04:38:12.389Z"), "image1" : DBRef("image", ObjectId("503eee30fe75bd58c4000017")), "image2" : DBRef("image", ObjectId("503eee32fe75bd58c400001c")), "s" : [ -2.2126018748216154, 4 ], "title" : "Which is better?", "vote1" : 0, "vote2" : 1 }
{ "_id" : ObjectId("503eee3cfe75bd58c400002d"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:38:20.292Z"), "_types" : [ "Question" ], "title" : "Which is better?", "report_count" : 0, "s" : [ 140.02780470765373, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eee3bfe75bd58c400002b")), "image1" : DBRef("image", ObjectId("503eee38fe75bd58c4000025")) }
{ "_id" : ObjectId("503eee45fe75bd58c400003b"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:38:29.250Z"), "_types" : [ "Question" ], "title" : "Which is better?", "report_count" : 0, "s" : [ -76.62281281346894, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eee44fe75bd58c4000039")), "image1" : DBRef("image", ObjectId("503eee41fe75bd58c4000034")) }
{ "_id" : ObjectId("503eee4ffe75bd58c400004d"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:38:39.555Z"), "_types" : [ "Question" ], "title" : "Which is better?", "report_count" : 0, "s" : [ 91.80633372495834, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eee4efe75bd58c400004b")), "image1" : DBRef("image", ObjectId("503eee4bfe75bd58c4000046")) }
{ "_id" : ObjectId("503eee59fe75bd58c400005b"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:38:49.902Z"), "_types" : [ "Question" ], "title" : "Which is better?", "report_count" : 0, "s" : [ -144.25263191156716, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eee59fe75bd58c4000059")), "image1" : DBRef("image", ObjectId("503eee55fe75bd58c4000054")) }
{ "_id" : ObjectId("503eee64fe75bd58c4000069"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:39:00.712Z"), "_types" : [ "Question" ], "title" : "Which is better?", "report_count" : 0, "s" : [ 164.1300587298494, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eee63fe75bd58c4000067")), "image1" : DBRef("image", ObjectId("503eee61fe75bd58c4000062")) }
{ "_id" : ObjectId("503eee6cfe75bd58c4000077"), "category" : DBRef("category", ObjectId("503ed335fe75bd141e000000")), "vote1" : 0, "vote2" : 0, "created" : ISODate("2012-08-30T04:39:08.980Z"), "_types" : [ "Question" ], "title" : "Which is better?", "report_count" : 0, "s" : [ -101.42088453306395, 20 ], "_cls" : "Question", "image2" : DBRef("image", ObjectId("503eee6cfe75bd58c4000075")), "image1" : DBRef("image", ObjectId("503eee68fe75bd58c4000070")) }

Here's how it looks in my terminal:

MongoDB output

What a pig to read. Adding .pretty() doesn't really help, because it's often too verbose:

dev:PRIMARY> db.question.find().pretty()
{
    "_cls" : "Question",
    "_id" : ObjectId("503edc84fe75bd2a96000001"),
    "_types" : [
        "Question"
    ],
    "created" : ISODate("2012-08-30T03:22:44.194Z"),
    "image1" : DBRef("image", ObjectId("503edc5efe75bd2a0c000005")),
    "image2" : DBRef("image", ObjectId("503edc5ffe75bd2a0c00000a")),
    "report_count" : 0,
    "s" : [
        154.55235184576958,
        18.9734399140749
    ],
    "title" : "Freetext",
    "vote1" : 1,
    "vote2" : 0
}
{
    "_cls" : "Question",
    "_id" : ObjectId("503ee119fe75bd345c000010"),
    "_types" : [
        "Question"
    ],
    "category" : DBRef("category", ObjectId("503ed335fe75bd141e000002")),
    "created" : ISODate("2012-08-30T03:42:17.545Z"),
    "image1" : DBRef("image", ObjectId("503ee113fe75bd345c000009")),
    "image2" : DBRef("image", ObjectId("503ee116fe75bd345c00000e")),
    "report_count" : 0,
    "s" : [
        132.59225429162245,
        18.162035529637897
    ],
    "vote1" : 1,
    "vote2" : 1
}
// ... screeds and screeds of input. Your terminal now hates you.

So Martyn hacked this up:

dev:PRIMARY> db.question.find().t()
+--------------------------+------------+----------------+-------------------------------------+------------------------------------------------------+------------------------------------------------------+--------------+--------------------------------------------+---------------------------+-------+-------+-------------------------------------------------------+
|           _id            |    _cls    |     _types     |               created               |                        image1                        |                        image2                        | report_count |                     s                      |           title           | vote1 | vote2 |                       category                        |
+--------------------------+------------+----------------+-------------------------------------+------------------------------------------------------+------------------------------------------------------+--------------+--------------------------------------------+---------------------------+-------+-------+-------------------------------------------------------+
| 503edc84fe75bd2a96000001 | "Question" | [ "Question" ] | ISODate("2012-08-30T03:22:44.194Z") | DBRef("image", ObjectId("503edc5efe75bd2a0c000005")) | DBRef("image", ObjectId("503edc5ffe75bd2a0c00000a")) | 0            | [ 154.55235184576958, 18.9734399140749 ]   | "Freetext"                | 1     | 0     |                                                       |
| 503ee119fe75bd345c000010 | "Question" | [ "Question" ] | ISODate("2012-08-30T03:42:17.545Z") | DBRef("image", ObjectId("503ee113fe75bd345c000009")) | DBRef("image", ObjectId("503ee116fe75bd345c00000e")) | 0            | [ 132.59225429162245, 18.162035529637897 ] |                           | 1     | 1     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eeac0fe75bd50b200000d | "Question" | [ "Question" ] | ISODate("2012-08-30T04:23:28.483Z") | DBRef("image", ObjectId("503eeab8fe75bd50b2000006")) | DBRef("image", ObjectId("503eeabbfe75bd50b200000b")) | 0            | [ 23.47876653187288, 20 ]                  | "Who is hotter? (Female)" | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee34fe75bd58c400001e | "Question" | [ "Question" ] | ISODate("2012-08-30T04:38:12.389Z") | DBRef("image", ObjectId("503eee30fe75bd58c4000017")) | DBRef("image", ObjectId("503eee32fe75bd58c400001c")) |              | [ -2.2126018748216154, 4 ]                 | "Which is better?"        | 0     | 1     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee3cfe75bd58c400002d | "Question" | [ "Question" ] | ISODate("2012-08-30T04:38:20.292Z") | DBRef("image", ObjectId("503eee38fe75bd58c4000025")) | DBRef("image", ObjectId("503eee3bfe75bd58c400002b")) | 0            | [ 140.02780470765373, 20 ]                 | "Which is better?"        | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee45fe75bd58c400003b | "Question" | [ "Question" ] | ISODate("2012-08-30T04:38:29.250Z") | DBRef("image", ObjectId("503eee41fe75bd58c4000034")) | DBRef("image", ObjectId("503eee44fe75bd58c4000039")) | 0            | [ -76.62281281346894, 20 ]                 | "Which is better?"        | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee4ffe75bd58c400004d | "Question" | [ "Question" ] | ISODate("2012-08-30T04:38:39.555Z") | DBRef("image", ObjectId("503eee4bfe75bd58c4000046")) | DBRef("image", ObjectId("503eee4efe75bd58c400004b")) | 0            | [ 91.80633372495834, 20 ]                  | "Which is better?"        | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee59fe75bd58c400005b | "Question" | [ "Question" ] | ISODate("2012-08-30T04:38:49.902Z") | DBRef("image", ObjectId("503eee55fe75bd58c4000054")) | DBRef("image", ObjectId("503eee59fe75bd58c4000059")) | 0            | [ -144.25263191156716, 20 ]                | "Which is better?"        | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee64fe75bd58c4000069 | "Question" | [ "Question" ] | ISODate("2012-08-30T04:39:00.712Z") | DBRef("image", ObjectId("503eee61fe75bd58c4000062")) | DBRef("image", ObjectId("503eee63fe75bd58c4000067")) | 0            | [ 164.1300587298494, 20 ]                  | "Which is better?"        | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
| 503eee6cfe75bd58c4000077 | "Question" | [ "Question" ] | ISODate("2012-08-30T04:39:08.980Z") | DBRef("image", ObjectId("503eee68fe75bd58c4000070")) | DBRef("image", ObjectId("503eee6cfe75bd58c4000075")) | 0            | [ -101.42088453306395, 20 ]                | "Which is better?"        | 0     | 0     | DBRef("category", ObjectId("503ed335fe75bd141e0000... |
+--------------------------+------------+----------------+-------------------------------------+------------------------------------------------------+------------------------------------------------------+--------------+--------------------------------------------+---------------------------+-------+-------+-------------------------------------------------------+
Better MongoDB output

Much better!

You can get mongo-tabular here.

Please fork & improve. If this proves useful, I hope the Mongo DB developers consider developing such functionality natively - no doubt cool things could be done.

Like this post? Subscribe to my RSS feed and follow me on twitter to hear about new posts early.

Want to share this post?

Sep 8 2010

I couldn't resist parodying this:

I believe the original is a parody too.

The first MySQL fanboy to defend it in the comments gets laughed at hysterically. Then I stab them in the face.

Like this post? Subscribe to my RSS feed and follow me on twitter to hear about new posts early.

Want to share this post?

Apr 22 2010

You have a SQL database and you want a column to be restrained to one of a few values. MySQL fanbois will tell you to use an enum. What should you use if you're running PostgreSQL?

There's a few possible solutions. David Wheeler wrote a great article on enforcing a set of values in PostgreSQL. You should definitely check it out to understand all the available options in detail, but here's the summary as taken from Josh Berkus' comment:

  • If the list will never change, and ordering is preset or doesn't matter: use an ENUM
  • If the list might change but changes require programming, OR the list will not change but needs to be alpha sorted: use a DOMAIN
  • If the list will change, and changes do not require other programming: use a lookup table.

You're probably familiar with ENUMs, and a lookup table is not hard to understand, but what is a domain?

Domains are a datatype that you can attach constraints to. They're different from ENUMs because you pick a datatype (e.g. TEXT), and then apply any arbitrary constraint to it. For example, you can simply re-implement an ENUM:

CREATE DOMAIN color TEXT
    CONSTRAINT valid_value CHECK (VALUE = 'red' OR VALUE = 'blue');

Or you can go one step further:

CREATE DOMAIN url TEXT
    CONSTRAINT valid_value CHECK (VALUE ~ '^https?://');

Let's see you do that with an ENUM!

Note that I said "attach constraints to". You can have more than one constraint apply to a domain at once:

CREATE DOMAIN url TEXT
    CONSTRAINT starts_right CHECK (VALUE ~* '^https?://')
    CONSTRAINT min_length CHECK (LENGTH(VALUE) > 10);

How does this differ from simply declaring a column constraint? Well for one thing, you can use the domain on columns across as many tables as you please. And if you have to change it, you can do so in one place:

ALTER DOMAIN url DROP CONSTRAINT min_length;
ALTER DOMAIN url ADD CONSTRAINT min_length CHECK (LENGTH(VALUE) > 15);

To use a domain, use its name as the type for a column:

CREATE TABLE rss_feed (
    url URL NOT NULL,
    content TEXT
);

I've put the name of the domain in capitals in the above example, which is allowed (and also is aesthetically pleasing given I generally specify all my datatypes using capitals).

To view all domains in your database, use \dD:

                                                                           List of domains
 Schema |       Name        |  Type   | Modifier |                         Check
--------+-------------------+---------+----------+--------------------------------------------------------------
 public | url               | text    |          | CHECK (VALUE ~ '^https?://'::text) CHECK(LENGTH(VALUE) > 15)

Dropping a domain is as simple as using DROP DOMAIN. Easy!

I have found a few gotchas with the PostgreSQL implementation of domains:

  • You can create domain constraints without a name, they will be named domainname_check, domainname_check1, and so on. Given that you need the name to alter the constraints later (one of the big advantages of domains after all!), it's better to just name them: CREATE DOMAIN url AS TEXT CONSTRAINT starts_right CHECK (VALUE ~* 'https?://');
  • ALTER DOMAIN DROP CONSTRAINT always returns success, even if the constraint doesn't exist.

These points aside, domains are a solid tool in a PostgreSQL admin's arsenal. As you can see, they're easy to use and more powerful than ENUMs. They're also SQL standard conforming - something that ENUMs are not!


Resources

Like this post? Subscribe to my RSS feed and follow me on twitter to hear about new posts early.

Want to share this post?