whoami•2y ago

Best practice for modeling many to many, one to many relationships as in relational database

I am looking for examples/demos on modeling such relationships and queries at ease, but didn't find much mentioning those in the doc. I am looking for prisma like API to assist me queries like get me the most recent posts along with their user's email address, if there are anything sample demo project link you can provide me with, I am more than appreciated.

9 Replies

lee•2y ago

i'm not sure if we have a fleshed out example app (the closest would be https://github.com/get-convex/convex-demos/tree/main/relational-data-modeling ) but i can help with your example

GitHub

convex-demos/relational-data-modeling at main · get-convex/convex-d...

Demo apps built on Convex. Contribute to get-convex/convex-demos development by creating an account on GitHub.

lee•2y ago

const recentPosts = await db.query('posts').order('desc').take(10); // queries by default are sorted by _creationTime, so order('desc') puts recent posts first and take(10) returns the most recent 10.

// this loop may look inefficient, but note convex runs functions close to the database, and has caching, so this is approximately as efficient as a sql join, with a lot more flexibility.
for (const post of recentPosts) {
  // you can use a Promise.all to run them in parallel too, if you want.
  const user = await db.get(post.user);
  post['user_email'] = user.email;
}
return recentPosts;

const recentPosts = await db.query('posts').order('desc').take(10); // queries by default are sorted by _creationTime, so order('desc') puts recent posts first and take(10) returns the most recent 10.

// this loop may look inefficient, but note convex runs functions close to the database, and has caching, so this is approximately as efficient as a sql join, with a lot more flexibility.
for (const post of recentPosts) {
  // you can use a Promise.all to run them in parallel too, if you want.
  const user = await db.get(post.user);
  post['user_email'] = user.email;
}
return recentPosts;

it looks long with comments. without comments it's

const recentPosts = await db.query('posts').order('desc').take(10);
for (const post of recentPosts) {
  const user = await db.get(post.user);
  post['user_email'] = user.email;
}
return recentPosts;

const recentPosts = await db.query('posts').order('desc').take(10);
for (const post of recentPosts) {
  const user = await db.get(post.user);
  post['user_email'] = user.email;
}
return recentPosts;

whoamiOP•2y ago

oh nice, this is a change in mental model for me indeed (I am more familiar with SQL/prisma like operations which gives me more sense of security, and many typescript programmers prefer a more functional way to construct their program), but I like its flexibility! I think you should have this example in the documentation so people understand their options dealing with relation modeling. I assume it works with many to many relationship in the same way right? I am also wondering has convex considered implementing a managed GraphQL API for the ease of use for developers? Think of it as an syntactic sugar and it won't conflict with convex design for flexibility as well

jamwt•2y ago

yep, we have. at various times we've had a graphql layer and a sql layer (query/select, specifically) on top of convex as prototypes. we haven't made any concrete decisions to ship anything yet, however

lee•2y ago

re: declarative vs imperative queries, you may be interested in https://stack.convex.dev/not-sql which discusses the tradeoffs that we're considering 😄

It's not you, it's SQL

SQL has been a part of computing for a very long time, and SQL-based database systems underly most of the world's applications. But recently, develope...

ian•2y ago

A more functional-looking version of it that does lookups in parallel is:

const recentPosts = await db.query('posts').order('desc').take(10);
return await Promise.all(recentPosts.map(aync (post) => {
  const user = await db.get(post.user);
  return {...post, user_email: user.email};
});

const recentPosts = await db.query('posts').order('desc').take(10);
return await Promise.all(recentPosts.map(aync (post) => {
  const user = await db.get(post.user);
  return {...post, user_email: user.email};
});

I've been meaning to write some cookbooks on joins. One post that talks about this written earlier this year is https://stack.convex.dev/upvoting-4-ways-on-convex It talks about one-many, many-many, etc

Implementing Upvoting 4 Ways on Convex

Implementing an "upvote" feature 4 ways with Convex.

whoamiOP•2y ago

Awesome will take a look!

ian•2y ago

Here’s a post I haven’t listed publicly yet, but hopefully helps. Would love your thoughts: https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas

Relationship Structures: Let's Talk About Schemas

In this post we’ll look at some patterns for structuring relationships in the Convex database.

whoamiOP•2y ago

awesome

Best practice for modeling many to many, one to many relationships as in relational database

Did you find this page helpful?