bitemyapp / bitemyapp/esqueleto
Improved Postgres Update
- Dominant language
- Haskell
- Stars
- 399
- Forks
- 107
- Avg merge
- 1d 17h
- Merged PRs (30d)
- 1
Description
This is going to be a mega issue that documents improving `update` for `Postgresql` specifically.
Related issues:
- [ ] #251 `update` doesn't work with experimental syntax
- [ ] #204 Add a `WHERE` to `insertSelectOnConflict`
- [ ] #44 Support `update ... returning`
# Postgres Docs
[These are the Postgres docs for `update`](https://www.postgresql.org/docs/current/sql-update.html). Copying the syntax, we have:
```
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
```
Right now, our `update` function in `esqueleto` only supports updating a single table (no `FROM` clause allowed), and we don't have the ability to return anything from it aside from the count of rows modified.
# A type and API
- `UPDATE ... RETURNING` returns a list of rows that were actually updated, so it should have a return type like `select`.
- The `SqlQuery` already allows us to do CTEs, but we'll have to be careful to structure them outside the `UPDATE` syntax.
- The `FROM` stuff is a bit tricky. Per docs,
> When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.
- More `FROM` trickiness - we can't update non-target tables. So the type introduced by the lambda should be slightly different from the type introduced by `from`.
The current signature is:
```haskell
update :: (SqlExpr (Entity val) -> SqlQuery ()) -> SqlWriteT m ()
```
To account for the above, we want to do something like:
```haskell
update
:: (SqlSelect a r, PersistEntity val, _)
=> (UpdateTarget val -> SqlQuery a)
-> SqlWriteT m [r]
```
## `UpdateTarget`
This would be a new type for introducing the entity. Presumably, it would wrap `SqlExpr` somehow. An ideal design would be to retain transparency and compatibility with the old API and existing functions, while also providing type-safety for the `UPDATE` clause's `SET`s (which can only touch the target table).
We can simply have `type UpdateTarget val = SqlExpr (Entity val)` - aka, no distinction between "a table targeted for update" and "a table brought into scope via `from`." This would be the *easiest* thing to do - no modification to anything required really. It's also the least safe option.
Having it be a `newtype` wrapper of some sort allows us to provide: `readUpdateTarget :: UpdateTarget val -> SqlExpr (Entity val)`. Then the user can do:
```haskell
update $ \fooWrite -> do
let fooRead = readUpdateTarget fooWrite
set fooWrite [FooName =. val "hello", FooAge +=. val 1]
where_ $ fooRead ^. FooName ==. val "goodbye"
```
This combination maybe common enough that we'd want to just pass both in the lambda. But that may be confusing too.
We could generalize `(^.)` to accept either a `SqlExpr (Entity val)` or `UpdateTarget val`. But there'd be work to generalize this to all other cases, too.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.