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

231–240 of 322 posts

Re: Ask HN: How do you test SQL?

#231

Post by Deukhoofd »

davedx wrote: post #230

SQL being relatively pure, functional, algebraic and whatnot, doesn't require the same rigor of automated test coverage that more "systems" programming languages do. (By "systems" I include all languages that people use to integrate the various parts of a software system - i.e. regular programming languages like Java, C#, TypeScript, C++ and so on. Not just low level languages.) Stored procedures are a different beas…

> on Windows SQL Management Studio lets you set breakpoints, on Mac you're SOL

Good news! Since SSMS18 you're SOL on Windows too, as Microsoft completely removed that feature :)

Re: Ask HN: How do you test SQL?

#232

Post by dragonwriter »

sicp-enjoyer wrote: post #171
ddyevf635372 wrote: post #54

Earlier quoted context omitted.

Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.

Why should this be faster than a local postgres instance with no traffic?

Because you have neither IPC nor IO, whereas with a local postgres server instance you have both?

Re: Ask HN: How do you test SQL?

#233

Post by timrichard »

nishs wrote: post #129
MuffinFlavored wrote: post #69

Earlier quoted context omitted.

Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?

Sorry for the digression first. (If anyone has different definitions for the ideas here, I would love to learn.) I think the answers would depend on the types of tests that the term "this" encompasses. From how I understand it, calling something a unit test or an integration test depends on the context of what is is being tested. For example, if a developer is writing a unit test for a HTTP handler, and the handler i…

I was wondering the other day how to classify tests that use a test double/fake like pg-mem, which isn't returning stubbed results but isn't the Dockerized test DB either :

https://github.com/oguimbal/pg-mem

Re: Ask HN: How do you test SQL?

#234

Post by collyw »

db48x wrote: post #186
jimmygrapes wrote: post #68

Earlier quoted context omitted.

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.

I used to think that, but once you start thing about them in the right way, the relational model is pretty nice.

Re: Ask HN: How do you test SQL?

#235

Post by gpvos »

stormdennis wrote: post #226
gpvos wrote: post #199

Earlier quoted context omitted.

CTE = common table expression, i.e. a WITH clause (possibly recursive) before your SELECT (or other) statement. https://learnsql.com/blog/what-is-common-table-expression/

Yes, I didn't know what it was so I had looked it up before I saw yours. The definition I got is below: A common table expression, or CTE, is a temporary named result set created from a simple SQL statement that can be used in subsequent SELECT, DELETE, INSERT, or UPDATE statements.

I knew about WITH clauses, just didn't know the name.

Re: Ask HN: How do you test SQL?

#236

Post by db48x »

collyw wrote: post #234
db48x wrote: post #186

Earlier quoted context omitted.

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

I used to think that, but once you start thing about them in the right way, the relational model is pretty nice.

Agreed, but it doesn’t solve every problem. And then there are the purely operational issues; for example a few days ago autovacuum was never able to completely finish vacuuming one particular table, but then the problem mysteriously went away and now it’s fine. Wonderful.

Re: Ask HN: How do you test SQL?

#238

Post by Foobar8568 »

Deukhoofd wrote: post #231
davedx wrote: post #230

SQL being relatively pure, functional, algebraic and whatnot, doesn't require the same rigor of automated test coverage that more "systems" programming languages do. (By "systems" I include all languages that people use to integrate the various parts of a software system - i.e. regular programming languages like Java, C#, TypeScript, C++ and so on. Not just low level languages.) Stored procedures are a different beas…

> on Windows SQL Management Studio lets you set breakpoints, on Mac you're SOL Good news! Since SSMS18 you're SOL on Windows too, as Microsoft completely removed that feature :)

I believe you are expected to use visual studio to manage all this crap... And debugging in it was awful years ago, SSMS was slightly less worse IMHO.

Re: Ask HN: How do you test SQL?

#239

Post by acheton »

MBCook wrote: post #35
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 woul…

We did something similar, being a small company we used batch files (checked into source control) to run database migrations on the different platforms we supported. Most database platforms have command line tools, although you need to be careful as there can be subtle differences in behaviour between the command line tools & those running with a UI.

Re: Ask HN: How do you test SQL?

#240

Post by vivegi »

efxhoy wrote: post #64

I 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. Ne…

There's some great advice here for batch ETL work. The Create Table .. As Select (CTAS) pattern is your friend and a great aid in testability.
Post reply on HN