Data Connect schemas, queries, and mutations

Firebase Data Connect lets you create connectors for your PostgreSQL instances managed with Google Cloud SQL. These connectors are combinations of a schema, queries, and mutations for using your data.

The Get started guide introduced a movie review app schema for PostgreSQL, and this guide takes a deeper look at how to design Data Connect schemas for PostgreSQL.

This guide pairs Data Connect queries and mutations with schema examples. Why discuss queries (and mutations) in a guide about Data Connect schemas? Like other GraphQL-based platforms, Firebase Data Connect is a query-first development platform, so as a developer, in your data modeling you'll be thinking about the data your clients need, which will greatly influence the data schema you develop for your project.

This guide starts with a new schema for movie reviews, then covers the queries and mutations derived from that schema, and lastly provides a SQL listing equivalent to the core Data Connect schema.

The schema for a movie review app

Imagine you want to build a service that lets users submit and view movies reviews.

You need an initial schema for such an app. You will extend this schema later to create complex relational queries.

Movie table

The schema for Movies contains core directives like:

  • @table(name) and @col(name) to customize the SQL table and column names. Data Connect generates snake_case names if not specified.
  • @col(dataType) to customize SQL column types.
  • @default to configure SQL column default values during insert.

For more details, check out the reference docs for @table, @col, @default.

# Movies
type Movie @table(name: "movie", key: "id") {
  id: UUID! @col(name: "movie_id") @default(expr: "uuidV4()")
  title: String!
  releaseYear: Int
  genre: String @col(dataType: "varchar(20)")
  rating: Int
  description: String
}

Key scalars and server values

Before looking more at the movie review app, let's introduce Data Connect key scalars and server values.

Key scalars are concise object identifiers that Data Connect automatically assembles from key fields in your schemas. Key scalars are about efficiency, allowing you to find in a single call information about the identity and structure of your data. They are especially useful when you want to perform sequential actions on new records and need a unique identifier to pass to upcoming operations, and also when you want to access relational keys to perform additional more complex operations.

Using server values, you can effectively let the server dynamically populate fields in your tables using stored or readily-computable values according to particular server-side CEL expressions in the expr argument. For example, you can define a field with a timestamp applied when the field is accessed using the time stored in an operation request, updatedAt: Timestamp! @default(expr: "request.time").

Movie metadata table

Now let's keep track of movie directors, as well as set up a one-to-one relationship with Movie.

Add the reference field to define a relationships.

You can use @ref directive to customize foreign key constraint.

  • @ref(fields) to specify the foreign key fields.
  • @ref(references) to specify the fields referenced in the target table. This reference defaults to the primary key, but fields with @unique are also supported.

For more details, check out the reference docs for @ref.

# Movie Metadata
# Movie - MovieMetadata is a one-to-one relationship
type MovieMetadata @table {
  # @unique ensures that each Movie only has one MovieMetadata.
  movie: Movie! @unique
  # Since it references to another table type, it adds a foreign key constraint.
  #  movie: Movie! @unique @ref(fields: "movieId", references: "id")
  #  movieId: UUID! <- implicitly added foreign key field
  director: String
}

Actor and MovieActor

Next, you want actors to star in your movies, and since you have a many-to-many relationship between movies and actors, create a join table.

# Actors
# Suppose an actor can participate in multiple movies and movies can have multiple actors
# Movie - Actors (or vice versa) is a many to many relationship
type Actor @table {
  id: UUID! @default(expr: "uuidV4()")
  name: String! @col(dataType: "varchar(30)")
}
# Join table for many-to-many relationship for movies and actors
# The 'key' param signifies the primary keys of this table
# In this case, the keys are [movieId, actorId], the foreign key fields of the reference fields [movie, actor]
type MovieActor @table(key: ["movie", "actor"]) {
  movie: Movie!
  # movieId: UUID! <- implicitly added foreign key field
  actor: Actor!
  # actorId: UUID! <- implicitly added foreign key field
  role: String! # "main" or "supporting"
  # optional other fields
}

User

Lastly, users for your app.

# Users
# Suppose a user can leave reviews for movies
type User @table {
  id: String! @default(expr: "auth.uid")
  username: String! @col(dataType: "varchar(50)")
}

Supported data types

Data Connect supports the following scalar data types, with assignments to PostgreSQL types using @col(dataType:).

Data Connect type GraphQL built-in type or
Data Connect custom type 		Default PostgreSQL type Supported PostgreSQL types
(alias in parentheses)
String GraphQL text text
bit(n), varbit(n)
char(n), varchar(n)
Int GraphQL int Int2 (smallint, smallserial),
int4 (integer, int, serial)
Float GraphQL float8 float4 (real)
float8 (double precision)
numeric (decimal)
Boolean GraphQL boolean boolean
UUID Custom uuid uuid
Int64 Custom bigint int8 (bigint, bigserial)
numeric (decimal)
Date Custom date date
Timestamp Custom timestamptz

timestamptz

Note: Local timezone information is not stored.
PostgreSQL converts and stores such timestamps as UTC.
Vector Custom vector

vector

See Perform vector similarity search with Vertex AI.
  • GraphQL List maps to a one-dimensional array.
    • For example, [Int] maps to int5[], [Any] maps to jsonb[].
    • Data Connect does not support nested arrays.

Use generated fields to build queries and mutations

Your Data Connect queries and mutations will extend a set of fields automatically generated Data Connect based on the types and type relationships in your schema. These fields are are generated by local tooling whenever you edit your schema.

  • As you discovered in the Get started guide, the Firebase console and our local development tooling use these auto-generated fields to provide you with ad hoc administrative queries and mutations you can use to seed data and verify the contents of your tables.

  • In your development process, you will implement deployable queries and deployable mutations bundled in your connectors, based on these auto-generated fields.

Auto-generated field naming

Data Connect infers suitable names for fields auto-generated based on your schema type declarations. For example, working with a PostgreSQL source, if you define a table named Movie, the server will generate:

  • Fields for reading data in single table use cases with the friendly names movie (singular, for retrieving individual results passing args like eq) and movies (plural, for retrieving result lists passing args like gt and operations like orderby). Data Connect also generates fields for multi-table, relational operations with explicit names like actors_on_movies or actors_via_actormovie.
  • Fields for writing data with familiar name like movie_insert, movie_upsert...

The schema definition language also lets you explicitly control how names are generated for fields using singular and plural directive arguments.

Directives for queries and mutations

In addition to the directives you use in defining types and tables, Data Connect provides the @auth, @check, @redact and @transaction directives for augmenting the behavior of queries and mutations.

Directive Applicable to Description
@auth Queries and mutations Defines the authentication policy for a query or mutation. See the authorization and attestation guide.
@check Authorization data lookup queries Verifies that specified fields are present in query results. A Common Expression Language (CEL) expression is used to test field values. See the authorization and attestation guide.
@redact Queries Redacts a part of the response from the client. See the authorization and attestation guide.
@transaction Mutations Enforces that a mutation always run in a database transaction. See the movie app mutation examples.

Queries for the movie review database

You define a Data Connect query with a query operation type declaration, operation name, zero or more operation arguments, and zero or more directives with arguments.

In the quickstart, the example listEmails query took no parameters. Of course, in many cases, data passed to query fields will be dynamic. You can use $variableName syntax to work with variables as one of the components of a query definition.

So the following query has:

  • A query type definition
  • A ListMoviesByGenre operation (query) name
  • A single variable $genre operation argument
  • A single directive, @auth.
query ListMoviesByGenre($genre: String!) @auth(level: USER)

Every query argument requires a type declaration, a built-in like String, or a custom, schema-defined type like Movie.

Let’s look at the signature of increasingly complex queries. You’ll end by introducing powerful, concise relationship expressions you can use to build your deployable queries.

Key scalars in queries

But first, a note about key scalars.

Data Connect defines a special type for key scalars, identified by _Key. For example, the type of a key scalar for our Movie table is Movie_Key.

You retrieve key scalars as a response returned by most auto-generated read fields, or of course from queries where you have retrieved all the fields needed to build the scalar key.

Singular automatic queries, like movie in our running example, support a key argument that accepts a key scalar.

You might pass a key scalar as a literal. But, you can define variables to pass key scalars as input.

query GetMovie($myKey: Movie_Key!) {
  movie(key: $myKey) { title }
}

These can be provided in request JSON like this (or other serialization formats):

{
  # …
  "variables": {
    "myKey": {"foo": "some-string-value", "bar": 42}
  }
}

Thanks to custom scalar parsing, a Movie_Key can also be constructed using the object syntax, which may contain variables. This is mostly useful when you want to break individual components into different variables for some reason.

Aliasing in queries

Data Connect supports GraphQL aliasing in queries. With aliases, you rename the data that is returned in a query’s results. A single Data Connect query can apply multiple filters or other query operations in one efficient request to the server, effectively issuing several "sub-queries" at once. To avoid name collisions in the returned data set, you use aliases to distinguish the sub-queries.

Here is a query where an expression uses the alias mostPopular.

query ReviewTopPopularity($genre: String) {
  mostPopular: review(first: {
    where: {genre: {eq: $genre}},
    orderBy: {popularity: DESC}
  }) { … }
}

Simple queries with filters

Data Connect queries map to all common SQL filters and order operations.

where and orderBy operators (singular, plural queries)

Returns all matched rows from the table (and nested associations). Returns an empty array if no records match the filter.

query MovieByTopRating($genre: String) {
  mostPopular: movies(
     where: { genre: { eq: $genre } }, orderBy: { rating: DESC }
  ) {
    # graphql: list the fields from the results to return
    id
    title
    genre
    description
  }
}

query MoviesByReleaseYear($min: Int, $max: Int) {
  movies(where: {releaseYear: {le: $max, ge: $min}}, orderBy: [{releaseYear: ASC}]) { … }
}

limit and offset operators (singular, plural queries)

You can perform pagination on results. These arguments are accepted but not returned in results.

query MoviesTop10 {
  movies(orderBy: [{ rating: DESC }], limit: 10) {
    # graphql: list the fields from the results to return
    title
  }
}

includes for array fields

You can test that an array field includes a specified item.

# Filter using arrays and embedded fields.
query ListMoviesByTag($tag: String!) {
  movies(where: { tags: { includes: $tag }}) {
    # graphql: list the fields from the results to return
    id
    title
  }
}

String operations and regular expressions

Your queries can use typical string search and comparison operations, including regular expressions. Note for efficiency you are bundling several operations here and disambiguating them with aliases.

query MoviesTitleSearch($prefix: String, $suffix: String, $contained: String, $regex: String) {
  prefixed: movies(where: {title: {startsWith: $prefix}}) {...}
  suffixed: movies(where: {title: {endsWith: $suffix}}) {...}
  contained: movies(where: {title: {contains: $contained}}) {...}
  matchRegex: movies(where: {title: {pattern: {regex: $regex}}}) {...}
}

or and and for composed filters

Use or and and for more complex logic.

query ListMoviesByGenreAndGenre($minRating: Int!, $genre: String) {
  movies(
    where: { _or: [{ rating: { ge: $minRating } }, { genre: { eq: $genre } }] }
  ) {
    # graphql: list the fields from the results to return
    title
  }
}

Complex queries

Data Connect queries can access data based on the relationships among tables. You can use the object (one-to-one) or array (one-to-many) relationships defined in your schema to make nested queries, i.e. fetch data for one type along with data from a nested or related type.

Such queries use magic Data Connect _on_ and _via syntax in generated read fields.

You'll be making modifications to the schema from our initial version.

Many to one

Let's add reviews to our app, with a Review table and modifications to User.

# User table is keyed by Firebase Auth UID.
type User @table {
  # `@default(expr: "auth.uid")` sets it to Firebase Auth UID during insert and upsert.
  id: String! @default(expr: "auth.uid")
  username: String! @col(dataType: "varchar(50)")
  # The `user: User!` field in the Review table generates the following one-to-many query field.
  #  reviews_on_user: [Review!]!
  # The `Review` join table the following many-to-many query field.
  #  movies_via_Review: [Movie!]!
}

# Reviews is a join table tween User and Movie.
# It has a composite primary keys `userUid` and `movieId`.
# A user can leave reviews for many movies. A movie can have reviews from many users.
# User  <-> Review is a one-to-many relationship
# Movie <-> Review is a one-to-many relationship
# Movie <-> User is a many-to-many relationship
type Review @table(name: "Reviews", key: ["movie", "user"]) {
  user: User!
  # The user field adds the following foreign key field. Feel free to uncomment and customize it.
  #  userUid: String!
  movie: Movie!
  # The movie field adds the following foreign key field. Feel free to uncomment and customize it.
  #  movieId: UUID!
  rating: Int
  reviewText: String
  reviewDate: Date! @default(expr: "request.time")
}

Query for many to one

Now let's look at a query, with aliasing, to illustrate _via_ syntax.

query UserMoviePreferences($username: String!) @auth(level: USER) {
  users(where: { username: { eq: $username } }) {
    likedMovies: movies_via_Review(where: { rating: { ge: 4 } }) {
      title
      genre
    }
    dislikedMovies: movies_via_Review(where: { rating: { le: 2 } }) {
      title
      genre
    }
  }
}

One to one

You can see the pattern. Below, the schema is modified for illustration.

# Movies
type Movie
  @table(name: "Movies", singular: "movie", plural: "movies", key: ["id"]) {
  id: UUID! @col(name: "movie_id") @default(expr: "uuidV4()")
  title: String!
  releaseYear: Int @col(name: "release_year")
  genre: String
  rating: Int @col(name: "rating")
  description: String @col(name: "description")
  tags: [String] @col(name: "tags")
}
# Movie Metadata
# Movie - MovieMetadata is a one-to-one relationship
type MovieMetadata
  @table(
    name: "MovieMetadata"
  ) {
  # @ref creates a field in the current table (MovieMetadata) that holds the primary key of the referenced type
  # In this case, @ref(fields: "id") is implied
  movie: Movie! @ref
  # movieId: UUID <- this is created by the above @ref
  director: String @col(name: "director")
}


extend type MovieMetadata {
  movieId: UUID! # matches primary key of referenced type
...
}

extend type Movie {
  movieMetadata: MovieMetadata # can only be non-nullable on ref side
  # conflict-free name, always generated
  movieMetadatas_on_movie: MovieMetadata
}

Query for one to one

You can query using _on_ syntax.

# One to one
query GetMovieMetadata($id: UUID!) @auth(level: PUBLIC) {
  movie(id: $id) {
    movieMetadatas_on_movie {
      director
    }
  }
}

Many to many

Movies need actors, and actors need movies. They have a many to many relationship you can model with a MovieActors join table.

# MovieActors Join Table Definition
type MovieActors @table(
  key: ["movie", "actor"] # join key triggers many-to-many generation
) {
  movie: Movie!
  actor: Actor!
}

# generated extensions for the MovieActors join table
extend type MovieActors {
  movieId: UUID!
  actorId: UUID!
}

# Extensions for Actor and Movie to handle many-to-many relationships
extend type Movie {
  movieActors: [MovieActors!]! # standard many-to-one relation to join table
  actors: [Actor!]! # many-to-many via join table

  movieActors_on_actor: [MovieActors!]!
  # since MovieActors joins distinct types, type name alone is sufficiently precise
  actors_via_MovieActors: [Actor!]!
}

extend type Actor {
  movieActors: [MovieActors!]! # standard many-to-one relation to join table
  movies: [Movie!]! # many-to-many via join table

  movieActors_on_movie: [MovieActors!]!
  movies_via_MovieActors: [Movie!]!
}

Query for many to many

Let's look at a query, with aliasing, to illustrate _via_ syntax.

query GetMovieCast($movieId: UUID!, $actorId: UUID!) @auth(level: PUBLIC) {
  movie(id: $movieId) {
    mainActors: actors_via_MovieActor(where: { role: { eq: "main" } }) {
      name
    }
    supportingActors: actors_via_MovieActor(
      where: { role: { eq: "supporting" } }
    ) {
      name
    }
  }
  actor(id: $actorId) {
    mainRoles: movies_via_MovieActor(where: { role: { eq: "main" } }) {
      title
    }
    supportingRoles: movies_via_MovieActor(
      where: { role: { eq: "supporting" } }
    ) {
      title
    }
  }
}

Aggregation queries

What are aggregates, and why use them?

Aggregate fields let you perform calculations on a list of results. With aggregate fields, you can do things like:

  • Find the average score of a review
  • Find the total cost of items in a shopping cart
  • Find the highest- or lowest-rated product
  • Count the number of products in your store

Aggregates are performed on the server, which offers a number of benefits over calculating them client side:

  • Faster app performance (since you avoid client side calculations)
  • Reduced data egress costs (since you send just the aggregated results instead of all of the inputs)
  • Improved security (since you can give clients access to aggregated data instead of the entire data set)

Example schema for aggregates

In this section, we'll switch to a storefront example schema, which is a good for explaining how to use aggregates:

  type Product @table {
    name: String!
    manufacturer: String!
    quantityInStock: Int!
    price: Float!
    expirationDate: Date
  }

Simple aggregates

_count for all fields

The simplest aggregate field is _count: it returns how many rows match your query. For each field in your type, Data Connect generates corresponding aggregate fields depending on the field type.

Query Response 
one
 
query CountProducts {
  products {
    _count
  }
}

For example, if you have 5 products in your database, the result would be:

{
  "products": [
    {
    "_count": 5
    }
  ]
}

All fields have a <field>_count field, which counts how many rows have a non-null value in that field.

Query Response
field_count
 
query CountProductsWithExpirationDate {
  products {
    expirationDate_count
  }
}

For example, if you have 3 products with an expiration date, the result would be:

{
  "products": [
    {
    "expirationDate_count": 3
    }
  ]
}
_min, _max, _sum, and _avg for numeric fields

Numeric fields (int, float, int64) also have <field>_min, <field>_max, <field>_sum, and <field>_avg.

Query Response
_min _max _sum _avg
 
query NumericAggregates {
  products {
  quantityInStock_max
  price_min
  price_avg
  quantityInStock_sum
  }
}

For example, if you have the following products:

  • Product A: quantityInStock: 10, price: 2.99
  • Product B: quantityInStock: 5, price: 5.99
  • Product C: quantityInStock: 20, price: 1.99

The result would be:

{
  "products": [
    {
    "quantityInStock_max": 20,
    "price_min": 1.99,
    "price_avg": 3.6566666666666666,
    "quantityInStock_sum": 35
    }
  ]
}
_min and _max for dates and timestamps

Date and timestamp fields have <field>_min and <field>_max.

Query Response
_min _maxdatetime
 
query DateAndTimeAggregates {
  products {
  expirationDate_max
  expirationDate_min
  }
}

For example, if you have the following expiration dates:

  • Product A: 2024-01-01
  • Product B: 2024-03-01
  • Product C: 2024-02-01

The result would be:

{
  "products": [
    {
    "expirationDate_max": "2024-03-01",
    "expirationDate_min": "2024-01-01"
    }
  ]
}

Distinct

The distinct argument lets you get all unique values for a field (or combination of fields). For example:

Query Response
distinct
query ListDistinctManufacturers {
  products(distinct: true) {
    manufacturer
  }
}

For example, if you have the following manufacturers:

  • Product A: manufacturer: "Acme"
  • Product B: manufacturer: "Beta"
  • Product C: manufacturer: "Acme"

The result would be:

{
  "products": [
    { "manufacturer": "Acme" },
    { "manufacturer": "Beta" }
  ]
}

You can also use the distinct argument on aggregate fields to instead aggregate the distinct values. For example:

Query Response
distinctonaggregate
 
query CountDistinctManufacturers {
  products {
    manufacturer_count(distinct: true)
  }
}

For example, if you have the following manufacturers:

  • Product A: manufacturer: "Acme"
  • Product B: manufacturer: "Beta"
  • Product C: manufacturer: "Acme"

The result would be:

{
  "products": [
    {
    "manufacturer_count": 2
    }
  ]
}

Grouped aggregates

You perform a grouped aggregate by selecting a mix of aggregate and non-aggregate fields on a type. This groups together all matching rows that have the same value for the non-aggregate fields, and calculate the aggregate fields for that group. For example:

Query Response
groupedaggregates
 
query MostExpensiveProductByManufacturer {
  products {
  manufacturer
  price_max
  }
}

For example, if you have the following products:

  • Product A: manufacturer: "Acme", price: 2.99
  • Product B: manufacturer: "Beta", price: 5.99
  • Product C: manufacturer: "Acme", price: 1.99

The result would be:

{
  "products": [
    { "manufacturer": "Acme", "price_max": 2.99 },
    { "manufacturer": "Beta", "price_max": 5.99 }
  ]
}
having and where with grouped aggregates

You can also use the having and where argument to only return groups that meet a provided criteria.

  • having lets you filter groups by their aggregate fields

  • where lets you filter the rows based on non-aggregate fields.

Query Response
havingwhere
query FilteredMostExpensiveProductByManufacturer {
  products(having: {price_max: {ge: 2.99}}) {
  manufacturer
  price_max
  }
}

For example, if you have the following products:

  • Product A: manufacturer: "Acme", price: 2.99
  • Product B: manufacturer: "Beta", price: 5.99
  • Product C: manufacturer: "Acme", price: 1.99

The result would be:

{
  "products": [
    { "manufacturer": "Acme", "price_max": 2.99 },
    { "manufacturer": "Beta", "price_max": 5.99 }
  ]
}

Aggregates across tables

Aggregate fields can be used in concert with generated one-to-many relationship fields to answer complex questions about your data. Here is a modified schema, with separate table, Manufacturer, we can use in examples:

  type Product @table {
    name: String!
    manufacturer: Manufacturer!
    quantityInStock: Int!
    price: Float!
    expirationDate: Date
  }

  type Manufacturer @table {
    name: String!
    headquartersCountry: String!
  }

Now we can use aggregate fields to do things like find how many products a manufacturer makes:

Query Response 
aggregatesacrosstables
 
query GetProductCount($id: UUID) {
  manufacturers {
    name
    products_on_manufacturer {
      _count
    }
  }
}

For example, if you have the following manufacturers:

  • Manufacturer A: name: "Acme", products_on_manufacturer: 2
  • Manufacturer B: name: "Beta", products_on_manufacturer: 1

The result would be:

{
  "manufacturers": [
    { "name": "Acme", "products_on_manufacturer": { "_count": 2 } },
    { "name": "Beta", "products_on_manufacturer": { "_count": 1 } }
  ]
}

Mutations for the movie review database

As mentioned, when you define a table in your schema, Data Connect will generate basic implicit mutations for each table.

type Movie @table { ... }

extend type Mutation {
  # Insert a row into the movie table.
  movie_insert(...): Movie_Key!
  # Upsert a row into movie."
  movie_upsert(...): Movie_Key!
  # Update a row in Movie. Returns null if a row with the specified id/key does not exist
  movie_update(...): Movie_Key
  # Update rows based on a filter in Movie.
  movie_updateMany(...): Int!
  # Delete a single row in Movie. Returns null if a row with the specified id/key does not exist
  movie_delete(...): Movie_Key
  # Delete rows based on a filter in Movie.
  movie_deleteMany(...): Int!
}

With these, you can implement increasingly complex core CRUD cases. Say that five times fast!

@transaction directive

This directive enforces that a mutation always run in a database transaction.

Mutations with @transaction are guaranteed to either fully succeed or fully fail. If any of the fields within the transaction fails, the entire transaction is rolled back. From a client standpoint, any failure behaves as if the entire request had failed with a request error and execution had not begun.

Mutations without @transaction execute each root field one after another in sequence. It surfaces any errors as partial field errors, but not the impacts of the subsequent executions.

Create

Let's do basic creates.

# Create a movie based on user input
mutation CreateMovie($title: String!, $releaseYear: Int!, $genre: String!, $rating: Int!) {
  movie_insert(data: {
    title: $title
    releaseYear: $releaseYear
    genre: $genre
    rating: $rating
  })
}

# Create a movie with default values
mutation CreateMovie2 {
  movie_insert(data: {
    title: "Sherlock Holmes"
    releaseYear: 2009
    genre: "Mystery"
    rating: 5
  })
}

Or an upsert.

# Movie upsert using combination of variables and literals
mutation UpsertMovie($title: String!) {
  movie_upsert(data: {
    title: $title
    releaseYear: 2009
    genre: "Mystery"
    rating: 5
    genre: "Mystery/Thriller"
  })
}

Perform updates

Here are updates. Producers and directors certainly hope that those average ratings are on trend.

The movie_update field contains an expected id argument to identify a record and a data field you can use to set values in this update.

mutation UpdateMovie(
  $id: UUID!,
  $genre: String!,
  $rating: Int!,
  $description: String!
) {
  movie_update(id: $id,
    data: {
      genre: $genre
      rating: $rating
      description: $description
    })
}

To perform multiple updates, use the movie_updateMany field.

# Multiple updates (increase all ratings of a genre)
mutation IncreaseRatingForGenre($genre: String!, $rating: Int!) {
  movie_updateMany(
    where: { genre: { eq: $genre } },
    data:
      {
        rating: $rating
      })
}

Use increment, decrement, append, and prepend operations with _update

While in _update and _updateMany mutations you can explicitly set values in data:, it often makes more sense to apply an operator like increment to update values.

To modify the earlier update example, assume you want to increment the rating of a particular movie. You can use rating_update syntax with the inc operator.

mutation UpdateMovie(
  $id: UUID!,
  $ratingIncrement: Int!
) {
  movie_update(id: $id, data: {
    rating_update: {
      inc: $ratingIncrement
    }
  })
}

Data Connect supports the following operators for field updates:

  • inc to increment Int, Int64 and Float data types
  • dec to decrement Int, Int64 and Float data types
  • append to append to list types, except Vector lists
  • prepend to prepend to list types, except Vector lists

Perform deletes

You can of course delete movie data. Film preservationists will certainly want the physical films to be maintained for as long as possible.

# Delete by key
mutation DeleteMovie($id: UUID!) {
  movie_delete(id: $id)
}

Here you can use _deleteMany.

# Multiple deletes
mutation DeleteUnpopularMovies($minRating: Int!) {
  movie_deleteMany(where: { rating: { le: $minRating } })
}

Write mutations on relations

Observe how to use the implicit _upsert mutation on a relation.

# Create or update a one to one relation
mutation MovieMetadataUpsert($movieId: UUID!, $director: String!) {
  movieMetadata_upsert(
    data: { movie: { id: $movieId }, director: $director }
  )
}

Let Data Connect supply values using field_expr syntax

As discussed in key scalars and server values, you can design your schema so that the server populates values for common fields like ids and dates in response to client requests.

In addition, you can make use of data, such as user IDs, sent in Data Connect request objects from client apps.

When you implement mutations, use field_expr syntax to trigger server-generated updates or access data from requests. For example, to pass the authorization uid stored in a request to an _upsert operation, pass "auth.uid" in the userId_expr field.

# Add a movie to the user's favorites list
mutation AddFavoritedMovie($movieId: UUID!) @auth(level: USER) {
  favorite_movie_upsert(data: { userId_expr: "auth.uid", movieId: $movieId })
}

# Remove a movie from the user's favorites list
mutation DeleteFavoritedMovie($movieId: UUID!) @auth(level: USER) {
  favorite_movie_delete(key: { userId_expr: "auth.uid", movieId: $movieId })
}

Or, in a familiar to-do list app, when creating a new to-do list, you could pass id_expr to instruct the server to auto-generate a UUID for the list.

mutation CreateTodoListWithFirstItem(
  $listName: String!
) @transaction {
  # Step 1
  todoList_insert(data: {
    id_expr: "uuidV4()", # <-- auto-generated. Or a column-level @default on `type TodoList` will also work
    name: $listName,
  })
}

For more information, see the _Expr scalars in the scalars reference.

Authorization data lookup queries

Data Connect mutations can be authorized by first querying the database and verifying the results of the query with CEL expressions. This is useful when, for example, you are writing to a table, and need to check the contents of a row in another table.

This feature supports:

  • The @check directive, which lets you evaluate the contents of fields, and based on the results of such evaluation:
    • Proceed with create, update and deletes defined by a mutation
    • Proceed to return the results of a query
    • Use returned values to perform different logic in your client code
  • The @redact directive, which lets you omit query results from wire protocol results.

These features are useful for authorization flows.

Equivalent SQL schema

-- Movies Table
CREATE TABLE Movies (
    movie_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    release_year INT,
    genre VARCHAR(30),
    rating INT,
    description TEXT,
    tags TEXT[]
);
-- Movie Metadata Table
CREATE TABLE MovieMetadata (
    movie_id UUID REFERENCES Movies(movie_id) UNIQUE,
    director VARCHAR(255) NOT NULL,
    PRIMARY KEY (movie_id)
);
-- Actors Table
CREATE TABLE Actors (
    actor_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(30) NOT NULL
);
-- MovieActor Join Table for Many-to-Many Relationship
CREATE TABLE MovieActor (
    movie_id UUID REFERENCES Movies(movie_id),
    actor_id UUID REFERENCES Actors(actor_id),
    role VARCHAR(50) NOT NULL, # "main" or "supporting"
    PRIMARY KEY (movie_id, actor_id),
    FOREIGN KEY (movie_id) REFERENCES Movies(movie_id),
    FOREIGN KEY (actor_id) REFERENCES Actors(actor_id)
);
-- Users Table
CREATE TABLE Users (
    user_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_auth VARCHAR(255) NOT NULL
    username VARCHAR(30) NOT NULL
);
-- Reviews Table
CREATE TABLE Reviews (
    review_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES Users(user_id),
    movie_id UUID REFERENCES Movies(movie_id),
    rating INT,
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (movie_id, user_id)
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
);
-- Self Join Example for Movie Sequel Relationship
ALTER TABLE Movies
ADD COLUMN sequel_to UUID REFERENCES Movies(movie_id);

What's next?