Steven Haryanto > SHARYANTO-SQL-Schema > SHARYANTO::SQL::Schema



Annotate this POD


View/Report Bugs
Module Version: 0.09   Source  


SHARYANTO::SQL::Schema - Routine and convention to create/update your application's DB schema


version 0.09


This module uses Log::Any for logging.

To use this module, you typically run the create_or_update_db_schema() routine at the start of your program/script, e.g.:

 use DBI;
 use SHARYANTO::SQL::Schema qw(create_or_update_db_schema);
 my $spec = {...}; # the schema specification
 my $dbh = DBI->connect(...);
 my $res = create_or_update_db_schema(dbh=>$dbh, spec=>$spec);
 die "Cannot run the application: cannot create/upgrade database schema: $res->[1]"
     unless $res->[0] == 200;

This way, your program automatically creates/updates database schema when run. Users need not know anything.


Why the name SHARYANTO::*?

I haven't decided on a better name. See SHARYANTO.

Why use this module instead of other similar solution?

Mainly simplicity. I write simple application which is often self-contained in a single module/script. This module works with embedded SQL statements instead of having to put SQL in separate files/subdirectory.

How do I see each SQL statement as it is being executed?

Try using Log::Any::For::DBI, e.g.:

 % TRACE=1 perl -MLog::Any::For::DBI -MLog::Any::App ...



Some other database migration tools that directly uses SQL:


Steven Haryanto <>


This software is copyright (c) 2013 by Steven Haryanto.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.


None are exported by default, but they are exportable.

create_or_update_db_schema(%args) -> [status, msg, result, meta]

With this routine (and some convention) you can easily create and update database schema for your application in a simple way using pure SQL.

Version: version is an integer and starts from 1. Each software release with schema change will bump the version number by 1. Version information is stored in a special table called meta (SELECT value FROM meta WHERE name='schema_version').

You supply the SQL statements in spec. spec is a hash which at least must contain the key latest_v (an integer) and install (a series of SQL statements to create the schema from nothing). It should be the SQL statements to create the latest version of the schema.

There should also be zero or more upgrade_to_v$VERSION keys, the value of each is a series of SQL statements to upgrade from ($VERSION-1) to $VERSION. So there could be upgrade_to_v2, upgrade_to_v3, and so on up the latest version.

This routine will check the existence of the meta table and the current schema version. If meta table does not exist yet, the SQL statements in install will be executed. The meta table will also be created and a row ('schema_version', 1) is added.

If meta table already exists, schema version will be read from it and one or more series of SQL statements from upgrade_to_v$VERSION will be executed to bring the schema to the latest version.

Currently only tested on MySQL, Postgres, and SQLite. Postgres is recommended because it can do transactional DDL (a failed upgrade in the middle will not cause the database schema state to be inconsistent, e.g. in-between two versions).

Arguments ('*' denotes required arguments):

Return value:

Returns an enveloped result (an array). First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (result) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

syntax highlighting: