bitemyapp / bitemyapp/esqueleto
Allow creation of a VIEW from an Esqueleto query
- Dominant language
- Haskell
- Stars
- 399
- Forks
- 107
- Avg merge
- 1d 17h
- Merged PRs (30d)
- 1
Description
One thing that'd be particularly nice to have is a means to create a `VIEW` from an `esqueleto` query.
I've used `VIEW`s in a few workplaces before, and the pattern went:
1. Define a `views.persistentmodels` file with all the `VIEW`s in it.
2. Have a SQL file that actually created/defined the views.
3. Regular `persistent` and `esqueleto` queries against the `VIEW` work just fine (though `UPDATE` and `INSERT` obviously have restriction)
Right now, the SQL file is the only thing we don't support in the ecosystem. It's kind of annoying to have a big Esqueleto query that works awesome for the query, but when we go to make it into a `VIEW`, we have to translate to SQL (even if that' sjust finagling the library into rendering the relevant SQL query).
The machinery to implement this is all there. A `SqlQuery a` contains enough information to "run" that query in a `SELECT`, so it should be possible to "run" that query in a `CREATE VIEW` as well.
I'm envisioning an API like:
```haskell
createView :: PersistEntity rec => SqlQuery (SqlExpr rec) -> Migration
createView = error "write me please, refer to 'select' as a reference for rendering the SQL"
```
Then, with two tables `User` and `Dog` and a view model `PetOwnership`, we'd have something like:
```haskell
petOwnershipMigration :: Migration
petOwnershipMigration =
createView $
from $ \(u `InnerJoin` d) -> do
on $ o ^. UserId ==. d ^. DogOwnerId
pure (castFields o d)
where
castFields :: SqlExpr (Entity User) -> SqlExpr (Entity Dog) -> SqlExpr (Entity PetOwnership)
castFields = error "TODO: Not entirely sure how this works actually :|"
```
And to add this to the database, we'd have the migrations switch to:
```diff haskell
- runMigration migrateAll
+ runMigration $ do
+ migrateAll
+ petOwnershipMigration
```
The generated SQL would look something like:
```
CREATE OR REPLACE VIEW pet_ownership AS ...
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.