Ask HN: How do you test SQL?
I've been looking for resources for our data team to apply best practices for testing SQL pipelines (we use DBT) but have not found anything. How do you test SQL pipelines? What patterns, tools and best practices would you recommend? Any good reference material you know of?
321 comments
[ 3.1 ms ] story [ 232 ms ] threadFor my own use-cases, I usually test this at the application level and not the DB level. This is admittedly not unit-testing my SQL (or stored procs or triggers) but integration-testing it.
However I have had great luck with using an ORM to load up the database and data, and then having a unit test that calls the function which does raw SQL in the middle. And now the raw database tests are integrated with the unit tests for the rest of the environment in a way that keeps them synchronized with the application code that also interacts with the same database.
And, of course, the limits of ORMs do not change the fact that they have use cases.
…not to say they that EF Core doesn’t have flaws (it does, and they’re legion) but the ORMs of today are nothing like the ORMs of the 1990s… or even like 2010’s NHibernate.
…and it works - but now you have possibly thousands of classes/structs that are all-so-similar but also subtly different - namely disjoint members (so they can’t exist in an inheritance hierarchy, e.g. NewUser won’t have a UserId value, result-types would be immutable, unless they need to be mutable, etc…). It’s all such a huge pain. In a C# project of mine that does something like this, it means that every business-entity typically has at least ~5 actual class/struct/interface types associated with it: e.g. NewUser, IReadOnlyUser, IWritableUserValues, struct UserKey, MutableUser, UpdateUserValues, etc.
…surely there’s a better way?
This is easier if you have the same input every time the tests run, like a frozen database image, because then you can basically have snapshot tests.
You can hook up dbt tests to your CI and Git(hub|lab) for data PRs.
Depending on your needs, you can also look into data observability tools such as Datafold (paid) or re_data (free)
OP (or others) - If you've used dbt tests, I'm curious where it fell short? Tt doesn't cover everything, but it's pretty good in my experience.
A ref() concept like dbt's is sufficient. When testing, have ref output a different (test-x) name for all your references.
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...
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.
Guide is here: https://supabase.com/docs/guides/database/testing
I ought to produce unit tests that prove that tuples from each join operation produces the correct dataset. I've only ever tested with 3 join operations in one query.
For a user perspective, I guess you could write some tooling that loads example data into a database and does an incremental join with each part of the join statement added.
Most of the times there is a layer around your sql (a repository, a bash script or whatever) that you can use for integration testing.
We've written our own tool to compare different data sources against each other. This allows, for example, to test for invariants (or expected variations) between and after a transformation.
The tool is open source: https://github.com/QuantCo/datajudge
We've also written a blog post trying to illustrate a use case: https://tech.quantco.com/2022/06/20/datajudge.html
More trivial example:
Took about a week of duplicating happy path tests, investigating gaps, and switching inputs/assertion types to get everything passing, but less than a week later we had our first verifiable test failure.
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 would miss.
Edit: also, it’s great for test/dev environments. You don’t have to worry about losing important data or filling new environments with data. Just start with the full test data and you’re good to go. It’s got stuff for all the corner cases already. Something got screwed up? Blow it away and reload, it’s not precious.
at my company they just told us to stop reporting edge cases. much easier, much cheaper.
Most places aren't like that, at least the ones I've seen. :)
our website crashes when the user types a double space. their solution is to have us tell users to stop typing in double spaces. since it only happens a few times a month its not considered a high priority. (details have been edited to protect the innocent)
https://www.testcontainers.org/
> Testcontainers for .NET is a library to support tests with throwaway instances of Docker containers for all compatible .NET Standard versions. The library is built on top of the .NET Docker remote API and provides a lightweight implementation to support your test environment in all circumstances.
Edit: Ok, example helps. https://dotnet.testcontainers.org/examples/aspnet/
I can declare docker infrastructure from my code, right?
Or along similar lines you could divide it such that developers can test things locally on their machines with SQLite, but once it gets pushed into CI (and passes code review etc.) it's tested against the heavy db.
I meant in situations like parent comment where you're using an ORM such as hibernate that supports multiple databases, you can test as much of the non-DB specific stuff with SQLite in-memory and then do a separate batch of tests with DB specific behaviour.
Snowflake, on the other hand, is just special.
Of course most people who have complex queries are probably not using sqlite and so may not care about testing the database.
That is by installed instances. Whether that translates to the amount of developers is not so clear.
SET SESSION synchronous_commit TO OFF;
(Update: I just looked in our test code, you can also replace the CREATE TABLE commands with "CREATE UNLOGGED TABLE" to disable write-ahead logging.)
There are possibly other tricks?
I slightly disagree with the tech debt comment, though. If you get a huge speed up, it may be worth paying for the occasional bug, depending on the circumstances. Or you could do both, and only run the test on Postgres occasionally.
jokes aside, redshift is based on pg^1, you can try an older version to get some semblance of it running locally.
1. https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-an...
I have seen teams give each developer a personal schema on a dev cluster, to ensure their Redshift SQL actually works. The downside is that now your tests are non-local, so it's a real tradeoff. In CI you probably connect to a real test cluster.
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 implementation includes calls to an external database, then one would have to use a mock for the database, in order for the test to be deemed a true unit test [1]. If the developer is writing an integration test for the same HTTP handler, then the database would have to be a real database implementation [2].
On other hand, if the developer were testing SQL queries for syntactical or logical correctness against a database, these tests would want to use a real database implementation. Note that though the test uses a real database, it is still a true unit test [3]. Additionally, note that using a mocked database here would not serve the purpose of the tests, which is to catch syntactical and logical errors against a database engine. This can, of course, only be achieved by using a real database—or, if you insisted on using a "mock", then, by implementing an entire SQL engine, with the exact same quirks as the real database's engine, inside the mock!
On the original question:
> Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?
I guess the answer would be: It would depend on the objectives of and types of tests. Do both of them, because some tests, such as unit tests on the HTTP handler, would use use mocks, while other tests, such as the SQL query correctness tests, would use the real database.
[1] A true unit test is one that has no external interactions, neither directly nor transitively, besides interactions with the system under test (SUT). The SUT here is the HTTP handler.
[2] An integration test should include external system interactions, if any. That's what integration means.
[3] The SUT is each SQL query and the its interaction with the database. There are no interactions in the test with systems outside the SUT, so it is still a true unit test.
https://github.com/oguimbal/pg-mem
¹: https://sqitch.org/
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.
The really great thing about this approach (IMHO), is that you can validate certain constraint violations.
For example, exclusion constraints are great for modelling certain use cases where overlapping ranges should be avoided. In our (go) code, the test cases can use the sqlstate code, or the constraint name to figure out if we hit the error we expect to hit.
This approach is pretty much as fast as our unit tests (your mileage may vary), but it prevents way more bugs from being merged into our codebase.
Finish time of last test: 2023-02-01 08:26:13.861 UTC
82 tests, or 0.856 seconds per test (sequentially).
In parallel, we take 6.941 seconds for 82 tests, or 0.085 seconds per test.
Our whole test suite (integration + unit tests) takes ~80 seconds to run for ~800 integration tests (each with their own DB) and 300 unit tests. And that's on my dev laptop (T14s, cpu: i7-1185G7) without much optimization (mainly fsync = off in postgresql.conf).
In fact, I just ran a quick test, and just putting the DB on a tmpfs cuts that time to ~40 seconds.
So overall 0.1 to 0.05 second per test on average, same ballpark as parent (and it's kind of an over estimation actually since we have a dozen or so of slow tests taking 5 to 10 seconds).
Note that the tests are run in parallel however.
Our overall strategy is to create a master "test" DB with a test dataset, and for each test, copy this master DB to a test specific DB (CREATE DATABASE <testdb> TEMPLATE <master>) so that tests can run in parallel without interfering with each other and without the significant overhead of a "from scratch" DB initialization.
For schema migrations, we build the "branch/PR" version and the "main" version, then we check that 'init DB with "main" + migration with the "branch/PR" version' results in the same schema as 'init the DB directly with the "branch/PR" version' using apgdiff.
This strategy could probably be extended to migrating from every older version by building each tag, but we don't have that need.
We could also probably improve checks on the data itself however as for now, we only check the schemas.
Few things to note:
* it's still possible to run the tests outside of docker and use a local DB instead with some light setup (it's faster than running everything in docker when developing)
* docker argument --tmpfs <dir/of/db> is quite good, assuming you have enough ram for your dataset
* few configuration tweaks on the DB, like max connection might be necessary.
Overall, we are quite happy with this setup as it permits to implement end to end integration tests quite easily without spending too much time mocking dependencies.
As a general pattern, I find instantiating dependencies internal to your service (like a DB or Queue) to be the way to go, with mocking only for external dependencies (like external APIs) or exceptionally to reach a specific code branch (specially error handling sections).
Since OP mentioned DBT (kind of weird, hopefully, it's at least DBT2, since DBT is very old), they mean to test the productivity of the system rather than correctness of some queries (typical tests that deal with workloads similar to DBT2 are, eg. pgbench). Running pgbench over a database setup in a container will tell you nothing / might just confuse you, if you don't understand the difference between how database is meant to be setup and what happens in container.
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 been recommended elsewhere)
* edited formatting
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<T> (there's the interface I suppose) and outputs a filtered IQueryable<T>. The unit test (see my next response) provides a test collection and expects a certain result.
> and integration tests
No, unit tests
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 deployed. Particularly if you're using an ORM, multiple enumerations over database queries, strange ORM choices in joins/exists/whatever can absolutely murder performance.
I would still recommend having integration and/or performance testing downstream of these types of unit tests though I agree they are useful.
Those sorts of things are programmed in EF by LINQ queries. All I'm saying is, test the LINQ queries the same way you would test non-EF LINQ queries. LINQ doesn't require a database.
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.
As the meme say: App worked before. App work afterwards. Can’t explain that.
[1] https://dotnet.testcontainers.org/
[2] https://fluentmigrator.github.io/
Quick web search confirms suspicions, it is not easy
https://www.metalevel.at/prolog/testing
https://en.wikipedia.org/wiki/SQL
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 conditions.
Analogue: cooking recipe. Languages: C, Pascal, Basic, etc (they can be structured or object oriented, still imperative)
Functional language (not mathematical definition, forget stateless, non-mutable etc for a moment): It is an approach that solves the problem of cardinality of the imperative approach. There is nothing which is "one", only "one of them". It's a little bit confusing how i try to define this, but this is the most important aspect of this. We only filter and transform elements of a set, and even if this is nothing (for not mathematical aspect), but syntax sugar, it gives a programmer a safe way to do things, without caring about the details of enumerating things or null checks, etc. Or another angle: functional programming eliminates loops, and put the sequence in the data, and keep conditions. It is really handy to be honest. Purely functional languages are mostly experiments, because making something functional is kinda self-motivated, but other languages will pick up more functional elements in time, which is great.
Analogue: assembly line in a factory. Thing are coming, they are changed (like car body painted) or removed (like quality control). Languages: LINQ, SQL, F#
Declarative language: your approach here is instead of describing how to do something, you only describe what you want as a result. CSS is a perfect example, however it is picking up non-declarative elements nowadays, original it was only capable to describe how a font a paragraph look like. HTML is also declarative. Instead of actually drawing a rectangle, you just say, this width, height on this position, etc.
Analogue: you order a coffee in coffee shop. You don't care any of the details, just the parameters of the coffee you want to drink. Languages: HTML, CSS
Of course there are blurred lines like you can say all high level language are declarative, because you never go down to the hardware level to manually do everything, but I think what is important the approach, how you start to solve the problem, and if we see this from this point, SQL cannot be a declarative language. It was designed to be as close to English as possible to make non-IT people do programming, but it successfully failed it's purpose, because it is a pretty good language for programmers, but it was never designed to be declarative.
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.
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
SQL fits this perfectly. For example JOINs don't describe actions, they describe relationships. Have you ever looked at what the query planners come up with to satisfy those relationships?
SQL does not fit my definition, because you reach your goals through multiple transformation and filtering, and this is how you reach your goal. you define the way, the process, not the end result. under there are some comments where i speak about this.
SQL is almost the _textbook definition_ of a declarative language.
It does not define the way. It defines the end result.
> This is the "texbook definition"
The textbook definition of gravity is F=Gx((M1xM2)/r2)
According to Newton's textbook. Ask today's physicists about this.
SQL describes WHAT data you want returned. A functional language describes HOW you retrieve that data.
SQL is a declarative DSL for set theory and data transformation. Also precisely why both OO and FP languages fail so miserably with database mapping libraries; the so-called impedance mismatch.
Declares this set joined with that set in this way, and then grab a subset where a condition is true, grouping them according to certain criteria, and putting them in order. All this despite partially being in memory, partly stored on disk, and indexing/performance being orthogonal to the syntax of the query language.
Show me a functional language for some data on disk, some in memory, with varying levels of indexing, and where accessing the information doesn't have any impact on the transformation API.
That textbook example was just about how our understanding improves over time, and how a textbook, or all of them is not absolute truth, and debatable, which is what I am doing exactly.
>SQL describes WHAT data you want returned. A functional language describes HOW you retrieve that data.
You need to phrase more precisely here, HOW and WHAT could be interpreted by your own way.
>SQL is a declarative DSL for set theory and data transformation.
Yes, exactly. Now think about lamdba expression added to a generic imperative languages. Isn't that doing the same thing? A set, transform and filter elements of the set like how you do it in SQL with record, but in this case with objects of classes? Are lamdba expressions functional or declarative? Is LINQ functional or declarative? If you say SQL is declarative, than those should be too, or am I wrong? Or LINQ is declarative, but lambda expressions are functional?
>Also precisely why both OO and FP languages fail
I disagree. OOP failed because most programmers did not understand it. I would argue if they are failed, but now I just go with your premise, and assume they are failing. I see the real reason behind this is multifaceted, and mostly coming from the stupidity of design patterns, software architects making too much money in big companies, so they have to deliver something, so creating overconvoluted abstractions, think about the java enterprise hell. Not understanding why restricting yourself as a programmer actually benefits you in the long term, and helps other people understand your sh*t, because it forces you to be more organized. But instead of this, we have classes with a million interface slapped on it. Just ask the average programmer what is a difference between an interface and an abstract class (or have they used it ever), maybe they gonna ask what is an abstract class.
Functional languages are not failing. Pure ones created for certain purposes, like experiment (proof of concept), scientific use, etc and their ideas make their way into all the popular imperative languages, like javascript or c#, where are really useful tools while not caring about the pristine philosophy behind them, just a really useful tool.
>database mapping libraries
I also would not bury them. I exactly know what are you speaking about when you mention "impedance mismatch". I actually wrote my own mapper, i dont dare showing the code to anyone, it's just a mess, and the performance is horrible, but instead "mapping out the database and showing them as object" I built it for forcing the OOP ideas into the database level, so you can have e.g. inheritance. And not how MS solved this in entity framework like by basically denormalizing the structure and smahing together the data, no, my code exactly knows which tables to go to put together one object, which can have multiple level of inheritance. I also introduced the OOP ideas of aggregates and composites, which is exactly my attempt to solve the impedance mismatch problem you mentioned before. Correct me if we don't speak about the same thing.
You speak about technical details of SQL server internal processes, I approach this question strictly from theoretical standpoint, analyzing the language itself, and categorizing them, without thinking of computers. The problem solving approach is my divide, not anything about implementation. I wrote about this a lot in other comments.
On the other hand, the details of query execution are left to the planner and optimizer.
What's the case that it's functional, but not declarative?
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 the words used to describe those things.
Not sure if it’s a straw man exactly, but I feel like I’m in a straw man’s garden.
But this is not my angle, i say the borders drawn up by the way how you, as a programmer, approach solving a problem. My take is declarative when you define the *end result* you want to get. You certainly don't do this with SQL, because that case the your product (what you write in the query) would be the end result set, the data itself. Of course in case of HTML you don't literally draw in the rectangle in the code, but you describe the end result. This is the difference.
Other arguments you wrote is out of scope for me regarding this conversation.
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.
There are no bits, bytes and cpus in this conversation. This is classification and taxonomy of artificial languages created for other purpose than communication between 2 persons.
In SQL you gives instruction in a functional way of thinking. Functional programming is a restricted variation of imperative one for a purpose the same way how object oriented programming is basically structured programming with restrictions imposed on the programmer (for a purpose again, and a very good reason for that). So fundamentally SQL is imperative, but the details of that completely hidden and therefore irrelevant.
In HTML you describe the result itself. So "SQL would only be declarative if the user literally wrote the entire result set of the query" is not completely, but somewhat true. The way how you define a result can vary, but SQL definitely does not do that. But the way how i can imagine SQL as declarative, if i "programming by example" or ask chatgpt to translate my natural language and create the query. But these are forced examples, because IMO SQL is absolutely not declarative, and cant really be.
If HTML is a programming language or not, it's debatable, but i think it's enough that is a language (programming or not) which is designed to describe a flow-kind of a text layout (like a word file) in the first versions, then other visible elements, so i think it's not completely, but kinda irrelevant question from the viewpoint of paradigm.
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.
I don't really have an aim, I just made a simple statement, and after that everything is back and forth. Classification of languages has no practical implication (or not too much), what it changes is how they are taught, like "INTRO TO PROGRAMMING 101 BEGINNER CLASS FOR DUMMIES"
>I suppose to some degree, everything is debatable.
I just wanted to say that i dont think it's important to decide if HTML is an actual programming language or not, I see it as a language that defines something for a computer. We can just put all alikes into the "Markup-not-programming-language" basket, and that invalidates all of my arguments. I feel in this case there is no big difference in functional/imperative vs declarative languages assuming most of the people's different-than-mine classification, so why even bother then?
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 know it too well), but it is not a language, it's a framework -HTML is absolutely, 100% declarative, yes
when you create html, you dont care how the rectangle is drawn, you focus on defining the result itself.
if sql was declarative, than the code of the SQL above would be: GIVE ME ALL THE COST OF THE EMPLOYEES --, I DONT CARE ABOUT THE DETAILS
the details, which in this example the difference between fulltime/contract employees would be handled by the framework (SQL server) which would already has a concept of two kind of employee like in HTML can have rounded tips of rectangle or angular tips, because the framework already understand these ideas
an SQL query itself (if no structured language used) is just a big function applied on a set. It has never have a concept of the things you do, like building an accounting solution to a company. HTML has the concept of paragraphs, button, etc etc, all the things you wanna do. but with SQL you step by step define what you want by conditions and transformations.
or another way to separate them -imperative is a combination of -sequence/condition/loop
-function -no loop (cardinality is always multiple) -sequences is encoded as data -conditions <- yes it has conditions
This is exactly what SQL is. There are exceptions, but they're exceptions.
> an SQL query itself (if no structured language used) is just a big function applied on a set.
I don't think you have a correct model of SQL. SQL is a language that expresses definitions, not functions. It is absolutely declarative.
Some extensions of SQL can provide functional capabilities, yes, but they are exceptional.
no, i meant the query is literally this: GIVE ME ALL THE COST OF THE EMPLOYEES
or if we want to formalize more: ALL COST OF ALL EMPLOYEES
the point here that in a declarative mindset EMPLOYEE is a word that SQL server understands, like HTML understand paragraph <p> or table with rows and cells <table><tr><td>
>I don't think you have a correct model of SQL. SQL is a language that expresses definitions, not functions. It is absolutely declarative.
that just not true. without defining the how through transformations and conditions, the only thing you get out of the sql server is a table how it is stored. until you reach a certain complexity you are true, it can be seen as declarative, but my point here, is the approach of problem solving is the separation here. you dont even have to speak about computers:
cooking recipe VS assembly lines in factories VS a robot with ai, which can do whatever is programmed to, but in certain cases work out the details by itself when not given specifically (not the best example, but started to get exhausted)
Also how would you say that by nature HTML and SQL is the same?
I dont think html is anything like sql (only in the sense that all markup languages are also declarative) but a comprable situation would be if you were trying to render some ascii art in html and had to use pre tags in order to tell the layout engine how to show it.
You are describe here the abstraction you create on a primitive (related to the system you design) system, and introduce ideas like "employee" "unions" etc If you do good job, when you programming you combine generic purpose systems and introducing new ideas in a way that another human can understand (words), but in certain way you "made the computer system understand" those things too. That's your job. So basically you teach SQL server that a union employee is a thing with the dbo.IsEmployeeUnion function.
In case of html you are not introducing new things. You actually can, because the browser is really flexible (like i make a non-functional quasi elements a lot, to make the html code itself more understandable), but HTML is not designed for you to introduce new elements/ideas (originally, nowadays kinda with CSS). Anyways this is how you see the difference in another way.
I try to tell all of you how the technical details, the implementation does not matter, only just the language itself, when we speak about paradigms, but it seems i'm failing.
By the way, i think training AI models does not fit to any of the paradigms we have here, it's gonna be (or it is?) a new way to solve problems, and that also has nothing to do with files, or any kind of implementation. The important is what you see on the screen, and what you do.
<script> 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
next-functional approach
return [1,2,3,...].select(x=x * 2)
-declarative approach (not possible, but for the sake of example)
EVEN NUMBERS = NUMBERS LIKE 2,4,6 AND CARRY ON <-this requires a framework, which understand every word i wrote there.
What i wanna ask you to re-explain these examples with your understanding of classification, but using the same task.
>Reality is that declarative languages would be little more than file formats without access to computation.
That what HTML is. A declarative language and a file which requires a framework.
> Your earlier example of SELECT (1+3); is something that can be done in HTML (many different ways), too.
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, <script> 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.
That's right, in practice everything is more like a mishmash of everything. Your function library could be seen as a declarative language over the top of a generic language.
I agree, and will die on this hill :-)
A "case" statement in SQL (or an "iif") is still declarative, how else would you express specific cases when necessary?
iif and case by itself is not declarative or imperative. only an entire language can be described as such
Imperative programming: really detailed cooking recipe
Functional programming: assembly line of a car factory
Declarative programming: a robot what can do what is programmed for, and sometimes can work with really unspecific instructions (like this one line in a file "Hello world" is not a valid HTML, but the browser still renders it)
"functional programming, but that doesn't change the fact that SQL is a declarative language for describing recordsets"
ok, im describing recordsets for you: this set has 4223 records. I'm thinking of something else, but i can't really describe a recordset with anything else. Describing is not what you do when you create a software. You have a problem and you literally write a solution like an author write a book. And as you said, with SQL, you use a functional approach, how you do it when you use lambda or LINQ. Therefore SQL is functional.
SQL is turing complete with the structured extensions, so in that way can be general purpose, but something is general purpose or not does not decide the paradigm. Usually languages are more restrictive moving to the declarative side of things, but that is by design. The point is that you write less text.
But the part that is declarative is sure difficult to test.
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?
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 <path> 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 can have conditions, can be combined, can be filtered, and all without a single function or loop in sight. Ergo declarative, not functional or imperative.
SQL is a DSL for set theory with some extensions tacked on. You can use functions with SQL, but its core is perfectly adequate without them.
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
Point to any programming language that is supposed to be a pure example of anything and folks will either find at least ten clear counterexamples in that language within five minutes or demonstrate how it is grossly limited to the point of uselessness for production use on a non-trivial scale.
SQL is far and away understood as a declarative language for what data you want out of a relational database. I challenge you to find any literature that does not describe it as a declarative language.
Now, can we munge definitions and pull in an odd true scotsman argument about it not being a "true declarative" language? I mean, yeah. But, this is like arguing that LISP is not a functional language by some specific modern view of that term. Certainly true, but far from useful. And almost certainly not what anyone you would talk to expects from those terms.
"That is, it is not generating anything." if you speak about code generation like code behind in VS, it has nothing do with the paradigm. If you speak about underlying technology, it does not matter. In case of declarative languages, the framework determines what the language is capable of. And that's the point of them, they are generalizing the solution for common problems. The browser do a lot of things in the background when it draws a rectangle the same way how SQL server is doing a lot things when you execute a query, but these things have nothing to do with programming paradigm, which can be understood without computers.
Imperative: cooking recipe
Functional: assembly line in a factory
Declarative: your assistant
(compare the real world examples to the things i mentioned)
> SQL is far and away understood as a declarative language for what data you want out of a relational database. I challenge you to find any literature that does not describe it as a declarative language.
Ok, and I challenge you to compare HTML and SQL. Are they the same by nature. Are the steps, the approach of problem solving is the same? Because if SQL is declarative, than it is really similar to HTML
> Now, can we munge definitions and pull in an odd true scotsman argument about it not being a "true declarative" language? I mean, yeah. But, this is like arguing that LISP is not a functional language by some specific modern view of that term. Certainly true, but far from useful. And almost certainly not what anyone you would talk to expects from those terms.
We are speaking about semantics and taxonomy here. These kind of a questions are always debatable, so i can't really say anything about this, because you are speaking of ways of discussion. What i'm saying that my taxonomy makes the most sense to me, and if someone truly can challenge it, it's gonna make my mind change, but "literature mentions it somehow else" is not an argument makes me change my mind.
And again, I get where you are wanting to go. But you are literally arguing against all literature on sql. And your definition of declarative is restricted to only markup languages, evidently. Prolog would also not be declarative, by this view. Which is akin to arguing lisp is not functional. Technically true. The least useful way of being true.
If you want to know why sql is declarative, consider, how is a join performed? Table scan, hash join, merge? Yes, there are many ways to poke at the query plan, but that is like claiming c is assembly because there are extensions.
Even for the ddl statements, how is an insert actually performed into the backing store? When you create an index, what is created? How is the data copied to it? How is the data stored at all? Btree? Something else?
But I compare SQL and HTML for you. In both cases you are creating a text/a script in order to achieve something. The way how you achieve this can be really different in HTML and SQL. You can create a chess algo in SQL, but you cannot in HTML (purely), because in case of declarative language the framework around it strongly limits what the language could do. How would you explain this, if SQL and HTML are the same by nature? Focus on the text you write, because that is your product. Where is a condition is HTML, and why there is not?
Bitmap (like all data: xml, csv, whatever) is not a language in a classical understanding, but if you wanna make it, yes, they are purely declarative.
"It is not a declaration of what you want, but is a definition of what you have" Nope, i can define this without having a computer:
imperative: cooking recipe
functional: assembly line in a factory
declarative: your assistant. or let's make SQL declarative: you ask chatgpt to write a query, and define this query in english
"declarative is restricted to only markup languages" no, the form of the language is irrelevant. CSS is not markup, but still declarative.
Elements of languages cannot be declarative or not, you can only describe the entire language as such, because we are speaking about programming paradigms.
C is assembly at the end of the day, as everything else for the computer itself, but this is irrelevant here. From purely imperative, which is basically machine code to purely declarative (maybe chatgpt?) it's a scale, you put everything somewhere. Functional programming is syntax sugar over iterative, but it's really important one, as it changes how your mind try to solve problems. So basically what I'm saying everything is an abstraction over another one, down below the machine code, but this is just how thing works, not how you use your brain to solve things. I'm saying all this, because not the technical details, the implementations behind is the point when we speak about a purely theoretical classification which programming paradigm is.
Worse than just ignoring the entire field. You are caught up in a strict taxonomy that just doesn't work. Cooking recipes are, amusingly, mixed. They are a declaration of all ingredients and supplies you will need, with an often hybrid list of instructions on how to mix them. Would I declare them as fully declarative? No, but nor would I declare that they are imperative only. That is a part.
And again, your definition of functional versus imperative versus declarative will get you in some amusing historical binds. With how strict you are trying to be, literally no programming language is declarative. Even haskel has do notation.
Please give some effort to understand why so many people are telling you you are wrong. You are not wrong, in that if you expand the concept of SQL to everything that is possible with the language today, there are concepts that are not declarative in it. You are sadly misguided in thinking that makes your point. Would be like my sneaking in the "script" tag to laugh at how HTML is not declarative. It is a markup language, not a programming one. And people have done a lot to add to it so that it can do more than it originally could. Often out of necessity to get stuff done.
But if you want: In the recipe the ingredients can be "int y;", like declaration, or something else like malloc() Stating that i will need something is not declarative programming. Declarative is defining the end result. So none of the ingredients or the declaration are declarative programming (if you want to turn recipe to declarative, you go to a restaurant), but as i told, you can't do this with elements of languages, it just does not make sense.
In practice language paradigms can overlap related to concrete products. You can use (and actually should) c# for functional programming, but that does not make the language functional, the fundamental design is imperative. In the future, everything will be declarative (chatgpt is the first glimpse), so in some way we're just wasting our time here. Also, speaking about AI, model training does not fit any of the current paradigms, it's gonna be a new one, and maybe the only one, who knows...
SQL: describing the subsets of related data you want and not caring if that data is on disk, in memory, indexed, located on another server, or calculated on the fly. The engine (wait staff and cooks) figure out the "how" and return what you've ordered/queried.
Ergo SQL is a declarative language.
Functional: being multiple line cooks, the food is transformed step by step by different cook, they do the same thing on multiple half-ready dishes.
Declarative: giving your order to the waiter and having the food returned to you fully prepared <-your definition BUT you need to have a restaurant to do this, for other cases, not necessarily (you can line cook at home if you really want...)
SQL: getting elements of the a set and by DOING different kind of transformations (line cooks) and removing some plates (a guy standing there and if he sees a plate, which is a different dish, he tossed it to the trash). The end result is the set of data you want.
Ergo SQL is a functional language.
The other tests from your link are just silly. You did not write code to terminate or to provide an answer for all inputs, you wrote code to provide the right answer.
To test some HTML for instance, think about what information you want the page to convey, load up a browser, and check that the info is displayed. Easy as that.
Definitely not an expert. But to my eyes checking for non-termination seems very close to things that happen in SQL all the time.
For example, a parent query joins two subqueries that both map data, and then maps on that data. To test, I could just test one specific scenario with specific values in all columns. That would be more of a concrete test case.
But I might want something more robust checking the outer bounds of acceptable data (like a min and a max). That seems much closer to the non-termination test.
Of course, I'm open to being corrected on this.
Example:
db = Fake().expect_query("SELECT * FROM users", result=[(1, 'Bob'), (2, 'Joe')])
Then you do:
db.query("SELECT * FROM users")
and get back the result.
In Python if you do this in a context manager, you can ensure that all expected queries actually were issued, because the Fake object can track which ones it already saw and throw an exception on exit.
The upside of this is, you don't need any database server running for your tests.
update: This pattern is usually called db-mock or something like this. There are some packages out there. I built it a few times for companies I worked for.
not a unique problem with sql, btw.
basically you cannot test queries against a big database. you just have to hope for the best.
If you use DuckDB to run the tests, you can reference those files as if they were tables (select * from 'in.parquet'), and the tests will run extremely fast
One challenge if you're using Spark is that test can be frustratingly slow to run. One possible solution (that I use myself) is to run most tests using DuckDB, and only e.g. the overall test using Spark SQL.
I've used the above strategy with PyTest, but I'm not sure conceptually it's particularly sensitive to the programming language/testrunner you use.
Also I have no idea whether this is good practice - it's just something that seemed to work well for me.
The approach with csvs can be nice because your customers can review these files for correctness (they may be the owners of the metric), without them needing to be coders. They just need to confirm in.csv should result in expected_out.csv.
If it makes it more readable you can also inline the 'in' and 'expected_out' data e.g. as a list of dicts and pass into DuckDB as a pandas dataframe
One gotya is SQL does not guarantee order so you need to somehow sort or otherwise ensure your tests are robust to this
Not much more to say, just observing, sorry if this is irrelevant commentary.
... 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 :/
The title is "Ask HN: How do you test SQL"
1. Make sure you're using a shared session between the test suite. So that spin-up only has to occur once per suite and not per test. This has the drawback of not allowing dataframe name reuse across tests, but who cares.
2. If you have any kind of magic-number N in the SparkSQL or dataframe calls (e.g coalesce(N), repartition(N)) change N to be parameterized, and set it to 1 for the test.
3. Make sure the master doesn't have any more than 'local[2]' set. Or less depending on your workstation.
I find this helps a heck of a lot with maintainability + debugging as well
I love CTEs, but the query semantics matter too.
I used this exact same method. Not only does it help me but those who come after trying to understand what's going on.
[0] https://www.postgresql.org/docs/current/queries-with.html#id...
Could you or anyone else on the post provide an example?
if you are storing fully denormalized concrete data about the value of salary/medical/retirement both pre- and post-tax that was actually paid to each pay period (because this can vary!), then you can define a view that does salary-per-employee (taxable, untaxable, etc), and then a view that rolls up employees-per-department. And you can write unit tests for all of those.
that's a super contrived example but basically once group aggregate or window functions and other complex sub-queries start coming into the picture it becomes highly desirable to write those as their own views. And you can write some simple unit tests for those views. there are tons of shitty weird sql quirks that come from nullity/etc and you can have very weird specific sum(mycol where condition) and other non-trivial sub-subquery logic, and it's simple to just write an expression that you think is true and validate that it works like you think, that all output groups (including empty/null groups etc) that you expect to be present or not present actually are/aren't, etc.
I'm not personally advocating for writing those as CTEs specifically as a design goal in preference to views, personally I'd rather write views where possible. But recursive CTEs are the canonical approach for certain kinds of queries (particularly node/tree structures) and at minimum a CTE certainly is a "less powerful context" than a generalized WHERE EXISTS (select 1 from ... WHERE myVal = outerVal) or value-select subquery. it's desirable to have that isolation from the outer SQL query cursor imo (and depending on what you're asking, it may optimize to something different in terms of plan).
Writing everything as a single query, where the sub-sub-query needs to be 100% sure not to depend on the outer-outer-cursor, is painful. What even is "DEEP_RANK()" in the context of this particular row/window? If you've got some bizarre (RANK(myId order by timestamp) or whatever, does it really work right? Etc. It's just a lot easier to conceptually write each "function" as a level with its own unit tests. Same as any other unit-testable function block, it's ideal if it's Obviously Correct and then you define compositions of Obvious Correctness with their own provable correctness.
And if it's not Obviously Correct then you need the proof even more. Encapsulate whatever dumb shit you have to do to make it run correctly and quick into a subquery and just do a "inner join where outerQuery.myId = myView.myId". Hide the badness.
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!).
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.
I've used that to do dumb shit like lever a table schema into an ORM mapping that it wasn't really designed for, that I had to maintain fallback compatibility conditions onto the tables.
views are really a db-level "interface" implementation that few people really exploit fully. Here is the definition, here is the implementation. And yes global temporary tables are such a cute cheat for unlimited-size query-specific data ;)
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.
The annoying part is that certain RDMBS engines (MySQL for instance) require you to write the INSERT keyword before any CTEs.
So, your T-SQL or PGSQL query:
`with cte1 as (...) insert into ... select ... from cte1;`
becomes:
`insert into ... with cte1 as (...) select ... from cte1;`
I know the difference is minor but when you deal with many different DB engines, it is simply annoying.
* reuse the same Spark session throughout the test suite
* Set shuffle partitions to 2 (instead of default which is 200)
* Use dependency injection to avoid disk I/O in the test suite
* Use fast DataFrame equality when possible. assertSmallDataFrameEquality is 4x faster than assertLargeDataFrameEquality. Some benchmarks here: https://github.com/MrPowers/spark-fast-tests#why-is-this-lib...
* Use column equality to test column functions. Don't compare DataFrames unless you're testing custom DataFrame transformations. See the spark-style-guide for definitions for these terms: https://github.com/MrPowers/spark-style-guide/blob/main/PYSP...
Spark is an underrated tool for testing SQL. Spark makes it really easy to abstract SQL into unit testable chunks. Configuring your tests properly takes some knowledge, but you can make the tests run relatively quickly.
You can structure your code to read from paths when run in the production environment, but inject DataFrames you build in memory for your test suite. Spark is designed to read multiple files in parallel, so it's not optimized to read a single tiny file. That's why it's best to avoid I/O in Spark test suites whenever possible.
You can achieve the same thing with "docker commit"-ing data into docker images of your dB engine of choice, and firing your queries on them, but that only really works with smaller datasets.
[1] https://devdb.cloud
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 check if the revert action works well too.
- I run `sqitch deploy` again to test if the migration works well from scratch.
- After the schema migration has been applied, I run integration tests with Typescript and a test runner, which includes a mix of application tests and database tests too.
- If everything goes well, then it runs the migration script to the staging environment, and eventually it runs on the production database after a series of other steps on the pipeline.
I test my database queries from Typescript in this way:
-in practice I'm not strict on separating the tests from the database queries and the application code, instead, I test the layers as they are being developed, starting from simple inserts on the database, where I test my application CRUD functions that is being developed, plus to the fixtures generators (the code that generate synthetic data for my tests) and the deletion and test cleanup capabilities.
-having those boilerplate code, then I start testing the complex queries, and if a query is large enough (and assuming there are no performance penalties using CTE for those cases), I write my largue queries on small chunks on a cte, like this (replace SELECT 1 by your queries):
`;Then on my tests I can quickly pick any step of the CTE to test it
and on my application, I just use the final query: The tests start with an empty database (sqitch deploy just ran on it), then each test creates its own data fixtures (this is the more time consuming part of the test process) with UUIDs as synthetic data so I don't have conflicts between each test data, which makes it possible to run the tests concurrenlty, which is important to detect bugs on the queries too. Also, I include a cleanup process after each tests so after finishing the tests the database is empty of data again.For sql queries that are critical pieces, I was be able to develop thounsands of automated tests with this approach and in addition to combinatorial approaches. In cases where a column of a view are basically a operation of states, if you write the logic in sql directly, you can test the combination of states from a spreadsheet (each colum is an state), and combining the states you can fill the expectations directly on the spreadsheet and give it to the test suites to ru...
There is another pattern too, when I implemented a RLS based multi-tenancy with RBAC support, which needed an relatively large sql codebase and needed to be battle tested because it was critical, I've splited a big part of the sql code in a lot of sql functions instead of views to test the code units or integrations (using something similar to dependency injection but for the data, to switch the tenant RBAC's contexts), because for the sql functions I can pass different Postgresql's Configuration Parameters to test different tenants for example.
Be wary of too many techniques that are supposed to be making it easier to test, but also make it hard for you to leave a query pipeline. In particular, SQL should be very easy in the "with these as our base inputs, we expect these as our base outputs." Trying to test individual parts of the queries is almost certainly doomed to massive bloat of the system and will cause grief later.