Views, Stored Prodecures and Functions

[matthewmueller]

Resolves: https://github.com/prisma/specs/issues/234

OLD

Keeping here for posterity, please read: https://github.com/prisma/specs/pull/248#issuecomment-542204184

---

Views are pretty limited in what they can do: https://www.postgresql.org/docs/12/sql-createview.html

view BrazilCustomers {
  name  String
  email String
  count Int
  @@as("select name, email, count(id) from customers where country = 'Brazil'")
}

I think we need to cover 2 things with our schema to support views:

1. add select queries. I added an @@as(<query>) to cover this.
2. readonly models. This will affect the APIs we generate with photon.

Personally, I would still prefer writing SQL here:

create view brazil_customers as 
select customer_name, contact_name, count(id) from customers where country = 'Brazil';

I haven't figured out a way to make our syntax as nice as this, without upending our existing syntax.

Alternatives

Target Lift, introduce @@readonly instead of introducing view:

model BrazilCustomers {
  name  String
  email String
  count Int
  @@readonly
  @@pg.migrate("select name, email, count(id) from customers where country = 'Brazil'")
}

Reduce duplication with more attributes

view BrazilCustomers {
  name  String
  email String
  count Int @as("count(id)")
  @@from("customers")
  @@where("country = 'Brazil'")
}

or

view BrazilCustomers {
  name  String
  email String
  count Int @as("count(id)")
  @@as(from:"customers" where: "country = 'Brazil'")
}

We would need someway to indicate that count Int refers to the count(id) aggregation. I've added @as(<field>) for this.

Templating to link fields

view BrazilCustomers {
  name  String
  email String
  count Int
  @@as("select ${name}, ${email}, ${count}(id) from customers where country = 'Brazil'")
}

How this looks in Photon

Since the query is already set in the schema, we wouldn't expose a where clause. Since it's readonly, we wouldn't expose create, update, upsert, or delete.

await photon.brazilcustomers.find({
  select: {
    name: true,
  }
})

Aside

• Seeing Hasura's workflow was helpful here: https://youtu.be/153iv1-qFuc?t=169. They have an easier job because they don't attempt migrations.

[divyenduz]

I personally prefer in order Reduce duplication with more attributes.

The option to write SQL select in @@as with or without the templates is error prone (without lint tools for SQL) and redundant (as you rightly pointed out) as I already specified all those field with their types in the field list on the view.

⚠️This doesn't work with count(id) currently

Can you please elaborate this? I didn't follow 🙏

[matthewmueller]

Thanks for reviewing @divyenduz! I've updated the Reduce duplication with more attributes to offer up one solution for count(id). Let me know if that still doesn't make sense.

[matthewmueller]

While looking into views, stored procedures and functions, it's become obvious that the Prisma schema may get bloated with complex views, particularly from introspection.

To keep our schema clean going forward, I propose we only provide stubs in the schema that link to actual implemention in a new lift/ subdirectory. To keep our application directory clean, I also propose that we move migrations/ into lift/. Here's my suggestion:

lift/
  views/
    brazil_customers.sql
  migrations/
    2018-setup/
       schema.prisma
       steps.json
    2019-update/
       schema.prisma
       steps.json
  procedures/
  functions/
    extract_day.sql

This allows views, procedures and function to go from having the SQL query inside the prisma schema:

view BrazilCustomers {
  name  String
  email String
  count Int
  @@as("select name, email, count(id) from customers where country = 'Brazil'")
}

To more like a stub that we enforce with lift:

view BrazilCustomers {
  name  String
  email String
  count Int
}

There's more details in the spec about how this affects photon.

[steebchen]

We could also outsource this to photon clients. I don't know of it's a good idea, but in theory you could just use a separate schema-level query builder implemented in the photon clients. This results in a fully type-safe photon-style query. It should also be possible to enforce implementing the view query by using the type system of the respective language. It's probably a lot more work than just using a normal SQL query, so it may not be worth, but I wanted to suggest it anyway.

[matthewmueller]

Regarding views in a separate file:

[janpio]

Views are not a Lift thing though, but a part of the schema, right?

[matthewmueller]

@mavilein this proposal currently assumes we'd be able to treat these SQL view files declaratively using something like create or replace view. A snapshot of these files could also live in migrations (something like migrations/2019/views/myview.sql)

Regarding hooks:

Would we be able to enforce that the schema's view matches what's currently in the database? I forget if we do introspection after the hooks run as well.

If that's the case, then I think your solution would work just fine and I'll update the spec to reflect this.

[mavilein]

@matthewmueller : Yeah we could do that. We describe the SQL schema after the hooks ran.

[matthewmueller]

Another option is following suit with terraform and having a file(filename) function. Something like:

view BrazilCustomers {
  name  String
  email String
  count Int
  @@file("./myview.sql")
}

[Bernix01]

any update on this?