I'm a big fan of sqlc (which this is essentially a port of), so this is great to see.
I think the sqlc approach is ultimately the right one for integrating SQL into code because it allows queries to be expressed using the full syntax of native SQL, and only needs to handle the input/output glue. So you get the best of both worlds: Pure SQL unencumbered by non-SQL code, and type safety. ORMs will always struggle to catch up with SQL's richness and expressiveness.
I've not used Gleam for anything yet, but this makes me more excited to try it out.
The problem with sqlc is dynamic queries (mostly dynamic filters and ordering, which don't really affect type safety of the parameter or result-set mappings).
Even after years, the solutions are unsatisfactory from a database query planner perspective.
A minority of my sqlc queries use CASE expressions, but I've never had an issue with them. As far as I can see, Postgres is good at optimizing them away. Is that not generally the case?
I wonder if a sufficiently smart sqlc could do the optimization itself. Basically have an SQL parser that is able to identify dynamic parts statically and encoding an efficient representation that allows constant folding at runtime.
Another weak point in sqlc is the absence of any composability, so every query has to be self-contained. But views can help you with that.
Anyway, at minimum you have prepared statements where a given plan may be great for one execution and terrible for the next. Maybe the database re-prepares for you, and then it's the same as a dynamic query.
Postgres is smarter about that, too! It evaluates whether the prepared statement is sensitive to parameter changes, and will either use a "generic" plan that's shared across all invocations, or a custom one that is hardwired to the values. There's a setting that controls the behaviour.
These sqlc-style libraries are a great solution to the problem of “make running a query as easy as calling a function”, but I’ve always thought SQL’s lack of composability is a more interesting problem that I haven’t seen addressed (the problems with views are well documented).
There's two different approaches to solving sql composability issues:
1. Compiles-to-SQL domain specific languages. This category spans from ORM DSLs embedded in another programming language, like Ruby/Rail's ActiveRecord/AREL or Django's ORM; to stand-alone text-based languages like PRQL Pipelined Relational Query Language" (https://prql-lang.org) that a compiler program converts to SQL text or SQL files. The downside to the DSL option is that it requires practitioners be fluent in both the SQL query they want, and in the DLS language - to know how to obtain the SQL query in the DSL.
2. Query fragment literals in the caller programming language, like sql`name = ${name}` in TypeScript (eg https://github.com/gajus/slonik). These are usually thin abstraction over concatenating a `{ queryText: string[], queryArgs: T[] }` structure. The author only needs to be fluent in SQL, and in the caller language, but do less to save you from the expressive limitations of SQL itself.
I've found query fragment composition to be the sweet spot. Easy SQL queries remain trivial to express and understand, since it's Just SQL:
sql`SELECT * FROM block WHERE id = ${args.id}`
But you can DRY up repetition in the codebase through regular function calls. Abbreviated example from Notion's client code:
function selectOfflinePageMetadata(args: { userId: string }) {
return sql`
SELECT
offline_page.id,
offline_page.space_id,
offline_page.download_status,
offline_page.last_downloaded_at,
offline_page.last_downloaded_version,
offline_page.last_downloaded_sync_cursor,
offline_page.target_sync_cursor,
CASE
WHEN EXISTS (
SELECT 1 FROM offline_action
WHERE offline_action.impacted_page_id = offline_page.id
AND offline_action.origin_page_id = offline_page.id
AND offline_action.autosync_type = 'not_autosynced'
)
THEN 1
ELSE 0
END AS is_explicitly_offlined_origin,
CASE
WHEN EXISTS (
SELECT 1 FROM offline_action
WHERE offline_action.impacted_page_id = offline_page.id
AND offline_action.origin_page_id = offline_page.id
AND offline_action.autosync_type = 'created_offline'
)
THEN 1
ELSE 0
END AS is_offline_created_origin,
CASE
WHEN EXISTS (
SELECT 1 FROM offline_action
WHERE offline_action.impacted_page_id = offline_page.id
AND offline_action.origin_page_id = offline_page.id
AND offline_action.autosync_type = 'frecent'
)
THEN 1
ELSE 0
END AS is_autosynced_origin
FROM offline_page
WHERE offline_page.meta_user_id = ${args.userId}
`
}
function selectOfflinePageById(args: {
userId: string
pageId: string
}) {
const { userId, pageId, } = args
return sql`
SELECT * FROM (${selectOfflinePageMetadata({ userId })}) WHERE id = ${pageId}
`
}
function selectOfflineAutosyncedOrigins(args: {
userId: string
spaceId: string
}) {
const { userId, spaceId, } = args
return sql`
WITH offline_page_metadata AS (
${selectOfflinePageMetadata({ userId })}
)
SELECT offline_page_metadata.* FROM offline_page_metadata
WHERE space_id = ${spaceId}
AND is_autosynced_origin = 1
ORDER BY last_downloaded_at ASC
`
}
I'm not sure if it solves your "view problem", but it does a pretty good job for _my_ view problem.
sqlc always seemed like a really neat concept to me. I love the idea of being able to write my queries as raw SQL in separate files, would make everything so much more interoperable with other tools. Having your queries defined as strings inside some other programming language always struck me as not so awesome.
I'd love to see something like this for Typescript and Effect's SQL integration with schemas.
Static type checking and Gleam can compile to JavaScript. Gleam and Elixir can be mixed in the same project too, so it’s easy to start adding Gleam to an elixir code base or use Elixir libraries in Gleam.
Could you elaborate on what you mean by "not as integrated as in Gleam"?
Are you referring to Hologram being a framework/library rather than a language-level feature? Or is it more about the developer experience - like tooling, compilation workflow, or how seamlessly it fits into the Elixir ecosystem compared to Gleam's native JS transpilation?
I'd love to understand your perspective better so we can potentially improve that integration feel!
This looks really cool, but heads up, there were no up front descriptions or links I could follow to find out what Gleam is (a programming language that runs on the Erlang VM).
To me, Gleam feels like if Elm’s type system met Rust’s syntax. I really like it. But I also really liked Elixir too, I just can’t live without Gleam’s type system anymore.
It reminds me of Jooq in Java-land. Does Parrot also try to fill-in the gaps in support between databases so that the same query works on all databases?
hi, author here!
it does not no :) it takes the json description of the sql queries generated by sqlc and converts them to gleam code. but if sqlc supported that feature, parrot could also take advantage of it.
The best query builder I’ve seen is Kysely and it works because of anonymous sum types. So far no other library in any other language has come remotely close. You’d think Rust would have that level of type safety, but alas no.
I'm a big fan of sqlc (which this is essentially a port of), so this is great to see.
I think the sqlc approach is ultimately the right one for integrating SQL into code because it allows queries to be expressed using the full syntax of native SQL, and only needs to handle the input/output glue. So you get the best of both worlds: Pure SQL unencumbered by non-SQL code, and type safety. ORMs will always struggle to catch up with SQL's richness and expressiveness.
I've not used Gleam for anything yet, but this makes me more excited to try it out.
The problem with sqlc is dynamic queries (mostly dynamic filters and ordering, which don't really affect type safety of the parameter or result-set mappings).
Even after years, the solutions are unsatisfactory from a database query planner perspective.
A minority of my sqlc queries use CASE expressions, but I've never had an issue with them. As far as I can see, Postgres is good at optimizing them away. Is that not generally the case?
I wonder if a sufficiently smart sqlc could do the optimization itself. Basically have an SQL parser that is able to identify dynamic parts statically and encoding an efficient representation that allows constant folding at runtime.
Another weak point in sqlc is the absence of any composability, so every query has to be self-contained. But views can help you with that.
PostgreSQL is probably smarter than SQLite. :)
Anyway, at minimum you have prepared statements where a given plan may be great for one execution and terrible for the next. Maybe the database re-prepares for you, and then it's the same as a dynamic query.
Postgres is smarter about that, too! It evaluates whether the prepared statement is sensitive to parameter changes, and will either use a "generic" plan that's shared across all invocations, or a custom one that is hardwired to the values. There's a setting that controls the behaviour.
https://www.postgresql.org/docs/current/sql-prepare.html
I've not compared the actual behaviour in the codebases I work on, however.
These sqlc-style libraries are a great solution to the problem of “make running a query as easy as calling a function”, but I’ve always thought SQL’s lack of composability is a more interesting problem that I haven’t seen addressed (the problems with views are well documented).
There's two different approaches to solving sql composability issues:
1. Compiles-to-SQL domain specific languages. This category spans from ORM DSLs embedded in another programming language, like Ruby/Rail's ActiveRecord/AREL or Django's ORM; to stand-alone text-based languages like PRQL Pipelined Relational Query Language" (https://prql-lang.org) that a compiler program converts to SQL text or SQL files. The downside to the DSL option is that it requires practitioners be fluent in both the SQL query they want, and in the DLS language - to know how to obtain the SQL query in the DSL.
2. Query fragment literals in the caller programming language, like sql`name = ${name}` in TypeScript (eg https://github.com/gajus/slonik). These are usually thin abstraction over concatenating a `{ queryText: string[], queryArgs: T[] }` structure. The author only needs to be fluent in SQL, and in the caller language, but do less to save you from the expressive limitations of SQL itself.
I've found query fragment composition to be the sweet spot. Easy SQL queries remain trivial to express and understand, since it's Just SQL:
But you can DRY up repetition in the codebase through regular function calls. Abbreviated example from Notion's client code: I'm not sure if it solves your "view problem", but it does a pretty good job for _my_ view problem.I haven't worked with many ORMs, but Django's is imo very good in tackling the lack of composability in SQL.
sqlc always seemed like a really neat concept to me. I love the idea of being able to write my queries as raw SQL in separate files, would make everything so much more interoperable with other tools. Having your queries defined as strings inside some other programming language always struck me as not so awesome.
I'd love to see something like this for Typescript and Effect's SQL integration with schemas.
SQL is barely parametrizable, so, in my opinion, the DX is much worse than using a query builder and not much better than simply using strings.
You just have to duplicate things a bit more, but with the help of LLMs building the queries is very simple and extending them even easier
https://github.com/adelsz/pgtyped
What is Gleam? No obvious hits when googling.
https://gleam.run/ - Gleam is a friendly language for building type-safe systems that scale!
What is the benefit over Elixir?
Static type checking and Gleam can compile to JavaScript. Gleam and Elixir can be mixed in the same project too, so it’s easy to start adding Gleam to an elixir code base or use Elixir libraries in Gleam.
As an elixir dev I'm jealous that gleam can transpile to JS as a target. Must make so interesting things possible.
For elixir, check out https://hologram.page/
Yea, I have seen that and its an interesting library but doesnt feel as integrated as in Gleam
Could you elaborate on what you mean by "not as integrated as in Gleam"? Are you referring to Hologram being a framework/library rather than a language-level feature? Or is it more about the developer experience - like tooling, compilation workflow, or how seamlessly it fits into the Elixir ecosystem compared to Gleam's native JS transpilation? I'd love to understand your perspective better so we can potentially improve that integration feel!
Indeed! Please check out this project I made to basically make the server an extension of the front end by having it reply to client side ui messages:
https://github.com/weedonandscott/omnimessage
Strong typing built in from the start. More approachable syntax (unless you are used to Ruby).
Static typing. Elixir already has strong typing (no implicit conversions).
This is like clorinde https://github.com/halcyonnouveau/clorinde but for Gleam rather than Rust
This looks really cool, but heads up, there were no up front descriptions or links I could follow to find out what Gleam is (a programming language that runs on the Erlang VM).
So, basically sqlc for Gleam? Sounds nice.
I wonder how Gleam compares to the type checking support being added to Elixir?
The two feel very different. Elixir is introducing gradual set-theoretic typing (https://hexdocs.pm/elixir/1.18.4/gradual-set-theoretic-types...) while Gleam has a static Hindley-Milner style type system.
To me, Gleam feels like if Elm’s type system met Rust’s syntax. I really like it. But I also really liked Elixir too, I just can’t live without Gleam’s type system anymore.
What are the options for GenServer, GenStage, Tasks, Supervisors in gleam?
provided by gleam_otp https://hexdocs.pm/gleam_otp/
Nice!
It reminds me of Jooq in Java-land. Does Parrot also try to fill-in the gaps in support between databases so that the same query works on all databases?
hi, author here! it does not no :) it takes the json description of the sql queries generated by sqlc and converts them to gleam code. but if sqlc supported that feature, parrot could also take advantage of it.
The best query builder I’ve seen is Kysely and it works because of anonymous sum types. So far no other library in any other language has come remotely close. You’d think Rust would have that level of type safety, but alas no.
Typescript is magic: https://kysely.dev/docs/examples/select/aliases