LINQ from .NET was pretty close to this. You could write a variant of SQL directly inline with your C#. I think technically there was also an ORM layer (Entity Framework) but for the most part it was just a query builder.

LINQ is still very much alive and well. I think you're thinking of LINQ to SQL which was an ORM in its own right.

Then along came entity framework which also supports LINQ querying syntax (or you can use expressions).

Their is actually a programing language that has this feature. It's called ABAP[1] and it has build in SQL Support with OpenSQL[2]. OpenSQL is just a SQL dialect which is translated to the equivalent SQL needed for the DB specific underlying DB. Sadly ABAP is a proprietary programming language only available on the SAP Netweaver stack.

[1] https://en.wikipedia.org/wiki/ABAP

[2] https://help.sap.com/viewer/fe24b0146c551014891ad42d6b2789e5...

Thanks, I never heard of this. That looks like a weird mix between COBOL and SQL?!

SQLC could be a good fit. It generates type-safe Go code from SQL.

https://github.com/kyleconroy/sqlc

Wow, that's interesting. Thanks for sharing, I will look into it!

This is why good ORM like SQLAlchemy actually are split in 2 parts: the declarative layer, and the core layer.

The declarative layer gives you the handy ORM syntax for simple operations.

The core layer is lower level, and allows to composes all possible SQL operations you can dream, but from the comfort of your programming language, including typing, completion, and so on.

Does something like that exist for Go ? I don't know the golang ecosystem very well.

My dream is to write actual SQL in the middle of my code, as a valid syntax and first-class construct. As I said, that's not really a realistic goal.

Do you have some examples of SQLAlchemy core layer you're talking about, just to have an idea of what you have in mind?

I couldn't find exactly an only Core example that you are looking for in the examples folder [0] so here is a more complicated tutorial query [1] and here's how I use Core in my project [2] (mostly short select statements so not really a comprehensive demonstration of SQLAlchemy Core's capabilities).

[0] https://github.com/sqlalchemy/sqlalchemy/tree/master/example...

[1] https://docs.sqlalchemy.org/en/14/core/tutorial.html#common-...

[2] https://github.com/atlasacademy/fgo-game-data-api/tree/maste...

Thank you :)

> My dream is to write actual SQL in the middle of my code, as a valid syntax and first-class construct. As I said, that's not really a realistic goal.

Sure, it is. See how regexes in some languages are first class datatypes, so you can have a regex literal in your code (with backreferences too, I believe).

There is no good reason that SQL statements can't also be a datatype, with SQL literals in the code like the way regexes do. I'm imagining something like this:

   sql_t stmt = /SELECT #1, #2 FROM #3, #4 WHERE #3.#1 = #4.#2/;
   sql_res_t res_cursor = sql_exec (stmt, t1_col1, t2_col2, T1, T2);

The stmt above is not a string, hence is part of the AST and can be checked at compile time. The execution might be a problem (returned columns have to be checked too).

Ur/Web has something like this (example: http://www.impredicative.com/ur/demo/sql.ur.html).