SurrealDB query builder based on reifying · ideas

Why? There is no world that there is one page that lists just the pets (for example "my pets"), and then another page that lists them with owners? (Like current clients that shows all the pets with the owner info)

notramo (Oct 29 2026 at 14:02):

But if the field doesn't even exist on the struct that the query returns, it's also type-checked at compile time.

Krzysztof Skowronek (Oct 29 2026 at 14:02):

You can remove the fils if you add a map at the end

notramo (Oct 29 2026 at 14:03):

I don't need these when writing in the own query language of SurrealDB.

Krzysztof Skowronek (Oct 29 2026 at 14:03):

If you don't do the map/select, it becomes select *

Karakatiza (Oct 29 2026 at 14:04):

@notramo @Krzysztof Skowronek let's continue here

notramo (Oct 29 2026 at 14:05):

I think it's possible in Roc to entirely omit the field from the result if it's not loaded. Much simpler than adding a [Loaded, Unloaded].

notramo (Oct 29 2026 at 14:06):

Also, a query result should be a simple Roc struct, that don't retain any reference to the database.

Karakatiza (Oct 29 2026 at 14:06):

I think it's not that. Currently fields are loaded with an explicit .fetch(). So the builder object has no way to differentiate between loaded and unloaded

Possibly this API itself needs to be improved

Karakatiza (Oct 29 2026 at 14:06):

should be a simple Roc struct

It is

Krzysztof Skowronek (Oct 29 2026 at 14:07):

By convention, in EF you usually add 2 properties:

comment.Post - that's the full object, needs a join
comment.PostId - just the foreign key, matched by name convention

Krzysztof Skowronek (Oct 29 2026 at 14:08):

So maybe the link could be just either Loaded(entity), or Link(foreign_key)?

Krzysztof Skowronek (Oct 29 2026 at 14:08):

Yeah, these are simple struts just wrapped in a tag

notramo (Oct 29 2026 at 14:09):

But this don't need to be an EF clone. EF is in a different language with a different database.
If you ever tried SurrealDB or EdgeDB in TypeScript, you would understand how much simpler this approach is.

Krzysztof Skowronek (Oct 29 2026 at 14:09):

I agree

Krzysztof Skowronek (Oct 29 2026 at 14:10):

So you are saying that in surreal when I do equivalent to "select * from pets" it skips the foreign keys/links?

notramo (Oct 29 2026 at 14:13):

Also, I would not call it an ORM. Most ORMs retain references to the database client, so the object can make lazy queries itself, and this is how waterfall queries sneak in. But with query builders, you build the query once, and get a plain struct that has no database-related code, can not run further queries or anything. It's a simple Roc struct, with the data only, no query builder or anything. If you want further queries, build a new one using the query builder.

Krzysztof Skowronek (Oct 29 2026 at 14:13):

I am only saying "in EF it's like this" because in general is pretty good at what it does, and if you want to be inspired by other orm, it should be on the list

Krzysztof Skowronek (Oct 29 2026 at 14:14):

notramo said:

Also, I would not call it an ORM. Most ORMs retain references to the database client, so the object can make lazy queries itself, and this is how waterfall queries sneak in. But with query builders, you build the query once, and get a plain struct that has no database-related code, can not run further queries or anything. It's a simple Roc struct, with the data only, no query builder or anything. If you want further queries, build a new one using the query builder.

That's how you use EF Core 99% of the time, without any change tracking is just a query builder and mapper from SQL response to plain dtos

notramo (Oct 29 2026 at 14:15):

So you are saying that in surreal when I do equivalent to "select * from pets" it skips the foreign keys/links?

Mostly. But there's no concept of "foreign key" in SurrealDB.

notramo (Oct 29 2026 at 14:17):

Record links are basically queried like if they were embedded in the record, the same syntax as a nested field.

Karakatiza (Oct 29 2026 at 14:18):

This means it could be possible to derive whether to load the table or not based on field access?

Krzysztof Skowronek (Oct 29 2026 at 14:19):

I guess in roc with all the type inference you can do a lot, like it you call fetch(|p| p.owner) just append that property to a type

But then it would be tricky to write a function that takes a pet, because the types would not match, unless you define it in a way that structured matching applies

notramo (Oct 29 2026 at 14:20):

CREATE person:alice SET
  name = 'Alice',
  friends = [person:bob, person:charlie];

-- traverse with normal field access
SELECT friends.name FROM person:alice;

-- or FETCH
SELECT * FROM person:alice FETCH friends;

notramo (Oct 29 2026 at 14:21):

There's no JOIN and no "foreign key".

Krzysztof Skowronek (Oct 29 2026 at 14:21):

notramo said:

Record links are basically queried like if they were embedded in the record, the same syntax as a nested field.

I've never used surreal specifically, I guess you are able to do something like 'owner.pets.map(|p| p.id)` to get just IDs of pets to put in a message queue or something?

Krzysztof Skowronek (Oct 29 2026 at 14:23):

Ok, your example came as I was typing, it makes it much clearer

Krzysztof Skowronek (Oct 29 2026 at 14:23):

So a single database trip is multiple statements like above?

notramo (Oct 29 2026 at 14:23):

Not necessarily.

Krzysztof Skowronek (Oct 29 2026 at 14:24):

Or is it just tow way to get the same result?

notramo (Oct 29 2026 at 14:24):

It is demonstrating two approaches to querying links.

notramo (Oct 29 2026 at 14:25):

SELECT * is not including links by default, so the FETCH is needed.

notramo (Oct 29 2026 at 14:25):

But I prefer to select only the fields that I use in that HTTP endpoint.

notramo (Oct 29 2026 at 14:25):

And that's easy with the automatic join.

Karakatiza (Oct 29 2026 at 14:29):

Should both ways be available through the ORM?

notramo (Oct 29 2026 at 14:30):

I would prefer to call it a query builder, not an ORM.

notramo (Oct 29 2026 at 14:30):

No need to expose these.

notramo (Oct 29 2026 at 14:30):

Automatically decide under the hood, which is better for the given query.

Karakatiza (Oct 29 2026 at 14:30):

I need a short way to say what it is :smile:

notramo (Oct 29 2026 at 14:31):

Here are the feature requests from the previous thread:

time series
geo
database functions

Karakatiza (Oct 29 2026 at 14:31):

Yep, did not lose them

Krzysztof Skowronek (Oct 29 2026 at 14:31):

notramo said:

I would prefer to call it a query builder, not an ORM.

That depends, how do you manage the schema? In EF the c# code is the source of truth, and it generates migrations if you change it, like add a property

You can also generate the code to match existing schema and just not use EF for migrations

Karakatiza (Oct 29 2026 at 14:32):

The more "decide under the hood" there is, the more of an ORM it becomes, does it not? :slight_smile:

notramo (Oct 29 2026 at 14:32):

Basically, if * is exposed, the fetch syntax is needed. If not, then it's not needed at all.

Krzysztof Skowronek (Oct 29 2026 at 14:32):

It also gives you a command to generate a self contained migration binary you can deploy in a side cart so that only one pod applies the migrations

notramo (Oct 29 2026 at 14:33):

Another feature that I would like to use:
https://surrealdb.com/docs/reference/query-language/language-primitives/data-types/files

notramo (Oct 29 2026 at 14:33):

Basically, if * is exposed, the fetch syntax is needed. If not, then it's not needed at all.

If you list out all the fields in the query, then it's not needed.

Krzysztof Skowronek (Oct 29 2026 at 14:35):

You can make the final map/select be mandatory, then this discussion done I guess

Karakatiza (Oct 29 2026 at 14:43):

I'll think about this: since everything is derived from Roc structs, there is not really a place for a * select - at any point all fields are known and listed. I guess this leaves * and FETCH only as escape hatch

notramo (Oct 29 2026 at 14:43):

Probably yes.

notramo (Oct 29 2026 at 14:43):

I haven't looked at it yet in detail.

notramo (Oct 29 2026 at 14:45):

Would it be possible to rebase on the latest changes? LLM agents are quite good at vibe-rebasing nowadays.

Karakatiza (Oct 29 2026 at 14:48):

I'm working on a big reifier change now, will probably rebase later today. I rebase a few times a week

Krzysztof Skowronek (Oct 29 2026 at 14:55):

So do you imagine this being a full orm with roc code being the source of truth, managing migrations, applying then and so on, or would you rather delegate this to surrealkit or something like that and just generate the types from the schema file?

notramo (Oct 29 2026 at 14:56):

Both solutions can be good.

notramo (Oct 29 2026 at 14:58):

I just want to try something that works, to evaluate the practicality of this feature in real-world usage.

notramo (Oct 29 2026 at 14:59):

Not just by checking out the code, but building an actual webapp.

Krzysztof Skowronek (Oct 29 2026 at 15:00):

Since surrealdb has live queries, it would great to be able to say '.live()' instead of '.bind()' and get a Stream of responses

Krzysztof Skowronek (Oct 29 2026 at 15:00):

And maybe 'live_diff()'

Krzysztof Skowronek (Oct 29 2026 at 15:00):

That's one thing I definitely miss from postgres

notramo (Oct 29 2026 at 15:01):

Oh, I forgot to add live queries to the feature request list.

Krzysztof Skowronek (Oct 29 2026 at 15:01):

On the other hand, postgres works very well as a transport for message queues, so you don't need rabbitmq

Tough call :D

Karakatiza (Oct 29 2026 at 15:04):

We are getting deep into designing a SurrealDB <insert_your_term_here>. @notramo I really hope you will be trying it out extensively, then

Krzysztof Skowronek (Oct 29 2026 at 15:05):

One thing I will say about orms, being able to just drop in raw SQL is a great way to cover features you are missing at the time

notramo (Oct 29 2026 at 15:05):

We are getting deep into designing a SurrealDB <insert_your_term_here>. notramo I really hope you will be trying it out extensively, then

The reason I want these features is that I don't want to try it in demo snippets and example programs, but by building a real, production app for an actual real-world domain.

Krzysztof Skowronek (Oct 29 2026 at 15:06):

It may be that you will find cases where the orm approach is not good enough

notramo (Oct 29 2026 at 15:10):

The example programs always look great, because they are not tied to a real-world domain, and it's easy to only add behavior to the app that can be expressed easily with the current features. The imaginary domain is designed in a way that fits the features of the library. On the other hand, when real-world concepts need to be modeled in code, we can't say "oh, let's not add this feature because our query builder can't express this" or "let's make this behave differently, because it results in simpler code". The behavior needs to be modeled after the real-world behavior, and that's what actually stress-tests these new ideas properly.

Karakatiza (Oct 29 2026 at 15:15):

I'm glad to know you plan to try it for something concrete

Richard Feldman (Oct 29 2026 at 15:53):

I just want to second that "ORM" is not terminology that makes sense to me in Roc. How do you have an Object-Relational Mapper in a language that does not have Objects? :stuck_out_tongue_wink:

Richard Feldman (Oct 29 2026 at 15:53):

same with DTO

Richard Feldman (Oct 29 2026 at 15:54):

terminology aside, I also have an extremely strongly-held belief that software engineering would have been better if ORMs never existed. I think every single one of them in history, without exception, has been a mistake. I am in general an extremely pro-nuance person, and yet I think ORMs are flat-out bad in the strongest possible terms.

Richard Feldman (Oct 29 2026 at 15:54):

query builders are totally fine though :smile:

Krzysztof Skowronek (Oct 29 2026 at 15:54):

To me orm means you can write a query and get objects from it, anything that does auto decoding is an orm

In dotnet people argue whether if a library does only that, maybe it should be called micro-orm

Richard Feldman (Oct 29 2026 at 15:55):

ORM is a mapping between objects and databases

Richard Feldman (Oct 29 2026 at 15:55):

hence the name :wink:

Karakatiza (Oct 29 2026 at 15:55):

SRM :nerd:

Richard Feldman (Oct 29 2026 at 15:57):

translating from relational rows to objects can be fine, and translating from objects to relational rows can be fine, but the two-way mapping part is where the problems happen

Krzysztof Skowronek (Oct 29 2026 at 15:57):

Yeah, maybe the dapper situation rewrote my definition

Why would you say it doesn't make sense for roc? Roc types/struts could be mapped to tables

notramo (Oct 29 2026 at 15:57):

Most of the queries don't need all of the fields, so it's just wasting RAM and CPU.

Richard Feldman (Oct 29 2026 at 15:58):

deserializing bytes into a Roc value doesn't need a separate name just because it's a database :smile:

Richard Feldman (Oct 29 2026 at 15:58):

a List(U8) coming from a db socket vs a network socket, getting translated from UTF-8 JSON bytes to a record vs serialized postgres bytes to a record...these are not meaningfully different operations, they just have different parsers

notramo (Oct 29 2026 at 15:59):

The best way for a query builder is to use structural types (anonymous structs) that are derived from the query, and only contain the fields for that given query. Roc has excellent support for these.

Richard Feldman (Oct 29 2026 at 15:59):

"query builder" is just assembling the SQL query to send to the database using a compositional builder style instead of string concatenation and/or interpolation

Richard Feldman (Oct 29 2026 at 16:01):

so query builders are obviously fine, that's just a way to compositionally build up a value that will be serialized into bytes and sent to the db

Richard Feldman (Oct 29 2026 at 16:02):

and serializing/deserializing data to/from a database is no different from doing it over the network, which we do all the time and doesn't need its own name

Richard Feldman (Oct 29 2026 at 16:02):

and all the stuff that ORMs do other than those is what I consider to be a mistake - e.g. the stuff "for ergonomics" that results in N+1 queries, which you normally need to go out of your way to write if you're just using ordinary SQL

notramo (Oct 29 2026 at 16:04):

In other words, there should be no way to tell whether a struct came from a database query or some other source. The query result should carry zero database client code or query builder code. It should be just the data requested. This is not true with ORMs, because a query result object is also a query builder.

Krzysztof Skowronek (Oct 29 2026 at 16:19):

Imo od orm let's you design the database structure exactly how you want, and has a query builder that does things well, is no different than just a query builder

Only difference is that your code cam be the absolute source of truth (unless you do change the database schema not through the orm)

Krzysztof Skowronek (Oct 29 2026 at 16:21):

notramo said:

In other words, there should be no way to tell whether a struct came from a database query or some other source. The query result should carry zero database client code or query builder code. It should be just the data requested. This is not true with ORMs, because a query result object is also a query builder.

Why do you think so? That's not axiomatic so to say

Dan G Knutson (Oct 29 2026 at 16:24):

In a dotnet context, would you call something like Dapper an ORM? Sometimes I hear people use "ORM" to mean any library that goes in that architectural slot. When the term was first popularized, it came along with a lot more Rails/Java baggage, though.

Krzysztof Skowronek (Oct 29 2026 at 16:27):

Dapper has a label of "micro-orm" because it does the decoding from database response to pure dotnet objects, and you give it raw sql, and it used to be like 6x faster than EF Core

Now EF core is as fast and can also take raw sql and mix it with the linq queries

Krzysztof Skowronek (Oct 29 2026 at 16:27):

https://sqlkata.com/ this would be a query builder

Krzysztof Skowronek (Oct 29 2026 at 16:30):

But this one doesn't know the schema, and tools like Rider (intellij for C#) can't help here, because you don't really see the string until runtime

Dan G Knutson (Oct 29 2026 at 16:30):

The "ORM" things that I tend not to like are:

it makes extra queries that the programmer didn't necessarily intend
it hides some SQL functionality I want access to
it expects you to use explicitly mutable-state-OO patterns

I don't know for sure, but I'm guessing others here have similar objections/background to me. I'm not familiar with EF Core. But I'm guessing that a Roc library that had at least some of the features you like about it wouldn't count as an "ORM" to many of us.

Krzysztof Skowronek (Oct 29 2026 at 16:32):

That's why I think EF core is amazing, because none of these things are true

notramo (Oct 29 2026 at 16:35):

But we don't have to model this query builder after EF Core.

Krzysztof Skowronek (Oct 29 2026 at 16:35):

It doesn't by default, in the old versions they had so called lazy loading, but no one uses that anymore
It exposes pure SQL in both queries and migrations
You can use change tracking and immutability, but you can also write your updates explicitly

https://learn.microsoft.com/en-us/ef/core/saving/execute-insert-update-delete#executeupdate

Dan G Knutson (Oct 29 2026 at 16:35):

There's also a thing where sometimes people use "object" to mean "non-SQL data structure" and sometimes they use "object" to mean "mutable data structure with app logic methods on it". I think it's useful to have a separate do-nothing type for "this is just a row from the db"

Krzysztof Skowronek (Oct 29 2026 at 16:36):

Yeah, but I object to the notion "all orms are bad", and we can make a good one for roc

Steve Howell (Oct 29 2026 at 16:36):

Krzysztof Skowronek said:

Yeah, but I object to the notion "all orms are bad", and we can make a good one for roc

Pun intended?

Krzysztof Skowronek (Oct 29 2026 at 16:36):

Not really :D

Dan G Knutson (Oct 29 2026 at 16:37):

What I mean is, the newer way to use EF Core you are describing may not be an ORM by Richard's defnition, or by the definition of the article that popularized the pattern.

Krzysztof Skowronek (Oct 29 2026 at 16:37):

Probably that's why I think it's good :)

notramo (Oct 29 2026 at 16:37):

I think the terminology is irrelevant here. Let's focus on SurrealDB in Roc, as we mostly agree how it should work.

Steve Howell (Oct 29 2026 at 16:39):

I also hate ORMs with a passion. I had to work with the Django ORM for about a decade building Zulip (the app we're all using now). It was plagued by everything mentioned above.

For most of its history Zulip had a small portion of its database layer written in SQLAlchemy (more of a query builder), and it was glorious. It has handling by far one of the most difficult problems that Zulip solves (complicated search filters) and just silently worked for about a decade. They finally moved that code to Django as well, for the obvious reason they don't want to support both.

notramo (Oct 29 2026 at 16:43):

I think the terminology is irrelevant here. Let's focus on SurrealDB in Roc, as we mostly agree how it should work.

@Krzysztof Skowronek, I don't really see the point in discussing EF Core in this thread. Unless you have a concrete idea that should be copied from it, discussion about it is mostly pointless. If you have an idea, let's discuss that specific idea and not EF Core in general.

notramo (Oct 29 2026 at 16:44):

The [Loaded(item), Unloaded] idea was discarded, because it's against the design principles of this library.

Krzysztof Skowronek (Oct 29 2026 at 16:46):

await context.Blogs .Where(b => b.Rating < 3) .ExecuteUpdateAsync(setters => setters.SetProperty(b => b.IsVisible, false));

I'll leave you with this, and yes, I'm done :)

Karakatiza (Oct 29 2026 at 16:46):

I am interested in Krzysztof's feedback. I am designing the builder from scratch, trying to make it Roc-idiomatic. But EF Core is relevant peer work.

notramo (Oct 29 2026 at 16:49):

I'm not saying Krzysztof should not contribute to this conversation. But I think promotion of EF Core in general (as opposed to specific ideas) does not meaningfully move the discussion forward.

notramo (Oct 29 2026 at 16:51):

I am interested in Krzysztof's feedback.

I'm also interested.

Karakatiza (Oct 29 2026 at 16:51):

I wouldn't call it promotion. Too bad if Krzysztof is so excited about it, I guess

notramo (Oct 29 2026 at 16:51):

I'll leave you with this, and yes, I'm done :)

@Krzysztof Skowronek, could you port this to Roc syntax? I don't see how it would work in Roc.

Karakatiza (Oct 29 2026 at 16:55):

The b => b.Rating < 3 is the reifying part, if that's what you're asking

notramo (Oct 29 2026 at 16:55):

await context.Blogs
    .Where(b => b.Rating < 3)
    .ExecuteUpdateAsync(setters => setters.SetProperty(b => b.IsVisible, false));

If I understand it correctly, the ExecuteUpdateAsync would involve a database client or a query builder inside the query result object, which we agreed to not have inside the query result struct.

notramo (Oct 29 2026 at 16:58):

Is the Where part doing a query or is it just a fluent builder? If it's just a builder, it's okay, if it's returning an object, it's not the best approach.

notramo (Oct 29 2026 at 17:02):

.ExecuteUpdateAsync(setters => setters.SetProperty(b => b.IsVisible, false));

@Karakatiza, if reification is available, it could be shorter if I understand correctly. Something like this?

.update(|post| post.is_visible = True)

notramo (Oct 29 2026 at 17:04):

Because the original example could be done entirely without reification, with a simple fluent builder.

notramo (Oct 29 2026 at 17:06):

@Krzysztof Skowronek, do you have an idea on how to insert raw SurrealQL for features that are not supported by the query builder?

notramo (Oct 29 2026 at 17:07):

Maybe something like Surreal.raw("…")? But then we lose type checking. The string could contain a syntax error.

Krzysztof Skowronek (Oct 29 2026 at 17:14):

in Roc it would be something like

ontext.Blogs
    .filter(|b| b.rating < 3)
    .execute_update(setters => setters.set_property(|b|  b.is_visible, Bool.False))

and this is a single database trip doing update blogs set isVisible = false where rating < 3

Krzysztof Skowronek (Oct 29 2026 at 17:15):

you get the column names from lambdas |b| b.is_visible with this reification

Krzysztof Skowronek (Oct 29 2026 at 17:15):

everything is fully typed of course

notramo (Oct 29 2026 at 17:15):

What is the purpose of setters? Couldn't we use this?

.update(|post| post.is_visible = True)

Krzysztof Skowronek (Oct 29 2026 at 17:16):

notramo said:

Maybe something like Surreal.raw("…")? But then we lose type checking. The string could contain a syntax error.

not necessarly, if you know the schema you can parse the raw SQL, right?

notramo (Oct 29 2026 at 17:16):

Comptime parsing?

Krzysztof Skowronek (Oct 29 2026 at 17:16):

.update(|post| post.is_visible = True) that's a comparison, not assignment

notramo (Oct 29 2026 at 17:17):

Comparison uses == in Roc.

notramo (Oct 29 2026 at 17:17):

It's confusing.

notramo (Oct 29 2026 at 17:17):

Sorry, I forgot that curly {} is required for assignment, the above syntax is invalid both for assignment and comparison.

notramo (Oct 29 2026 at 17:18):

.update(|post| {
  post.is_visible = True
})

(edit: this is also invalid, because post is immutable)

Krzysztof Skowronek (Oct 29 2026 at 17:18):

so = is not a valid syntax, right?

the purpose of setters is so that reification can parse this and say user want to set b.is_visible to False

Krzysztof Skowronek (Oct 29 2026 at 17:19):

in roc you could say that a better syntax would be to just return an updated struct/record

notramo (Oct 29 2026 at 17:20):

How would that look?

Krzysztof Skowronek (Oct 29 2026 at 17:20):

so blogs.filter(|p| p.id == 5).update(|p| {..p, soft_delete : Bool.True}) or whatever the syntax is

Krzysztof Skowronek (Oct 29 2026 at 17:21):

that code would never run, so you can do whatever you want actually :)

notramo (Oct 29 2026 at 17:23):

But if it never runs, how can I do something like { ..post, active: !post.active }, i.e. using the current value to compute the new?

Krzysztof Skowronek (Oct 29 2026 at 17:23):

what's better, you can do stuff like

blogs.filter(|p| p.id == 5).update(|p| {..p, body: new_body, body_lenght: new_body.len()}) -- all that would happen in the db

Krzysztof Skowronek (Oct 29 2026 at 17:25):

that's the reification - you get a syntax tree that says update got passed a lambda that updates field "active" to a value of "not field acive" then you have the query builder looks at that and outputs update blog set active = not active where id = 5

Krzysztof Skowronek (Oct 29 2026 at 17:25):

that's the magic bit

Krzysztof Skowronek (Oct 29 2026 at 17:28):

(flashbang warning) https://dev.to/turalsuleymani/solid-introduction-to-expression-trees-in-c-1c4i

Krzysztof Skowronek (Oct 29 2026 at 17:29):

btw, in F# (the functional language for dotnet) there is a shorthand lambda syntax, so |x| x.SomeProperty can be written as just _.SomeProperty - quite usefull for cases like this

Krzysztof Skowronek (Oct 29 2026 at 17:29):

btw, the filter(|p| p.id == 5) also NEVER actually runs

notramo (Oct 29 2026 at 17:33):

I still don't understand how it would be possible to include raw SurrealQL for unsupported features, in a type-safe way.

notramo (Oct 29 2026 at 17:35):

If the string is parsed in comptime, it would require a full SurrealQL parser in Roc. In that case, I don't see an advantage of reification over simply writing the entire query as SurrealQL into a Str with comptime parsing, just like roc-pg does.

Krzysztof Skowronek (Oct 29 2026 at 17:35):

you would need to parse the passed string at compile time

if it can't be parsed then, you have add the type annotation to the result

latest_blogs: List(Blog)
latest_blogs = db.execute_raw("you fancy sql")?

# vs

latest_blogs = db.blogs.sort_by_desc(|b| b.creation_time).take(10).bind()
``

notramo (Oct 29 2026 at 17:37):

The advantage of this query builder is that we don't need the comptime SurrealQL parsing.

Krzysztof Skowronek (Oct 29 2026 at 17:38):

then just type the output with annotation or let it get inferred

Krzysztof Skowronek (Oct 29 2026 at 17:38):

and fails just like you would Json.from_str

notramo (Oct 29 2026 at 17:41):

But if comptime SurrealQL parsing is added, why not just write the entire query as SurrealQL into a Str and check it in comptime, just like roc-pg does?

Krzysztof Skowronek (Oct 29 2026 at 17:41):

I think it should be even fine to do things like

latest_blogs: Query(Blog)
latest_blogs = db.query_raw("you fancy sql") # query is lazy in this example

latest_blogs.filter(|b| b.title.len() > 10).bind()

so you would end up with

with latest_blogs as ( your_fancy_sql) select * from latest_blogs where length(title) > 10)

Krzysztof Skowronek (Oct 29 2026 at 17:44):

because like you said then you have to maintain the whole parser up to date

plus, comptime parsing would help you with something more dynamic, like if request.show_archived ... and you have to add a where clause at runtime conditionally

Krzysztof Skowronek (Oct 29 2026 at 17:44):

that's where the query builder comes in

Krzysztof Skowronek (Oct 29 2026 at 17:46):

because you can esily do

$query = db.blogs

if request.hide_archived {
    $query = $query.filter(|b| !b.is_archived)
}

if request.filter = Some(filter) {
     $query = $query.filter(|b| b.title.contains(filter) || b.body.contains(filter))
}

return $query.bind()

notramo (Oct 29 2026 at 17:48):

It's possible with comptime SurrealQL parsing too: write one query as a static Str with no dynamic interpolation or concatenation, and pass hide_archived as an argument, and add the if inside SurrealQL.

Krzysztof Skowronek (Oct 29 2026 at 17:49):

yeah, sure, but that SQL would get HUGE

notramo (Oct 29 2026 at 17:50):

Not that much. SurrealQL is more concise than SQL.

Krzysztof Skowronek (Oct 29 2026 at 17:50):

at previous job we a request with like 100 different parameters :) the function to build the query was couple hundred lines

Krzysztof Skowronek (Oct 29 2026 at 17:51):

oh, and there is one big advantage of the reification - when you use the LSP "find all references" you automatically get the queries

Krzysztof Skowronek (Oct 29 2026 at 17:52):

and then having a full ORM comes with an advantage of when you rename stuff, everything still works

Krzysztof Skowronek (Oct 29 2026 at 17:53):

you can apply migration and rename the database side, or just add a config that property new_name is actually backed by a column old_name

Krzysztof Skowronek (Oct 29 2026 at 17:54):

I don't know whether roc decoders support mapping to different names

Krzysztof Skowronek (Oct 29 2026 at 18:05):

can the roc comptime parsing handle concatenating of static strings?

archive_filter = "(is_archived = false)"

get_blogs = "select id, body from blogs where " + archive_filter
get_comments = "select author, body from comments where blog_id = :blog_id and ${archive_filter}"

would this tell me that comments don't have the is_archived column for example?

Richard Feldman (Oct 29 2026 at 18:06):

Krzysztof Skowronek said:

and then having a full ORM comes with an advantage of when you rename stuff, everything still works

this is equally true of query builders

Richard Feldman (Oct 29 2026 at 18:07):

the thing that's preventing breakage is having a single string literal somewhere for the column name (or whatever) that is reused everywhere

Richard Feldman (Oct 29 2026 at 18:07):

there are lots of ways to do that which have nothing to do with ORMs

Krzysztof Skowronek (Oct 29 2026 at 18:08):

and maybe one more "cool" thing that isn't maybe necessary mandatory, EF Core has global filters. You can add a middleware to http request that gets the tenant id and adds .filter(|blog| blog.tenant_id = tenant_id) to all queries.

you can of course do blogs.IgnoreGlobalFilters(). on some specific queries

Krzysztof Skowronek (Oct 29 2026 at 18:09):

Richard Feldman said:

and then having a full ORM comes with an advantage of when you rename stuff, everything still works

this is equally true of query builders

that's only true if the builder is schema aware, and you have either use something like nameof(Blog.body) or this whole reification system

otherwise it's just strings

Richard Feldman (Oct 29 2026 at 18:10):

yes of course

Richard Feldman (Oct 29 2026 at 18:10):

and likewise it's only true of ORMs if they are schema aware

Richard Feldman (Oct 29 2026 at 18:11):

the feature that makes renaming work is "you know the column's name and you reuse a constant for that everywhere instead of using string literals"

Krzysztof Skowronek (Oct 29 2026 at 18:11):

so if you can model the tables with objects, is that orm or just a query builder? :)

Richard Feldman (Oct 29 2026 at 18:11):

well you can't model a table with objects in Roc, since Roc doesn't have objects

Krzysztof Skowronek (Oct 29 2026 at 18:11):

sure, struct/records

Richard Feldman (Oct 29 2026 at 18:12):

Richard Feldman said in #ideas > SurrealDB query builder based on reifying:

"query builder" is just assembling the SQL query to send to the database using a compositional builder style instead of string concatenation and/or interpolation
so query builders are obviously fine, that's just a way to compositionally build up a value that will be serialized into bytes and sent to the db
and serializing/deserializing data to/from a database is no different from doing it over the network, which we do all the time and doesn't need its own name
and all the stuff that ORMs do other than those is what I consider to be a mistake - e.g. the stuff "for ergonomics" that results in N+1 queries, which you normally need to go out of your way to write if you're just using ordinary SQL

Richard Feldman (Oct 29 2026 at 18:12):

getting the db schema is no different from getting a json schema

Richard Feldman (Oct 29 2026 at 18:12):

schema just means "I know what the shapes are"

Richard Feldman (Oct 29 2026 at 18:12):

nothing to do with ORMs

Krzysztof Skowronek (Oct 29 2026 at 18:14):

yeah, but I can imagine

Database := {
    blogs: List(Blog)
    users: List(User)
}.{ ...

notramo (Oct 29 2026 at 18:14):

and maybe one more "cool" thing that isn't maybe necessary mandatory, EF Core has global filters. You can add a middleware to http request that gets the tenant id and adds .filter(|blog| blog.tenant_id = tenant_id) to all queries.

This is better done with computeds and row level security.

notramo (Oct 29 2026 at 18:14):

SurrealDB has built-in auth support.

Krzysztof Skowronek (Oct 29 2026 at 18:15):

the tenant was just an example, another common use case is to apply filter(|x| !x.soft_deleted)

notramo (Oct 29 2026 at 18:16):

Soft deletion filtering is definitely not something I want to hide in a middleware.

Krzysztof Skowronek (Oct 29 2026 at 18:16):

just an example :)

Richard Feldman (Oct 29 2026 at 18:19):

comparing this:

.filter(|user| user.email = email)

...to something like this, in a normal query builder style that requires no new language features:

.where_equals(users.email, email)

a major thing I dislike about the first design is that I look at it and think "this is a lambda on a filter, I know what the performance characteristics of that are: I'm running this predicate lambda once per item in question" when actually that is syntax sugar for a WHERE clause whose performance considerations will have absolutely nothing to do with the performance considerations of filter

Richard Feldman (Oct 29 2026 at 18:21):

the more this language feature gets explored, the less compelling it seems to me :sweat_smile:

Krzysztof Skowronek (Oct 29 2026 at 18:21):

yeah, but you are asuming that you are not aware the first filter is used for a database query building

notramo (Oct 29 2026 at 18:22):

The lambda syntax would allow for filtering that are not simple equality comparisons, but can use the values of the field on both sides.

Krzysztof Skowronek (Oct 29 2026 at 18:22):

Richard Feldman said:

.where_equals(users.email, email)

how do you get the users.email from this without reification or nameof? or is this user.email = "email"?

Richard Feldman (Oct 29 2026 at 18:23):

even if I am, suppose I change it to something like this:

.filter(|user| user.email.matches(|email| validate_email(email.trim())))

Richard Feldman (Oct 29 2026 at 18:23):

like in a normal lambda I'm like cool, that should work just fine

Richard Feldman (Oct 29 2026 at 18:23):

does that even work in SQL? does validate_email get translated to inline SQL? what if it's using Roc builtin operations that SQL doesn't support, like vector operations?

notramo (Oct 29 2026 at 18:24):

I think raw SurrealQL even beats this option too.

Krzysztof Skowronek (Oct 29 2026 at 18:24):

depends :)

it doesn't work in c#, but in roc if validate_email is pure, it could work

notramo (Oct 29 2026 at 18:24):

But I want to try this reification-based query builder in practice. There's a prototype, so why not?

Richard Feldman (Oct 29 2026 at 18:24):

Krzysztof Skowronek said:

Richard Feldman said:

.where_equals(users.email, email)

how do you get the users.email from this without reification or nameof? or is this user.email = "email"?

presumably the type of users.email (note users, not user) would be Column or something similar

Krzysztof Skowronek (Oct 29 2026 at 18:24):

either way, you would get copmile time error this function cannot be translated to SQL

Richard Feldman (Oct 29 2026 at 18:26):

ok so it looks kinda neat, but it's not actually as powerful as it looks, and if you try to write a normal Roc lambda in it, you will maybe get something with wildly different perf characteristics than what you expect, and alternatively you might get a compile error that you'd normally never get because you're just calling functions and doing something that looks like it should work in the language?

Richard Feldman (Oct 29 2026 at 18:26):

this is just the exact opposite of what I think good programming is

Richard Feldman (Oct 29 2026 at 18:27):

this discussion is giving me a stronger and stronger view that we should not have this feature in the language

Richard Feldman (Oct 29 2026 at 18:27):

if this library existed, I would recommend against using it

Richard Feldman (Oct 29 2026 at 18:28):

I mean it's all open source, feel free to experiment, but I want to be really clear about what the odds are that Roc will ever support it: right now they are extremely close to zero

Krzysztof Skowronek (Oct 29 2026 at 18:28):

so then then say

blogs = builder.from(blogs.table).filter(blogs.likes, Compare.gt(5)).select([blogs.id, blogs.title, blogs.body)).load()

blogs.map(|{id, title, body}) { ... })

the body in the builder is not the same body as in the map, so those are not the same usages, renaming doesn't work

Richard Feldman (Oct 29 2026 at 18:29):

"looks cool on the surface and turns out to be full of unpleasant surprises" has plagued programming for the past 25 years and I feel very strongly about fighting it, not contributing to it

Krzysztof Skowronek (Oct 29 2026 at 18:29):

I am using this daily and it's more than fine, especially with the escape hatch for raw sql

Richard Feldman (Oct 29 2026 at 18:30):

and you're free to do that! I'm just trying to be super clear about where I stand on it. :smile:

notramo (Oct 29 2026 at 18:31):

I am using this daily and it's more than fine, especially with the escape hatch for raw sql

Some people even say this about PHP!

Richard Feldman (Oct 29 2026 at 18:31):

Krzysztof Skowronek said:

so then then say
blogs = builder.from(blogs.table).filter(blogs.likes, Compare.gt(5)).select([blogs.id, blogs.title, blogs.body)).load()

blogs.map(|{id, title, body}) { ... })
the body in the builder is not the same body as in the map, so those are not the same usages, renaming doesn't work

again though, this has nothing to do with ORMs - you can define a schema that maps one string literal to a different record field, in which case you only have to change that one string literal and it's fine if map is using a record with a different field name

Krzysztof Skowronek (Oct 29 2026 at 18:31):

hell, LSP could give the editor a diagnostic to set differnt colors for the code that mapped

Krzysztof Skowronek (Oct 29 2026 at 18:32):

yeah, but I do "find all references" I will not see the queries, especially if everything becomes an anonymous record with structured typing

Richard Feldman (Oct 29 2026 at 18:32):

separately, if you want to have the record field name be inferred from the string in the schema, you can do that, and might have to more renames in more places, but you'd get compile errors for all the mismatches and the total number of renames would be equal to the number of different records (with the same field name) that referred to the same db name

notramo (Oct 29 2026 at 18:34):

I think if we start using the prototype for real-world apps (not demo examples), a whole bunch of other issues will come up.

notramo (Oct 29 2026 at 18:35):

If I find issues, that will be a stronger argument against the feature.

notramo (Oct 29 2026 at 18:36):

On the other hand, if everything goes smoothly, at least I'll have a working Roc app that uses SurrealDB.

Karakatiza (Oct 29 2026 at 19:41):

I am encountering and seem to solve cleanly much more complex issues, so chugging along with now two more planned major reifier improvements, which will improve the query builder experiment.

I don't expect to change Richard's opinion now, rather stating my opinion: saying the perf characteristics now become unexpected in a reified lamda is not realistic, I think. You glance over the code when you need the semantics. When you wonder where could be performance issues, without profiling first, you pay attention to details like what is the execution context of some pure code - is it Roc compiled binary, or is it database filter expression.

The code does not need a 100-word essay prefacing every change of execution context - if you are trying to debug logic or performance you should know what you are working with, and whether you are using a query builder that translates your code to DB commands, without red flag emojis highlighting the code.

Reifying in its current design is implicit in that it doesn't involve extra ceremony like CODE_HERE_MAY_HAVE_DIFFFERENT_PERFORMANCE_CHARACTERISTICS(), but it is very much explicit and strict about what happens where.

I don't find the argument that can be reduced to "I don't know what + means anymore, I'd rather use .safe_sql_plus()" compelling. I think if you don't pay attention the latter won't save you either. I mean, it's not about the naked operator use, of course.

Karakatiza (Oct 29 2026 at 19:42):

Anyway, it's a good thing rebasing is cheap now ^_^

Karakatiza (Oct 29 2026 at 19:49):

For now I am more worried about not being able to solve some type checker and other implementation challanges, which leave reifying a bit limited and making its utility questionable

Karakatiza (Oct 30 2026 at 19:22):

@notramo do you want to use cli or webserver for your app? It looks like the latter's Tcp API is not sufficient for a long-term websocket connection for live queries.

notramo (Oct 30 2026 at 19:22):

Doesn't basic-webserver have a websocket?

Karakatiza (Oct 30 2026 at 19:24):

No, unless I missed something

Karakatiza (Oct 30 2026 at 19:28):

Do you want it serverless by any chance?..

notramo (Oct 30 2026 at 19:38):

If it's simpler. But I prefer to run servers on VPS, it's a lot cheaper than cloud.

notramo (Oct 31 2026 at 15:17):

What's the problem with Tcp?

Karakatiza (Oct 31 2026 at 15:46):

The issue is with basic-webserver's design.
Claude:

Every read gives up after 30 seconds. The limit is hard-coded (src/tcp.rs:22). On a quiet live query, the read fails with TimedOut and the app has to reconnect. Bytes from a partly finished read may be lost; the subagent inferred this rather than verified it.
Waiting for data ties up a worker. A read holds one of the 32 Roc worker slots until bytes arrive (src/tcp.rs:357-386, Sleep.roc:3-6). Waiting for pushed messages inside a request handler uses up a slot for as long as it waits.
Nothing runs unless a request comes in. The app's only entry points are init!, respond! and shutdown! (main.roc:11-15). No background loop can keep reading the socket between requests.
Only one user of the connection at a time. A second read or write on the same stream returns StreamBusy (src/tcp.rs:362-380). A handler parked on a read would block every other handler that wants to send an RPC.
App state cannot change. Context is read-only startup data (design.md:144). There is nowhere to keep half-received frames, the table of pending request ids, or the state of live subscriptions.

Karakatiza (Oct 31 2026 at 15:48):

I work on a cloudflare workers platform that supports websocket, but you need to want to design your app around the workers' limitations

notramo (Oct 31 2026 at 15:49):

@Luke Boswell, would it be somehow possible to work around this using basic-webserver?

Karakatiza (Oct 31 2026 at 15:52):

websocket is a popular protocol. I was thinking of coming up with a roc-websocket package, which would define a public API that apps and packages can rely on, and various platfroms could add support for it, e.g. each wrapping own Tcp implementation to get to that same API.
Sans the "platform extensions" this feels like the way to go

notramo (Oct 31 2026 at 15:53):

Yes, a pure Roc websocket/HTTP implementation would allow it to be used with any platform that can provide a TCP socket.

Karakatiza (Oct 31 2026 at 16:13):

I mean, that would require the same Tcp API; I assume platforms may want to expose different Tcp API and capabilities, but they could agree in a WebSocket API. This is not an informed opinion, rather a conservative "lowest common denominator" approach

notramo (Nov 02 2026 at 19:25):

@Karakatiza, what is the status of this project?

Karakatiza (Nov 02 2026 at 19:27):

Haha :smile: I was actually blocked waiting for basic-webserver 0.20.0. I hope I can get you something to play with in a few hours - the SurrealDB package on basic-webserver with adapter for my roc-websocket, all on nightly 11-01 reify fork

Karakatiza (Nov 02 2026 at 19:28):

You ruined the surprise smh

notramo (Nov 02 2026 at 19:30):

Don't rush, it's not urgent. Better do it properly than some quick hack.

Karakatiza (Nov 02 2026 at 19:32):

There are no hacks, but often you just need to release something rather than dwell on it

Karakatiza (Nov 02 2026 at 19:33):

And I need third party feedback

Karakatiza (Nov 02 2026 at 22:50):

Ok, won't happen today

notramo (Nov 02 2026 at 22:50):

No problem. Take your time.

Karakatiza (Nov 04 2026 at 01:03):

upstream deleted the old Boxy backend (--specialize=no) and wrote a new one

Stay tuned

Richard Feldman (Nov 04 2026 at 01:24):

oh yeah, I scratch-rewrote boxy over the past few days :smile:

Richard Feldman (Nov 04 2026 at 01:24):

it's way faster now!

Karakatiza (Nov 04 2026 at 17:37):

@notramo surreal: "https://github.com/Karakatiza666/roc-reify-surrealdb/releases/download/0.0.1/7MmdAaAt5bThg6ih3nR9ewVgw4CtNGvuK5HNQhEgZoSV.tar.zst" is released. Build on it using the reify branch of my Roc fork, rebased on nightly 11-03.

See examples/server.roc for a simple app based on basic-webserver platform and WebSocket connection. The platform currently limits the websocket connection to 30 seconds, so you may choose HTTP transport instead - WebSocket is only needed for live queries.

See other examples and docs for more syntax.

notramo (Nov 04 2026 at 17:39):

Thanks! I'll try it out tomorrow.

Karakatiza (Nov 04 2026 at 18:22):

For LLMs you can use roc-syntax-mcp with @roc-syntax/surrealdb plugin. For features that use the compiler you need to point it to the fork binary

Last updated: Nov 04 2026 at 18:54 UTC