Protocol improvements and JOIN relations support.

[Isakdl]

Protocol improvements

This thread is a collection of improvements for the yaml protocol files, specifically to add new functionality with the database integration. I have taken inspiration from both Prisma and Hibernate when crafting this document.

The goal is to create an easy-to-use and lightweight syntax that encompasses most use cases. Feel free to give feedback or come up with suggestions.

Extensions

Allow extensions on classes to inherit fields that can be shared between classes.

We can do this by creating an abstract class and then making use of extends to use the abstract class. This would then map directly to the same concepts in Dart.

# vehicle.yaml
abstract: Vehicle
fields:
  maxSpeed: int

# car.yaml
class: Car
extends: Vehicle
fields:
  wheels: int

# horse.yaml
class: Horse
extends: Vehicle
fields:
  legs: int

The resulting Car class would have the properties maxSpeed and wheels.

To force the implementer to create a database table you have to set the table key. Here the name doesn't matter but as a convention, we could simply set T.

abstract: Vehicle
table: T
fields:
  maxSpeed: int

In this case, if the class that extends the Vehicle class does not have the table key an error is thrown. No real table would be created for abstract classes.

Default values

Allow the implementer to define a default value. This value should propagate to both dart and the database.

class: User
table: user
fields:
  name: String
  trusted: boolean, default=true

Special keywords

Allow the use of special keywords to define certain generic functionality.

Today we have the keywords database and api to limit the fields to either of them.

I suggest changing this to scope=database or scope=api and scope=all where the last one is the default. Of course, we would still have to allow the old syntax until the next major version.

Add the following keywords.

• unique would enforce a unique restriction in the database, a more convenient way to add this than right now.
• createdAt a special functionality to manage a DateTime field to be set only when the row is created.
• updatedAt a special functionality to let Serverpod manage a DateTime field to be updated each time the row is updated.
• createdBy a special functionality to let Serverpod manage the field and link it to a user_id. Take this value from the session object, if the field is not nullable an error would be thrown if there is no authenticated user in the session object.
• updatedBy a special functionality to let Serverpod manage the field and link it to a user_id. Changes every time the row is updated.

Example of a unique restricted field.

class: Company
table: company
  name: String
  vatNo: String, unique

An example of audit fields.

class: Audit
table: audit # The keywords below require a table
fields:
  createdAt: DateTime, createdAt
  updatedAt: DateTime, updatedAt
  createdBy: int, createdBy # If no authenticated user, updating/inserting will fail
  updatedBy: int, updatedBy

To allow creating objects without an authenticated user.

class: Audit
table: audit
fields:
  createdBy: int?, createdBy # set to null if no authenticated user

Alternative syntax

Adding several different keywords to a field may become very long and hard to read. Allow the implementer to use either comma-separated values for the fields, or structure them in a yaml hierarchy.

Both of the following examples would be valid and equivalent.

# Comma separated
class: User
table: user
fields:
  name: String
  trusted: boolean, default=true, scope=database

# Yaml style
class: User
table: user
fields:
  name: String
  trusted:
    type: boolean
    default: true
    scope: database

Database relations

Allow for proper database relation support where data is fetched and saved automatically by Serverpod. This functionality should be configurable where the behavior can be changed, for example, if we do not want to have a cascading delete on a relation.

This proposal has a subtle change in how we view the data. The default perspective of the protocol files is from the API perspective and focuses on Objects and fields rather than tables and columns.

Relations keywords

relation

Can be written as relation or relation().

Example:

class: User
table: user
fields:
  address: Address, relation

The relation keyword signifies a relationship between two tables, if this keyword is used it means that the underlying object refers to another object with a table in the database. The relation keyword can only be used if table is defined for both classes in the relationship.

When the relation keyword is used, if the field is an Object defined in another protocol.yaml, the underlying column is converted to a foreign key reference. The column name is modified to have _id appended at the end. In the above example, the address field would be address_id in the database, with the type set to the same as the reference id.

The reference field can be configured by specifying reference=<field> which always defaults to reference=id. This will set the column we use as a foreign key in the relationship between objects.

Example:

class: User
table: user
fields:
  address: Address, relation(reference=zip)

If we only want to store the id itself in the model, we wouldn't automatically know which model to map this to. For this use-case, we can use the keyword parent and set it to the referenced class name.

Example:

class: User
table: user
fields:
  addressId: int, relation(parent=Address)

For the avoidance of doubt, all references shall in the protocol file always be defined from the class/object perspective, I.E. address rather than address_id.

The update and delete behavior can be configured for relation by defining onUpdate, and onDelete these map to the corresponding referential action in Postgresql.

Available referential action:

• NoAction: No action is defined, giving an error if a constraint violation happens.
• Restrict: The Restrict action is the same as NoAction.
• SetDefault: sets the default value, this requires there is a default value configured.
• Cascade: cascades the action from the parent to the child
• SetNull: sets the value to null, which can only be used if the field is optional.

The syntax is as follows:

relation(onUpdate=NoAction, onDelete=SetNull), The internal order should not matter.

The current behavior of relations in Serverpod can be described as relation(onUpdate=NoAction, onDelete=Cascade)

Default values could be changed from the current behavior without necessarily creating a breaking change, as we have a new syntax. But perhaps it makes the most sense to keep them the same.

1:1 relations

Creating a one-to-one relationship can be done by setting the relation key on one side of the model, and adding the unique key. Without the unique key, the relation would be a 1:n relationship. The other side needs to have the object defined as optional.

Example:

class: User
table: user
fields:
  address: Address, relation, unique

class: Address
table: address
fields:
  user: User?
  country: String
  city: String
  street: String
  zip: String

In this example address is a required field of a user, you need to have an address to create a user. The underlying table for the user contains the foreign key pointing to the address table.

Address has zero or one user attached to it. One side of the relationship has to be optional or you would not be able to create the objects.

The relation can be defined the other way around.

Example:

class: User
table: user
fields:
  address: Address?

class: Address
table: address
fields:
  user: User, relation, unique
  country: String
  city: String
  street: String
  zip: String

If we do not specify the relation keyword we treat the field as a JSON column in the database.

class: User
table: user
fields:
  address: Address?

class: Address
table: address
fields:
  country: String
  city: String
  street: String
  zip: String

1:n relations

Creating a 1:n relationship is done in a similar fashion, but where one side has a list. The list value would not propagate into the real database but only live on the dart side.

Example:

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee>

# employee.yaml
class: Employee
table: employee
fields:
  company: Company, relation
  name: String
  birthday: DateTime

In this example, a company can have zero or more employees, an employee must have a company.

Making the company optional should also be valid.

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee>

# employee.yaml
class: Employee
table: employee
fields:
  company: Company?, relation
  name: String
  birthday: DateTime

If the employee has no relation defined to the company, the employees list becomes a JSON column in the database.

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee> # No relation => JSON column

# employee.yaml
class: Employee
table: employee
fields:
  company: Company # No relation => JSON column
  name: String
  birthday: DateTime

The implementation of this requires some extra thought as it is pretty easy to run into a lot of performance issues with these queries, such as the n+1 problem.

References on this issue from other ORMs:

• https://thecodingmachine.io/solving-n-plus-1-problem-in-orms
• https://www.prisma.io/docs/guides/performance-and-optimization/query-optimization-performance
• https://blog.devgenius.io/avoid-n-1-problem-using-eloquent-orm-e74d27e7946d
• https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-jpa-and-hibernate

With that said you will always be able to build extremely convoluted structures, in the end, there will always be a responsibility on the programmer to make sound decisions.

m:n relations

Many-to-many relationships are normally described in SQL by creating a third table that links the other two tables. We can manage this table automatically behind the scene.

Example:

# post.yaml
class: Post
table: post
fields:
  content: String
  tags: List<Tag>, relation

# tag.yaml
class: Tag
table: tag
fields:
  name: String
  posts: List<Post>, relation

SQL definition:

CREATE TABLE post (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL
);

CREATE TABLE tag (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

CREATE TABLE relation_post_tag (
  post_id INTEGER,
  tag_id INTEGER,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES post(id),
  FOREIGN KEY (tag_id) REFERENCES tag(id)
);

If we want to allow explicitly defined tables we probably need to rethink how we manage the table id values. Today the primary key is automatically mapped to an implicit id field. For a relation table like this, it is probably not what you want.

self relations

In theory, there should be no problem creating self-referencing relationships, but if we do expand them when querying they would only ever work for very small datasets/use cases.

class: BlogPost
table: blog_post
fields:
  content: String
  previousBlogPost: BlogPost?, relation
  nextBlogPost: BlogPost?, relation

A more sound solution is probably to simply store the ids and manually expand the objects when needed.

class: BlogPost
table: blog_post
fields:
  content: String
  previousBlogPostId: int?, relation(parent=BlogPost)
  nextBlogPostId: int?, relation(parent=BlogPost)

Manual queries

To manage the queries manually and build the objects by hand as you would in Serverpod today, you can set it up like this:

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee>?, scope=api

# employee.yaml
class: Employee
table: employee
fields:
  companyId: int, relation(parent=Company)
  company: Company?, scope=api
  name: String
  birthday: DateTime

View tables

Discussion on this topic in this other thread.

[vlidholt]

Extensions: Just to clarify. It should be possible to extend non-abstract classes too, right? Instead of table: T, maybe requireTable: true would be better.

Default values: How do we handle more complex types (DateTime, Uuid) or like other serializable classes. Or are default values only for basic values, bool, int, String, etc?

Special keywords: Very cool!

Alternative syntax: Like this too. :)

1:n relations
It may not be clear that the employees list will be auto fetched based on reading the company.yaml. Perhaps we should require a relation keyword here too, to make it clear that it will be fetched rather than read from json.

We may also want to encourage reference by id in the employee table, as it otherwise would fetch the complete company when getting an employee.

For 1:1 relations, also require that we use the relation keyword on both sides.

E.g.:

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee>, relation

# employee.yaml
class: Employee
table: employee
fields:
  companyId: int, relation
  name: String
  birthday: DateTime

[Isakdl]

Extensions: yes a A -> B -> C relation in the objects should be valid.

# a.yaml
abstract: A
fields:
  name: String

# b.yaml
class: B
extends: A
fields:
  age: int

# c.yaml
class: C
extends: B
fields:
  birthday: DateTime

Using the same field name in the extending class I think should be considered an error.

Example:

# a.yaml
abstract: A
fields:
  message: String

# b.yaml
class: B
extends: A
fields:
  message: String # This is invalid

I like the requireTable keyword, it makes it more obvious what is going on.

abstract: MyClass
requireTable: true
fields:
  text: String

To clarify this means that any real class extending MyClass would have a column in their table for the field text.

Default values:

• DateTime: I think we should support using the native database time function "now()" as an option for the default. An ISO8601 string should also be valid.
• UUID: I believe we can support this too, the question is on the implementation. There are postgres extensions that allow us to randomly generate UUID's in the database (http://www.ossp.org/pkg/lib/uuid/). Since we control the communication with the database we could also add the random generation in dart. Validating that a string is a UUID is trivial for any hardcoded strings.

Relations

I think the problem with potentially fetching the world when you have complex relationships, can be solved on the query side. Either by letting the programmer select if the object should be included or not. However in that case the field would have to be set as nullable. And/or we could implement lazy loading of data, I.E. not querying the data unless the field is accessed.

On the encourage references by id I think perhaps the best solution is to do this similar to Prisma, and force the programmer to specify the field for the foreign key. This can be done with the keyword field inside a relation, for example: relation(field=companyId).

I agree forcing the programmer to specify the relation keyword on lists does make it more obvious, and makes it easier for us to distinguish between a JSON entry and a relation.

Example:

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee>, relation

# employee.yaml
class: Employee
table: employee
fields:
  companyId: int
  company: Company?, relation(field=companyId)
  name: String
  birthday: DateTime

When fetching we could do it like this:

Employee.find(
  session,
  include: Employee.include({
    company: Company.include({
      employees: Employee.include(),
    }),
  }),
);

Where include returns an object that we can use to build the query. Such as:

class Employee extends TableRow {

  ...
  static EmployeeInclude include(EmployeeInclude param) {
    ...
    return param;
  }
}


class EmployeeInclude {
  CompanyInclude? company;

  EmployeeInclude({this.company});
}


class CompanyInclude {
  EmployeeInclude? employees;

  CompanyInclude({this.employees});
}

With this type of setup, we can include n number of expanded objects, m level deep, even recursively. Of course, adding many levels will result in poor performance at some point but this is on the developer to take care of.

If an include is set to null it means we do not include that object as a child, and therefore won't need to query the data.

---

# company.yaml
class: Company
table: company
fields:
  name: String
  employees: List<Employee>, relation

# employee.yaml
class: Employee
table: employee
fields:
  companyId: int, relation(parent=Company)
  name: String
  birthday: DateTime

Another restriction not mentioned before. The relation keyword should never be allowed to be used in conjunction with scope=api as this does not make sense, at least for foreign key relations. However scope=database should be allowed.

Note that a List relation does have an implicit API scope since we are not storing lists in the database (with relations).

[masreplay]

Why not just use Prisma

[lukehutch]

I agree something like Prisma is probably the way to go. There's no need to reinvent the wheel here. Prisma even implements automated migrations (so you could have probably saved yourselves a lot of time recently, compared to writing all the code for migrations from scratch!).

Failing that, I wonder if there is a way to define the database schemata in Dart, not yaml. I would strongly prefer Dart syntax.

[vlidholt]

Building our own ORM gives us much more flexibility for the future. E.g., as you mentioned it would be nice to use annotations in Dart and that is something we can do if we wing our own solution.

[Isakdl]

An idéa on the scope namings. We have a potentially third case that is not covered today where we would like a specific field to be hidden from the client but it shouldn't be persisted in the database either.

This can be achieved today if the entire object is defined as serverOnly, but even here the current namings don't make too much sense.

Example:

class: Example
table: example
serverOnly: true # hide the object from the client (api)
fields:
  name: String, api # will not be stored in database,

Instead we could treat the DTO toggle and the Entity toggle as two different variables.

we use the scope variable to toggler the DTO visibility, with the values public and private. I think this makes more sense than for example client and server, because public means both client and server, where private would be server only. Default is public.

To toggle the entity we can use a single keyword to opt out of the database storage (if a table is defined), transient as this is a word to describe none-presistent data in other ORMs. Default is that data is stored.

Example usage:

class: Example
table: example
fields:
  name: String, scope=private, transient # server side only but not stored in the db
  age: int, transient # visible to the client, but not stored in the db
  nick: String, scope=private # server side only AND is stored in the db
  url: String # visible to client and stored in db

class: Example
table: example
serverOnly: true # entire object is hidden from client
fields:
  name: String, transient # not stored in database

[SandPod]

I think this makes sense 👍

Just for clarification, with this setup we wouldn't be able to make a field public only right?
Any major drawbacks we need to take into consideration there?

[Isakdl]

Revised solution:

introduce negated boolean keys in the protocol files by adding a !

example: !api and api=false would be the inverse of api or api=true

Instead of transient we use persist but this is true by default and you have to negate the key to opt out of database storage.

Scope: one of serverOnly or all, in the future we can also add clientOnly.

class: Example
table: example
fields:
  name: String, scope=serverOnly, !persist # server side only but not stored in the db
  age: int, !persist # visible to the client, but not stored in the db
  nick: String, scope=serverOnly # server side only AND is stored in the db
  url: String # visible to client and stored in db

[alguintu]

class: User
table: user
fields:
  name: String
  trusted: boolean, default=true

Is the default flag working in 1.1.1 or 1.2.0-rc.1?

[SandPod]

The default flag is not yet supported, the issues for implementing it is tracked here: #1140