Type safe SQL query library

[chouzar]

Hi! @lpil I really like the work you did with the yesql library; how bad of an idea is that a query builder library be SQL itself? For example on elixir I would love to have something like:

defmodule Repo do
  def teams_where(params = %{}) do 
    ~Q"""
      select
        id, name,
      from
        teams
      where 
        name = @name
    """
  end
end

# And then use it in yesql fashion like:
Repo.teams_where(%{name: "Nike"}) |> Repo.execute_query()

I have not really delved into Gleam to know how to build something similar but if anything the function could have a type definition like:

QueryParams -> Query

The trick here would be how to create a Query type that goes beyond a simple String, and maybe actually have some mechanism to define a valid Query type (parsing?), specially with so many SQL idioms (the database adapter should define this?).

Query composability could be as simple as composing functions:

Repo.teams() |> Repo.where(%{name: "Nike"}) |> Repo.execute_query()

Complex query optimizations can be solved in SQL itself :thinking: WITH clauses have been enough for me on PostgreSQL and MicrosoftSQL.

[lpil]

Hi! Great points, thank you!

I personally prefer the approach that Yesql takes with SQL strings, however with Gleam I think the best SQL library would be one that allows the SQL queries to be type checked with the other Gleam code so can verify that it is valid, and so that we can statically assert the right type values are returned.

Gleam doesn't currently have a metaprogramming system that would allow us to parse and type check the SQL at compile time, so perhaps a DSL for building queries would be a more effective approach here. I've not personally done any design work or research here, so I don't have any specific ideas. If I were using SQL today in Gleam I would use SQL strings :)

I believe that F# has type checking of SQL strings using something called a type provider, perhaps this is an area we need to look into

[chouzar]

Gleam doesn't currently have a metaprogramming system that would allow us to parse and type check the SQL at compile time, so perhaps a DSL for building queries would be a more effective approach here.

By DSL here you mean "functions + types" or a special idiom?

I believe that F# has type checking of SQL strings using something called a type provider, perhaps this is an area we need to look into

Right! Type providers are one of the main reasons I would like to delve more into F#, this would be a great time to research more into it.

[lpil]

By DSL here you mean "functions + types" or a special idiom?

Sorry I'm not being clear, I mean a set of functions designed for building SQL queries in declarative and hopefully type safe fashion.

[chouzar]

Sorry I'm not being clear, I mean a set of functions designed for building SQL queries in declarative and hopefully type safe fashion.

No worries! I just wondered if there was any way of doing it with no macros (type providers sound like an excellent option).

I'm all in for a type safe SQL, but I have this irrational aversion of learning a new way of writing querys. It would be amazing if this set of functions could be integrated into a current idiom of the standard library like C# Linq (Gleam users would have to learn the "Enumerable" idiom/functions anyhow).

[lpil]

At least for the first iteration we won't have any built in language support, I'm looking to be quite conservative with what goes in there for now :)

[chouzar]

I'm not familiar at all with Rust's ecosystem but the SQLx library just appeared at my github feed (starred from a fellow elixir programmer) 😄. Just leaving the reference here for posterity.

Seems to be using the Rust macro system to type check "stringified" SQL querys.

[lpil]

That looks very nice, thank you. That design would be ideal for Gleam!