🔎 SQLi – I've released the most flexible and type-safe Query Builder for Go 🔎

# [ $davids.sh ] · message #276

🔎 SQLi – I've released the most flexible and type-safe Query Builder for Go 🔎

In every Query Builder/ORM, I always expected at least 4 key features:

Extract database constants (schema names, tables, columns, etc.)
Extract table types
Generate type-safe functions for Insert, Update, and Delete (which look identical in every project)
Generate a type-safe way to write dynamic SQL queries

I haven't seen anything on the market that solves all these issues—maybe except kysely.js, but it relies too heavily on TS string literals, making it hard to replicate in most other languages.

But I found a way to solve this and created the SQLi library, which addresses all these points (currently for Go).

Check it out, give it a try, share your feedback, and may everyone level up their skills! 🦾

#db #sql #orm #go

$ download custom_emoji: AnimatedSticker (9).tgs

@ Aleksey Zhidkov · # 1750

And what about jooq doesn't suit you (besides the stack) or have you not seen it before?
@ Aleksey Zhidkov · # 1751

Or more precisely, are there fundamental differences between SQLi and jOOQ?
@ [ $davids.sh ] · # 1752
Here are 3 points:
- I haven't heard of jooq
- It seems to be only for Java, SQLi is currently for Go, but I will expand it (just need to add templates for other languages)
- It has classic dot notation (select().from()...), I use functional notation (query(select(), from(), ...), which (1) is much closer to SQL, (2) the result of any function is an object { sql: string, args: any[] }, meaning you can write your own functions / such structures and use them internally, i.e., if you need some customization, you don't need to dig into the library and extend object methods, (3) we are not limited to a specific DB, meaning, even if there's no introspection for a particular DB, the types themselves can be written manually, and SQLi will work smoothly with any SQL syntax
@ Aleksey Zhidkov · # 1753

Okay, understood, thank you
@ [ $davids.sh ] · # 1754
As an example of customization: our custom database has a MERGE table WHERE ... COLLISION ... operator, which merges multiple records together. In SQLi, the merge and collision functions don't exist, but we can do this:
```
function merge(tableName: string) {
  return {
    sql: `MERGE ${tableName}`,
  }
}

function collision(type: "free" | "restricted") {
  return {
    sql: `COLLISION $`,
    args: [type]
  }
}
```
And use it like this:
```
query(merge(tableName), where(...), collision("free"))
```
This means you don't even need to commit anything to SQLi to add new operators.
@ Ivan Chernov · # 1755

And what's the difference from SQLc?
@ [ $davids.sh ] · # 1756

SQLc takes pure SQL statements and generates code from them.

This is great for complex static SQL (I've used it myself).

BUT if you need to build dynamic SQL (add/remove clauses depending on incoming conditions, for example, to build a complex search string from the cheapest flight search engine Aviasales), it's very difficult to do with SQLc.

You'll have to write native SQL CASE WHERE, which is extremely hard to control.

Therefore, if you have a task to write very type-safe but static SQL, it's better to use SQLc, and use SQLi in conjunction with it for dynamic queries.
@ Vassiliy ITK Kuzenkov · # 1757

And what about the type safety of such a solution?

In my opinion, it's better to write queries in SQL now, and then generate the interface from SQL files without writing queries in the code at all. This results in much better DX when the language is unable to offer anything more expressive than SQL (and that's very difficult).

Here's an example https://github.com/giacomocavalieri/squirrel/tree/main and sqlc(?) in Go
@ [ $davids.sh ] · # 1758

Oh, and SQLi also has automatic generation of all types of INSERT / UPDATE / DELETE, which you'll have to write yourself every time in SQLc.
@ Vassiliy ITK Kuzenkov · # 1759

And what's the difficulty in controlling a case where?
@ Vassiliy ITK Kuzenkov · # 1760

AI writes them now, you feed your database to the agent locally, and it'll write you a million boring SQL queries if you want.
@ [ $davids.sh ] · # 1761

Each function checks the correctness of names and argument types.

In the future, it will be possible to add checks for the compatibility of different functions, but there is no time for that now.
@ [ $davids.sh ] · # 1762

For some, writing large CASE WHERE statements might be fine, and a winning argument could be made for it. HOWEVER, CASE WHERE simply cannot handle mutually exclusive or mutually complementary arguments.

And in all my recent projects over the last 5 years, I've constantly encountered such situations and moved away from SQLc to query builders (which didn't have other SQLi features).
@ [ $davids.sh ] · # 1763

My attempts to ask neural networks to write SQL queries have only disappointed me: so far, they get wildly confused about the facts and write either non-working or non-optimal code.

As soon as we get into the territory of JSON in PG, they start hallucinating.

BUT if there's a cool, proven solution, I'd be happy to hear it.
@ Vassiliy ITK Kuzenkov · # 1764

I try to give agents as much context as possible (I need to tune the neural network's semantic graph to my topics) and access. If I want more, I use MCP, and I look for them here https://smithery.ai/

But for basic INSERT / UPDATE / DELETE, it's enough to give a good prompt and correctly write the cursor rules (specify where the migrations are located, in what style the operations should be). And then it just eats it up and that's it. With JSON in PG (if you have many such requests), you can give them a validator at the code level, show a correct example, and direct them to read the documentation 😄
@ [ $davids.sh ] · # 1765

Ooh, nice, I'll watch)