7 ms·
Catching SQL errors at build time
- deleted 7y ago[deleted]
- contradictioned 7y agoThat reminds me on sqlj https://en.m.wikipedia.org/wiki/SQLJ https://en.m.wikipedia.org/wiki/SQLJ I only heard about that in lectures but unfortunately could not really use it (sqlj was only compatible with Java pre-generics, so that was no option). Nice to see this idea revived.
- oauea 7y agoNot really the same but https://www.jooq.org/ https://www.jooq.org/ is really nice
- mixedCase 7y agoInteresting. I had been using postguard in Node.js which also statically checks queries. But I wonder, how does this linter handle conditionally concatenated SQL strings in Go?
- houqp 7y agoGood question. This is actually pretty straight forward to implement and it's on my todo list :) We just need to iterate through all phi SSA nodes recursively, see: https://github.com/houqp/sqlvet/blob/master/pkg/vet/gosource.go#L248 https://github.com/houqp/sqlvet/blob/master/pkg/vet/gosource....
- sverhagen 7y agoI can see how something like this is a nice addition to your tool set, but you really should have good automated tests at build time anyway to catch SQL errors.
- rgharris 7y agoI agree, end-to-end tests have been really valuable for catching SQL issues in my experience. > Identify unsafe queries that could potentially lead to SQL injections This feature seems like a perfect use case for static analysis and would be a great tool in addition to automated tests.
- sverhagen 7y agoAnd static analysis is not limited to build time so it can provide value even earlier.
- pletnes 7y agoOr later, if your program grows from a small hack to an important application.
- namibj 7y agoJust don't use string mangling to combine parameters and queries. The most is switch/case in a loop or something like it doing state machine execution to combine fixed strings. And that only because one does not accidentally build something general enough to allow for SQL injection with such harsh restrictions.
- cgh 7y agoThis can even be done locally if your tests start and populate an in-memory database like Derby. Before anyone says it, I’m not talking about ordinary unit tests, but they can be part of the test suite.
- taeric 7y agoWith embedded databases, I'm fine of not just checking syntax of queries, but the logic that relies on the store. Not a panacea, but very nice regardless.
- npstr 7y agoThat's why I love using sql query builders on top of code generation from the migrations. Helps with writing new, type-safe queries and if migrations break the queries, we will know at compile time, its truly amazing.
- rossmohax 7y agoFirst class support for SQL type checking and domain model mapping: https://github.com/nikita-volkov/hasql-th https://github.com/nikita-volkov/hasql-th
- Pxtl 7y agoOn Microsoft SQL server theres a tool in SQL Server Data Tools called "Database Projects". This lets you have the whole database schema in a file tree that can be compiled into a binary object that the SqlPackage.exe executable can diff or publish migrations against a running database. This provides compile-time analysis of your whole SQL schema. It's a goddamned buggy disaster and the usability is basically zero, but the concept is cool.
- atwebb 7y ago>It's a goddamned buggy disaster and the usability is basically zero, but the concept is cool. Yep, the concept is pretty nice, the implementation can be harsh.
- windowsworkstoo 7y agoAnother nice aspect of this is the tooling (Dlls) can be programmed yourself with a .NET language. Ive used the same tooling to build a pre-deploy check for any suspicious DROPs and ALTERs that force manual intervention - it gives you the ability to parse T-SQL exactly how the engine will do it.
- Pxtl 7y agoWait, so instead of using SqlPackage.exe you're using the .NET objects to do the deployment, and are auditing the changes as you deploy them? That's cool! Anything posted public on this?
- windowsworkstoo 7y agoNothing public and the assembly is basically undocumented but it's basically start with this assembly and go down the rabbit hole :) https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsql130parser.-ctor?view=sql-dacfx-140.3881.1 https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlser... Our use case is SqlPackage builds the diff SQL, then we sanity check it using our custom code built on that parse for any DROPs / ALTERs and log/notify for confirmation
- skinnyarms 7y agoI love podcasts and I'm glad for any service that helps me find useful content. That said, I'm not quite getting how I'm supposed to be using this. From a consumer viewpoint, I have a front page recommending episodes and a weekly summary. I get that, cool. Now if I want to heart episodes to tell other listeners about episodes I particularly enjoy...I go to the website and search/submit the episode...I guess? Anyway, I'm interested - I just wanted to share my first impression in case it was useful.
- cpach 7y agoWrong thread :-p
- skinnyarms 7y agoWoah, uh...that's weird. Thanks!
- DJBunnies 7y agoCool and all, but wouldn't one prefer db tests?
- lgeorget 7y agoBoth are useful I'd say. Imagine a plug-in for your IDE that performs the static analysis and catches the syntax errors and some trivial semantic errors (typos in some field's name for instance) each time you save the file. I definitely find value in such a tool.
- gwd 7y agoWhy use a typed language like Go at all then? Types and static compile-time checking catch different types of errors than tests do. I don't know about you, but I am not smart enough to write bug-free code consistently; I can use as much help as I can get. In any case, it takes quite a bit longer to run my test suite than it does to compile. Unfortunately, I tried to give it a spin, and it said: ERROR: sqlvet only supports projects using go modules for now. Ah well.
- zamalek 7y agoThat strongly depends on whether you believe that static languages are useless in the face of unit tests, or whether you believe that static languages are improved by unit tests. The dynamic language camp would have no use for a tool like this.
- bwblabs 7y agoReminds me of the !sql in rust-postgres-macros: https://github.com/sfackler/rust-postgres-macros#sql https://github.com/sfackler/rust-postgres-macros#sql With the difference: it uses the PostgreSQL parser, not a generic SQL parser
- vpzom 7y agoOoh, that looks nice! Though it doesn't seem particularly maintained
- conroy 7y agoVery cool! I maintain a similar tool: sqlc https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc In addition to catching SQL errors, it generates type-safe Go wrapping code. Right now it supports PostgreSQL and MySQL. I’m (obviously) a huge fan of the SQL first approach and hope more people adopt it for their own projects.
- cube2222 7y agoCheck out OctoSQL if you like a SQL first approach: https://github.com/cube2222/octosql https://github.com/cube2222/octosql Disclosure: I'm one of the authors.
- AlexITC 7y agoWe have had compile-time checks on SQL in Scala for years, I believe slick 3 (https://scala-slick.org/ https://scala-slick.org/) was the pioneer in the language. One detail that I really liked from Slick is that the compilation fails if your queries can't be performed against your database schema (think about missing columns, or wrong names or types). While compile-time checks can be very useful, in my experience, there is nothing like having small integration tests that run against a real database which can easily be mounted by docker.