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

181–190 of 322 posts

Re: Ask HN: How do you test SQL?

#181

Post by paulmd »

RobinL wrote: post #43

Try 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…

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 thread is closed, or until the session calls commit, but the semantics will (theoretically) be optimized for one thread and rapid inserts/clears.

If you build your unit tests so that a thread will not commit until it's finished, or clear it before/after, it's pretty ideal for that. You can feed in data for that test and it won't be visible anywhere else.

Potentially if you scripted your table creates you could add an additional rule that GLOBAL TEMPORARY TABLE gets added to any other tabledefs but only for unit tests. Or just update both in parallel.

The other useful use-case I've found for this is that you can do an unlimited-size "WHERE myId IN (:1, :2 ...)" by doing a GTT expressed as "INNER JOIN myId = myGtt.myid", and this has the advantage of not thrashing the query planner for every literal sqltext resulting from different lengths of myList (this will fill up your query cache!). Since it's one literal string it always hits the same query plan cache.

I am told this has problems in oracle's OCI when using PDBs, apparently GTTs will shit up the redo-log tablespace (WAL equivalent). But so do many many things, PDBs are apparently a much different and much less capable and incredibly immature implementation that seems essentially abandoned/not a focus from what I've been told. I was very surprised having originally written that code in regular Oracle and not having had problems but PDBs just aren't the same (right down to nulls being present in indexes!).

Re: Ask HN: How do you test SQL?

#182

Post by devdada »

Creating a separate isolated environment is totally the way to go, and migrate the data from your existing database to it. This way the data turns to "dummy" data but it provides you with something to conduct testing with.

Re: Ask HN: How do you test SQL?

#184

Post by yakshaving_jgt »

spamtarget wrote: post #178
yakshaving_jgt wrote: post #169

Earlier quoted context omitted.

Your comments in this thread have made for a pretty tough read, but I think your angle is finally made clear here. Supposedly, “every high level language is declarative”. It’s an opinion I suppose, but I doubt it’s one you’re going to find much support for. What I think this feels like to most people is that things aren’t what people think they are because you’ve decided to reimagine the commonly used definitions of…

I'm sorry, i'm speaking with multiple people on multiple threads here, and don't have too much time to express myself perfectly. Every language is declarative is an interpretation that can make sense in certain angle, like when you use some imperative language, you create mini libraries for yourself, and that's already steps to a declarative variation, but this does not changes the fundamental design of the language.…

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 need for SQL. The result set of a query is called "data". It's not a declarative language.

You're free to redefine terms, but as I mentioned, you're going to have a hard time finding people to agree with you.

Re: Ask HN: How do you test SQL?

#186

Post by db48x »

jimmygrapes wrote: post #68
RobinL wrote: post #43

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

Sometimes using databases feels just like repeatedly slamming your head into your desk, so that fits.

Re: Ask HN: How do you test SQL?

#188

Post by n0n0n4t0r »

paulmd wrote: post #181
RobinL wrote: post #43

Try 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…

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.

Re: Ask HN: How do you test SQL?

#189

Post by WuxiFingerHold »

davps wrote: post #46

This approach didn't use an ORM and run the tests concurrently against the same database. I follow those steps on my pipeline: Every time I commit changes the CI/CD pipeline follow those steps, on this order: - I use sqitch for the database migration (my DB is postgresql). - Run the migration script `sqitch deploy`. It runs only the items that hasn't been migrated yet. - Run the `revert all` feature of sqitch to chec…

This is a beautiful and powerful approach (and great explanation), IMO. Personally, I'd keep all CTEs (your steps) as units and combine them separately. But you've commented on that in your other comment.

Re: Ask HN: How do you test SQL?

#190

Post by nikita »

Disclosure. I'm CEO of Neon (neon.tech).

One of the premises that we have is the ability to instantly create a test environment by creating a branch. I'd love to hear what you think about it.

Post reply on HN