5 ms·

> no unit testing I also prefer to do stuff on the server because doing things in the database does invariably seem to drift towards a confusing, brittle mess.

> no unit testing I also prefer to do stuff on the server because doing things in the database does invariably seem to drift towards a confusing, brittle mess. However, I do wonder if that's necessarily the case or just a limitation of our frameworks. There's no principle that would prevent someone from setting up a simple test suite for database code. It would be a lot of work, but maybe there's an architecture nirvana right in front of our eyes if someone just were to properly engineer a good framework around Postgres functions.

aurareturn 3y ago
There's no IDE that can detect Postgres coding errors, no refactoring support, no Git integration. 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.
- maxbond 3y ago
  This is a big problem, but I am encouraged by this project. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp
  - nextaccountic 3y ago
    Does it support multiple queries per sql file? My use case is to use this to autocomplete queries made with Cornucopia [0] 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); There are multiple queries each separated by ; and on top of each query, there's a comment giving a name to the query (it's more like a header) 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 https://github.com/cornucopia-rs/cornucopia [1] https://cornucopia-rs.netlify.app/book/writing_queries/writing_queries#named-parameters https://cornucopia-rs.netlify.app/book/writing_queries/writi...
    - maxbond 3y ago
      Thank you for turning me on top Cornucopia, it looks awesome. I've used the very similar aiosql in Python, but I hadn't realized there was a Rust analog. 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/file.sql https://github.com/supabase/postgres_lsp/blob/main/example/f...
- codesnik 3y ago
  I've made some automatic schema dumping scripts for supabase/postgres code to become searchable and git-diffable and PR-friendly. And I eventually was able to make unit testing to work good enough that I even wrote migrations in a test-first way, it was just quicker to iterate. But overall experience felt like I'm constantly combating problems, solved looong time ago for other languages and ecosystems. It was weirdly fun, but what killed my interest is that row level security policies kill performance of even simple queries so much, and EXPLAIN doesn't help to well with it.
  - nextaccountic 3y ago
    Is it on github?
    - codesnik 3y ago
      no. it was a private project, and my solution wasn't general enough. for functions/views/etc: I've used https://github.com/omniti-labs/pg_extractor 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 to remove a lot of useless comments and flags from the dump (pg_dump output isn't too readable). This oneliner can strip too much, though, comments in functions shouldn't start from the 0 column. The same script had been run on CI too, to verify that developer didn't forget to run it in the PR.
  - nextaccountic 3y ago
    > what killed my interest is that row level security policies kill performance of even simple queries so much 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)
  - ttfkam 3y ago
    Had the opposite experience. Row-level policies had minimal overhead (<5%?) but improved security at the app layer tremendously. It was intensely satisfying to have cases where UI developers were complaining that data was missing. Turned out the access tags were wrong, and the data as tagged shouldn't have been visible in the first place. App had set the wrong tags but turns out would have happily returned the data if the policies hadn't been there. 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.
- sverhagen 3y ago
  Have you tried IntelliJ? I honestly do most of my SQL in psql, out of habit. But I'm in IntelliJ all the time for Java, and from what I've seen their query editor is way impressive.
  - thrixton 3y ago
    Second this but for Rider, should be the same engine I think. Jetbrains are really kicking some goals. Scratch file with a quick query in about 2 seconds is amazing
- doctor_eval 3y ago
  I wrote something [0]. It lets you write plpgsql functions, views, triggers and casts in a way that can be re-created without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. 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 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)
  - chuckhend 3y ago
    That looks like an awesome tool. I am going to try it out. Gave you a star!
    - doctor_eval 3y ago
      Awesome, thanks! I update it whenever I work with it, which has ramped up in the last few weeks (added cast support a few days ago). It uses pg_analyse to parse the SQL and uses a few tricks to get everything updated. 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.
  - evanelias 3y ago
    > without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. 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/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
- macNchz 3y ago
  There’s at least one PL/pgSQL linter out there. 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.
- bradyd 3y ago
  > There's no IDE that can detect Postgres coding errors, no refactoring support, no Git integration JetBrains DataGrip does all of that.
csnweb 3y ago
There are tools for Postgres unit testing https://wiki.postgresql.org/wiki/Test_Frameworks https://wiki.postgresql.org/wiki/Test_Frameworks. Which is not to say there isn’t any room for improving them.
- aurareturn 3y ago
  Write more Postgres functions to unit test Postgres functions. :))
  - maxbond 3y ago
    Isn't that normally the premise of testing? Eg writing Python functions to test other Python functions?
    - aurareturn 3y ago
      Yes but Python is nice to write. Postgres functions are not. At least not to me.
      - maxbond 3y ago
        Depending on what kind of deployment you have, you could use Tcl, Rust, or even Python if you could use untrusted extensions. (Not a comment on this particularly testing framework, but Postgres server-side programming more generally.) But I hear you, PgSQL can be very annoying and unergonomic, and it's not a language most people you're hiring will know upfront. Pushing things onto the backend isn't unreasonable. When I write tests for PgSQL, I write them in Python and run them from the client side, not on the server.
        ttfkam 3y ago
        pl/pgSQL is very good (and ergonomic) for a logical extension to SQL, aka set theory programming with intermediate state. It isn't and was never targeted toward general purpose programming like Python. That said, 100% agree that unit tests should live outside the DB. Querying for sets inside or outside makes no functional difference, and your DB doesn't need all that extra cruft.
sverhagen 3y ago
There's maybe no standard solution which could drive this forward as a practice, the way JUnit has taken on the Java community (I'm sure there are examples like that everywhere that may resonate). But nothing is stopping you from running Postgres in a Docker container, and wrap it in a unit test with the technology of your choice. The Postgres scripts (ddl, triggers, functions) could live in the same project. Setup, run tests, teardown. Each unit test can set up the data for a particular test case. That way you also codify the Postgres scripts, for which it then becomes "just" a deployment matter to get them from Git into the target environment. I have written all sorts of test harnesses, and I wish more people would think out of the box of their standard tool sets. Sure, I'm a Java developer, or one could be a SQL coder, but nothing is stopping us from writing a darn script. (By the way, I'm not suggesting running a large data warehouse in a unit test. But the parents said "unit testing", which suggests there exist units of smaller, isolated functionality in your project, if you're willing to find them.)
- quicksilver03 3y ago
  You have just described Testcontainers [1] , and if you are a Java developer you may want to look into Testcontainers for Java [2]. For example, in one of my projects I use TestNG to instantiate a MariaDB container, run the Flyway migrations on it and then populating the tables with the test data: mAPIDBContainer = new MariaDBContainer<>(DockerImageName.parse(MARIADB_CONTAINER_TAG)) .withDatabaseName("apidb") .withPassword("password") .withNetwork(containerNetwork) .withNetworkAliases("apidb") .withExposedPorts(3306); mAPIDBContainer.start(); Flyway flyway = Flyway.configure() .dataSource(mAPIDBContainer.getJdbcUrl(), mAPIDBContainer.getUsername(), mAPIDBContainer.getPassword()) .encoding("UTF-8") .locations("classpath:apidb/migrations") .load(); flyway.migrate(); ScriptUtils.runInitScript(new JdbcDatabaseDelegate(mAPIDBContainer, ""), "sql/apidb/apidb-test-data.sql"); [1] https://testcontainers.com/ https://testcontainers.com/ [2] https://java.testcontainers.org/ https://java.testcontainers.org/
- sasmithjr 3y ago
  > But nothing is stopping you from running Postgres in a Docker container, and wrap it in a unit test with the technology of your choice I agree. I'm in .NET land, I use EFCore as my ORM, and I use EFCore's migration features. My ORM models are in a separate project (same git repo) from both the web server project and test project, and any hand written SQL gets added to the migration scripts that EFCore generates. I spin up a docker container for postgres, my test code clears any existing DB, creates a DB in the container, and then runs the EFCore migrations on the DB. I have simple tests that make sure my CTEs are working correctly and that things like expected unique indices are also setup. This works both locally and in Github Actions. I just wouldn't call any of this a "unit" test. I put all this squarely in my integration test suite. I figure if IO is happening as a consequential part of the test (i.e. not setup/teardown), it's an integration test. I wonder how much that distinction is tripping people up? A lot of people think of unit tests as small, independent, and quick, so by only thinking about unit testing, they automatically rule out tests that have app code call out to a DB. Based on the sibling comment, I'm going to have to take a look at Testcontainers. I'm not sure how much it'll simplify at this point, but who knows!
rjbwork 3y ago
In the MS ecosystem they have the SSDT data tools and unit test projects. It uses the designer functionality of VS to allow you to write your arrange, act, and teardown in pure SQL, and gives some basic condition builders for result sets tor your assert, with an escape hatch if that's insufficient.

aurareturn 3y ago

There's no IDE that can detect Postgres coding errors, no refactoring support, no Git integration. 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.

maxbond 3y ago
This is a big problem, but I am encouraged by this project. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp
- nextaccountic 3y ago
  Does it support multiple queries per sql file? My use case is to use this to autocomplete queries made with Cornucopia [0] 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); There are multiple queries each separated by ; and on top of each query, there's a comment giving a name to the query (it's more like a header) 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 https://github.com/cornucopia-rs/cornucopia [1] https://cornucopia-rs.netlify.app/book/writing_queries/writing_queries#named-parameters https://cornucopia-rs.netlify.app/book/writing_queries/writi...
  - maxbond 3y ago
    Thank you for turning me on top Cornucopia, it looks awesome. I've used the very similar aiosql in Python, but I hadn't realized there was a Rust analog. 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/file.sql https://github.com/supabase/postgres_lsp/blob/main/example/f...
codesnik 3y ago
I've made some automatic schema dumping scripts for supabase/postgres code to become searchable and git-diffable and PR-friendly. And I eventually was able to make unit testing to work good enough that I even wrote migrations in a test-first way, it was just quicker to iterate. But overall experience felt like I'm constantly combating problems, solved looong time ago for other languages and ecosystems. It was weirdly fun, but what killed my interest is that row level security policies kill performance of even simple queries so much, and EXPLAIN doesn't help to well with it.
- nextaccountic 3y ago
  Is it on github?
  - codesnik 3y ago
    no. it was a private project, and my solution wasn't general enough. for functions/views/etc: I've used https://github.com/omniti-labs/pg_extractor 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 to remove a lot of useless comments and flags from the dump (pg_dump output isn't too readable). This oneliner can strip too much, though, comments in functions shouldn't start from the 0 column. The same script had been run on CI too, to verify that developer didn't forget to run it in the PR.
- nextaccountic 3y ago
  > what killed my interest is that row level security policies kill performance of even simple queries so much 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)
- ttfkam 3y ago
  Had the opposite experience. Row-level policies had minimal overhead (<5%?) but improved security at the app layer tremendously. It was intensely satisfying to have cases where UI developers were complaining that data was missing. Turned out the access tags were wrong, and the data as tagged shouldn't have been visible in the first place. App had set the wrong tags but turns out would have happily returned the data if the policies hadn't been there. 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.
sverhagen 3y ago
Have you tried IntelliJ? I honestly do most of my SQL in psql, out of habit. But I'm in IntelliJ all the time for Java, and from what I've seen their query editor is way impressive.
- thrixton 3y ago
  Second this but for Rider, should be the same engine I think. Jetbrains are really kicking some goals. Scratch file with a quick query in about 2 seconds is amazing
doctor_eval 3y ago
I wrote something [0]. It lets you write plpgsql functions, views, triggers and casts in a way that can be re-created without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. 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 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)
- chuckhend 3y ago
  That looks like an awesome tool. I am going to try it out. Gave you a star!
  - doctor_eval 3y ago
    Awesome, thanks! I update it whenever I work with it, which has ramped up in the last few weeks (added cast support a few days ago). It uses pg_analyse to parse the SQL and uses a few tricks to get everything updated. 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.
- evanelias 3y ago
  > without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. 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/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
macNchz 3y ago
There’s at least one PL/pgSQL linter out there. 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.
bradyd 3y ago
> There's no IDE that can detect Postgres coding errors, no refactoring support, no Git integration JetBrains DataGrip does all of that.

csnweb 3y ago

There are tools for Postgres unit testing https://wiki.postgresql.org/wiki/Test_Frameworks https://wiki.postgresql.org/wiki/Test_Frameworks. Which is not to say there isn’t any room for improving them.

aurareturn 3y ago
Write more Postgres functions to unit test Postgres functions. :))
- maxbond 3y ago
  Isn't that normally the premise of testing? Eg writing Python functions to test other Python functions?
  - aurareturn 3y ago
    Yes but Python is nice to write. Postgres functions are not. At least not to me.
    - maxbond 3y ago
      Depending on what kind of deployment you have, you could use Tcl, Rust, or even Python if you could use untrusted extensions. (Not a comment on this particularly testing framework, but Postgres server-side programming more generally.) But I hear you, PgSQL can be very annoying and unergonomic, and it's not a language most people you're hiring will know upfront. Pushing things onto the backend isn't unreasonable. When I write tests for PgSQL, I write them in Python and run them from the client side, not on the server.
      - ttfkam 3y ago
        pl/pgSQL is very good (and ergonomic) for a logical extension to SQL, aka set theory programming with intermediate state. It isn't and was never targeted toward general purpose programming like Python. That said, 100% agree that unit tests should live outside the DB. Querying for sets inside or outside makes no functional difference, and your DB doesn't need all that extra cruft.

sverhagen 3y ago

There's maybe no standard solution which could drive this forward as a practice, the way JUnit has taken on the Java community (I'm sure there are examples like that everywhere that may resonate). But nothing is stopping you from running Postgres in a Docker container, and wrap it in a unit test with the technology of your choice. The Postgres scripts (ddl, triggers, functions) could live in the same project. Setup, run tests, teardown. Each unit test can set up the data for a particular test case. That way you also codify the Postgres scripts, for which it then becomes "just" a deployment matter to get them from Git into the target environment. I have written all sorts of test harnesses, and I wish more people would think out of the box of their standard tool sets. Sure, I'm a Java developer, or one could be a SQL coder, but nothing is stopping us from writing a darn script. (By the way, I'm not suggesting running a large data warehouse in a unit test. But the parents said "unit testing", which suggests there exist units of smaller, isolated functionality in your project, if you're willing to find them.)

quicksilver03 3y ago
You have just described Testcontainers [1] , and if you are a Java developer you may want to look into Testcontainers for Java [2]. For example, in one of my projects I use TestNG to instantiate a MariaDB container, run the Flyway migrations on it and then populating the tables with the test data: mAPIDBContainer = new MariaDBContainer<>(DockerImageName.parse(MARIADB_CONTAINER_TAG)) .withDatabaseName("apidb") .withPassword("password") .withNetwork(containerNetwork) .withNetworkAliases("apidb") .withExposedPorts(3306); mAPIDBContainer.start(); Flyway flyway = Flyway.configure() .dataSource(mAPIDBContainer.getJdbcUrl(), mAPIDBContainer.getUsername(), mAPIDBContainer.getPassword()) .encoding("UTF-8") .locations("classpath:apidb/migrations") .load(); flyway.migrate(); ScriptUtils.runInitScript(new JdbcDatabaseDelegate(mAPIDBContainer, ""), "sql/apidb/apidb-test-data.sql"); [1] https://testcontainers.com/ https://testcontainers.com/ [2] https://java.testcontainers.org/ https://java.testcontainers.org/
sasmithjr 3y ago
> But nothing is stopping you from running Postgres in a Docker container, and wrap it in a unit test with the technology of your choice I agree. I'm in .NET land, I use EFCore as my ORM, and I use EFCore's migration features. My ORM models are in a separate project (same git repo) from both the web server project and test project, and any hand written SQL gets added to the migration scripts that EFCore generates. I spin up a docker container for postgres, my test code clears any existing DB, creates a DB in the container, and then runs the EFCore migrations on the DB. I have simple tests that make sure my CTEs are working correctly and that things like expected unique indices are also setup. This works both locally and in Github Actions. I just wouldn't call any of this a "unit" test. I put all this squarely in my integration test suite. I figure if IO is happening as a consequential part of the test (i.e. not setup/teardown), it's an integration test. I wonder how much that distinction is tripping people up? A lot of people think of unit tests as small, independent, and quick, so by only thinking about unit testing, they automatically rule out tests that have app code call out to a DB. Based on the sibling comment, I'm going to have to take a look at Testcontainers. I'm not sure how much it'll simplify at this point, but who knows!

rjbwork 3y ago

In the MS ecosystem they have the SSDT data tools and unit test projects. It uses the designer functionality of VS to allow you to write your arrange, act, and teardown in pure SQL, and gives some basic condition builders for result sets tor your assert, with an escape hatch if that's insufficient.