SQL Layer

It would also feel great to read a guide titled "convex for sql nerds !" - to build confidence in doing the transition.

32 Replies

jamwt•9mo ago

Hey -- a lot like https://labs.convex.dev/convex-ents (which is a different, but also succint/expressive dialog for e.g. joins and conditionals and things), it's very possible to build a simple SQL layer on top of convex's primitives.

Convex Ents - Convex Ents

Relations, default values, unique fields and more for Convex

jamwt•9mo ago

we've even talked about how one would approach it we probably don't have time to get to it anytime soon, but it's something that could be built by anyone -- doesn't require working at convex

Chakib OuhajjouOP•9mo ago

cool !

jamwt•9mo ago

convex-ents hides the Promise.all from you in the manner you're referring to

Chakib OuhajjouOP•9mo ago

Amazing, hear me I dont want to be jealous of EdgeDB DX 😉 I will build a lot a lot of queries. ERP kind of app. Amazing link thanks @jamwt

lee•9mo ago

If you don't want to go full Ents, we also have https://stack.convex.dev/functional-relationships-helpers

Stack

Database Relationship Helpers

Traverse database relationships in a readable, predictable, and debuggable way. Support for one-to-one, one-to-many, and many-to-many via utility func...

lee•9mo ago

But Ents is very cool 😎

Chakib OuhajjouOP•9mo ago

I don't know how do you feel about it, but the query api is extremely important DX wise. I love that the runtime is amazing and you can do many thinks. But settling on the official API for querying is key. Designing from the outside->in vs inside->out. I just feel that convex can look better and it can !

jamwt•9mo ago

I think it's important to note that the existing convex api is what you might call "LL DB" -- a langugae of primitves that essentially represents how databases really work I do think a lot of projects will want to remove boilerplate for e.g. joins but it doesn't require much to clean up the repetitive patterns

Chakib OuhajjouOP•9mo ago

Haha, the solidjs philosophy ! risky but nice adventure !

jamwt•8mo ago

if you need full SQL (subqueries, sophisticated joins, window functions, aggregates...) you're really doing too much in an OLTP path and our goal is to help developers not write programs that won't work/scale in real projects unfortuantely, SQL is the #1 reason why that happens in most full stack projects so yeah, I think finding the right abstractions that clean up a very few common things like one-hop left joins and possibly cascading deletes is about all you need and what most big companies end up using in practice ents is a good start on creating something that tidies that up for folks who are on the DRY warpath

Chakib OuhajjouOP•8mo ago

Again that article is great ! I am just wondering if we are not missing an opportunity to make a little better. (js is not helping - no pre-emptive scheduling). Maybe i want to "Not think about time (sync/async) when I am doing data queries" I will play with ents !!

jamwt•8mo ago

yeah, I get that -- but you sorta want a coroutine runtime I think unfortunately we can't really accomplish that in the JS/TS world -- even the ergonomic layers on the stuff we have, if they roll up all the Promise.all in the world, will still result in a final async that will have to be awaited in JS but there's a vision for a convex runtime 2.0 that will support more general languages powered by like a WASM runtime (instead of raw v8) that can open up the ability to have even more expressive APIs -- including leveraging languages environments with a different way of handling asynchronous I/O than JS. in practice this is probably at least 2 years away! we still have to "land the plane" on the existing platform before we can even think about the next one your edgeql idea seems cool, and yeah, I think you and michal would agree on a lot of things re: your sketch here and what he's done in ents -- check it out!

jamwt•8mo ago

also, he did make a nice comparison of the concision of a few different approaches: https://labs.convex.dev/convex-vs-prisma

Prisma vs Convex

Compare Prisma examples with Convex

jamwt•8mo ago

SQL isn't one of the examples, but we perhaps should make it one

jamwt•8mo ago

ents vs. "vanilla" convex: https://labs.convex.dev/convex-vs-prisma?left=convexEnts

Prisma vs Convex

Compare Prisma examples with Convex

Chakib OuhajjouOP•8mo ago

await everywhere, interesting:

// Return all users and include their posts and profile
const users = await Promise.all(
  (await ctx.db.query("users").collect()).map(async (user) => ({
    ...user,
    posts: await ctx.db
      .query("posts")
      .withIndex("authorId", (q) => q.eq("authorId", user._id))
      .collect(),
    profile: await ctx.db
      .query("profiles")
      .withIndex("userId", (q) => q.eq("userId", user._id))
      .unique(),
  })),
);

// Return all users and include their posts and profile
const users = await Promise.all(
  (await ctx.db.query("users").collect()).map(async (user) => ({
    ...user,
    posts: await ctx.db
      .query("posts")
      .withIndex("authorId", (q) => q.eq("authorId", user._id))
      .collect(),
    profile: await ctx.db
      .query("profiles")
      .withIndex("userId", (q) => q.eq("userId", user._id))
      .unique(),
  })),
);

all those queries are awaited. so why not x x x x x x to make it disappear. it's await by default ! How to fill the blank here ? @jamwt don't we just a need .run() at the end ? I think the idea is go less imperative and more declarative, declare a query and run it at the end

jamwt•8mo ago

convex query functions often manipulate the data inside the JS function pretty heavily convex query functions are repacing your entire "controller", not just your database queries

Chakib OuhajjouOP•8mo ago

Wondering if we can say a query is a data structure first, then functions. function resolve the attributes. the query is a shape. kindof idea.

jamwt•8mo ago

I think you could easily build a layer that does this on our primitives for sure, if it's the way you like the model data. unfortunately for convex the platform, we can't make assumptions quite this opinionated b/c we need the platform to be able to support any dataflow paradigm for any team and any project and that's why we're advocating building these things as libraries on top of the current primitives rather than, say, replacing these primitives

Chakib OuhajjouOP•8mo ago

what you are saying here is groundbreaking !

jamwt•8mo ago

the higher-layer you go, the more I strongly believe there is not one "right answer", but there are a lot of "good answers", for a particular team, particular project, particular methodology technical leadership likes for modeling data we want all those good answers to be possible for sure even we are starting to "take out" some stuff out of the foundational layer in convex and build more "in userspace" moving forward. we'd probably take some things back now if we could the components project is a way forward to formalize this way of building more and "democratize" who gets to build really good abstarctions in convex -- ideally it should be anyone in the world, not just employees of convex

Chakib OuhajjouOP•8mo ago

can I have your take on something just to validate/invalidate my intuition ? While I think we I can stay very close to 'official' way. do you think would look very close is we do .run() at the end. In ecto (phoenix framework). we do this>

# Imports only from/2 of Ecto.Query
import Ecto.Query, only: [from: 2]

# Create a query
query = from u in "users",
          where: u.age > 18,
          select: u.name

# Send the query to the repository
Repo.all(query)

# Imports only from/2 of Ecto.Query
import Ecto.Query, only: [from: 2]

# Create a query
query = from u in "users",
          where: u.age > 18,
          select: u.name

# Send the query to the repository
Repo.all(query)

jamwt•8mo ago

aka I don't want to break "confect" for @RJ 😄

Chakib OuhajjouOP•8mo ago

I guess I should give it a try. it's kindof similar to what vue has to go through Options vs Composition api. I guess the triumph for me would be "convex primitives without async/wait." I like the title. research project 🙂 thanks @jamwt , this is amazing ! Great design / benchmark too ! would love to see EdgeDB here 😉 I am digging deeper. great pointers ! I am experimenting with query DX, i was able to write the 'list' query from the tutorial like this:

export const list2 = query({
  args: {},
  handler: (ctx) => {
    // Grab the most recent messages.
    return ctx.db
      .query("messages")
      .order("desc")
      .take(100)
      .then((messages) =>
        Promise.all(
          messages.map((message) =>
            ctx.db
              .query("likes")
              .withIndex("byMessageId", (q) => q.eq("messageId", message._id))
              .collect()
              .then((likes) => ({
                ...message,
                likes: likes.length,
              }))
          )
        )
      )
      .then((messages) =>
        messages.reverse().map((message) => ({
          ...message,
          body: message.body.replaceAll(":)", "😊"),
        }))
      );
  },
});

export const list2 = query({
  args: {},
  handler: (ctx) => {
    // Grab the most recent messages.
    return ctx.db
      .query("messages")
      .order("desc")
      .take(100)
      .then((messages) =>
        Promise.all(
          messages.map((message) =>
            ctx.db
              .query("likes")
              .withIndex("byMessageId", (q) => q.eq("messageId", message._id))
              .collect()
              .then((likes) => ({
                ...message,
                likes: likes.length,
              }))
          )
        )
      )
      .then((messages) =>
        messages.reverse().map((message) => ({
          ...message,
          body: message.body.replaceAll(":)", "😊"),
        }))
      );
  },
});

it needs a bit more love in this part:

 .then((messages) =>
        Promise.all(
          messages.map((message) =>
            ctx.db
              .query("likes")

 .then((messages) =>
        Promise.all(
          messages.map((message) =>
            ctx.db
              .query("likes")

there is clearly something to do here to navigate relations with less friction (without using more powerfull hammers like Effect, that's another discussion 😉 ) I spent some times looking at Effect and Ents and. And I feel these libs take me too far away from the convex primitives. I need @RJ help here, I am a freshly 😝 coming from Elixir

RJ•8mo ago

Nothing wrong with that code, I don't think @Chakib Ouhajjou! But if you want a terser way do these sorts of joins, I do think Ents is the way to go

Chakib OuhajjouOP•8mo ago

I'll rather have convex primitives being extensible instead of having to reach out to another library just because something is missing. It's a quality that I think is reasonable to expect since it's js and not some external dsl. So it should be easy to build conventions/guides on how to extend it. Imagine you starting using a library and the moment you need something simple missing you have to switch to another library. it doesn't make sense to me in 2024. I'll rather have the official library cover most common cases and be extensible. We can get inspiration from https://vueuse.org/ - Vue Composition utilities. A "plugin architecture" of some sort, maybe ?

lee•8mo ago

I would say the convex primitives are extensible, and the way you extend primitives is with libraries. As for "plugin architecture" that also sounds like changing the syntax via plugins like Ents. I agree Ents can be heavyweight, so in this scenario, to remove the Promise.all(messages.map(...)) I would use asyncMap from https://stack.convex.dev/functional-relationships-helpers . We also welcome third party libraries that help make patterns easier to use, like Confect (and Ents 🙂 )

Stack

Database Relationship Helpers

Traverse database relationships in a readable, predictable, and debuggable way. Support for one-to-one, one-to-many, and many-to-many via utility func...

Chakib OuhajjouOP•8mo ago

there are different ways to manage the community effort building on top of convex: - 1. the react way: here is the minimal lib, do whatever you want -> you endup with 20 libs doing the same thing. - 2. the vue way: style guides, opinniated way on how to extend things. (architecture of extensibility) All I am hearing so far here is that you encourage 1. This will lead to libs that are not cohesive, no leadership. I do prefere cohesive ecosystems you loose freedom but you get guidance, and the community as whole gets more productive. It's a strategic choice of course for convex. I am just suggesting here to go the all in on an opinniated approach, it creates better DX. I am being pushed to CHOOSE from one of the 3 ways to do a query. These 3 ways are MUTUALLY exclusive in a team setup.

lee•8mo ago

yep that totally makes sense. We've been trying to go with a core set of primitives that are extensible but without pushing devs to use any of them. We've considered adding joins to the base syntax, to make it more obvious they're supported. Because they are supported, at three layers of abstraction: either you write 3 lines of JavaScript with a Promise.all -- thinking "i am writing code", or 2 lines with an asyncMap -- thinking "i am writing a join", or 1 line if you convert your project to Ents -- thinking "i am traversing a graph". But because our story around joins is so disjointed (heh), devs get put off.

Chakib OuhajjouOP•8mo ago

It's performance vs ergonomics So 2 ways will do. I would say design wise going against joins ergonomic is a tough sell. (From design perspective - reference: design of every day things book) I want to say, technically the platform is amazing. But when you see drizzle and convex. I think I can have both DXs in one cohesive package. Other design pattern that can inspire the SQL layer design is JSX and ReScript. A little compile step that shows the result. To conclude SQL is here to stay. We just have to create a nicer narrative around it. It looks like a low hanging fruit for the convex team. That's my intuition. The narrative that I am proposing: "Use Convex Query Language, or js with primitives to have full control" Start with ergonomics, we write code for humans. Optimize with primitives when you have to. I used to do C and assembly language for video game consoles. Great cohesive DX. I thinks we should write a few different proposals and see what sticks with the community. I see 3 approaches: - primitives + query builder (like drizzle or ecto from elixir) - primitives + nicely compiled query language (like ReScript) - core primitives + community primitives (like vue) And we can start with the usage documentation first. No implementation. My understanding is that @jamwt is pushing for "core primitives + community primitives" So do guys encourage the community to build ctx.db.myPrimitive() and putting the in a global catalogue with documentation. (All libraries in elixir host the docs in hexdocs.pm) Elixir is the best in this regard, then vue, react is extreme liberalism.

Chakib OuhajjouOP•8mo ago

Both Elixir and Vue have a documentation tool 🔥 Better UX for the reader. I think we should avoid having convex API docs and Ents docs using different documentation tools. Please check hexdocs and https://content.nuxt.com/ for inspiration.

Nuxt Content

Nuxt Content made easy for Vue Developers

Nuxt Content reads the content/ directory in your project, parses .md, .yml, .csv and .json files to create a powerful data layer for your application. Use Vue components in Markdown with the MDC syntax.

SQL Layer

Did you find this page helpful?