5 ms·

Love postgres and use it extensively. However, there's always an issue when I start doing the more advanced stuff: how do I combine that with all my years of ex

by adlpz 3y ago

Love postgres and use it extensively. However, there's always an issue when I start doing the more advanced stuff: how do I combine that with all my years of experience with version control, code reviews, types, tests, static analysis and all the niceties of coding in general? Migrations?

vmfunction 3y ago
If you want to serious develop postgres as how you develop code, then you should also use a LSP. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp
- cpursley 3y ago
  Thanks, I need to add this to my list as well.
- realaleris149 3y ago
  If you are using IntelliJ (Ultimate), it also have very good support for SQL (syntax highlighting, autocomplete, etc.) and is Postgresql flavor aware. https://www.jetbrains.com/help/idea/database-tool-window.html https://www.jetbrains.com/help/idea/database-tool-window.htm...
agentultra 3y ago
It's not a great DX for sure. Once you start stuffing a ton of application logic in Postgres triggers (or worse split logic between your application layer and triggers) your system will explode in complexity really fast. Developers will insert a row in an inconspicuous table and things will break in ways that seem mysterious because it's not obvious, looking at the application code, why the system is misbehaving. There are commands in most postgres clients, even psql, to view the _currently_ defined functions... but when you go to debug those you will have to look through migrations to see how the function came into it's current state... bisecting through history here is not very useful since each change to the function is a new file. I think this can be fixed though and made much easier, it's just not there yet. In general I don't think the developer tooling is up to par to push very much of your application logic into postgres itself. I recommend using triggers for consistency and validation or table-local updates (ie: timestamps, audit logs) but keep process-oriented behaviour (iow: when this happens, then that, else this, wait for call and insert here, etc) in the application layer (chasing a cascade of triggers is not fun and quite annoying). ... all that being said, you can do unit testing in Postgres. And there is decent support for languages other than pgSQL (ie: javascript, ocaml, haskell, python, etc). It's possible to build dev tooling that would be suitable to make Postgres itself an application development platform. I'm not aware of anyone who has done it yet.
cpursley 3y ago
Migrations. All my database logic lives in version control. Popular tooling like Phoenix, Hasura, etc have good built in migration stories. https://www.bytebase.com https://www.bytebase.com looks really promising. Hover, I do struggle with one big issue: changing database logic (views, functions, etc) that has other logic dependent on it. This seems like a solvable problem.
0xbadcafebee 3y ago
RDBMS's are still very much an 80s thing. Great at dynamic queries, horrible at managing changes in an immutable, atomic, versioned way. The structure and relationship of data is so brittle it's kind of crazy it's still used. Probably it's because so many people have grown dependent on its particular flaws; the incumbent just lumbers on.
- tmountain 3y ago
  There’s always a trade off though. Append-only storage models are great for use cases where they are truly necessary, but they also store substantially more data than mutable databases do, and designing for something like Datomic carries a heavier cognitive cost than a simple RDBMS.
gen220 3y ago
You might be curious to look up how Alembic and Django accomplish migrations. Even if Python's not your jam, the concepts/semantics are language/framework agnostic. Basically, you store each migration in a file, and you "squish" the migration history down to a table definition once you've decided you're happy with the change and it's been affected across all the different deployment environments. It's not perfect, but it works reasonably well.
realaleris149 3y ago
There is a bit of tooling needed but is already around. For Java for example I had very good experience with a combination of flyway [1] for migrations, testcontainers [2] for making integration tests as easy as unit tests and querydsl [3] for a query and mapping layer. [1] https://github.com/flyway/flyway https://github.com/flyway/flyway [2] https://java.testcontainers.org/modules/databases/postgres/ https://java.testcontainers.org/modules/databases/postgres/ [3] https://github.com/querydsl/querydsl https://github.com/querydsl/querydsl

by adlpz 3y ago

vmfunction 3y ago

If you want to serious develop postgres as how you develop code, then you should also use a LSP. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp

cpursley 3y ago
Thanks, I need to add this to my list as well.
realaleris149 3y ago
If you are using IntelliJ (Ultimate), it also have very good support for SQL (syntax highlighting, autocomplete, etc.) and is Postgresql flavor aware. https://www.jetbrains.com/help/idea/database-tool-window.html https://www.jetbrains.com/help/idea/database-tool-window.htm...

agentultra 3y ago

It's not a great DX for sure. Once you start stuffing a ton of application logic in Postgres triggers (or worse split logic between your application layer and triggers) your system will explode in complexity really fast. Developers will insert a row in an inconspicuous table and things will break in ways that seem mysterious because it's not obvious, looking at the application code, why the system is misbehaving. There are commands in most postgres clients, even psql, to view the _currently_ defined functions... but when you go to debug those you will have to look through migrations to see how the function came into it's current state... bisecting through history here is not very useful since each change to the function is a new file. I think this can be fixed though and made much easier, it's just not there yet. In general I don't think the developer tooling is up to par to push very much of your application logic into postgres itself. I recommend using triggers for consistency and validation or table-local updates (ie: timestamps, audit logs) but keep process-oriented behaviour (iow: when this happens, then that, else this, wait for call and insert here, etc) in the application layer (chasing a cascade of triggers is not fun and quite annoying). ... all that being said, you can do unit testing in Postgres. And there is decent support for languages other than pgSQL (ie: javascript, ocaml, haskell, python, etc). It's possible to build dev tooling that would be suitable to make Postgres itself an application development platform. I'm not aware of anyone who has done it yet.

cpursley 3y ago

Migrations. All my database logic lives in version control. Popular tooling like Phoenix, Hasura, etc have good built in migration stories. https://www.bytebase.com https://www.bytebase.com looks really promising. Hover, I do struggle with one big issue: changing database logic (views, functions, etc) that has other logic dependent on it. This seems like a solvable problem.

0xbadcafebee 3y ago

RDBMS's are still very much an 80s thing. Great at dynamic queries, horrible at managing changes in an immutable, atomic, versioned way. The structure and relationship of data is so brittle it's kind of crazy it's still used. Probably it's because so many people have grown dependent on its particular flaws; the incumbent just lumbers on.

tmountain 3y ago
There’s always a trade off though. Append-only storage models are great for use cases where they are truly necessary, but they also store substantially more data than mutable databases do, and designing for something like Datomic carries a heavier cognitive cost than a simple RDBMS.

gen220 3y ago

You might be curious to look up how Alembic and Django accomplish migrations. Even if Python's not your jam, the concepts/semantics are language/framework agnostic. Basically, you store each migration in a file, and you "squish" the migration history down to a table definition once you've decided you're happy with the change and it's been affected across all the different deployment environments. It's not perfect, but it works reasonably well.

realaleris149 3y ago

There is a bit of tooling needed but is already around. For Java for example I had very good experience with a combination of flyway [1] for migrations, testcontainers [2] for making integration tests as easy as unit tests and querydsl [3] for a query and mapping layer. [1] https://github.com/flyway/flyway https://github.com/flyway/flyway [2] https://java.testcontainers.org/modules/databases/postgres/ https://java.testcontainers.org/modules/databases/postgres/ [3] https://github.com/querydsl/querydsl https://github.com/querydsl/querydsl