Our great sponsors
-
SqlKata Query Builder
SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
-
-
pggen
Generate type-safe Go for any Postgres query. If Postgres can run the query, pggen can generate code for it.
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
Less about the exact syntax and more about the tool, for example: https://github.com/sqlkata/querybuilder. I just chose that since it was on top of a search but the idea is the same. Your code generates raw SQL, so it's 100% interchangeable with writing SQL yourself however the builder library deals with the syntax, proper ordering, quoting, full attribute names, etc. Some such libraries even let you define your schema in code to make your SQL generation type safe.
Interesting. I've been looking at https://github.com/bokwoon95/go-structured-query but I'll have to look at reform too, now. Thanks.
If you love C++ Template magic look at https://github.com/rbock/sqlpp11
This allows "normal" C++ code, which by the compiler is converted into the query string, allowing code like
for (const auto& row : db(select(all_of(foo)).from(foo).where(foo.hasFun or foo.name == "joker")))
I believe Honey SQL might be just what you are looking for:
https://github.com/seancorfield/honeysql
I wrote such a C++ implementation during my days at Facebook around 8 years ago:
https://engineering.fb.com/2016/03/18/data-infrastructure/dr...
There is an example under "Functional programming primitives".
It was a C++ implementation that fell victim to Greenspun's 10th rule. So I wrote a specification for it, first in Clojure and then in python.
The C++ execution engine is open source:
https://github.com/facebookarchive/iterlib/
Main problems writing such code:
* The output of SQL is generally flat. GraphQL makes it nested, but doesn't support all the operators SQL does natively.
You might like the approach I took with pggen[1] which was inspired by sqlc[2]. You write a SQL query in regular SQL and the tool generates a type-safe Go querier struct with a method for each query.
The primary benefit of pggen and sqlc is that you don't need a different query model; it's just SQL and the tools automate the mapping between database rows and Go structs.
[1]: https://github.com/jschaf/pggen
[2]: https://github.com/kyleconroy/sqlc
I totally agree with the views of the author. That is basically why I started to build an ORM that does not include a query builder. Best of both worlds.
Now the design was far from easy, and keeping things reasonably simple without abstracting the SQL part was my biggest challenge. And this would not have been possible without the template-string tagging feature of modern JS, so it wouldn't be doable for many other languages.
https://github.com/Seb-C/kiss-orm
> My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done.
Some self-promotion (shameless, I know): https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2
I'm intending to rewrite it ("the first one is always to throw away" - I put too much unnecessary functionality into it and not enough RDBMS server backends) but I've used it in a few projects (use the latest branch) and am happy with it for postgres or sqlite usage.
See https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/s... for example usage, but the basic premise is:
1. Send parameterised string to DB.