Brendan Hansknecht (Jul 05 2024 at 17:15): Brendan Hansknecht (Jul 05 2024 at 17:15):So, I'm guessing that @Agus Zubiaga knows and the answer is somehow record builders, but general question: How do we make a nicer api for something like sqlite queries? If you look at basic webserver today, it is:
execute :
{
path : Str,
query : Str,
bindings : List Binding,
}
-> Task (List (List InternalSQL.SQLiteValue)) Error
Obviously for performance reasons, we could add prepared statements and keep those alive, but I am more concerned about the output type. The output type is a list of lists of tagged values. So for every row, we are paying the cost of an extra allocation. On top of that, the returned values have to be pattern matched. They basically have runtime type info.
For this query, SELECT id, task FROM todos WHERE status = :status, the preferred type is almost certainly a List { id: I64, task: Str}. That both skips n allocations and has concrete types instead of a generic wrapper type.
Agus Zubiaga (Jul 05 2024 at 17:57):Right. The way this works in roc-pg is that before getting row data, we get a Row Description message which tells us what type each column holds and the name corresponding to each index.
The result of a command is an opaque type that looks like this:
CmdResult := {
fields : List RowField, # name, type (amongst other things)
rows : List (List (List U8)),
}
The way you access the result is through a decoder API that can be used with record builders. All together a query looks like this:
Pg.Cmd.new "select name, price from products"
|> Pg.Cmd.expectN (
Pg.Result.succeed {
name: <- Pg.Result.str "name" |> Pg.Result.apply,
price: <- Pg.Result.dec "price" |> Pg.Result.apply
}
)
|> Pg.Client.command client
It currently doesn't do this (I'm going to rewrite a lot of roc-pg once I'm done with module params), but the idea is that we can use the type information from the row description to check that types of each columns match.
Agus Zubiaga (Jul 05 2024 at 18:01):Right now it will attempt to decode them anyway
Brendan Hansknecht (Jul 05 2024 at 18:02):Oof, List (List (List U8))... cries in allocations.
Brendan Hansknecht (Jul 05 2024 at 18:03):Theoretically, it could decode on the fly and directly build the output type?
Agus Zubiaga (Jul 05 2024 at 18:04):Yeah, for sure. That's just what it's now, but it doesn't have to be
Agus Zubiaga (Jul 05 2024 at 18:05):I also want to build a cursor-based API for streaming applications
Brendan Hansknecht (Jul 05 2024 at 18:05):I wonder what the cost of FFI is. Cause theoretically, I could run the sqlite state machine in roc. That would enable it to build up whatever type and have a similar API that can avoid allocations.
Brendan Hansknecht (Jul 05 2024 at 18:05):Probably a lot smaller than the cost of many allocations.
Agus Zubiaga (Jul 05 2024 at 18:06):Yeah, that makes sense
Brendan Hansknecht (Jul 05 2024 at 18:20):I think I might take a crack at improving the sqlite API for basic webserver. Won't make a query builder (still just string queries), but want to try and make a cursor API that can use record builders to extract a list of rows.
Brendan Hansknecht (Jul 06 2024 at 05:55):So thinking about this more. Instead of record builder, would you actually just want to use the more flexible version of decode (#ideas > Revamped Encode and Decode ) for using a cursor style api to build out roc types?
I guess I'm not exactly sure how a record builder would apply to something generating a list of records like an sql query.
Agus Zubiaga (Jul 06 2024 at 11:13):Ah, right. I guess Decode would be a better fit for a raw-SQL API. You’d probably have to fail in some cases such as nested records, but that’s probably fine.
I tried implementing it for roc-pg originally, but the current implementation isn’t flexible enough. I needed custom state, but I believe that’s fixed by this flexible proposal.
Agus Zubiaga (Jul 06 2024 at 11:16):For a type safe query builder, though, record builders are better because they allow you to compose the selection and decoding as one
Brendan Hansknecht (Jul 06 2024 at 15:50):Luckily for me sqlite doesn't really have types anyway, just suggestions. As in, it records a type for every element, but the type of an element does not need to match the type of a column. The column type is just the "recommended" type. Besides the special case of null, it will happily attempt to convert any type to any other type.
Brendan Hansknecht (Jul 06 2024 at 15:53):Yeah :mind_blown: was my reaction too when learning this
Brendan Hansknecht (Jul 06 2024 at 15:54):https://youtu.be/sgVpOaJLoG0?si=l8Cg5t_Z8oXDQUHJ
Agus Zubiaga (Jul 06 2024 at 16:00):ah ok, at least you can make tables "strict"
Agus Zubiaga (Jul 06 2024 at 16:01):still annoying that everything has to be tagged (I imagine) in order to support these flexible types
Brendan Hansknecht (Jul 06 2024 at 16:02):For sure, but nothing is nested and loading a raw value from sqlite should be cheap with a cursor API. So not that much of a cost.
Brendan Hansknecht (Jul 06 2024 at 16:04):The rust sqlite library doesn't even expose getting the real column type ... :joy:
Brendan Hansknecht (Jul 06 2024 at 16:04):Just the current row column type
Brendan Hansknecht (Jul 06 2024 at 16:10):Ah, looks like rusqlite is the more popular library with more features like this.
Brendan Hansknecht (Jul 06 2024 at 16:11):That said, I probably could pretty easily live with a raw sqlite3 wrapper for roc. Don't really need fancy rust macros and features. Just raw query binding and cursor access.
Brendan Hansknecht (Jul 06 2024 at 18:39):I attempted to make a basic form of something that matches the roc-pg record builder for sqlite. It directly reads a column at a time, converting result and building a record. I have it type checking, but I don't think roc likes the equivalent of roc-pg but using a bunch of Task instead of Result.
The end code looks pretty decent though:
queryTodosByStatus = \dbPath, status ->
stmt = SQLite3.prepareAndBind! {
path: dbPath,
query: "SELECT id, task FROM todos WHERE status = :status;",
bindings: [{ name: ":status", value: String status }],
}
SQLite3.decode!
stmt
(
SQLite3.succeed {
id: <- SQLite3.i64 "id" |> SQLite3.apply,
task: <- SQLite3.str "task" |> SQLite3.apply,
}
)
On this branch if anyway wants to look into why it won't compile: https://github.com/roc-lang/basic-webserver/compare/main...sqlite-improvements
Brendan Hansknecht (Jul 06 2024 at 18:40):Ends up being a no lambda set found error.
Brendan Hansknecht (Jul 06 2024 at 20:58):Also, I think I have everything wired up on the rust side. So assuming we can figure out the roc compiler error or a workaround, this should be a much nicer and more performant sqlite api. One that is able to just build a final list of the wanted result type without any runtime tagging being required.
Also has the power to reuse prepared statements, but unless we get prepared statement cached at init time, that won't be particularly useful.
Caveat: also need to verify if statements get freed at the correct time.
Brendan Hansknecht (Jul 06 2024 at 21:23):@Agus Zubiaga quick record builder question:
Is there a reason to separate Pg.Result.apply from Pg.Result.str?
As in, you would embed Pg.Result.apply into Pg.Result.str and then the record builder code would be:
Pg.Cmd.new "select name, price from products"
|> Pg.Cmd.expectN (
Pg.Result.succeed {
name: <- Pg.Result.str "name",
price: <- Pg.Result.dec "price"
}
)
|> Pg.Client.command client
Yeah, that’s what I did for the query builder. That is more convenient, but the downside is that the resulting type is a function which is not as nice if you want to map it right there.
Agus Zubiaga (Jul 06 2024 at 21:26):That’s not an issue in the proposed map2-based record builders, though
Agus Zubiaga (Jul 06 2024 at 21:27):which one of the main reasons I’m looking forward to it tbh
Brendan Hansknecht (Jul 06 2024 at 21:29):If you map in the builder, can you return new error types? I guess so, the errors unify. So like you theoretically could take that string and run decode on it right in the builder then pass it to apply.
Agus Zubiaga (Jul 06 2024 at 21:30):I described that issue on the last point here: https://roc.zulipchat.com/#narrow/stream/304641-ideas/topic/map2-based.20record.20builders/near/446284737
Brendan Hansknecht (Jul 07 2024 at 01:52):@Ayaz Hafiz Do we have any good way to start debugging something like:
no lambda set found for (`36.IdentId(76)`, [
InLayout(
42,
),
InLayout(
425,
),
]): LambdaSet {
set: [
( 36.76, [InLayout(42), InLayout(274)]),
],
args: [
InLayout(UNIT),
],
ret: InLayout(
269,
),
representation: InLayout(
275,
),
full_layout: InLayout(
276,
),
}
?
ROC_CHECK_MONO_IR=1 I don't think is reached (at least timing execution didn't change with this set.
ROC_PRINT_IR_AFTER_SPECIALIZATION=1 is not reached before the crash.
Ayaz Hafiz (Jul 07 2024 at 02:07):not really.. but the problem here is that InLayout(425) is not InLayout(274). You can print out the full representation of those types with interner.dbg_deep
Richard Feldman (Jul 07 2024 at 02:09):is this likely related to the cross-module bug?
Richard Feldman (Jul 07 2024 at 02:09):(the bug where "this lambda set is not this other one, but ought to be" is a symptom)
Ayaz Hafiz (Jul 07 2024 at 02:28):i cant tell without more investigation
Last updated: Jul 06 2025 at 12:14 UTC