5 ms·

Most SQL centric projects I've touched had a severe lack of automated tests. Not saying it can't be done but that's my experience.

simonw 4y ago
I'd love to find a SQL testing tool that has similar ergonomics to pytest. ... or for someone to build a tiny framework on top of pytest that helps write automated SQL tests.
- drewcoo 4y ago
  > SQL testing tool that has similar ergonomics to pytest What exactly does that mean? You can write SQL queries and run them through PyTest and if you do it in CI there's no repetitive human injury involved.
  - simonw 4y ago
    Sure, you can do that: but most people don't. I would be delighted to see a tool grab enough mindshare to become the safe default testing tool for SQL code, in a similar way to pytest in Python world.
- heywhatupboys 4y ago
  If you praise pytest as some unique test runner, I pressume you haven't worked with many actually good testing engines... SQL has more than enough to compete with the stdlib og Pythong for testing
  - simonw 4y ago
    If you can point me to testing frameworks that are better (as in more enjoyable to use) than pytest I would LOVE to hear about them. (pytest isn't part of the Python standard library.) Especially if you've got some suggestions for JavaScript, where I have yet to find anything that makes me enjoy test writing in the same way that pytest does.
    - heywhatupboys 4y ago
      `rspec` is very nice. There exists similar (copied) tools for JS and Python alike :)
- giraffe_lady 4y ago
  pgTAP
RobinL 4y ago
Absolutely agree. I guess this is part of the tension and tradeoffs here: lots more people understand SQL, but many of them don't come from a software engineering background. And historically it has been relatively hard to set up simple, fast running unit tests on SQL pipelines, though I think that's becoming easier nowadays.
pjmlp 4y ago
Most projects I've touched had a severe lack of automated tests. Not saying it can't be done but that's my experience. Is the actual reality, not only SQL.
- woooooo 4y ago
  Yeah but sql is even harder as it implies an embedded engine to even execute a first "hello world" test. This gets even more complicated when you start using vendor-specific extensions for string manipulation, json parsing etc in the data pipelines case. It might be literally impossible to execute your SQL outside of prod or a prod-like environment.
  - dagss 4y ago
    If you e.g. develop for Microsoft SQL, you include the docker image they provide when running your tests.
  - camgunz 4y ago
    Pretty much all languages need a runtime. It's just as easy to run SQL tests in Docker on a DB as it is to run Python tests.
CuriouslyC 4y ago
It's a cultural thing, not a problem with the architecture. DBAs, analysts and scientists don't write tests in general, so when someone with that background builds a project you'll tend to see that. On the flip side, a lot of engineers seem afraid of the database, and since it lacks visible coverage metrics, they will skip writing tests for it. It's actually pretty easy to write test SQL test scripts for stuff.
- Pxtl 4y ago
  Imho the biggest challenge is that testing prioritizes the zero-side-effects fully-isolated-to-one-class stateless unit test as king (which is good! That's the gold standard for a reason), which is the polar opposite of testing a giant ball of hyper-interconnected state that is an RDBMS. I've had good luck writing scripts where the first one sets up preconditions, then it runs a million interconnected tests with no isolation between them, and then it tears down. It means you can't run a single test in isolation and you have to be aware of each test's postconditions, but it's a good "worse is better" solution for "I need to start testing now, not after I rearchitect my whole project to be isolatable and right a crapload of scaffolding and boilerplate". But every testing framework fights against this pattern.
  - crazygringo 4y ago
    I haven't had any problem testing with RDBMS, so I'm wondering if there's something I'm not understanding. For me, a testing script will load a clean schema+procedures with no data (empty tables), and the schema never changes. Each test inserts a handful of rows as needed in relevant tables, runs the query, and then checks the query result and/or changed table state. Then DELETE all table contents, and move on to the next test. Finally delete the entire test database once you're done. If you have queries that alter tables, then each test starts with a brand-new schema and deletes the whole database once done. The only kinds of tests this doesn't scale to are performance tests, e.g. is this query performant on a table with 500 million rows? But to me that's not the domain of unit tests, but rather full-scale testing environments that periodically copy the entire prod database. So I'm curious why you consider an RDMBS to be hyper-interconnected state with interconnected tests?

simonw 4y ago

I'd love to find a SQL testing tool that has similar ergonomics to pytest. ... or for someone to build a tiny framework on top of pytest that helps write automated SQL tests.

drewcoo 4y ago
> SQL testing tool that has similar ergonomics to pytest What exactly does that mean? You can write SQL queries and run them through PyTest and if you do it in CI there's no repetitive human injury involved.
- simonw 4y ago
  Sure, you can do that: but most people don't. I would be delighted to see a tool grab enough mindshare to become the safe default testing tool for SQL code, in a similar way to pytest in Python world.
heywhatupboys 4y ago
If you praise pytest as some unique test runner, I pressume you haven't worked with many actually good testing engines... SQL has more than enough to compete with the stdlib og Pythong for testing
- simonw 4y ago
  If you can point me to testing frameworks that are better (as in more enjoyable to use) than pytest I would LOVE to hear about them. (pytest isn't part of the Python standard library.) Especially if you've got some suggestions for JavaScript, where I have yet to find anything that makes me enjoy test writing in the same way that pytest does.
  - heywhatupboys 4y ago
    `rspec` is very nice. There exists similar (copied) tools for JS and Python alike :)
giraffe_lady 4y ago
pgTAP

RobinL 4y ago

Absolutely agree. I guess this is part of the tension and tradeoffs here: lots more people understand SQL, but many of them don't come from a software engineering background. And historically it has been relatively hard to set up simple, fast running unit tests on SQL pipelines, though I think that's becoming easier nowadays.

pjmlp 4y ago

Most projects I've touched had a severe lack of automated tests. Not saying it can't be done but that's my experience. Is the actual reality, not only SQL.

woooooo 4y ago
Yeah but sql is even harder as it implies an embedded engine to even execute a first "hello world" test. This gets even more complicated when you start using vendor-specific extensions for string manipulation, json parsing etc in the data pipelines case. It might be literally impossible to execute your SQL outside of prod or a prod-like environment.
- dagss 4y ago
  If you e.g. develop for Microsoft SQL, you include the docker image they provide when running your tests.
- camgunz 4y ago
  Pretty much all languages need a runtime. It's just as easy to run SQL tests in Docker on a DB as it is to run Python tests.

CuriouslyC 4y ago

It's a cultural thing, not a problem with the architecture. DBAs, analysts and scientists don't write tests in general, so when someone with that background builds a project you'll tend to see that. On the flip side, a lot of engineers seem afraid of the database, and since it lacks visible coverage metrics, they will skip writing tests for it. It's actually pretty easy to write test SQL test scripts for stuff.

Pxtl 4y ago
Imho the biggest challenge is that testing prioritizes the zero-side-effects fully-isolated-to-one-class stateless unit test as king (which is good! That's the gold standard for a reason), which is the polar opposite of testing a giant ball of hyper-interconnected state that is an RDBMS. I've had good luck writing scripts where the first one sets up preconditions, then it runs a million interconnected tests with no isolation between them, and then it tears down. It means you can't run a single test in isolation and you have to be aware of each test's postconditions, but it's a good "worse is better" solution for "I need to start testing now, not after I rearchitect my whole project to be isolatable and right a crapload of scaffolding and boilerplate". But every testing framework fights against this pattern.
- crazygringo 4y ago
  I haven't had any problem testing with RDBMS, so I'm wondering if there's something I'm not understanding. For me, a testing script will load a clean schema+procedures with no data (empty tables), and the schema never changes. Each test inserts a handful of rows as needed in relevant tables, runs the query, and then checks the query result and/or changed table state. Then DELETE all table contents, and move on to the next test. Finally delete the entire test database once you're done. If you have queries that alter tables, then each test starts with a brand-new schema and deletes the whole database once done. The only kinds of tests this doesn't scale to are performance tests, e.g. is this query performant on a table with 500 million rows? But to me that's not the domain of unit tests, but rather full-scale testing environments that periodically copy the entire prod database. So I'm curious why you consider an RDMBS to be hyper-interconnected state with interconnected tests?