[HN Gopher] Catching SQL errors at build time
       ___________________________________________________________________
        
       Catching SQL errors at build time
        
       Author : houqp
       Score  : 56 points
       Date   : 2020-01-12 17:19 UTC (5 hours ago)
        
 (HTM) web link (github.com)
 (TXT) w3m dump (github.com)
        
       | bwblabs wrote:
       | Reminds me of the !sql in rust-postgres-macros:
       | 
       | https://github.com/sfackler/rust-postgres-macros#sql
       | 
       | With the difference: it uses the PostgreSQL parser, not a generic
       | SQL parser
        
         | vpzom wrote:
         | Ooh, that looks nice!
         | 
         | Though it doesn't seem particularly maintained
        
       | DJBunnies wrote:
       | Cool and all, but wouldn't one prefer db tests?
        
         | lgeorget wrote:
         | Both 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 wrote:
         | Why 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.
        
       | [deleted]
        
       | skinnyarms wrote:
       | I 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 wrote:
         | Wrong thread :-p
        
       | contradictioned wrote:
       | That reminds me on 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.
        
       | mixedCase wrote:
       | Interesting. 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 wrote:
         | Good 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....
        
       | rossmohax wrote:
       | First class support for SQL type checking and domain model
       | mapping: https://github.com/nikita-volkov/hasql-th
        
       | sverhagen wrote:
       | I 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.
        
         | cgh wrote:
         | This 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.
        
         | rgharris wrote:
         | I 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 wrote:
           | And static analysis is not limited to build time so it can
           | provide value even earlier.
        
             | pletnes wrote:
             | Or later, if your program grows from a small hack to an
             | important application.
        
         | taeric wrote:
         | With 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 wrote:
       | That'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.
        
       | Pxtl wrote:
       | On 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.
        
       ___________________________________________________________________
       (page generated 2020-01-12 23:00 UTC)