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

311–320 of 322 posts

Re: Ask HN: How do you test SQL?

#311

Post by spamtarget »

ttfkam wrote: post #297
spamtarget wrote: post #109

Earlier quoted context omitted.

everything is partly declarative. you can draw a rectangle line by line, or you can put this code into a function or a procedure, and call it declarative, because it is something like that. i did not speak about testing, however what a declarative language can do is defined by the framework under it. so you test that I guess?

SVG: Draw rectangles and triangles. If you don't have a script tag in it, totally declarative. You don't draw line by line. You describe what you want and have the engine work it out. Just because that description can be complex such as a definition doesn't detract from this point. Kinda like SQL. Describe the set of data you want from other sets of data and have the engine work it out. It's set theory. In math, sets…

>SVG: Draw rectangles and triangles. If you don't have a script tag in it, totally declarative

I totally agree. It is declarative the same way how HTML is.

I don't see how SQL and SVG is similar from the viewpoint of approaching the problem. In SVG you concretely define what you want, and that is happening. In SQL you don't define the data you get, like ok, this cell will be 5, another will 8. I understand what you want to say, I just see those completely different ways.

>can be combined, can be filtered, and all without a single function or loop in sight

This is the key here, because this is exactly true to functional programming.

Compare SQL->LINQ->lambda expression

If they work the how you say, then

SQL - declarative

LINQ - declarative and functional the same time

Lambda exp - functional

This idea is completely nonsensical for me

Re: Ask HN: How do you test SQL?

#312

Post by quantified »

gardenhedge wrote: post #303
quantified wrote: post #139

Earlier quoted context omitted.

A real nice thing about Postgres and Mysql is that in the JVM world the H2 and HSQLDB engines have large compatibility, you can use them in-JVM for unit test speed in many cases. Doesn't help developing the SQL, does help with testing. Snowflake, on the other hand, is just special.

I assume this can't be done with Oracle DBs

I haven't explored myself, but I would bet they have good Oracle compatibility for at least the subset of semantics they support.

Re: Ask HN: How do you test SQL?

#313

Post by tianzhou »

claytonjy wrote: post #277
grilledcheez wrote: post #244

Earlier quoted context omitted.

At my job, we're breaking down a monolith into services with a hand-me-down database schema. DB changes are manual, every dev runs against a shared test DB, and everybody dreads doing schema changes. I've been looking for a way to transition into version controlled migrations and it looks like sqitch might be a solid option, as the language-specific frameworks are too opinionated. Thanks for recommending!

You're very welcome! FWIW there are other general migration frameworks worth considering; the two most popular seem to be Flyway and Liquibase. I've heard good things about both, and don't have a particularly strong defense for my sqitch preference. I like that it's simple, has great docs, and has verification as a natural step in the workflow.

We built a GUI based solution called https://github.com/bytebase/bytebase. You may also take a look.

Re: Ask HN: How do you test SQL?

#314

Post by vogtb »

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

+1 for pgtap. Surprised it's not been mentioned more in this thread.

I'm using it w/ supabase, and it works really well.

They have a small doc on it that's a better primer than the pgtap docs: https://supabase.com/docs/guides/database/extensions/pgtap>.

Pretty easy to get started, I'm doing something like this in a Makefile.

  supabase_tests := $(call rfind, supabase/tests/*_tests.sql)
  DB_URL := "postgresql://postgres:postgres@localhost:54322/postgres"
  test-supabase: $(supabase_tests)
    @echo "Testing supabase..." && \
        echo "${supabase_tests}" && \
        psql -f supabase/tests/setup.sql ${DB_URL} && \
        $(foreach t,$(supabase_tests),\
            echo "Test: $(t)..." && psql -f $(t) ${DB_URL} $(__EXEC)) && \
        psql -f supabase/tests/teardown.sql ${DB_URL}

Re: Ask HN: How do you test SQL?

#315

Post by kiwicopple »

vogtb wrote: post #314
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...

+1 for pgtap. Surprised it's not been mentioned more in this thread. I'm using it w/ supabase, and it works really well. They have a small doc on it that's a better primer than the pgtap docs: https://supabase.com/docs/guides/database/extensions/pgtap >. Pretty easy to get started, I'm doing something like this in a Makefile. supabase_tests := $(call rfind, supabase/tests/*_tests.sql) DB_URL := "postgresql://postgres…

the test runner is now built into the supabase CLI: https://supabase.com/docs/reference/cli/supabase-test

Guide is here: https://supabase.com/docs/guides/database/testing

Re: Ask HN: How do you test SQL?

#316

Post by johnthescott »

sqldb testing is historically problematic, for at least correctness AND performance.

in postgresql a cool tool for performance is"hypothetical indexing", which predicts how the optimizer will use indexes in any sql query. i could see an automated testing tool written around "hypothetical indexing".

also, i believe MSServer supports HI.

Re: Ask HN: How do you test SQL?

#317

Post by mwigdahl »

atwebb wrote: post #160
curiousllama wrote: post #110

Earlier quoted context omitted.

> Try and write any complex SQL as a series of semantically meaningful CTEs I find this helps a heck of a lot with maintainability + debugging as well

It can wind up with more of a procedural thought though than set-based. Not always, just something to pay attention to and pushing filters early / explicitly. It is asking more of the optimizer a lot of the times and that's were a bunch of the cautions come in to play.

Just tuned a query like this today. Many chained CTEs, joined together at each stage to do range checking logic within subgroups. There were so many self joins that the row estimates (and requested memory grant) were through the roof.

I love CTEs, but the query semantics matter too.

Re: Ask HN: How do you test SQL?

#318

Post by reactorpres »

systems wrote: post #164

Well, I used to work at a place where they used https://tsqlt.org/ for testing it worked great I dont know the technical detail of how to set it up, it was already setup when I worked there But basically, we wrote SQL script that included statements to 1. create the db structure, tables or views 2. insert statement to enter test data (you can insert corner cases etc..) 3. ran the function or procedure 4. ran an asser…

tSQLt is a great tool. If only it could get ported to some other dbs.

Re: Ask HN: How do you test SQL?

#319

Post by preseinger »

spamtarget wrote: post #173
preseinger wrote: post #148

Earlier quoted context omitted.

You do not understand SQL. HTML and SQL are essentially unrelated. It would be to your benefit to spend some time reading and learning on these topics.

Of course they are, however they are both languages. And you classify them into the same category, so it should have some similarities, right?

I am not classifying them into the same category. I am explicitly doing the opposite. SQL is declarative, HTML is a markup language, they are categorically different.

Re: Ask HN: How do you test SQL?

#320

Post by indymike »

spamtarget wrote: post #305
indymike wrote: post #285

Earlier quoted context omitted.

"Declarative language" does not mean "free of computation". It does mean the language generally specifies an output. HTML does have computation in the form of tags. What makes HTML and SQL declarative instead of imperative is that in both SQL and HTML the document specifies an specific output that is wanted from the browser (in HTMLs case) or database (in SQLs case). Your earlier example of SELECT (1+3); is something…

I never meant "free of computation", I actually mentioned a lot that forget computers and softwares, and focus on languages. is Javascript, not html (I mean what's inside), they just smashed together. >specifies an specific output That specific output definition is really different. Consider this: -task: give me even numbers -imperative approach: for i=1 to 100 result += i * 2 next -functional approach return [1,2,3,…

> No, not with strictly sticking to the standard and not use any trickery. HTML was not designed to do any kind of calculation. That's why javascript came later.

I thought you would go here, and I understand where you are coming from: Declarative language != file format and declarative is not a paradigm like functional, procedural, object-oriented.

Every useful declarative language allows for some level of computation. Take a template language - YAML + some template tags. Aside allowing variable substitution, every time, the language grows a way to make those substitutions more useful, so you get things like:

{{ variable_containing_a_date + interval(days=1) }}

Back to functional, oo, procedural: some declarative languages import a paradigm.

Back to the script tag:

Even though it was added after the original spec, is part of the standard for HTML and is one of the reasons it pushed many other formats to the side and the web won. I would submit that HTML+JavaScript is the platform, not just HMTL. One can write declarative code in an imperative language, and the reverse is true.

I do really appreciate you thinking on this, but I think we're best to use the existing definition and example of declarative languages, and not just banish declarative to static file formats.

Post reply on HN