Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: How do you test SQL?

news.ycombinator.com

Post reply on HN
Print view View original

31–40 of 322 posts

Re: Ask HN: How do you test SQL?

#31

Post by DaiPlusPlus »

btilly wrote: post #27
teej wrote: post #16

Earlier quoted context omitted.

I haven't seen an ORM that handles analytical queries well. I'd rather write raw SQL than use SQLAlchemy for complex queries with multiple joins, aggregations, and window functions.

I agree about the limitations of ORMs. However I have had great luck with using an ORM to load up the database and data, and then having a unit test that calls the function which does raw SQL in the middle. And now the raw database tests are integrated with the unit tests for the rest of the environment in a way that keeps them synchronized with the application code that also interacts with the same database. And, of…

There’s ORMs and there’s ORMs - at one end you have the (reprehensible) Active Record anti-pattern, at the other end you have EF Core extended with one’s own build-time type generation - they’re both “ORMs” to everyone involved, but they’re totally incomparable.

…not to say they that EF Core doesn’t have flaws (it does, and they’re legion) but the ORMs of today are nothing like the ORMs of the 1990s… or even like 2010’s NHibernate.

Re: Ask HN: How do you test SQL?

#32

Post by csteubs »

inetknght wrote: post #25
ramenmeal wrote: post #18

We 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.

bonus points if you add test data for integration tests with sad paths too

Absolutely this. I stood up a negative test suite for continuous DB queries late in 2020 and it's caught many potential show stopper integration issues since; about 45% more YoY than pre-suite.

Took about a week of duplicating happy path tests, investigating gaps, and switching inputs/assertion types to get everything passing, but less than a week later we had our first verifiable test failure.

Re: Ask HN: How do you test SQL?

#34

Post by winrid »

I try to do some kind of compile-time query checking. I really like sqlx with Rust, and other languages have some kind of equivalent (although maybe not as nice) like JOOQ. If you can store the queries in some kind of configuration, like SQL files, then this is easy no matter the language.

Re: Ask HN: How do you test SQL?

#35

Post by MBCook »

ramenmeal wrote: post #18

We 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.

This is what we did at my last job. You can catch DB specific issues that a false implementation wouldn’t show and make sure all your code paths work as expected.

Every time new issues cropped up we would put new data in the test data designed to reproduce it. Every edge case we would run into.

It provided so much confidence because it would catch and trigger so many edge cases that testing with mocks or by hand would miss.

Edit: also, it’s great for test/dev environments. You don’t have to worry about losing important data or filling new environments with data. Just start with the full test data and you’re good to go. It’s got stuff for all the corner cases already. Something got screwed up? Blow it away and reload, it’s not precious.

Re: Ask HN: How do you test SQL?

#36

Post by allanbreyes »

ramenmeal wrote: post #18

We 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.

This is a great way to test for backwards-incompatible changes if your fleet is running canaries or instances on different versions backed by a singleton database. You apply the migrations, checkout the app from the old version, and then re-run your test suite. Any failures are a reasonably high signal that some backwards-incompatible migration was introduced.

Re: Ask HN: How do you test SQL?

#37

Post by spamtarget »

uticus wrote: post #33

Related - how is any declarative language tested? Quick web search confirms suspicions, it is not easy https://www.metalevel.at/prolog/testing

SQL is not a declarative language. It is a functional language, and structured language on the top as extensions. HTML is a declarative language.

Re: Ask HN: How do you test SQL?

#38

Post by luhn »

spamtarget wrote: post #37
uticus wrote: post #33

Related - how is any declarative language tested? Quick web search confirms suspicions, it is not easy https://www.metalevel.at/prolog/testing

SQL is not a declarative language. It is a functional language, and structured language on the top as extensions. HTML is a declarative language.

> Although SQL is essentially a declarative language (4GL), it also includes procedural elements.

https://en.wikipedia.org/wiki/SQL

Re: Ask HN: How do you test SQL?

#39

Post by luckystarr »

I use a fake object in place of a database connection which gives fake responses when the correct SQL query is sent to it.

Example:

db = Fake().expect_query("SELECT * FROM users", result=[(1, 'Bob'), (2, 'Joe')])

Then you do:

db.query("SELECT * FROM users")

and get back the result.

In Python if you do this in a context manager, you can ensure that all expected queries actually were issued, because the Fake object can track which ones it already saw and throw an exception on exit.

The upside of this is, you don't need any database server running for your tests.

update: This pattern is usually called db-mock or something like this. There are some packages out there. I built it a few times for companies I worked for.

Re: Ask HN: How do you test SQL?

#40

Post by zabzonk »

assuming you are asking about sql select statements, the problem is knowing what the correct answer is so you can test against it. for most data, you don't, and probably cannot know this.

not a unique problem with sql, btw.

Post reply on HN