I'm starting to move more logic into the database, using triggers, views,
functions, CTEs, etc. When plv8/json comes out for postgres, I can see
myself putting lots of logic in there.
I'm having problems with the "standard" way of doing database migrations in
sequel and activerecord. Both sequel and activerecord let you put
arbitrary sql code into timestamped files. When each file is ran, a
schema_versions table is updated with the filename (or timestamp in the
filename), which keeps record of which migrations have been applied to the
current database.
If a lot of coding is being done at the database level, that means that
modifications to existing views, functions, etc usually result in something
like this:
*Migration 1* defines a function and a view that uses that function.
create function calculate(x int) returns int as $$
return x + 1;
$$ language sql;
create view foos as (
select something, calculate(something) from a_table
);
*Migration 2* changes the type of the function. I have to drop all objects
that depend on foo, and recreate them by copying their entire body -- even
if there weren't any changes in most of the other code!
-- Have to drop view before I can change the type of the `calculate`
function.
drop view foos;
create or replace calculate(x bigint) returns bigint as $$
return x + 1;
$$ language sql;
-- I could do `drop function calculate(int) cascade`, but I
might accidentally drop some objects that wouldn't get recreated below.
-- Now I have to recreate foo.
create view foos as (
select something, calculate(something) from a_table
);
If I'm building a system based on views and functions and triggers, my
migrations would be filled with duplicated code, and it's difficult to find
the latest version of the code. You might say "don't do that!", but for my
purposes (e-commerce, shipping, transactions), I'm finding it's a lot
easier and faster to have the database ensure the integrity of the data by
doing the logic inside the database.
You can (of course) dump the current database schema (which includes all
the code definitions), but I think you lose comments. And you wouldn't
generally want to edit a giant file that contains the whole schema.
Any ideas on how to solve this problem?
My best idea is to how the sql code contained in their own canonical files
(app/sql/orders/shipping.sql, app/sql/orders/creation.sql, etc). Everyone
develops directly on these. Whenever it's time for a release, then you'd
want to make a new migration file, look at all the changed code since the
previous release, figure out the dependency chain of the database objects
that need to be dropped and recreated, and then copy the sql from the
canonical sql files into a new sequel/activerecord migration file. But
it's a pain. :/
Thoughts are very welcome. I hope I explained this well enough, I'm cutting
back on my caffeine intake and I'm a little groggy atm.
Oh, I asked a similar question on Stack
Overflow: http://stackoverflow.com/q/9711405/17076
The result was a function that let me pass in:
- sql code to run
- database views to drop and recreate
The function would retrieve the view definition, drop the views, run the
sql code, then recreate the view definition (in reverse order of dropping).
Perhaps a system of functions like this would help solve the problem of
having to copy/paste sql code into the migration files.
Joe
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/ujiWlAy6EcsJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
