If you're into JS there was an query builder posted a few days back that was fairly closely mapped to SQL, it in turn was inspired by Linq (and EFCore behind it).

I didn't look forward to working with C# initially but Linq has been a fresh air since your statements more or less map 1:1 to SQL and probably quite overlooked because of "Microsoft"(and that Linq with old EF could have nasty surprises).

What people don't know/realize is that because Linq expressions in C# are left "uncompiled" they can be passed to the SQL layers and converted to idiomatic SQL, so you have all the typesafety of C# and regular C# code but get SQL code that is executed on the server (there is some minor impedance mismatch but it's minor enough and mainly with strings).

https://news.ycombinator.com/item?id=41455719

This is what jOOQ does. Best SQL library I've ever used. https://www.jooq.org/

We use jOOQ, and love it. But sqlc also makes nice trade offs. I see it does Kotlin generation: if this lib was around when we picked jOOQ, I's certainly had considered it.

Differences:

* jOOQ is an eDSL with an optional schema-to-classes generator

* you write jOOQ queries in Java (or Kotlin as we do)

* there's quite a bit of type-safety added when using the generator: the schema needs to be match the queries you write or you get compile errors

* jOOQ queries are built are run time adding a little overhead that sqlc does not

* writing jOOQ is very close writing SQL (a very thin abstraction), sqlc is "just SQL" it seems

Yep sqlc is more akin to Kotlin's SQLDelight https://github.com/cashapp/sqldelight

That's a really nice project indeed. We looked at it I remember, when we pciked jOOQ, but it was too new to bet the farm on back then. It progressed really nicely.

Building on top of IntelliJ's parser and 'PSI' stack as a headless engine sounds slightly mad at first but seems to provide a lot of leverage, as demonstrated by the number of dialects the team is able to support via mixins. By contrast sqlc appears to require a lot of code to support each additional dialect.

I keep hearing about how powerful pg is. It would be great to see an example application that uses all it's features.

Possibly close enough: https://github.com/omnigres/omnigres

> Unfortunately, plrust extension is responsible for many gigabytes of artifacts in the image. Typically, if you need Rust, this is not a big problem as both development machines and servers can handle this just fine.

Lmao. At least they provide a "slim" flavor without it.

Current image builds are to be phased out soon(ish). They were a way to get something into people's hands. And yes, plrust/rust are definitely space hogs :)

this is what inspired me to look more into postgres’ features - https://theartofpostgresql.com/

quite a lot of ideas what you can do with it.

It would be great to have some sort of sql string to type parsing as some sort of plugin to TypeScript.

Didn’t use myself, but AFAIK slonik library is doing what you’ve described: https://github.com/gajus/slonik

It doesn't seem to be doing what I meant. I mean typing compile time and most importantly while using the IDE. Without having to generate types manually or importing them from a generated file.

I have probably misunderstood the documentation, the built-in sql tagged template function seems to be providing static type safety using conditional types magic. But, yeah, maybe I am too optimistic about this. Well, too bad then.

You may be interested in https://docs.postgrest.org/en/v12/ an automatic way of creating a REST API from the database. It even uses the database to do authentication which is very rarely seen in production (CREATE ROLE, GRANT SELECT, etc). And you create functions in the database to customize things including implementing custom RPC methods when the standard REST is insufficient.

Many devs however would consider creating functions and stored procedures a bridge too far to cross.

I see nothing related to REST. No hypertext and hypermedia.

I totally agree and think it's because polyglot programming still kinda sucks. Either you use a universally frustrating ORM or dataframe library or you pass your db strings that even your most integrated IDEs can't really integrate into the rest of your code.

If we figured out how to do type inference and go-to-definition and all the other nice LSP stuff across language boundaries in a good way, I'd hope the things you mention would all get a lot more widely used.

You missed the ole' reliable, temp tables. I always end up using them heavily for non-trivial analytic queries since it gives you so much control over query execution.

In terms of actually using SQL more effectively, I think the ideal is just a small utility to use reflection and map a ResultSet (or equivalent) into a strongly typed object using reflection.

Even inserts can get tricky because there are so many different knobs you can tune. It's pretty rare that people just need to insert into a single table with no additional selects beforehand which means there is room to play around with preemptive locking, isolation level, etc.

Whenever I write a backend, it's a thin layer on top of the RDBMS like you said. I don't know if a lib or framework could help with this. It's more about designing the schema well, avoiding excessive tooling (ORMs, query builders, etc), not trying to abstract away the DB, and writing ample integration tests.

If you get that stuff out of the way, you can focus on the real problems like design, xact isolation, and performance, for which there's tons of conflicting advice rather than an agreed-upon approach. And then there's sharding. It's hard enough already.

Personally I haven't found the need for type safety in code, or even the code-SQL boundary. The DB tables have types, as does my OpenAPI or Protobuf or whatever API spec. That's basically everything already. If something slips past my tests, it's because the tests are bad, and stronger typing wouldn't have helped.

I have successfully used stronger typing systems (Rust in this example) to code-generate tests.

So they can be utilized to save us some work.

That's neat. It should be just as easy in theory to generate tests from the OpenAPI spec, but idk what tooling there is.

Ah, we have `openapi-generator` already (look it up) and it works well enough. I plan on starting to use OpenAPI only for types and be able to generate structs and various other types in several languages -- fingers crossed.