151–160 of 322 posts
Earlier quoted context omitted.
Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.
You can get the 'in-memory' speed advantage by putting the datastore on a ramdisk (or even just disabling fsync, which is pretty easy to do in postgresql).
Earlier quoted context omitted.
This is what we did at my last job. You can catch DB specific issues that a false implementation wouldn’t show and make sure all your code paths work as expected. Every time new issues cropped up we would put new data in the test data designed to reproduce it. Every edge case we would run into. It provided so much confidence because it would catch and trigger so many edge cases that testing with mocks or by hand woul…
thats a huge amount of work and money. 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. :)
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.
would love to do this, but how does one spin up a redshift cluster inside of a docker container?
Other languages are too complicated. :(“
Everyone today: “tries using sql
Oh wow, the tooling is quite basic, and you can’t express complex data structures and imperative code. :(“
What did you expect?
Look, I spent 4 years in this rabbit hole, and here’s my advice:
Don’t try to put the square peg in the round hole.
You want easy to write, simple code and pipelines? Just use sql.
Have a dev environment and run everything against that to verify it.
Do not bother with unit testing your CTEs, it’s hard to do, there are no good tools to do it.
If you want Strong Engineering TM, use python and spark and all the python libraries that exist to do all that stuff.
It won’t be as quick to write, or make changes to, but it will be easier to write more verifiably robust code.
If you treat either as something it is not (eg. Writing complex data structures and frameworks in sql) you’re using the wrong tool for the outcome you’re trying to achieve.
It’ll take longer and “feel bad”, not because the tool is bad, but because you’re using it in a bad way.
Earlier quoted context omitted.
> 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. SQ…
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.
SQL is almost the _textbook definition_ of a declarative language.
It does not define the way. It defines the end result.
Earlier quoted context omitted.
One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERIALIZED` before the CTE definition to ensure that they are optimized same as a sub-SELECT would be.
This is a good point, although it’s worth noting that there are definitely cases where you want the sun table to be materialised (esp. when that table is small and referenced many times)
[0] https://www.postgresql.org/docs/current/queries-with.html#id...
Related - how is any declarative language tested? Quick web search confirms suspicions, it is not easy https://www.metalevel.at/prolog/testing
SQL is not a declarative language. It is a functional language, and structured language on the top as extensions. HTML is a declarative language.
Earlier quoted context omitted.
"don't get anything other than the HTML that you create" that's the point of declarative languages "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,…
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…
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.
My favorite interview question. No, I mean when I'm being interviewed. The sheepish grins let me know I'm not alone. 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 be…
Try 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 I find this helps a heck of a lot with maintainability + debugging as well