Add documentation and examples

[lpil]

Hello!

I'm interested in this library but I can't tell what the scope is or how to use it. Could you add some documentation and examples? For example, filling in the README and adding doc comments for the main functions.

Thank you, Louis

[inoas]

@lpil I am working on it, it somewhat improved now (on github)

• I have yet to do interface modules for update and delete (select, unions, insert is more or less done)
• maybe transactions before 1.0
• and then it is mostly docs work and using it myself to have feedback and polish/change the library via practical application

[inoas]

Here is 0.2.0 https://hexdocs.pm/cake

[inoas]

Here is 0.4.0 https://hexdocs.pm/cake

I have added a lot of doc blocks and more tests I will focus on tests + snapshots and then add some getting started guide in the readme

[jly36963]

After looking through the docs and tests, most things were very intuitive and easy to pick up. However, I don't see a way to get the sql/params String/List(a) from a Select or Query type, without using internal functions (eg: cake/internal/query, cake/internal/prepared_statement, cake/test/test_helper, cake/test/help_support).

Given the following, how do I get the sql/params out? (I couldn't find any non-internal functions to do so.)

import cake/internal/param
import cake/internal/prepared_statement
import cake/internal/query
import cake/select as sql_select
import cake/where as sql_where
import gleam/io
import gleam/list
import gleam/pgo
import gleam/result
import snag
import snag_utils.{snag_try}
import types.{type Ninja, Ninja, get_ninja_sql_decoder, ninja_from_sql_tuple}

pub fn query_to_sql(query: query.Query) {
  io.debug(query)
  // TODO: How do I get sql/params from query?
  // I'm okay with params being `List(param.Params)` instead.
  let sql = ""
  let params: List(pgo.Value) = []
  #(sql, params)
}

pub fn ninja_get(db: pgo.Connection, id: String) -> snag.Result(Ninja) {
  // Build query, get sql/params
  let #(sql, params) =
    sql_select.new()
    |> sql_select.from_table("ninjas")
    |> sql_select.select(sql_select.col("*"))
    |> sql_select.where(
      sql_where.col("id") |> sql_where.eq(sql_where.string(id)),
    )
    |> sql_select.to_query
    |> query_to_sql

  // Get decoder that pgo expects
  // Like: `fn(Dynamic) -> Result(a, List(DecodeError))`
  let decoder = get_ninja_sql_decoder()

  // Execute query
  // NOTE: `snag_try` returns `snag.Result(a)` instead of `result.Result(a, List(DecodeError))`
  use res <- snag_try(
    pgo.execute(sql, db, params, decoder),
    "Failed to query db for ninja",
  )
  // Convert list of pgo response tuples to list of record types
  use ninjas <- result.try(
    res.rows |> list.map(ninja_from_sql_tuple) |> result.all,
  )
  // Get first
  use ninja <- snag_try(list.first(ninjas), "No ninja found")
  Ok(ninja)
}

[inoas]

First of all, thank you @jly36963 for trying cake and giving feedback <3

  // TODO: How do I get sql/params from query?

Did I get you: You want to get the perpared statement params from the prepared statement? You would want a public interface to this? https://github.com/inoas/gleam-cake/blob/main/src/cake/internal/prepared_statement.gleam#L68

If yes: Maybe it valuable moving the whole module into the public namespace? But maybe it is also better to just create a public proxy module that maps a few of the prepared_statement module functions?

[ihgrant]

I can't speak for @jly36963 but I believe I have the same question - namely, in my case, when I want to run a query using sqlight, its query function expects a String for the query, but I don't see a public method to turn a cake Query into a String. Similarly, it looks like pgo expects a #(String, List(something)) tuple as input, rather than Query.

[jly36963]

Yeah, I'm looking for a way to convert Query or Select types into #(String, List(a)), so that I can later use it with a sql driver.

In my above example, pgo.execute will require String (query) and List(pgo.Value) (params).

The closest examples of this behavior I can think of are:

• knex > toSQL (docs)
• squirrel > ToSql (docs)

I'm hoping for a public to_sql function like the one in this updated example below. NOTE: I'm not sure if there is a more direct route from Select/Query to #(String, List(param.Param))

import cake/internal/dialect
import cake/internal/param
import cake/internal/prepared_statement
import cake/internal/query
import cake/select as sql_select
import cake/where as sql_where
import gleam/list
import gleam/pgo
import gleam/result
import snag
import snag_utils.{snag_try}
import types.{type Ninja, Ninja, get_ninja_sql_decoder, ninja_from_sql_tuple}

/// Convert query to sql/params
pub fn to_sql(query: query.Query) -> #(String, List(param.Param)) {
  // idk, guessing on the placeholder prefix
  let placeholder_prefix = "$"
  let ps =
    query.to_prepared_statement(query, placeholder_prefix, dialect.Postgres)
  let sql = prepared_statement.get_sql(ps)
  let params = prepared_statement.get_params(ps)
  #(sql, params)
}

/// Convert cake param to pgo value
pub fn param_to_value(param: param.Param) -> pgo.Value {
  case param {
    param.BoolParam(b) -> pgo.bool(b)
    param.IntParam(i) -> pgo.int(i)
    param.FloatParam(f) -> pgo.float(f)
    param.StringParam(s) -> pgo.text(s)
    param.NullParam -> pgo.null()
  }
}

pub fn ninja_get(db: pgo.Connection, id: String) -> snag.Result(Ninja) {
  // Build query, get sql/params
  let #(sql, raw_params) =
    sql_select.new()
    |> sql_select.from_table("ninjas")
    |> sql_select.select(sql_select.col("*"))
    |> sql_select.where(
      sql_where.col("id") |> sql_where.eq(sql_where.string(id)),
    )
    |> sql_select.to_query
    |> to_sql

  // Get decoder that pgo expects
  // Like: `fn(Dynamic) -> Result(a, List(DecodeError))`
  let decoder = get_ninja_sql_decoder()

  let params = list.map(raw_params, param_to_value)

  // Execute query
  // NOTE: `snag_try` returns `snag.Result(a)` instead of `result.Result(a, List(DecodeError))`
  use res <- snag_try(
    pgo.execute(sql, db, params, decoder),
    "Failed to query db for ninja",
  )
  // Convert list of pgo response tuples to list of record types
  use ninjas <- result.try(
    res.rows |> list.map(ninja_from_sql_tuple) |> result.all,
  )
  // Get first
  use ninja <- snag_try(list.first(ninjas), "No ninja found")
  Ok(ninja)
}

[inoas]

You are obviously both (@ihgrant, @jly36963) correct, even in the new demos I am using internal APIs. https://github.com/inoas/gleam-cake/blob/main/docs/demo-apps/demo_helper/src/demo_helper/postgres.gleam

I will add a public module and fix the demos as well and release that as 0.10.0 ASAP.

[inoas]

@ihgrant @jly36963 please check again if this is good for you https://hexdocs.pm/cake at v0.10.0

In case it is too much or too little or any other feedback, please let me know.

I have also updated the demo apps and test suite to use the new modules that expose what was requested here.

[ihgrant]

Thanks for the quick turnaround! I updated the package and my code but it looks like, at least for sqlight, I still need to use cake/internal/prepared_statement to turn the prepared statement into a sql string and parameters. working example here: https://codeberg.org/ihgrant/pathfinder-server-gleam/src/branch/main/src/database.gleam#L121-L123

I'm a gleam newbie so I may well be missing something too (:

[inoas]

1. I have updated the lib again to rename the library dialect modules so they are in future easier to separate from adapters implemented in user land. https://hex.pm/packages/cake/0.10.1
2. @ihgrant in 0.10 and 0.10.1 you should be able to do this:

   let sql = cake.get_sql(ps)
   let params = cake.get_params(ps)

   In 0.10.1 I have further updated the test suite to only use public APIs. https://github.com/inoas/gleam-cake/blob/main/test/test_support/adapter/sqlite.gleam#L33-L34 is an example. and https://github.com/inoas/gleam-cake/blob/main/docs/demo-apps/demo_helper/src/demo_helper/postgres.gleam#L20-L28 as well as https://github.com/inoas/gleam-cake/blob/main/docs/demo-apps/demo_helper/src/demo_helper/postgres.gleam#L49-L50 is another.

I plan do add companion apps that supply these adapters outside of cake which each depend on gleam sql clients.

[ihgrant]

Ahh perfect, missed that in the docs. Thank you!

[jly36963]

The examples are great. I feel like this solves the problem I was originally posting about; specifically, how to convert a query to something executable by a (postgres) driver.

The only thing I would like to see changed, and its more a nit than a hard requirement, is that I still need to use

import cake/internal/param
import cake/internal/query

Is there a way I can get param and query (records / custom types) without using internals?

Otherwise, I'm stoked about the functionality and the docs/examples -- thanks for the work on this 🙌

[inoas]

What do you need cake/internal/query for @jly36963 ?

Everything there should be (or will be) available via nicer sematically named public DSL/API modules such as select.gleam etc.

---

You need access to map param like this, right?

    |> list.map(fn(param: Param) -> Value {
      case param {
        BoolParam(param) -> pgo.bool(param)
        FloatParam(param) -> pgo.float(param)
        IntParam(param) -> pgo.int(param)
        StringParam(param) -> pgo.text(param)
        NullParam -> pgo.null()
      }
    })

[inoas]

@jly36963

I think I will move the param module into public namespace? Good idea?

I will probably make it opaque and also provide a getter function, does that make sense to you?

[jly36963]

@inoas That sounds great.

I think the only thing I really need from that module is Param (and its variants), as I want to create params (eg: StringParam(t)) or extract the inner value when I'm converting to pgo.Value(t) (eg: pgo.text(t))

[jly36963]

I'm working on an example with wisp/cake/pgo, once I finish I'll send a link -- it might be useful in showing which types from internal modules I'm currently using in order to effectively use the builders

[inoas]

Seems opaque does not work, but it should be ok.

[inoas]

You might need to adapt a few module imports and function names https://github.com/inoas/gleam-cake/pull/3/files

[inoas]

v0.11.0 released @jly36963 @ihgrant let me know if this works better for you or if you still have any issues.

[jly36963]

I made a quick project using cake 0.10.1, and I like it alot. this link might not always be valid (if I move my notes around in the future), but here's an example of how I'm using it with wisp.

Stoked about this library

[inoas]

I made a quick project using cake 0.10.1, and I like it alot. this link might not always be valid (if I move my notes around in the future), but here's an example of how I'm using it with wisp.

Stoked about this library

For insertion I see you are reaching for internal library parts here: https://github.com/jly36963/example-projects/blob/master/gleam/wisp_cake_pgo_example/src/pg_utils/ninjas.gleam#L52-L66

Did you see this test, does it help you to use public APIs? https://github.com/inoas/gleam-cake/blob/main/test/cake_test/insert_records_test.gleam#L21-L47

[inoas]

... and for https://github.com/jly36963/example-projects/blob/master/gleam/wisp_cake_pgo_example/src/pg_utils/ninjas.gleam#L92-L100 ... would something like https://github.com/inoas/gleam-cake/blob/main/test/cake_test/update_test.gleam#L33-L41 work for you?

NOTE: I am on purpose with the exception of prepared_statement module not using opaque types so you CAN reach into internals at any time. But they are not safe to stay compatible for minor/bugfix releases (whereas the public APIs will respecting semver, after 1.0.0 which should happen soon(tm)).

[inoas]

Another tip, if it does not confuse you is to move all queries into a separate module (or NOT...) and the instead of:

import cake/delete as sql_delete
import cake/insert as sql_insert
import cake/internal/param
import cake/select as sql_select
import cake/update as sql_update
import cake/where as sql_where

... write:

import cake/delete as d
import cake/insert as i
import cake/param as p
import cake/select as s
import cake/update as u
import cake/where as w

... which trims source code a lot and helps with readability imho.

[jly36963]

I write Python on a regular basis, and pylint complains about 1- and 2-letter variable names (except when in a short scope like a list comprehension).

So for

import cake/delete as sql_delete

It's mostly a product of being conditioned by pylint 😂

I like shorter names, but 100% agree that 10+ one-letter variables in a given file make code reviews painful

[inoas]

So 0.11.0 is out I hope it addresses all your current issues.

[jly36963]

With 0.11.0, I think I'm off internals with the exception of read_query and write_query example

[inoas]

With 0.11.0, I think I'm off internals with the exception of read_query and write_query example

But those you only need for type signatures, right? I am not sure what to do about that.

[jly36963]

In this example I can probably omit the types and let type inference fill it, but generally I run into issues when public modules expose private types (via function params and/or returns).

I'm okay with that being a conversation for another day -- as it stands I think the points of friction I was running into have all been resolved

[inoas]

I will see if I can expose all the internal types required by reexporting them.

[jly36963]

With 0.12.0 I'm completely off of internals 🎉