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

211–220 of 322 posts

Re: Ask HN: How do you test SQL?

#211

Post by yakshaving_jgt »

spamtarget wrote: post #203
yakshaving_jgt wrote: post #184

Earlier quoted context omitted.

That's quite the slippery slope though. You don't literally draw a rectangle with HTML, but you also don't tell the database's query planner how to do its job. You're also not comparing things on equal terms. HTML isn't any kind of programming or scripting language. If I understand you correctly, SQL would only be declarative if the user literally wrote the entire result set of the query. This would then obviate the…

Technical and implementation details again. When we speak about programming paradigm, we speak about the language alone, not any software system, this is a theoretical discussion. It does not matter how it is executed, what matter is how you express yourself in that language to achieve your goals. There are no bits, bytes and cpus in this conversation. This is classification and taxonomy of artificial languages creat…

> this is a theoretical discussion

With all due respect, I don’t understand what your aim is with this discussion, and at this point I think I’m more confused than when we started. What is your aim here?

> If HTML is a programming language or not, it's debatable

I suppose to some degree, everything is debatable. But this point isn’t somewhere where we’re going to find common ground. Another commenter said it well — with HTML, you get what you write. It’s not a programming language.

In any case, I appreciate you taking the time to expand on your reasoning. I’m just still struggling to make heads or tails of it.

Re: Ask HN: How do you test SQL?

#212

Post by MrBuddyCasino »

n0n0n4t0r wrote: post #188
paulmd wrote: post #181

Earlier quoted context omitted.

If your database supports it, unit tests are an absolutely ideal use-case for temporary tables or global temporary tables. A global temporary table can be defined with the same schema as the correct table and will "exist" for the purpose of view/CTE definitions, but any data inserted into the table will only ever be visible from that specific thread context. The rules depend but basically either it exists until the t…

Off topic incoming (sorry ) I used this trick (join temporaryFoo instead of where foo in ...) in production fifteen years ago, using MySQL. The gain was really astonishing. Several instructions can be optimized using joins on specialty craft tables (I know of LIMIT for instance). This is one of the worst drawbacks of orm everywhere: nobody even seems to think about those optimisations anymore.

That is why ORMs have fallzout of fashion.

Re: Ask HN: How do you test SQL?

#214

Post by zx14 »

nextlevelwizard wrote: post #208

Test the queries your application is making. I wouldn't put much effort into this. You have to trust that other people test their stuff anyway so why make a difference with a database? I'd much rather test that your backups work. And that can be done by dumping the database (possibly verifying the content of that dump), taking a backup, restoring the backup to a fresh container, then comparing dump of that freshly re…

He is the one trying to test his stuff though, hence the question.

Re: Ask HN: How do you test SQL?

#215

Post by dagss »

Probably not that relevant for a data team, but this is what we do as a backend team:

We use Microsoft SQL's docker image and spin it up in the background on our laptop/CI server so port 1433 has a database.

Then we have our homegrown migration file runner that will compute a hash of the migrations, make a database template_a5757f7e, and run the hundreds of migrations on it, whenever we add a new SQL migration (todo: make one template build on the previous).

Then we use the BACKUP command to dump the db to disk (within the docker image)

Finally, each test function is able to make a new database and restore that backup from file in less than a second. Populate with some relevant test data, run code, inspect results, drop database.

So our test suite uses hundreds of fresh databases and it still runs in a reasonable time.

(And..our test suite is written in Go, with a lot of embedded SQL strings, even if a lot of our business logic is in SQL)

Re: Ask HN: How do you test SQL?

#216

Post by pharmakom »

Testing databases with Docker, dummy data, etc. can be very slow so it’s a big win to use Bazel as the test executor. This enables caching between runs and between machines. Saves us about 20 mins of CI time every build.

Re: Ask HN: How do you test SQL?

#217

Post by nesarkvechnep »

purerandomness wrote: post #8

There's pgTAP for Postgres [1], the same approach probably is valid for other databases. Here's [2] a slide deck by David Wheeler giving an introduction into how it works. [1] https://pgtap.org/ [2] https://www.slideshare.net/justatheory/unit-test-your-databa...

Unfortunately, I’m not surprised people test queries in their applications’ unit tests. What they’re actually testing is the ORM/query builder. Instead, with pgTAP, you can test specifically your queries.

Re: Ask HN: How do you test SQL?

#219

Post by KronisLV »

I think that when it comes to testing databases... most people just don't.

Look at this JetBrains survey: https://www.jetbrains.com/lp/devecosystem-2021/databases/

Around half of the people never debug stored procedures. Three quarters of people don't have tests in their databases. Only half of the people version their database scripts.

Personally: the answer is containers. Spin up a database in a container (manually or on CI server) and do whatever you need with it. Seed it with some test data, connect an app to it, check that app tests pass when writing to and reading from a live database (as opposed to mock data stores or something like H2), then discard the container.

Even if you don't have a traditional app, throwaway instances of the real type of DB that you'll be using are great, both for development and testing.

Re: Ask HN: How do you test SQL?

#220

Post by pharmakom »

uticus wrote: post #19

My favorite interview question. No, I mean when I'm being interviewed. The sheepish grins let me know I'm not alone. Best ideas IMO (no particular order): - make SQL dumber, move logic that needs testing out of SQL - use an ORM that allows composing, disconnect composition & test (ie EF for .NET groups, test the LINQ for correct filtering etc, instead of testing for expected data from a db) (I see this has already be…

Interesting. We have totally different philosophies here.

1. Moving logic out of SQL can break transactionality and hurt performance (more data leaves the DB)

2. ORMs hide the SQL from you, making all sorts of other things harder

My favoured approach is to test my application against a real local database, built from a fresh snapshot each time.

I do not change my application code to make testing easier. The application code is optimised for maintainability - so simplicity and ease of reading.

Post reply on HN