Shape support for selecting columns

[KyleAMathews]

This is a common request.

People will also want to rename columns (SELECT column AS foo)

// simple columns
useShape({
  url: `/table/foo`,
  columns: [`id`, `title`, `created_at`]
})

// Rename a column
useShape({
  url: `/table/foo`,
  columns: [`id`, { name: `title`, alias: `title_v1` }, `created_at`]
})

HTTP API

There's no universal way to represent arrays in urls — especially if it's not a simple array as here but can have sub-objects. So let's just JSON.stringify the data and add it as a columns parameter. E.g.

curl http://localhost:3000/v1/shapes/foo?columns=?columns=["id",{"name":"title","alias":"title_v1"},"created_at"]

Generally clients would url encode the json to ensure full compatibility but handwriting locally works as well.

[thruflo]

Just for more context: Electric syncs data using Shapes. Shapes target rows in a root_table, optionally with a where clause. For example to sync all users younger than 25:

curl http://localhost:300/v1/shapes/users?where="age<25"

By default, Shapes return the whole row. In this case, the row would contain all columns in the users table. Now, imagine that the users table contained a password_hash column that you didn't want to expose. Or imagine that it contained a bio column that contained a long text biography that you weren't going to use.

In these cases, you would ideally want to be able to sync users where you either select (i.e.: whitelist to include) or ignore (i.e.: blacklist to not include) specific columns.

Implementation

This can be supported using select and/or ignore parameters in the shape definition.

[alco]

Just some random thoughts re deriving efficiency gains from user's shape definitions.

We can edit a table's REPLICA IDENTITY based on the union of the set of all shapes that mention the table. For example, if a table has 50 columns but shapes consistently sync only like 5 of them, we can greatly reduce the number of bytes sent by Postgres to Electric by restricting the table's REPLICA IDENTITY to those 5 columns.

Another thing to consider is pruning unused bytes early on when processing logical messages in Electric. Right now we decode logical messages from raw bytes into Elixir maps in full. But if we can inform the decoder about which columns are SELECTed by the union of all shapes referencing the table, it will be able to skip unreferenced columns and avoid carrying them around for other processing steps, including shape storage.

[alco]

Instead of modifying a table's REPLICA IDENTITY, we could modify the column list on the publication in a similar way - https://www.postgresql.org/docs/current/logical-replication-col-lists.html.

[matt-aitken]

We've done tests using our production database and this feature is required for us to use Electric for parts of our app.

We only want to sync a few columns and don't want to sync other large columns because the data becomes too big.

[robacourt]

We already have a where clause, why not have a select clause as well:

curl http://localhost:3000/v1/shapes/foo?select=id,name

Benefits:

• Looks simpler in typical cases such as the above
• When migrating an existing app, the select clause can be directly ported over
• Adding support for aliases and various other features we may want at some stage such as calculated fields could be done in a syntax that everyone already knows

[KyleAMathews]

I did think about select — the problem I saw though is that select supports a lot of things we can't support in a sync context e.g.

• distinct,
• Arithmetic operations: Perform calculations within the SELECT statement (e.g., SELECT price * quantity AS total)
• String concatenation: Combine string columns or literals (e.g., SELECT first_name || ' ' || last_name AS full_name)
• Functions: Utilize built-in PostgreSQL functions for various operations (e.g., SELECT UPPER(name), SELECT AVG(salary)).
• Aggregate functions: Perform calculations on groups of rows, such as COUNT, SUM, AVG, MAX, and MIN (e.g., SELECT department, AVG(salary) FROM employees GROUP BY department).

(an LLM helped me brainstorm).

So it seems like a smaller % of select features are supported.

I do like the comma separated list though 🤔 — stringified JSON in URLs is icky.

Hmm yeah, I dunno — select is probably fine and probably better as it's what people would look for — the client API would make it clear you can only pass in column names. We wouldn't allow for arbitrary strings.

[balegas]

Yeah, I also wanted the SELECT, but reality is that we're not offering a SELECT.

Taking the point that we cannot do SELECT, I think its reasonable to push back on support column renaming.

• Renaming is bad because now you can have two shapes that have same data but different columns names. We should handle that de-duplication internally (even if not on a first pass).
• What's the purpose of renaming if you can't modify the selected column. I know people will want this for convenience, but doesn't seem too necessary.
• It also makes URLs worse :)

[KyleAMathews]

Yeah true, renaming is a fairly trivial activity that can be done elsewhere.

One advantage I can think of for us doing it is we'll want to generate typescript types for shapes at some point and having the renames there will be fine. Having the final state of data encoded in the shape definition is ergonomic.

I also don't think that there'll be that much duplicate shapes that are the same other than a column rename. That doesn't seem like something we'll need to worry about.

[balegas]

I also don't think that there'll be that much duplicate shapes that are the same other than a column rename. That doesn't seem like something we'll need to worry about.

Fair enough. I know, "shapes will be generated by code" ... :)

One advantage I can think of for us doing it is we'll want to generate typescript types for shapes at some point and having the renames there will be fine. Having the final state of data encoded in the shape definition is ergonomic.

We don't need to fix the api before we need it. Another point why people do renames is to get a sane name after joins etcs.

I'd say we left renaming out for now:

• api doesn't look very good
• we don't need if for our immediate priorities
• easily to add once people ask for it

[KyleAMathews]

Sure yeah that seems pragmatic. It's a very simple additional step to add renaming if it's actually ever needed 👍

YAGNI BABY!