Flyway, https://flywaydb.org/
Ask HN: How do you test SQL?
61–70 of 322 posts
Re: Ask HN: How do you test SQL?
#62I really love (as with lots of things) how Ecto from the Elixir community handles this, you have an extra database and because Postgres has awesome transactions you can even run all theses tests of your whole data layer in parallel, including any SQL. Ecto is largely a domain specific language for writing modular SQL so that helps test things too.
Re: Ask HN: How do you test SQL?
#63We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
would love to do this, but how does one spin up a redshift cluster inside of a docker container?
Re: Ask HN: How do you test SQL?
#64I write mostly batch ETL stuff. All plain psql and bash. We don’t have a good testing setup to be honest. What we do use instead:
Plenty of constraints, uniques and foreign keys and not nulls. Enum types.
Visuals, dump to csv and plot some graphs. Much easier to find gaps and strange distributions visually.
Asserts in DO blocks, mostly counts being equal.
Build tables in a a _next suffix schema and swap when done.
Never mutating the source data.
Using psqls ON_ERROR_STOP setting.
Avoid all but the most trivial CTEs, preferring intermediate tables that can be inspected. Constraints and assertions on the intermediate tables.
“Wasting” machine resources and always rebuilding from scratch when feasible. CREATE TABLE foo AS SELECT is much simpler than figuring out which row to UPDATE. Also ensures reproducibility, if you’re always reproducing from scratch it’s always easy. State is hard.
Overall i’m quite happy with the workflow and very rarely do we make mistakes that unit tests would have caught. Our source data is complex and not always well understood (10+ years of changing business logic) so writing good tests would be very hard. Because we never touch the raw source data any errors we inevitably make are recoverable.
This talk by Dr Martin Loetzsch helped a lot: https://youtu.be/whwNi21jAm4
Re: Ask HN: How do you test SQL?
#65To be honest. I would love testing to work. Have set up and maintained several unit test suites in Jest. Wrote several large e2e test suites in Cypress. I don't think anyone won time from simply having a manual checklist and testing manually. Maybe me and my former teammates are doing it wrong. Talking 8+ teams, from corporate to startup. But loved de proven wrong. E2e def. catched most issues.
I'm not sure I understand your comment, you seem to say that automating testing isn't better than manual testing, yet the e2e tests caught most issues?
Re: Ask HN: How do you test SQL?
#66This linter can really enforce some best practices https://github.com/sqlfluff/sqlfluff
A list of best practices: https://docs.getdbt.com/guides/legacy/best-practices
And shameless plug but there's a chapter on modeling in my book: https://theinformedcompany.com
Re: Ask HN: How do you test SQL?
#67Earlier quoted context omitted.
SQL is not a declarative language. It is a functional language, and structured language on the top as extensions. HTML is a declarative language.
The most important requirement for a functional language is that functions are first-class values. i.e. Not SQL. On the other hand, the details of query execution are left to the planner and optimizer. What's the case that it's functional, but not declarative?
You are speak about things which called delegates, typed functions, defined by the parameters it gets, and the data it returns. Using delegates does not make a language functional.
If you really want to speak about the underlying technology, than every high level language is declarative. You can draw a triangle by drawing three sides, or you can just put this in a function like drawtriangle(v1,v2,v3), and tadam, your language is declarative. The underlying tech is not important, the approach of the developer is important.
See my longer answer above
Re: Ask HN: How do you test SQL?
#68Try and write any complex SQL as a series of semantically meaningful CTEs. Test each part of the CTE pipeline with an in.parquet and an expected_out.parquet (or in.csv and out.csv if you have simple datatypes, so it works better with git). And similarly test larger parts of the pipeline with 'in' and 'expected_out' files. If you use DuckDB to run the tests, you can reference those files as if they were tables (select…
My ignorance of the topic (and experience with a mostly unrelated one) is showing, but all I could think of when you said CTE was "chronic traumatic encephalopathy". This made a lot more sense when you generalized answering the question as if it's a given that Python is necessary (I know that's not your intent, but that's how it comes off).
Not much more to say, just observing, sorry if this is irrelevant commentary.
Re: Ask HN: How do you test SQL?
#69We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?
Re: Ask HN: How do you test SQL?
#70We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
would love to do this, but how does one spin up a redshift cluster inside of a docker container?
Well the first step is to get Amazon to part with their lucrative closed source software.