3 ms·
SQL relies on RDBMS's type system, just like C++ devs rely on C++ OOP. the problem lies with lax/weak table design that allows too lax data (all string nullabl
by slt2021 2y ago
SQL relies on RDBMS's type system, just like C++ devs rely on C++ OOP.
the problem lies with lax/weak table design that allows too lax data (all string nullable fields for example), instead of strict types like int, float, varchar, foreign key constraints etc.
you dont need unit test if you table design is clean, because SQL operators are deterministic and are tested by the RDBMS vendor
just need integration test, dry run test, periodic data integrity checks at the system boundaries when data is ingested/egress
SQL represents operations on the Sets of data and their relations, so you need not unit test on a single row, but a test suite on your entire DataSet (backup copy or synthetic copy)
- earnestinger 2y ago> you dont need unit test if you table design is clean, because SQL operators are deterministic and are tested by the RDBMS vendor Imagine well structured tables in 3rd normal form. And then a view which joins 20 tables and creates a report with myriad of case/then expressions. Many things can go wrong. (Wrong join conditions, duplicate rows, omitted rows, wrong formulas) You might say that there should be no such logic in database and I understand the sentiment, but circumstances (e.g. performance) might say it’s needed.
- slt2021 2y agoPerformance of complex query is totally depends on the query planner, which depends on 1) table size, 2) data cardinality, 3) table statistics It is only testable on real production database, so you can do test of complex queries on backup of your prod to get a sense of performance. But still query can degrade unexpectedly under consistent load in prod from other queries