Auto-generate a Database Schema Diagram

Generate a Live Database Schema Diagram for your Catalyst Application

When building a web application, I always create a URL endpoint that generates a database schema diagram on the fly. This is a quick reference item that never gets old.

This approach can be used with any Perl web framework. The provided example is for the Catalyst Framework using a DBIx::Class ORM. This example provides the url http://mywebsite.com/dbdiagram

=head2 dbdiagram

Render an image representing the current data model

=cut

use SQL::Translator;
sub dbdiagram :Local :Args(0) {
    my ($self, $c) = @_;

    my $translator = SQL::Translator->new(
        parser => 'SQL::Translator::Parser::DBIx::Class',
        data   => $c->model('Ezn')->schema,
        producer => 'Diagram',
        producer_args => {
            output_type => 'png',
            title       => "My clever database diagram",
            add_color   => 1,
            font_size   => 'large',
        },
    ) or die SQL::Translator->error;

    $c->res->content_type('image/png');
    $c->res->body($translator->translate);
}

Don't Forget to restrict to authorized users!

See also:

• Catalyst Framework
• DBIx::Class
• SQL::Translator::Parser::DBIx::Class
• SQL::Translator::Producer::Diagram

Comments

[fluffy]

Oh wow, this is perfect! I recently started a job in which a primary role is maintaining and extending a rather large Catalyst app and I have never used Catalyst before. I'm also about ten years out-of-date on my Perl knowledge. So, every little bit helps. :)

[Mitch Jackson]

Congrats on the new job, thanks for letting me know this was helpful.

[Paulo Renato]

A production system should not have this endpoint at all. This is bad as a security practice and means that your automation as failed or does not exist at all.

Please if you really need it just expose it from the command line.

NOTE: having ssh access to a production system also means failure in you automation pipeline.

[Mitch Jackson]

You raise a good point: this feature is obviously to support development, not for production

[Thorsten Hirsch]

What's :Local and :Args(0)?

[Mitch Jackson]

These function attributes are particular to the Catalyst MVC Framework.

You would include this function in a Catalyst::Controller package. In this case, you may choose YourApp::Controller::Debug as the controller name.

Catalyst will map this dbdiagram action to an application URL, as specified by these function attributes.

With :Local, this action URL part will be the function name, relative to the controller name. http://yourapp/debug/dbdiagram

A Catalyst action can receive function arguments as parts of the request URL. :Args(0) specifies this action will receive 0 arguments this way. This makes Pretty URL's easy. dbdiagram might accept a database name, and table name as arguments. This action would respond to the URL: http://yourapp/debug/dbdiagram/database_name/table_name.

sub dbdiagram :Local :Args(2) {
    my ($self, $c, $db_name, $table_name) = @_;

    # ...
}