
Catching SQL errors at build time - houqp
https://github.com/houqp/sqlvet
======
Pxtl
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.

~~~
zamalek
We've used it on our on-premise product for the past maybe 5 years. I still
think it's the future for maintaining a SQL database, and have wanted to do
something similar for Postgres/OSS.

I'm curious what bugs you've run into, we have generally stopped being worried
about the reliability of the diff scripts (we use those, though, we don't
generate them on every deployment i.e. the .exe).

~~~
evanelias
There are a few tools providing this workflow on open source databases. I'm
the author of one,
[https://github.com/skeema/skeema](https://github.com/skeema/skeema), for
MySQL and MariaDB. Or for Postgres check out
[https://github.com/djrobstep/migra](https://github.com/djrobstep/migra).

------
sverhagen
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.

~~~
rgharris
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
And static analysis is not limited to build time so it can provide value even
earlier.

~~~
pletnes
Or later, if your program grows from a small hack to an important application.

------
rossmohax
First class support for SQL type checking and domain model mapping:
[https://github.com/nikita-volkov/hasql-th](https://github.com/nikita-
volkov/hasql-th)

------
conroy
Very 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
Check 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
We 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.

------
bwblabs
Reminds 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
Ooh, that looks nice!

Though it doesn't seem particularly maintained

------
mixedCase
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
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...](https://github.com/houqp/sqlvet/blob/master/pkg/vet/gosource.go#L248).

------
contradictioned
That 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
Not really the same but [https://www.jooq.org/](https://www.jooq.org/) is
really nice

------
npstr
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.

------
DJBunnies
Cool and all, but wouldn't one prefer db tests?

~~~
lgeorget
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.

------
skinnyarms
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
Wrong thread :-p

~~~
skinnyarms
Woah, uh...that's weird. Thanks!

