Why use stored procedures?

• no round trip time
  • especially useful if you have to "piece together" your data
• work with the database-native data-types
  • jsonb
• can be used to create a API-layer in the database
  • low overhead
  • known protocol
    • if you're already talking to the database

Disadvantages of stored procedures

• they're "hidden in the database"
  • especially if you're not familiar with the database tooling
• difficult to manage

Database Schema Management: Migrations

the somewhat standard way

• have migrations steps:

  • for one version up do this
    • alter table foo add column bar text
    • create index foo_bar_idx on foo(bar)
  • for one version down do that
    • alter table foo drop column bar
    • drop index foo_bar_idx

• crappy way to manage stored procedures

  • if stored procedures are part of your application
  • because code changes more rapidly than the schema

Example: Mojo::Pg::Migrations

• Allows database schemas to evolve easily over time
• Uses 'migration files':
  • just a collection of sql statements
  • separated by comments of the form -- VERSION UP/DOWN
• Lets you migrate from any version to any version, up or down, in small steps
• Migrations are performed in transactions
  • 'Transactional DDL' is a PostgreSQL feature
• Locking is used to prevent multiple migrations running at the same time
  • important for the auto_migrate feature
• The current version is stored in the automatically created table 'mojo_migrations'

Example

(From perldoc Mojo::Pg::Migrations)

-- 1 up
create table messages (message text);
insert into messages values ('I ♥ Mojolicious!');
-- 1 down
drop table messages;
-- 2 up (...you can comment freely here...)
create table stuff (whatever int);
-- 2 down
drop table stuff;

$db->migrations->from_file('migrations.sql')->migrate;

Introducing: ‘dbdings’

"do something sensible with the database"

Why 'dbdings'?

• because we wanted to be able to version control stored procedures
  • one .sql file per stored procedure
    • so git/cvs can track version history nicely
  • 'standard' Edit-Compile-Run cycle
    • 'compile' -> 'load stored procedure'
• because the existing open source (Perl) database migration solutions that I could find do not have special handling of stored procedures
  • every change to a stored procedure is a schema upgrade
  • not a single file per procedure
• because I wanted something small and simple (KISS)

How dbdings works (migration)

• on disk db/schema/.version contains the 'should' version
• in the database table _schema contains the 'is' version
• to go from version N to N+1 (upgrade):
  • execute db/schema/v{N+1}/alter.sql to upgrade
  • execute db/schema/v{N+1}/verify.sql to do checks
• to go from version N to N-1 (downgrade):
  • execute db/schema/v{N}/downgrade.sql to upgrade
  • execute db/schema/v{N-1}/verify.sql to do checks
• both scripts are executed in 1 transaction
  • using PostgreSQL's transactional DDL
• dbdings can also create the schema from scratch
  • I'll skip over the details for now

Example

db/schema/v8/alter.sql:

alter table jobs add column current_depth integer
    not null default 1;
alter table jobs_archive add column current_depth integer;

db/schema/v8/downgrade.sql:

alter table jobs drop column current_depth;
alter table jobs_archive drop column current_depth;

Example cont'd

db/schema/v8/verify.sql:

DO $BODY$
BEGIN
        ASSERT (SELECT EXISTS(
                SELECT
                        true
                FROM
                        information_schema.columns
                WHERE
                        table_schema='jobcenter'
                        AND table_name='jobs'
                        AND column_name='current_depth'
        )), 'no column current_depth in table jobs??';
        ASSERT (SELECT EXISTS(
                SELECT
                        true
                FROM
                        information_schema.columns
                WHERE
                        table_schema='jobcenter'
                        AND table_name='jobs_archive'
                        AND column_name='current_depth'
        )), 'no column current_depth in table jobs_archive?';
        RAISE NOTICE 'all tests succesfull?';
END
$BODY$;

Schema commands

• check: check schema version in db and repo
• create: create schema in db
• downgrade [ver]: try to downgrade db schema version to [ver]
• upgrade: try to upgrade db schema version to repo version
• verify: verify latest schema upgrade

How dbdings works (stored procs)

• db/procs/*.sql is the 'should' situation
• table _procs contains the 'is' situation according to dbdings
  • md5sum of the function code
• 'dbding compare' detects
  • procedure changed on disk (only)
    • use 'dbdings load [proc]' to load into db
    • use 'dbding update' to load all changed procedures
  • procedure changed in db (only)
    • maybe changed by psql's '\ef [proc]'.
    • use 'dbdings save [proc]' to save to disk
    • refuse to overwrite with 'update'
    • may overwrite with 'load'
  • procedure changed both on disk and in db
    • panic!
    • user needs to decide what needs to be done

Stored procedures commands

• compare: compare version between db and repo
• load : load from repo into db
• save : save from db to repo
• update: update procs in db from repo

Possible enhancements

• Current status 'It Works For Me' ™

• Better integration with git?

  • record git commit hash somewhere?
  • 'downgrade' also downgrades checkout?
  • do upgrades/downgrades 1 step at a time
    • makes it easier to delete things

• Locking like Mojo::Pg?

Possible future work

• Turn it into a seperate standalone tool?

• Support for other databases?

  • Needs a way to do a md5sum of the proc in the db

Questions? Suggestions?

‘dbdings’ is part of the JobCenter:

• https://github.com/a6502/jobcenter
• https://gitlab.com/a6502/jobcenter

(file db/dbdings)