PgRebase is a tool that allows you to easily handle your postgres codebase for functions, triggers, custom types and views.
If you started outsourcing data manipulation to your database through postgresql cool features, you probably realized this is painful. Postgresql's functions, triggers, custom types and views are not your usual codebase, they live in postgres, and you often have to drop them if you want to edit them, eg when you change a function signature. You could edit them directly in psql, but then it's migrating servers / other devs installation that becomes difficult.
The classic tool for this is the migration software, asking you to manage migration files. This is great for handling tables, not so great to make frequent changes to your functions. Can we do better?
Still confused about what the problem is here? See this more in depth explanation on Hacker News.
PgRebase allows you to manage your functions/triggers/types/views as plain files in
filesystem. You put them in a sql/ directory, one file per
function/trigger/type/view.
$ tree sql/
sql/
├── functions/
│ └── assign_user_to_team.sql
├── triggers/
│ └── user_updated_at.sql
├── types/
│ └── follower.sql
└── views/
└── user_json.sql
No need to add drop statement in those files, PgRebase will take care of it.
In watch mode (useful for development), just save your file, pgrebase will update your database. In normal mode (useful for deployment), pgrebase will recreate all functions/triggers/types/views found in your filesystem directory.
You can now work with postgres codebase live reload, then call pgrebase just after your migration task in your deployment pipeline.
go get github.com/oelmekki/pgrebase
Binary will be in $GO_PATH/bin/pgrebase. This is a static binary, so it's
safe to copy it in your project (providing any system calling it is from the
same architecture).
You can also download prebuilt PgRelease from release page (only for linux/amd64).
$ export DATABASE_URL=postgres://user:pass@host/db
$ ./pgrebase sql/
Loaded 10 functions
Loaded 25 views
Loaded 5 triggers - 1 trigger with error
error while loading sql/triggers/user_updated_at.sql
column users.updated_at does not exist
Loaded 3 types
$ ./pgrebase -w sql/
Loaded 10 functions
Loaded 25 views
Loaded 6 triggers
Loaded 3 types
Watching filesystem for changes...
FS changed. Building.
When working in development environment, you'll probably want to use watch mode
(-w) to have your changes automatically loaded.
For deployment, add pgrebase to your repos and call it after your usual
migrations step:
DATABASE_URL=your_config ./pgrebase ./sql
You can specify dependencies for files using require statement, provided those files are of the same kind. That is, function files can specify dependencies on other function files, type files can define dependencies on other type files, etc.
Here is an example about how to do it. Let's say your sql/functions/foo.sql
files depends on sql/functions/whatever/bar.sql:
$ cat sql/functions/foo.sql
-- require "whatever/bar.sql"
CREATE FUNCTION foo()
[...]
Filenames are always relative to your target directory (sql/ in that
example), and within in, to the code kind (functions/ here).
Do not try to do funky things like adding ./ or ../, this is no path
resolution, it just tries to match filenames.
You can add multiple require lines:
-- require "common.sql"
-- require "hello/world.sql"
-- require "whatever/bar.sql"
CREATE FUNCTION foo()
[...]
There is no advanced debugging for circular dependencies for now, so be sure not to get too wild, here (or else, you will have a "maybe there's circular dependencies?" message and you will have to figure it out for yourself).
-
pgrebase doesn't keep any state about your codebase and does not delete what is in your database and is not in your codebase. This means that if you want to remove a trigger/type/view/function, deleting its file is not enough. You have to use your usual way to migrate db and remove it.
-
trigger files should contain both trigger creation and the function it uses. This is to avoid dropping function still used by trigger (if processing functions first) or create trigger before its function (if triggers are processed first)
-
files should only contain the definition of the view/function/type/trigger they're named after (with the exception of trigger files declaring the function they use). Hazardous results will ensue if it's not the case: only the first definition will be dropped, but the whole file will be loaded in pg.
-
pgrebase single top concern is to not delete any data. This means that no DROP will CASCADE. This means that if your database structure depends on anything defined in pgrebase codebase, it will fail to reload it when it implies dropping it (that is, most of the time). An important implication of that is that you should not add types in pgrebase codebase if they are used as column types in your tables. For those, declare your types using your usual migration technique. To be more clear: only define here types that are only used by your functions/views/triggers.
Pgrebase is a fairly recent project. I already use it on production and it works fine for me, but given I'm probably its only user for now, it's probably biased toward how I write my sql code. If you find any problem while parsing your sql code, please let me know!
PgRebase was born after discussing with Derek Sivers about moving business logic to the database. Make sure to read his research, it's awesome!