for functions/views/etc: I've used https://github.com/omniti-labs/pg_extractor then removed roles from dump (because the fluctuated between developers) and executed
perl -000 -i -lne 'print if ! /^--/ && ! /^SET /' `find schema/ -name '*.sql'` || exit 1
The same script had been run on CI too, to verify that developer didn't forget to run it in the PR.
That's shocking to hear
Do you feel that doing access control outside the db is faster overall? (considering it most likely involves more round trips into the db)
Apps forget that extra AND on the WHERE clause *all the time*. Just one ad hoc script querying the database can ruin your whole security-oriented day.
Do policies make schema design slower? Yes. Do they make queries slower? Not in my experience, but that may be due to our familiarity with Postgres and its planner. Do they basically eliminate data leaks to the end user? Absolutely.
DB policies to me are like Rust vs C++. Someone maybe able to write C++ faster and with less training, but having those extra checks at the outset can save so much time and heartache down the road. It's an investment, not a cost.
Jetbrains are really kicking some goals.
Scratch file with a quick query in about 2 seconds is amazing
Cornucopia queries look like this (here is a something.sql file)
--! authors
SELECT first_name, last_name, country FROM Authors;
--! insert_author
INSERT INTO Authors(first_name, last_name, country)
VALUES (:first_name, :last_name, :country);
I think the only thing that might require specific support in postgres_lsp is using the :parameter_name syntax for prepared statements [1] (in vanilla Postgres would be something like $1 or $2, but in Cornucopia it is named to aid readability). But, if postgres_lsp is forgiging enough to not choke on that, then it seems completely fit for this use case.
[0] https://github.com/cornucopia-rs/cornucopia
[1] https://cornucopia-rs.netlify.app/book/writing_queries/writi...
To tell the truth I've been waiting for postgres_lsp to mature before trying it out, but based on this example [1] I think it does support multiple queries.
Since it uses a parser extracted from Postgres, the nonstandard syntax would probably trip it up, but there's probably a way to fix that.
[1] https://github.com/supabase/postgres_lsp/blob/main/example/f...
It also manages migrations for tables, types, and other stuff in a really simple way. Upgrades are fully atomic, and it lets you write unit tests in SQL - which are run after every upgrade, run inside save points so they don’t affect the database, and can run during production deployments.
It’s sort of my own personal (open source) Swiss Army knife of plpgsql development. It’s a complete work in progress, not production ready, probably has bugs, and needs more and better documentation - but I use it daily. It lets me use Postgres as my main development environment.
[0] https://github.com/pgpkg/pgpkg
(It also lets you import packages from other sources so you can create libraries of reusable code, within some limits)
The best thing (IMO) is that there is basically no funny stuff, no filename conventions, no funny delimiters. It’s just regular Postgres SQL, and a couple of very small config files. Works perfectly with git.
This is 100% the key to sanity with managing database stored procedures and functions -- ability to manage them in Git like normal code and deploy them like code.
In contrast, the workflow from traditional imperative database "migration" tools is just super awkward for developing and maintaining any non-trivial number of SQL stored programs (procs, funcs, triggers, views, etc).
I wrote a blog post about this a few months ago, and although my product is aimed at MySQL and MariaDB, many of the concepts discussed apply to any relational DB: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
I did decide that declarative tables were too hard when I wrote the first predecessor of pgpkg in bash 10+ years ago - but maybe I should reconsider now that I’m working in Go!
The thing is, it’s a bit of a rabbit hole, there isn’t much tooling around this stuff despite stored functions being so, so much easier for writing database logic than anything else.
I feel the industry has wasted an enormous amount of time on ORMs and other nonsense when stored procedures have been under our noses the whole time.
https://github.com/sqldef/sqldef (Go)
https://github.com/bikeshedder/tusker (Python but being ported to Rust)
https://github.com/tyrchen/renovate (Rust)
https://github.com/blainehansen/postgres_migrator (Rust)
Some of these are based on parsing SQL, and others are based on running the CREATEs in a temporary location and introspecting the result.
The schema export side can be especially tricky for Postgres, since it lacks a built-in equivalent to MySQL's SHOW CREATE TABLE. So most of these declarative pg tools shell out to pg_dump, or require the user to do so. But sqldef actually implements CREATE TABLE dumping in pure Golang if I recall correctly, which is pretty cool.
There's also the question of implementing the table diff logic from scratch, vs shelling out to another tool or using a library. For the latter path, there's a nice blog post from Supabase about how they evaluated the various options: https://supabase.com/blog/supabase-cli#choosing-the-best-dif...
The problem I had was that I could never come up with a declarative scheme that would allow reliable data transformation for all databases over the long term.
For example - if I have a table “lookup” with two columns “key” and “name”, over a period of years we might see a transformation like this:
alter table lookup rename column name to description;
[…later…]
alter table lookup add column name text default description;
[…later…]
alter table lookup drop column description;
I couldn’t ever come up with a solution to this that’s simpler than a sequence of migration scripts, which is always repeatable. I haven’t had a look at Skeema yet but am curious how you deal with this?
re: SHOW CREATE TABLE, I was referring to the export logic, not diff logic. In other words, when a new user adopts a declarative schema management tool, they need some way of dumping their existing database schema to the filesystem as a set of CREATE statements. Most pg tools seem to just leverage pg_dump for this, but in my opinion that's not great since it's an external dependency.
And then ideally there's also a way to re-sync the filesystem in the future as needed, pulling the latest definitions from a given DB server. This is also an export, but it should be smart enough to only overwrite CREATE statements where the corresponding object has actually changed, to avoid stomping on formatting or inline comments.
The ability to do a "pull" operation is very useful in development workflows: engineers can make DDL changes to a dev DB directly while developing a feature, and then pull those changes into the filesystem to turn them into a git commit / pull request. It's also sometimes useful in production workflows, in case someone had to make an "out of band" emergency hotfix directly to the prod DB, outside of the schema management tool.
> alter table lookup rename column name to description
> I haven’t had a look at Skeema yet but am curious how you deal with this?
Skeema just doesn't support renames directly at all. In practice this actually works out fine, since renames are hugely problematic in production databases anyway due to deploy-order concerns: there's no way to deploy an application change at the same exact moment as the RENAME is executed in SQL, so it cannot be performed "online". Best practice with schema changes is for applications to be able to work fine with both the old and new schema, and renames typically break this rule. (Well, unless you do a convoluted multi-step dance with view-swapping, for DBMS that support transactional DDL... this is doable in pg, but not in all other DBs.)
In Skeema, if you really need to do a rename, you can do it out-of-band (outside Skeema) on all environments (prod/stage/dev/etc), and then use `skeema pull` to update the filesystem definition to match.
Or for new tables that aren't populated in prod yet, happily a rename is entirely equivalent to drop-then-re-add. So this case is trivial, and Skeema can be configured to allow destructive changes only on empty tables.
Got it. Yeah - tbh I hadn't thought about that, but declarative tables never been on my radar (for the reasons mentioned).
> Skeema just doesn't support renames directly at all
Right! that's one way to deal with it! :)
> there's no way to deploy an application change at the same exact moment as the RENAME is executed in SQL
I guess it depends on your ops environment, but I don't think renames are special; any schema update can be incompatible with client code if you're not careful. That said, in the systems I've worked with (telco and saas), the schema upgrade has always run with applications stopped - the migration code is shipped with the server application, and run as part of its startup sequence. Even if there are multiple clients, the only requirement is that they are all stopped before the upgraded processes start. (This is something you can delegate to K8s for example).
I think the interesting thing is that there are such very different approaches to schema upgrades. Skeema and pgpkg obviously have different views about how migrations should be managed, despite the fact that you (presumably) and I have both done many tens of thousands of them over the years.
One important difference, as you mention, is that PG has transactional DDL - which really changes the game in terms of how you can think about upgrades. pgpkg is deliberately PG specific; it will never support any other kind of database, because the assumptions it makes are invalid in most other systems.
When I worked on a system that used a lot of postgres triggers and stored procedures we built a little mechanism on top of our existing database migration tool that would check a directory of plpgsql files and generate migrations when they were created or updated. It worked fine.
It wasn’t the most perfect developer workflow, and I was suspicious when I first encountered the way the software used all of the stored procedures, however I came to appreciate that we were able to be a bit freer with changes to the application code because of this semi-isolated layer that took care of some critical stuff right in the database.
A lot of the ecosystem has to be built out for me to want to use Postgres functions. The benefit is not there.
Edit: I didn't mean writing simple SQL queries. I meant writing your business/app logic in Postgres functions.