#The type system is quite good for a 1.0

1 messages · Page 1 of 1 (latest)

errant saffron
#

I decided a fun little project would be a SQL generator. I found Gleam's type system to be so good.

pub fn main() {
  let users = Field("users", _)
  let companies = Field("companies", _)

  let query =
    Query("users", [
      Select([
        users("id"),
        users("name"),
        users("first_name"),
        users("last_name"),
      ]),
      Where(users("id"), Eq, Value("3")),
      Join(users("company_id"), companies("id")),
      Offset(0),
      Limit(20),
    ])

  io.println(to_sql(query))
}

SELECT users.id, users.name, users.first_name, users.last_name FROM users
WHERE users.id == 3
JOIN users.company_id ON companies.id
WHERE users.company_id = companies.id
OFFSET 0
LIMIT 20

You really hit a homerun on the type system. It's simple but flexible. Love this!

sand ether
#

Ah thank you so much!

#

Very kind

#

btw @idle sphinx, an SQL library here! I know you're very interested in this area

idle sphinx
#

yeah, cool 🙂
I like how the users and companies function works for setting the table 🙂

#

are you using prepared statements @errant saffron ?

errant saffron
#

not atm, just starting to write up tests for different query builder parts.

idle sphinx
#

mine doesn't look that good but I am more concerned about the internal types and bulding atm:

  let cats_sub_query =
    query.from_part_from_table(table_name: "cats")
    |> query.select_query_new_from()

  let dogs_sub_query =
    query.from_part_from_table(table_name: "dogs")
    |> query.select_query_new_from()

  let where =
    query.OrWhere([
      query.WhereEqual(
        query.WhereColumn("cats.age"),
        query.WhereParam(param.IntParam(10)),
      ),
      query.WhereEqual(
        query.WhereColumn("cats.name"),
        query.WhereParam(param.StringParam("5")),
      ),
      query.WhereIn(query.WhereColumn("cats.age"), [
        // query.WhereParam(param.NullParam), // this is bullshit anyway
        query.WhereParam(param.IntParam(1)),
        query.WhereParam(param.IntParam(2)),
      ]),
    ])

  let select_query =
    cats_sub_query
    |> query.query_select_wrap()
    |> query.from_part_from_sub_query(alias: "cats")
    |> query.select_query_new_from()
    |> query.select_query_select([
      query.select_part_from("cats.name"),
      query.select_part_from("cats.age"),
      query.select_part_from("owners.name AS owner_name"),
    ])
    |> query.select_query_set_where(where)
    |> query.select_query_order_asc("cats.name")
    |> query.select_query_order_replace(by: "cats.age", direction: query.Asc)
    |> query.select_query_set_limit(1)
    |> query.select_query_set_limit_and_offset(1, 0)
    |> query.select_query_set_join([
      query.InnerJoin(
        with: query.JoinTable("owners"),
        alias: "owners",
        on: query.WhereEqual(
          query.WhereColumn("owners.id"),
          query.WhereColumn("cats.owner_id"),
        ),
      ),
      query.CrossJoin(
        with: query.JoinSubQuery(query.query_select_wrap(dogs_sub_query)),
        alias: "dogs",
      ),
    ])
    |> query.query_select_wrap
    |> iox.dbg
# 
Select(SelectQuery(
  FromSubQuery(
    Select(SelectQuery(
      FromTable("cats"),
      [],
      [],
      NoWherePart,
      NoLimitOffset,
      [],
      NoEpilogPart,
    )),
    "cats",
  ),
  [
    SelectString("cats.name"),
    SelectString("cats.age"),
    SelectString("owners.name AS owner_name"),
  ],
  [
    InnerJoin(
      JoinTable("owners"),
      "owners",
      WhereEqual(
        WhereColumn("owners.id"),
        WhereColumn("cats.owner_id"),
      ),
    ),
    CrossJoin(
      JoinSubQuery(Select(SelectQuery(
        FromTable("dogs"),
        [],
        [],
        NoWherePart,
        NoLimitOffset,
        [],
        NoEpilogPart,
      ))),
      "dogs",
    ),
  ],
  OrWhere([
    WhereEqual(SQLite:
      WhereColumn("cats.age"),
      WhereParam(IntParam(10)),
    ),
    WhereEqual(
      WhereColumn("cats.name"),
      WhereParam(StringParam("5")),
    ),
    WhereIn(
      WhereColumn("cats.age"),
      [
        WhereParam(IntParam(1)),
        WhereParam(IntParam(2)),
      ],
    ),
  ]),
  LimitOffset(1, 0),
  [OrderByColumnPart("cats.age", Asc)],
  NoEpilogPart,
))
#("db_params", [10, "5", 1, 2])

SELECT cats.name, cats.age, owners.name AS owner_name FROM (SELECT * FROM cats) AS cats INNER JOIN owners AS owners ON owners.id = cats.owner_id CROSS JOIN (SELECT * FROM dogs) AS dogs WHERE (cats.age = ?1 OR cats.name = ?2 OR cats.age IN (?3, ?4)) ORDER BY cats.age ASC NULLS LAST LIMIT 1 OFFSET 0"

Ok([#("Biffy", 10, "bob")])
errant saffron
#

Here's what I got so far

let users = Field("users", _)
let companies = Field("companies", _)
let users_query =
  Query(Table("users"), [
    SelectAll,
    Where(users("is_actve"), Eq, SQLBool(True)),
  ])

let query =
  Query(SubQuery(users_query), [
    Select([
      users("id"),
      users("name"),
      users("first_name"),
      users("last_name"),
    ]),
    Join(Left, users("company_id"), companies("id")),
    OrWhere([
      Where(users("role"), Eq, Text("manager")),
      Where(users("role"), Eq, Text("admin")),
    ]),
    Where(users("first_name"), Eq, Text("Gordon")),
    OrderBy(users("first_name"), Asc),
    Offset(0),
    Limit(20),
  ]

Outputs to:

SELECT users.id, users.name, users.first_name, users.last_name FROM (SELECT * FROM users
WHERE users.is_actve = true)
LEFT JOIN users ON companies.id = users.company_id
WHERE (users.role = 'manager' OR users.role = 'admin')
WHERE users.first_name = 'Gordon'
ORDER BY users.first_name ASC
OFFSET 0
LIMIT 20

I'll work on it some more tonight, going to take a break.

idle sphinx
#

keep us in the loop 🙂

#

at the moment I am struggling how to type where functions (non aggregating functions)

errant saffron
#

The only part of the type system, I huff at is Where(users("is_actve"), Eq, SQLBool(True)) I wish I could just say Where(users("is_actve"), Eq, True)

idle sphinx
#

could be a function that takes n arguments, each being a literal or a parameter (prepared statement parameter)

errant saffron
#

Yeah

idle sphinx
#

so one can write maybe |> where.column(Users, "is_active", Eq, True) which internally just maps to Where(users("is_actve"), Eq, SQLBool(True))in your case

errant saffron
#

I see, you're going for more of a builder pattern

idle sphinx
#

imho gleam leaves no other option due to the lack of optional args

#

also opaque types have a lot of benefits - a lot more control imho

errant saffron
#

idk anything about opaque types

idle sphinx
#

the constructors are hidden from the outside and you need helper functions to invoke them

errant saffron
#

Thanks 🙂

idle sphinx
#

Where I struggle is say I want to support CONCAT() and it as an arity of n, so how would I make sure the user supplies a string that has 5 placeholders and the list has 5 items without using results, so at compile time?

#

I can build in concat2,3,4,5,6

#

I could iterate through the string check for placeholders, pop args from the list and if none left crash or use the last one

#

I could add a validation step for queries that returns Result at one time only that is optional to run before executing it

#

maybe I should create a function builder that returns Ok/Result and the user must explicitly assert that it is okay (it counts the args against the placeholders), and maybe that function builder can ask postgres if the function exists in the first place?

errant saffron
#

I'd keep it simple and just do a list of strings

#

Concat(List(String)) Concat([users("first_name"), Text(" "), users("last_name"])

idle sphinx
#

Yeah but then I have to know the function already, and there are a lot of functions in different sql adapters

# 
SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='pg_catalog'
ORDER BY routines.routine_name, parameters.ordinal_position;

or

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) as result
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'pg_catalog'
ORDER BY result ASC

unless I am mistaken above 3000-5500 on postgres built-in

errant saffron
#

If might be worth doing what Elixir's Ecto does and if you don't have support for that function or SQL, give them a say to provide raw SQL, idk

#

fragment("expr")

idle sphinx
#

yeah and all I would want is match the amount of args in the function call string to those given

#

but yes fragments are good

#

I ll probably fill the args with NULL if the list is too short - and maybe allow a validation step for the queries at runtime.
that is for arbitrary functions that still to prepared statements.

idle sphinx
#

@errant saffron I think I will just allow fragments at the low level query builder API and then add some ready made sql functions via gleam functions on top like LOWER/UPPER etc.

pub type Fragment {
  FragmentLiteral(fragment: String)
  FragmentPrepared(fragment: String, param: Param)
}

pub type WhereValue {
  WhereColumn(column: String)
  WhereParam(param: Param)
  WhereFragments(fragment: Fragment, more_fragments: List(Fragment))
}
#

this way I can keep track of prepared statements and if someone forgets a placeholder in FragmentPrepared I can add one at the tail of the string so the prepared statement count always matches.

#

I will make them opaque later on

#

I have a similar problem in terms of counting with scalar queries (sub queries that are not the value side) let's see