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

91–100 of 322 posts

Re: Ask HN: How do you test SQL?

#91

Post by slaymaker1907 »

DaiPlusPlus wrote: post #29
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.

The problem with writing raw SQL (which I do personally prefer myself, too) is now you need to generate types and/or mappings for each distinct query’s resultset schema - doing that by-hand is tedious and error-prone (or use untyped dict objects for every row, ew) - so what you really need is a project build-step that finds every query in your project and runs it against a prototype database instance in order to get…

I like the approach of just using reflection to do the mapping and throw an error when things don't quite match up like with https://stackoverflow.com/a/21956222/7608007. Combined with something like record types or Lombok, it's not that much effort to create a bean class for each result mapping you care about.

Re: Ask HN: How do you test SQL?

#92

Post by uticus »

joshschreuder wrote: post #50
uticus wrote: post #28

Earlier quoted context omitted.

> using interfaces Kinda, but personally I describe as using LINQ queries. The dbcontext just isn't hooked up. It's a method that takes in an IQueryable (there's the interface I suppose) and outputs a filtered IQueryable . The unit test (see my next response) provides a test collection and expects a certain result. > and integration tests No, unit tests

Just as a real world (somewhat) counterpoint to this - you need to be very careful about performance metrics in particular. Functionality is pretty easy to verify with LINQ / mock data sets (though it is also easy to mock things in a way that aren't representative of real data by mistake) but the performance characteristics of a real SQL engine vs. unit testing in this way can lead to some real gotchas once it's depl…

great point and agree - and, actually it argues even better against my point #1 about moving business logic out of SQL queries.

Re: Ask HN: How do you test SQL?

#93

Post by OJFord »

spamtarget wrote: post #58
luhn wrote: post #38

Earlier quoted context omitted.

> Although SQL is essentially a declarative language (4GL), it also includes procedural elements. https://en.wikipedia.org/wiki/SQL

-3 votes shows how this is a common misunderstanding. If SQL was a declarative language, there would not be expression in it, for example SELECT (1+2) , only SELECT 3 These categories based on the approach of the programming, not the underlying technology. Imperative languages (high level): a step-by-step description of a process, like giving orders to someone/something in a sequence, organized further with loops and…

> If SQL was a declarative language, there would not be expression in it, for example SELECT (1+2) , only SELECT 3

SQL is I think generally considered not entirely declarative, but that is not an example that shows that. Is there any declarative language that satisfies that, that has no addition (or operator? Or addition of constants? I'm not really clear what about it you think makes it not declarative?).

By this rule, Prolog and HCL/Terraform are not declarative either.

Re: Ask HN: How do you test SQL?

#94

Post by claytonjy »

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.

Yup, same. Last time i set this up i used Sqitch¹ for migrations, which encourages you to write tests for each migration; caught a lot of bugs early that way, all in a local-first dev environment. Worked especially well for Postgres since plpgsql makes it easy to write tests more imperatively.

¹: https://sqitch.org/

Re: Ask HN: How do you test SQL?

#95

Post by [deleted] »

preseinger wrote: post #85
spamtarget wrote: post #75

Earlier quoted context omitted.

Really? SELECT CASE WHEN employee.type = 'contract' THEN salary CASE WHEN employee.type = 'full-time' THEN salary + benefit_costs END CASE FROM employee Tell me how is this declarative? -SQL is functional -CSV is not a language, it's a data format -GraphQL, im not familiar -JSON is literally executable javascript code, arguable -React is a javascript framework, binding is a declarative nature (if it has it, i dont kn…

Does the presence of conditional expressions mean that the language which permits them is necessarily not declarative?

[deleted]

Re: Ask HN: How do you test SQL?

#96

Post by indymike »

spamtarget wrote: post #75
mLuby wrote: post #48

Earlier quoted context omitted.

You tell SQL what you want, not how to get it. That's declarative. SQL : CSV :: GraphQL : JSON :: React : HTML

Really? SELECT CASE WHEN employee.type = 'contract' THEN salary CASE WHEN employee.type = 'full-time' THEN salary + benefit_costs END CASE FROM employee Tell me how is this declarative? -SQL is functional -CSV is not a language, it's a data format -GraphQL, im not familiar -JSON is literally executable javascript code, arguable -React is a javascript framework, binding is a declarative nature (if it has it, i dont kn…

I’ve not encountered your definition of declarative before. SQL is often cited in cs texts as an example of a declarative language. That said, SQL does have a lot of imperative features, but those features are used to declare the result of the sql dml query.

Re: Ask HN: How do you test SQL?

#97

Post by jimmygrapes »

jph00 wrote: post #77
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.

CTE==Common Table Expression. It's not specific to any particular language. It's basically like a view, except you can define them the same place as you use them, and they can be recursive.

Thank you for the clarification, that actually makes sense in context!

... Although also now I'm a little. put off because you reminded me of working with DOS-based SAP interfaces and Oracle's Java-based attempts to make "interactive views" circa 2006 :/

Re: Ask HN: How do you test SQL?

#99

Post by spamtarget »

OJFord wrote: post #93
spamtarget wrote: post #58

Earlier quoted context omitted.

-3 votes shows how this is a common misunderstanding. If SQL was a declarative language, there would not be expression in it, for example SELECT (1+2) , only SELECT 3 These categories based on the approach of the programming, not the underlying technology. Imperative languages (high level): a step-by-step description of a process, like giving orders to someone/something in a sequence, organized further with loops and…

> If SQL was a declarative language, there would not be expression in it, for example SELECT (1+2) , only SELECT 3 SQL is I think generally considered not entirely declarative, but that is not an example that shows that. Is there any declarative language that satisfies that, that has no addition (or operator? Or addition of constants? I'm not really clear what about it you think makes it not declarative?). By this ru…

one way of seeing this, everything, which is high level language is declarative, but if we do this, we make this classification meaningless. it was just an example, for an operator, which is an "activity" is not part of a declarative mindset (usually). you can twist the idea of SQL to be declarative, but in this case we go back to my first sentence, and distinction will have no purpose.

i give you other angles.

imperative has structures like: sequence (by code), loops and conditions

functional

** has no loop, the loop itself the cardinality, which is always multiple

** has no sequence, it's encoded by data (edit here, actually it has)

** has (of course) condition

So, by basically we redefine the fundamental elements of the imperative model

Purely declarative languages has no sequence/loop or conditions (in programming understanding)

Answering to you, HTML (especially earlier) is pretty close to purely declarative, i does not have loops or conditions, however, this is not 100% true, but close. I don't know those languages you mentioned, so i can not have an opinion about them

Post reply on HN