Brendan Hansknecht (Jan 02 2025 at 22:58): Brendan Hansknecht (Jan 02 2025 at 22:58):Ok, two more things that I still don't like with the sqlite api. Thought it would be good to get ideas here.
For a simple query, the sqlite api is pretty reasonable (if a bit much to learn at once):
Sqlite.query_many! {
path: db_path,
query: "SELECT id, task FROM todos WHERE status = :status;",
bindings: [{ name: ":status", value: String "completed" }],
rows: { Sqlite.decode_record <-
id: Sqlite.i64 "id",
task: Sqlite.str "task",
},
}
This will grab all todos with a specific status. It will automatically decode them to a Result (List { id: I64, task: Str }) _. I think this api is reasonable enough.
I think the api is a lot less reasonable with prepared queries (simple example). The split of preparing the query and binding/decoding the result leads to a less understandable result:
init! = \{} ->
stmt =
Sqlite.prepare! {
path: db_path,
query: "SELECT id, task FROM todos WHERE status = :status;",
}
...
actually_query! = \stmt ->
result =
Sqlite.query_many_prepared! {
stmt,
bindings: [{ name: ":status", value: String "completed" }],
rows: { Sqlite.decode_record <-
id: Sqlite.i64 "id",
task: Sqlite.str "task",
},
}
...
Maybe the decoder should be specified when preparing the query? That said, there is still not a great link between the bound variables and the original query. The split definitely hurts understandability, but preparing queries is quite important for perf.
Secondly, we don't have an api for transactions yet. And they really are not nice to do manually. If you want max perf, it requires adding 3 extra prepared stmts to your model (begin, end, and rollback). On top of that, the actual transaction logic feels a bit inconvenient.
Part of the problem with designing an api for this is that you need to store a prepared query for begin, end, and rollback if you want max perf.
Not that max perf truly matters here, but I really want to design the api to always use prepared queries if possible.
Anyway, open to any thoughts and suggestions. Just feel like the api isn't quite right yet.
Jasper Woudenberg (Jan 02 2025 at 23:24):I'm curious, why is bindings a list?
For the prepared statement problem, if you move the decoder into the prepared statement as you suggest, would it be possible to let Sqlite.prepare! return a function?
init! = \{} ->
query : { status: Str } => List { id: I64, task: Str }
query =
Sqlite.prepare! {
path: db_path,
query: "SELECT id, task FROM todos WHERE status = :status;",
bindings: \{ status } -> [{ name: ":status", value: status }],
rows: { Sqlite.decode_record <-
id: Sqlite.i64 "id",
task: Sqlite.str "task",
},
}
...
actually_query! = \stmt ->
result = query({ status: "completed" })
...
Then the type of the function becomes the prepared statement returns is self-explanatory, even without the SQL nearby.
Jasper Woudenberg (Jan 02 2025 at 23:28):Neat use of record builders BTW!
Sam Mohr (Jan 02 2025 at 23:29):That's @Agus Zubiaga 's record building handiwork, he pioneered the feature in the first place
Brendan Hansknecht (Jan 02 2025 at 23:33):why is
bindingsa list?
Simplicity
Brendan Hansknecht (Jan 02 2025 at 23:35):I don't want a full query language in roc. Just want an sql string. As such, not sure the best way to do bindings.
Open for suggestions for that too. Only really has to be a list at the low level to pass to the platform (Though even that could get split into many separate bind calls technically)
Jasper Woudenberg (Jan 02 2025 at 23:35):For the transaction, maybe a similar thing is possible to the prepared statement is possible, where there is a prepare_transactionfunction to be called from init. prepare_transaction will immediately prepare the three transaction queries and subsequently return the function to create transactions in actual code.
Jasper Woudenberg (Jan 02 2025 at 23:40):Or, another idea, a single Slite.setup function to use in init:
init! = \{} ->
db : {
transaction : {} => {},
list_todos! : { status: Str } => List { id: I64, task: Str },
}
db = { Sqlite.setup! <-
transaction : Sqlite.prepare_transaction!,
list_todos! : Sqlite.prepare! {
path: db_path,
query: "SELECT id, task FROM todos WHERE status = :status;",
bindings: \{ status } -> [{ name: ":status", value: status }],
rows: { Sqlite.decode_record <-
id: Sqlite.i64 "id",
task: Sqlite.str "task",
},
}
}
...
actually_query! = \db ->
result = (db.list_todos) { status: "completed" }
...
Brendan Hansknecht said:
I don't want a full query language in roc. Just want an sql string. As such, not sure the best way to do bindings.
Open for suggestions for that too. Only really has to be a list at the low level to pass to the platform (Though even that could get split into many separate bind calls technically)
I like the straigt-forward binding between query and Roc code, that makes sense to me. In the example I notice the list just has a single element, a better way to phrase my question is when you would have multiple bindings in that list, or zero.
Brendan Hansknecht (Jan 02 2025 at 23:44):It's based on the query. One per :name variable in the query.
Brendan Hansknecht (Jan 02 2025 at 23:44):So select everything would have none
Brendan Hansknecht (Jan 02 2025 at 23:44):Selecting with many conditions would have many
Jasper Woudenberg (Jan 02 2025 at 23:45):Ohhh, I didn't read that right. I thought there were multiple params in that record already, but it's key-value pairs. That explains it, thanks!
Brendan Hansknecht (Jan 02 2025 at 23:48):Ah. Yeah, just kv pairs
Jasper Woudenberg (Jan 02 2025 at 23:53):Oh, that last idea with Sqlite.setup! doesn't make sense, if it's supposed to return a plain record. I guess the app author could opt put all their queries in a record, but they wouldn't need help from the library for that.
Brendan Hansknecht (Jan 02 2025 at 23:54):That said, even your base idea for changes to prepare would solve the issues.
Brendan Hansknecht (Jan 02 2025 at 23:55):And not sure if there is a better higher level solution for transactions but the simple solution may be enough.
Brendan Hansknecht (Jan 02 2025 at 23:58):Just prepare_transaction! : [ Deferred, Immediate, Exclusive ] => (higher level functions for running a lambda as a transaction)
Brendan Hansknecht (Jan 02 2025 at 23:59):Then it would just be missing save points, but I think that is fine for now. Just keep it simple.
Brendan Hansknecht (Jan 03 2025 at 00:00):I'll make this changes. Want to get the nicer API in before whenever we do the next release if possible.
Brendan Hansknecht (Jan 03 2025 at 00:00):Also, let me know if there is a better alternative to bindings as a list you can think of.
Brendan Hansknecht (Jan 03 2025 at 00:55):Thoughts for prepared queries. I think this reads a lot better overall.
main! = \_args ->
db_path = try Env.var! "DB_PATH"
query_todos_by_status! = try Sqlite.prepare_query_many! {
path: db_path,
query: "SELECT id, task FROM todos WHERE status = :status;",
bindings: \status -> [{ name: ":status", value: String status }],
rows: { Sqlite.decode_record <-
id: Sqlite.i64 "id" |> Sqlite.map_value Num.toStr,
task: Sqlite.str "task",
},
}
todo = try query_todos_by_status! "todo"
try Stdout.line! "Todo Tasks:"
try List.forEachTry! todo \{ id, task } ->
Stdout.line! "\tid: $(id), task: $(task)"
completed = try query_todos_by_status! "completed"
try Stdout.line! "\nCompleted Tasks:"
try List.forEachTry! completed \{ id, task } ->
Stdout.line! "\tid: $(id), task: $(task)"
Ok {}
And transactions now work like this:
exec_transaction! = try prepare_transaction! { path: "path/to/database.db" }
try exec_transaction! \{} ->
try Sqlite.execute! {
path: "path/to/database.db",
query: "INSERT INTO users (first, last) VALUES (:first, :last);",
bindings: [
{ name: ":first", value: String "John" },
{ name: ":last", value: String "Smith" },
],
}
# Oh no, hit an error. Need to rollback.
# Note: Error could be anything.
Err NeedToRollback
Both of these feel ok. Though some of the type signatures are definitely a bit complex.
Brendan Hansknecht (Jan 03 2025 at 01:30):https://github.com/roc-lang/basic-cli/pull/302
Luke Boswell (Jan 03 2025 at 02:15):What is the type of query_todos_by_status!?
Luke Boswell (Jan 03 2025 at 02:16):If these are examples, I feel like we should type annotations... even if they're not required.
Brendan Hansknecht (Jan 03 2025 at 02:22):PreparedQueryManyStmt Str { id: Str, task: Str } _
Where _ is whatever the error type is.
Brendan Hansknecht (Jan 03 2025 at 02:24):Luke Boswell said:
If these are examples, I feel like we should type annotations... even if they're not required.
I personally don't like that. Roc can be used without type annotations and adding the annotations makes everything feel more complex than it is.
Brendan Hansknecht (Jan 03 2025 at 02:25):Also, debating changing the name to QueryManyFn for some simplicity.
Brendan Hansknecht (Jan 03 2025 at 02:33):Pushed the simplified names
Brendan Hansknecht (Jan 03 2025 at 05:13):Ok, so this design still has a major flaw. Due to generating the entire prepared statement into a query function, the errors suck. They are a pain to store in the Model for basic webserver.
Simple example:
init! : {} => Result Model _
init! = \{} ->
# Read DB_PATH environment variable
db_path =
Env.var! "DB_PATH"
|> Result.mapErr \_ -> ServerErr "DB_PATH not set on environment"
|> try
query_todos_by_status! =
Sqlite.prepare_query_many! {
path: db_path,
query: "SELECT id, task FROM todos WHERE status = :status;",
bindings: \name -> [{ name: ":status", value: String name }],
rows: { Sqlite.decode_record <-
id: Sqlite.i64 "id",
task: Sqlite.str "task",
},
}
|> Result.mapErr \err -> ServerErr "Failed to prepare Sqlite statement: $(Inspect.toStr err)"
|> try
Ok { query_todos_by_status! }
What is model. It is a:
{ query_todos_by_status! : Str => Result (List {
id : I64,
task : Str,
}) (Sqlite.SqlDecodeErr [
FailedToDecodeInteger [],
UnexpectedType [
Bytes,
Integer,
Null,
Real,
String,
],
]) }
With this type alias: QueryManyFn in out err : in => Result (List out) (SqlDecodeErr err)
it is a:
Model : { query_todos_by_status! : Sqlite.QueryManyFn Str { id : I64, task : Str } [FailedToDecodeInteger [], UnexpectedType [Bytes, Integer, Null, Real, String]] }
This quickly gets to be a mess (especially with many queries). I am unable to put an _ there cause:
Type alias definitions may not use inferred types (_).
On top of that, even when I type it correctly, it becomes a closed tag union. Due to being a closed tag, the errors no longer propagate.
So I went a different route:
Model err : { query_todos_by_status! : Sqlite.QueryManyFn Str { id : I64, task : Str } err }
This feels like it should work . But it breaks due to the Model being passed to the platform. The use in the platform does not expect a type that takes a type variable.
I tried replacing the entire use in the platform with a type variable, but that also breaks. Cause a model type variable contains no info and can not be used as a Model err
So the apis are much nicer to use, but the types have become really hard to work with.
Brendan Hansknecht (Jan 03 2025 at 05:16):To make the code actually work, I need to type it like so:
Model : {
query_todos_by_status! : Sqlite.QueryManyFn Str { id : I64, task : Str } [
FailedToDecodeInteger [],
UnexpectedType [Bytes, Integer, Null, Real, String],
StdoutErr [
AlreadyExists,
BrokenPipe,
Interrupted,
NotFound,
Other Str,
OutOfMemory,
PermissionDenied,
Unsupported,
],
ServerErr Str,
],
}
Code here: https://github.com/roc-lang/basic-webserver/tree/sqlite-better-queries
Jasper Woudenberg (Jan 03 2025 at 08:17):How would you feel about creating a type alias for just the error?
# Sqlite.roc
QueryError : SqlDecodeErr [
FailedToDecodeInteger [],
UnexpectedType [
Bytes,
Integer,
Null,
Real,
String,
],
ServerErr Str,
]
Then the appliction Model types becomes:
Model : {
query_todos_by_status! : Str => Result (List { id : I64, task : Str }) Sqlite.QueryError
}
I think that's a pretty reasonable type, every bit of it is useful documentation of how query_todos_by_status! is supposed to be used. It takes up a similar amount of space as the Sqlite.QueryManyFn-based type, and using _ instead of the input, output, and/or error type should work.
Jasper Woudenberg (Jan 03 2025 at 08:41):Brendan Hansknecht said:
Also, let me know if there is a better alternative to bindings as a list you can think of.
The one thing that occured to me is that it would be possible to take any Roc Record as an argument list and then use an encoder to pull out the key-value pairs and put them in the query.
A nice benefit of that approach is that where in the current approach the bindings params contains some "runtime type-annotations" (the constructors to the value field), in the encoder approach the application author could instead specify the types of params using regular Roc type annotations.
A downside is that whereas the current "runtime type-annotations" are required, Roc type annotations are optional. That might lead to problems if the application author chooses not to add them, given the Model needs to be concrete in order for it to be passed to the platform.
A possible fix for that would be to keep the bindings params, but use it solely to describe the expected input type. That would also make it possible to make certain invalid input types compile-time errors, such as attempting to pass List U64] as a param value.
# single param
bindings: Sqlite.Bindings Str
bindings = Sqlite.string
# record of params
bindings : Sqlite.Bindings { status : Str, limit : U64 }
bindings = { Sqlite.bindings <-
status: Sqlite.str,
limit: Sqlite.u64,
}
# Sqlite.roc
Bindings a := {}
str : Bindings Str
str = @Bindings {}
u64 : Bindings U64
u64 = @Bindings {}
prepare_query! :
{
path : Str,
query : Str,
bindings : Bindings in,
row : SqlDecode out (RowCountErr err),
}
=> Result (in => Result out err) [SqliteErr ErrCode Str]
Another potential benefit of the Sqlite.Bindings + encoder params API: It would make the types of the parameters passed in knowable when preparing queries. I imagine that means that the following errors could be handled at prepare time, rather than at query time:
UnexpectedType [
Bytes,
Integer,
Null,
Real,
String,
]
Jasper Woudenberg said:
How would you feel about creating a type alias for just the error?
# Sqlite.roc QueryError : SqlDecodeErr [ FailedToDecodeInteger [], UnexpectedType [ Bytes, Integer, Null, Real, String, ], ServerErr Str, ]
That wouldn't t work:
ServerErr is from the app, not from sqlite. Brendan Hansknecht (Jan 03 2025 at 15:01):Also, unexpected type comes from decoding a row. If you try to decode as strings and one of the items in a column is an int, that will lead to the error.
Jasper Woudenberg (Jan 03 2025 at 16:19):Ah, makes sense, too bad. Does it work to remove Sqlite.QueryManyFn and use _ in the Model on the error position?
Another approach is to add a onError argument to prepare_query_many!. That would allow the application author to map that error to a more reasonable type, and subsequently you have that type rather than the original in the Model.
Brendan Hansknecht (Jan 03 2025 at 16:22):Jasper Woudenberg said:
Ah, makes sense, too bad. Does it work to remove
Sqlite.QueryManyFnand use_in theModelon the error position?
This is what I really wish worked, but isn't allowed in type aliases.
Brendan Hansknecht (Jan 03 2025 at 16:22):Yeah, onError may be the best solution.
Brendan Hansknecht (Jan 03 2025 at 16:23):This feels like a case where there should be a much nicer solution but I'm not seeing it.
Brendan Hansknecht (Jan 03 2025 at 16:27):Brendan Hansknecht said:
Ok, so this design still has a major flaw. Due to generating the entire prepared statement into a query function, the errors suck. They are a pain to store in the
Modelfor basic webserver.
@Ayaz Hafiz or @Richard Feldman any thought on the issues being described starting in this comment? Feels like the type system is just too rigid for this design making errors a pain.
A big part of the issue is that stored lambdas no longer return open tags. Maybe that is a bug?
Richard Feldman (Jan 03 2025 at 16:30):stored lambdas no longer return open tags
I assume the reason for this is that the hidden type variable doesn't propagate to the lambdas? :thinking:
Ayaz would know
Ayaz Hafiz (Jan 03 2025 at 16:34):yeah the hidden "open" type variable doesn't apply to unions under aliases
Ayaz Hafiz (Jan 03 2025 at 16:34):it would break the alias type
Richard Feldman (Jan 03 2025 at 16:35):because the variable doesn't appear in the alias's type parameters?
Ayaz Hafiz (Jan 03 2025 at 16:36):yes
Ayaz Hafiz (Jan 03 2025 at 16:37):and the type errors would be really weird, because now aliases don't necessarily have the same type
Richard Feldman (Jan 03 2025 at 16:37):so it would have to be
# Sqlite.roc
QueryError a : SqlDecodeErr [
FailedToDecodeInteger [],
UnexpectedType [
Bytes,
Integer,
Null,
Real,
String,
]a,
ServerErr Str,
]
ye[
Brendan Hansknecht (Jan 03 2025 at 17:20):The only issue with adding an err type variable is that the Model can't have any type variables with how it is passed to the platform currently.
Brendan Hansknecht (Jan 03 2025 at 17:20):Any idea how to deal with that. I think the type has to be concrete for the platform to use it.
Brendan Hansknecht (Jan 03 2025 at 17:21):Maybe it is just a sign that this kind of design doesn't really work well cause the errors will become a mess and have to be stored.
Ayaz Hafiz (Jan 03 2025 at 17:21):i don't think there is a good way to deal with that without upcasting/downcasting
Ayaz Hafiz (Jan 03 2025 at 17:22):the less granular api would be to say that the API can return any error, or make the error more opaque (e.g. UnexpectedType Str)
Ayaz Hafiz (Jan 03 2025 at 17:23):fwiw i don't think there's really any recovery with sql query errors. You either log the error and emit a 500 or forward the error to the user. So I think a less granular API is okay.
Brendan Hansknecht (Jan 03 2025 at 19:31):That's a good tip.
Brendan Hansknecht (Jan 07 2025 at 15:09):So I just had a realization. If we are willing to switch back to having server = { init!, respond! }. We can link the type variables in basic-webserver.
That enables use to use the platform type as.
Server model init_err respond_err : {
init! : {} => Result model init_err,
respond! : Http.Request, model => Result Http.Response respond_err,
}
Then in the app we can define the model as:
Model err : {
query_todos_by_status! : Sqlite.QueryManyFn Str { id : I64, task : Str } err,
}
This enables us to keep all the error information and avoid being explicit about all of the error tag union merging.
Thoughts?
Brendan Hansknecht (Jan 07 2025 at 15:40):That leads to this: https://github.com/roc-lang/basic-webserver/blob/6d47b5b3b992ac36a2aeda6cd01c9362b94de32d/examples/todos.roc#L12-L58
Which on one hand is a lot nicer to use, but the types still aren't nice.
Last updated: Jul 06 2025 at 12:14 UTC