Git, merge requests, developers.
Ask HN: How do you test SQL?
161–170 of 322 posts
Re: Ask HN: How do you test SQL?
#162We 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.
would love to do this, but how does one spin up a redshift cluster inside of a docker container?
Would redshift serverless help at all? I realize that it may not have parity with the existing functionality, just a thought.
Re: Ask HN: How do you test SQL?
#163This 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…
how do you know that what your select statements return is correct? on a real database?
Yes, it always runs on a real database, without mocks, as integration tests where you can test each CTE's auxiliary statement separately, which acts as a step of our sql pipeline. So the initial data is inserted on the tables and then I can exercise the assertions on the sql queries or views against the real database. In theory it could be easy to rearrange the concatenations of the CTE strings from above so it can be tested as a unit when it's put together with the previous auxiliary step as a temporary table but I never had the need for that since the integrations are simpler and works really well for me. The essential part of my approach is to treat the sql code as concatenated pieces of strings, and call slices of that with just the right concatenation to exercise the test to that slice on the real database, which is valid since the query will always be valid.
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.
Re: Ask HN: How do you test SQL?
#164Well, 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 assert to confirm if results are to our expectation
test script were ran by the CI/CD process
Re: Ask HN: How do you test SQL?
#165Earlier quoted context omitted.
you can force SQL to fit in this, but this way everything become a declarative language. the approach of solving problems is the difference. 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.
No. SQL is almost the _textbook definition_ of a declarative language. It does not define the way. It defines the end result.
You make a statement without reasoning or examples: empty argument.
You are not arguing any more, just want to be right.
> 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.
Re: Ask HN: How do you test SQL?
#166Try 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…
> Try and write any complex SQL as a series of semantically meaningful CTEs.
Could you or anyone else on the post provide an example?
Re: Ask HN: How do you test SQL?
#167Earlier quoted context omitted.
Comparing to html is nonsensical. Period. It is not instructions on how to do anything, but markup of a document. Would be akin to asking if a bitmap is declarative. It is literally the data. It is not a declaration of what you want, but is a definition of what you have. In that vein, you would need a new taxonomy of "definitive" languages. And again, I get where you are wanting to go. But you are literally arguing a…
"Comparing to html is nonsensical" No, absoltely not. We are speaking about programming paradigms. We can speak about those without computers, we never have to speak about HTML. A food recipe is a program, what you are executing when you cook, and it is imperative. Going to the restaurant you can achieve the same (the dish) but in a declarative way. But I compare SQL and HTML for you. In both cases you are creating a…
You keep retreading the same ground. Have you actually gone out and researched why so many people are telling you that SQL is declarative? Right now, it is reading as though you are being willfully ignorant of the entire field.
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.
Re: Ask HN: How do you test SQL?
#168Earlier quoted context omitted.
You don't care about how the SQL server gets the data the same way how you don't care about what is happening in the background when you use File.ReadAllText The technology behind does not define the programming paradigm. You can speak about these paradigms without computers. Imperative programming: really detailed cooking recipe Functional programming: assembly line of a car factory Declarative programming: a robot…
I think what you're saying is accurate for describing what style of programming you might be doing, afterall we can write C++ in a declarative style. if you're coding in SQL then you're doing functional programming, but that doesn't change the fact that SQL is a declarative language for describing recordsets. you wouldn't refer to SQL as a functional programming language because it's not general purpose.
No, programming style can be "hacky", "script kiddie" or "architect kind design" or whatever. I'm speaking about "programming paradigms".
"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.
Re: Ask HN: How do you test SQL?
#169Earlier quoted context omitted.
The most important requirement for a functional language is that functions are first-class values. i.e. Not SQL. 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?
You are speak about things which called delegates, typed functions, defined by the parameters it gets, and the data it returns. Using delegates does not make a language functional. If you really want to speak about the underlying technology, than every high level language is declarative. You can draw a triangle by drawing three sides, or you can just put this in a function like drawtriangle(v1,v2,v3), and tadam, your…
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 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.
Re: Ask HN: How do you test SQL?
#170The hard part about testing SQL is decoupling from infrastructure and big data sources. We use DuckDB, and pandas dataframes mock data sources to unit test SQL. Python testing frameworks (or simple assert statements) can be used to compare inputs and outputs. When the tests pass, we can change from DuckDB to Spark. This helps decouple testing Spark pipelines from the SparkSession and infrastructure, which saves a lot…
fwiw, you can share a spark session between unit tests. Even persist a spark session throughout the day so your tests run against a hot session. Straight TDD with spark is perfectly fine if you know what you're doing. I'm not saying it's easy or there's an easy guide somewhere, but it's possible. If you're using Pyspark via the API, it's likely an incredibly important part of your process.
Fair enough, agreed.
It is tricky to “mock” as you said.
Our CICD platform and their owners get unhappy if we spawn an ad hoc spark session for testing purposes.
There is also a general expectation that unit tests are self contained and portable. So you could execute them in mac, linux, and arm ISA without much effort.
Another point was that we need to make this mocking or test setup easy because data scientist and ML Modellers are the most important persona who needs to write these tests ideally.
So mocking the data source with an abstraction layer and passing pandas dataframes, worked reasonably well for our use case.