Shape support for include trees

[dexion]

Hi! Your project is really interesting!

I have a question. Let's take a schema like this:

project has uniq id:UUID
task -> belongs to project by project_id
issue -> belongs to task by task_id
comment -> belongs to issue by issue_id

Table projects_users for m2m link:

project_id: uuid
user_id: uuid

Now user has a lot of accessible comments through: project -> tasks -> issues
In a regular SQL we can retrieve comments from DB like this:

select * from comments
	JOIN projects_users
	JOIN project on ...
	JOIN task ...
	JOIN issues ...
	JOIN comment on ....
where project_users.user_id == current_user.id

How can we limit electric's shapes of comments to be only accessible to user from auth-token when we call /v1/shape/comments?offset=-1? In this example, we shouldn't get all diffs, only diffs for comments linked to one project.

I would like to see an advanced example of usage in your docs. Maybe you can extend your example with projects-issues-comments?

[KyleAMathews]

Hey check out the auth example to see how to do this.

https://github.com/electric-sql/electric/tree/main/examples/auth

The auth doc page covers similar territory
https://next.electric-sql.com/guides/authentication-and-authorization

We only support single table shapes atm so you'll need to sync each table and do joins client-side.

[thruflo]

I've updated the title and set to a feature request because I think this question is really asking if Shapes support include trees. As per the previous shape docs/implementation for old Electric Shapes were designed and envisaged as having a:

• a root table
• a query, with where clauses used to filter the rows in that table
• an include tree, a directed acyclic graph of related data

With a code snippet example looking like:

await db.projects.sync({
  where: {
    id: 'abcd'
  },
  include: {
    issues: {
      include: {
        comments: {
          include: {
            author: true
          }
        }
      }
    }
  }
})

This was modelled on Prisma relation queries and more generally on ORM queries with an association tree, such as Ecto preloads:

Repo.all from p in Project,
           join: t in assoc(p, :tasks),
           join: i in assoc(t, :issues),
           join: c in assoc(i, :comments),
           preload: [tasks: {t, issues: {i, comments: {c, }}]

A shape with an include tree allows you to do things like sync a project with all its nested tasks, issues and comments. This is exactly what the question above is asking how to achieve.

[dexion]

a query, with where clauses used to filter the rows in that table

Not quite so. WHERE can contain conditions for any of the joined tables. We would like to select from comments, but our constraints are on project_users.user_id == current_user.id and comments don't have any direct link to current_user, only through nested\parent tables.

[thruflo]

Right, I follow. You're also looking to use the auth context in a where clause / include tree filter.

Sorry, hope you don't mind me co-opting this into an include tree feature request; I just wanted to make sure we had a discussion for this and thought your post was close enough.

The auth context is something we can but don't yet have available to where clauses executed in Electric. It's assumed in the discussion on #1587 and may be something we can add more quickly.

[PorcoRosso85]

+1

If I am not mistaken, I think @dexion wants to draw a Shape by the auth-token - user - project - comment relationship.And that request is the same for me.

I also want to synchronize a subset with the Electric server and the client PGLite, as described in the example below.
"Electric syncs little subsets of your Postgres data into local apps, services and environments. Those subsets are defined using Shapes."
https://electric-sql.com/docs/guides/shapes#shapes

[dexion]

Hi! On page https://electric-sql.com/docs/guides/shapes#single-table in section "Include tree workarounds" - it's an insecure way to get data from the server. You can dump the whole table as client, and we can't restrict clients to rows. IMHO, it's a basic issue for main application's cases when you retrieve data from a server. That is why we should have a mechanism to restrict rows on the server-side instead of client-side.