Data Connect schemas, queries, and mutations

Note: Firebase Data Connect is in Public Preview. Data Connect will be subject to the "Pre-GA Offerings Terms" in the Service Specific Terms section of the Google Cloud Platform Terms of Service. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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.

Tip: The complete set of GraphQL language extensions for Data Connect is documented in the language reference guide.

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.

Note: The following schema is "fully-qualified", meaning the configuration of all tables and all fields within tables are explicitly defined using Data Connect declarations. Data Connect also lets you declare your tables and fields using sensible defaults, without full specification.

Movie table

The schema for Movies contains core directives like:

• @table, which allows us to set operation names using the singular and plural arguments
• @col to explicitly set column names
• @default to allow defaults to be set.

Note: The underscore character, _, cannot be used in field names. Data Connect uses that character when it generates supplemental fields, and implicit queries and mutations.

# 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")
}

Server values and key scalars

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

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 expressions. For example, you can define a field with a timestamp applied when the field is accessed using the expression updatedAt: Timestamp! @default(expr: "request.time").

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.

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 @ref directive to define relationships.

# 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")
}

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(name: "Actors", singular: "actor", plural: "actors") {
  id: UUID! @col(name: "actor_id") @default(expr: "uuidV4()")
  name: String! @col(name: "name", dataType: "varchar(30)")
}
# Join table for many-to-many relationship for movies and actors
# The 'key' param signifies the primary key(s) of this table
# In this case, the keys are [movieId, actorId], the generated fields of the reference types [movie, actor]

type MovieActor @table(key: ["movie", "actor"]) {
  # @ref creates a field in the current table (MovieActor) 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, see: implicit.gql
  actor: Actor! @ref
  # actorId: UUID! <- this is created by the above @ref, see: implicit.gql
  role: String! @col(name: "role") # "main" or "supporting"
  # optional other fields
}

User

Lastly, users for your app.

# Users
# Suppose a user can leave reviews for movies
# user:reviews is a one to many relationship, movie:reviews is a one to many relationship, movie:user is a many to many relationship
type User
  @table(name: "Users", singular: "user", plural: "users", key: ["id"]) {
  id: UUID! @col(name: "user_id") @default(expr: "uuidV4()")
  auth: String @col(name: "user_auth") @default(expr: "auth.uid")
  username: String! @col(name: "username", dataType: "varchar(30)")
  # The following are generated from the @ref in the Review table
  # reviews_on_user
  # movies_via_Review
}

Tip: The complete set of GraphQL language extensions for Data Connect is documented in the language reference guide.

Supported data types

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

Note: If you assign a PostgreSQL serial dataType in your schema (for example, myField: Int @col(dataType: "serial")). the corresponding database column is a PostgreSQL serial (autoincrementing) type.

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.

Implicit and predefined queries and mutations

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

In your development process, you will implement predefined queries and predefined mutations based on these implicit operations.

Note: Client code can only call your predefined queries and mutations. Clients cannot call implicit queries or mutations.

Tip: To discover the generated implicit queries while building predefined queries, use our Visual Studio Code extension, either locally or in an IDX environment.

Implicit query and mutation naming

Data Connect infers suitable names for implicit queries and mutations from your schema type declarations. For example, working with a PostgreSQL source, if you define a table named Movie, the server will generate implicit:

• Queries for 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 queries for multi-table, relational operations with explicit names like actors_on_movies or actors_via_actormovie.
• Mutations named movie_insert, movie_upsert...

The schema definition language also allows you to explicitly set names for operations using singular and plural directive arguments.

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 available in implicit queries you can build on in your predefined 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 implicit mutations, 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
  }
}

Tip: The complete set of GraphQL language extensions for Data Connect is documented in the language reference guide.

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 implicit queries.

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.

# Users
# Suppose a user can leave reviews for movies
# user:reviews is a one to many relationship,
# movie:reviews is a one to many relationship,
# movie:user is a many to many relationship
type User
  @table(name: "Users", singular: "user", plural: "users", key: ["id"]) {
  id: UUID! @col(name: "user_id") @default(expr: "uuidV4()")
  auth: String @col(name: "user_auth") @default(expr: "auth.uid")
  username: String! @col(name: "username", dataType: "varchar(30)")
  # The following are generated from the @ref in the Review table
  # reviews_on_user
  # movies_via_Review
}
# Reviews
type Review @table(name: "Reviews", key: ["movie", "user"]) {
  id: UUID! @col(name: "review_id") @default(expr: "uuidV4()")
  user: User! @ref
  movie: Movie! @ref
  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
      description
    }
    dislikedMovies: movies_via_review(where: { rating: { le: 2 } }) {
      title
      genre
      description
    }
  }
}

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
    }
  }
}

Tip: The complete set of GraphQL language extensions for Data Connect is documented in the language reference guide.

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!

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.

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

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

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 }
  )
}

Tip: The complete set of GraphQL language extensions for Data Connect is documented in the language reference guide.

Equivalent SQL schema

Note: This schema does not represent the DDL or transformation of the Data Connect schema. It's provided for illustration only.

-- 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?

• Learn how to call your queries and mutations from an automatically-generated web SDK, Android SDK, iOS SDK, and Flutter SDK.