OpenCRUD

Working Draft – April 2018

Introduction

OpenCRUD is a GraphQL CRUD API specification for databases that was started by Prisma.

1Overview

OpenCRUD is a fully GraphQL compliant query language to access and modify data. OpenCRUD provides API flavours for many popular databases including MySQL and MongoDB.

For example, this OpenCRUD query retrieves a single user:

{
  user(where: { id: 4 }) {
    name
  }
}

returns:

{
  "user": {
    "name": "Mark Zuckerberg"
  }
}

1.1Rationale

GraphQL is a flexible query language supporting many different data access patterns. In practice, simple CRUD operations turn out to be a very common pattern. Standardising this very common pattern enables the community to build tooling specific to the common CRUD style API.

2Relational

2.1Areas covered

This specification describes all aspects of a flexible GraphQL API suitable for relational databases.

2.2Focus on API, not implementation/runtime characteristics

OpenCrud is a collection of specifications for GraphQL APIs that are designed to work well with specific database technologies. OpenCRUD is concerned with the API surface, not the implementation. As such two implementations of OpenCRUD could choose to store data in different ways, but applications interacting with the data through the OpenCRUD API wouldn’t be able to tell the difference.

2.3SDL

Examples are used throughout this spec to show the final schema generated for a specific data model. In all examples, the SDL notation is used to define the data model. The benefit of SDL is that it is database independent, so we can use the same notation accross all supported databases.

The following directives have special meaning when used in example SDL in this spec:

  • @unique: The field has a unique constraint
  • @relation: specifies a relation between two fields

2.4Queries

2.4.1Queries: Top level

  • Overview
  • Single fields multi fields
  • Multi connection fields
  • Node field
  • Example
2.4.1.1Overview

GraphQL defines the top level Query type. OpenCRUD defines multiple top level fields that are generated for each database type (table in a relational database)

2.4.1.2Single fields

Retrieve a single data record.

2.4.1.3Multi fields

Retrieve multiple data records

2.4.1.4Multi connection fields

Retrieve multiple data records. This field is Relay compliant and contains the aggregate and groupBy sub fields.

2.4.1.5Node field

The node field is specified by the Relay spec and allow a client to retrieve any data record by its id.

2.4.1.6Naming

OpenCRUD does not specify how fields generated for each data type must be named. It is up to each OpenCRUD implementation define a naming system. The reference implementation uses the followig naming convention:

  • single field: [data type name]
  • multi field: [pluralized data type name] (using the evo‐inflector library)
  • multi connecton field: [pluralized data type name]Connection

OpenCRUD implementations can choose to make field names configurable instead of convention based.

2.4.1.7Example

This example illustrates all top level fields

Data Model
type User {
  id: ID! @unique
  name: String!
}
Generated Schema
type Query {
  users(
    where: UserWhereInput
    orderBy: UserOrderByInput
    skip: Int
    after: String
    before: String
    first: Int
    last: Int
  ): [User]!
  user(where: UserWhereUniqueInput!): User
  usersConnection(
    where: UserWhereInput
    orderBy: UserOrderByInput
    skip: Int
    after: String
    before: String
    first: Int
    last: Int
  ): UserConnection!
  node(id: ID!): Node
}
Query
query {
  user(where: { id: "1" }) {
    name
  }
  users(where: { name_contains: "Karl" }) {
    name
  }
  usersConnection(where: { name_contains: "Karl" }) {
    edges {
      node {
        name
      }
    }
  }
  node(id: "1") {
    name
  }
}

2.4.2Queries: Relations

  • Overview
  • Connections
    • Aggregations
    • Cursor
  • Example
2.4.2.1Overview

In relational databases a relation is used to connecto two related tables. OpenCRUD defines how relations are exposed in the GraphQL schema. Relations in OpenCRUD generate two fields:

  • multi fields are great for most cases where you need to retrieve related data
  • multi connection fields are compatible with Relay and contain the aggrete and groupBy fields that enable avdanced aggregations.
2.4.2.2Connections

> WIP

2.4.2.3Example

This example illustrates all top level fields

Data Model
type User {
  id: ID! @unique
  name: String!
  posts: [Post!]!
}

type Post {
  id: ID! @unique
  title: String!
}
Generated Schema
type User implements Node {
  id: ID!
  name: String!
  posts(
    where: PostWhereInput
    orderBy: PostOrderByInput
    skip: Int
    after: String
    before: String
    first: Int
    last: Int
  ): [Post!]
  postsConnection(
    where: PostWhereInput
    orderBy: PostOrderByInput
    skip: Int
    after: String
    before: String
    first: Int
    last: Int
  ): PostConnection!
}

type Post implements Node {
  id: ID!
  title: String!
}
Query
query {
  user(where: {id: "1"}) {
    name
    posts {
      title
    }
    postsConnection {
      edges {
        node {
          title
        }
      }
    }
  }
}

2.4.3Queries: Filters

  • Overview
  • Data types
  • Single field
  • Multi field
  • Boolean expressions
  • Cross‐relation filters
2.4.3.1Overview

OpenCRUD filters are designed to surface as many capabilities of the underlying database as possible while maintaining a simple and intuitive API surface. Filters are available on all top level fields and relation fields.

2.4.3.2Data types

The available filters depend on the type of a field. For example an Integer field supports the greater than filter while a String field supports the contains filter that match on substrings.

type UserWhereInput {
  AND: [UserWhereInput]
  OR: [UserWhereInput]

  # String field
  field: String # equals
  field_not: String # not equals
  field_contains: String # contains substring
  field_not_contains: String # does not contain substring
  field_starts_with: String
  field_not_starts_with: String
  field_ends_with: String
  field_not_ends_with: String
  field_lt: String # less than
  field_lte: String # less then or equals
  field_gt: String # greater than
  field_gte: String # greater than or equals
  field_in: [String] # in list
  field_not_in: [String] # not in list

  # Integer field
  field: Integer # equals
  field_not: Integer # not equals
  field_lt: Integer # less than
  field_lte: Integer # less then or equals
  field_gt: Integer # greater than
  field_gte: Integer # greater than or equals
  field_in: [Integer] # in list
  field_not_in: [Integer] # not in list
  
  # Float field
  field: Float # equals
  field_not: Float # not equals
  field_lt: Float # less than
  field_lte: Float # less then or equals
  field_gt: Float # greater than
  field_gte: Float # greater than or equals
  field_in: [Float] # in list
  field_not_in: [Float] # not in list
  
  # Boolean field
  field: Boolean # equals
  field_not: Boolean # not equals
  
  # DateTime field
  field: DateTime # equals
  field_not: DateTime # not equals
  field_in: [DateTime] # in list
  field_not_in: [DateTime] # not in list
  field_lt: DateTime # less than
  field_lte: DateTime # less then or equals
  field_gt: DateTime # greater than
  field_gte: DateTime # greater than or equals
  
  # Enum field
  field: Enum # equals
  field_not: Enum # not equals
  field_in: [Enum] # in list
  field_not_in: [Enum] # not in list
  
  # List[T] field
  field_contains: T # contains single scalar T
  field_contains_every: [T] # contains all scalar T
  field_contains_some: [T] # contains at least 1 scalar T
 
  # many Relation field
   field_every: FilterCondition # condition must be true for all nodes
  field_some: FilterCondition # condition must be true for at least 1 node
  field_none: FilterCondition # condition must be false for all nodes
  field_is_null: Boolean # is the relation field null
 
  # one Relation field
  field: UserWhereInput # condition must be true for related node
}
2.4.3.3Single field

Fields for relations to a single data record allow you to filter by exact matches on unique fields:

{
  user(where: {id: "1"}){
    name
  }
}
2.4.3.4Multi field

Fields for relations to many data records allow you to filter by all filters specified above:

{
  users(where: {name_not_in:["Karl", "Viggo"]}){
    name
  }
}
2.4.3.5Boolean expressions

Filter conditions can be nested and combined arbitrarily using the boolean expressions AND and OR:

{
  users(where: {OR: [
    {name_not_in: ["Karl", "Viggo"]},
  	{AND: [{id: "1"}, {name: "Karl"}]}
  ]}) {
    name
  }
}

Two filter conditions on the same level are an explicit AND, so the above query can be simplified like this:

{
  users(where: {OR: [
    {name_not_in: ["Karl", "Viggo"]},
  	{id: "1", name: "Karl"}
  ]}) {
    name
  }
}
2.4.3.6Cross-relation filters

In relational databases it is common to filter based on columns in a related table. In OpenCRUD this concept is called relation filters. The following query retrieves all users that are related to the post with id 1.

{
  users(where:{posts_some:{id: "1"}}){
    name
  }
}

The same result could be achieved by inverting the query:

{
  post(where: {id: "1"}) {
    author {
      name
    }
  }
}

Relation filters have the same power as normal filters and can use one of tree modifiers:

  • every
  • none
  • some
{
  every: users(where:{posts_every:{id: "1"}}){name}
  none: users(where:{posts_none:{id: "1"}}){name}
  some: users(where:{posts_some:{id: "1"}}){name}
}

2.4.4Queries: Aggregations

  • Overview
  • Aggregate Functions
    • avg
    • median
    • max
    • min
    • count
    • sum
  • Group
  • Example: combine groupBy and Aggregations

2.5Mutations

2.5.1Mutations: CRUD

  • Overview
  • Create
  • Update
  • Delete
2.5.1.1Overview

OpenCRUD exposes the following mutations to do simple data manipulation

2.5.1.2Create

Create a single data racord:

mutation {
  createUser(data: { name: "Karl" }) {
    id
  }
}
2.5.1.3Update
mutation {
  updateUser(where: { id: "1" }, data: { name: "Karl" }) {
    id
  }
}
2.5.1.4Delete
mutation {
  deleteUser(where: { id: "1" }) {
    id
  }
}

2.5.2Mutations: Batch Operations

  • Overview
  • Update
  • Delete
2.5.2.1Overview

OpenCRUD exposes the followig mutations to manipulate a batch of data records. The count field returns a count of the number of records affected

2.5.2.2Update
mutation {
  updateManyUsers(where: {name_not: "Karl"}, data:{name: "Karl"}) {
    count
  }
}
2.5.2.3Delete
mutation {
  deleteManyUsers(where: {name_not: "Karl"}) {
    count
  }
}

2.5.3Mutations: Nested

2.5.3.1Overview

OpenCRUD nested mutations is a powerful way to manipulate relational data.

  1. 1Overview
    1. 1.1Rationale
  2. 2Relational
    1. 2.1Areas covered
    2. 2.2Focus on API, not implementation/runtime characteristics
    3. 2.3SDL
    4. 2.4Queries
      1. 2.4.1Queries: Top level
        1. 2.4.1.1Overview
        2. 2.4.1.2Single fields
        3. 2.4.1.3Multi fields
        4. 2.4.1.4Multi connection fields
        5. 2.4.1.5Node field
        6. 2.4.1.6Naming
        7. 2.4.1.7Example
      2. 2.4.2Queries: Relations
        1. 2.4.2.1Overview
        2. 2.4.2.2Connections
        3. 2.4.2.3Example
      3. 2.4.3Queries: Filters
        1. 2.4.3.1Overview
        2. 2.4.3.2Data types
        3. 2.4.3.3Single field
        4. 2.4.3.4Multi field
        5. 2.4.3.5Boolean expressions
        6. 2.4.3.6Cross-relation filters
      4. 2.4.4Queries: Aggregations
    5. 2.5Mutations
      1. 2.5.1Mutations: CRUD
        1. 2.5.1.1Overview
        2. 2.5.1.2Create
        3. 2.5.1.3Update
        4. 2.5.1.4Delete
      2. 2.5.2Mutations: Batch Operations
        1. 2.5.2.1Overview
        2. 2.5.2.2Update
        3. 2.5.2.3Delete
      3. 2.5.3Mutations: Nested
        1. 2.5.3.1Overview