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
, which allows us to set operation names using thesingular
andplural
arguments@col
to explicitly set column names@default
to allow defaults to be set.
# 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.
ID type
In GraphQL, the ID
type is defined as an opaque type that is serialized as a
String. GraphQL is agnostic to ID format, but will coerce strings and integers
from input.
PostgreSQL keys are typically integers or UUIDs, not strings.
Data Connect automatically generates such keys from your schema. You
can tailor key generation with the @default
directive, as shown in the Actor
table's id
field definition: id: ID! … @default(generate: "UUID")
.
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
}
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. |
Vector | Custom | vector | vector |
- GraphQL
List
maps to a one-dimensional array.- For example,
[Int]
maps toint5[]
,[Any]
maps tojsonb[]
. - Data Connect does not support nested arrays.
- For example,
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.
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 likeeq
) andmovies
(plural, for retrieving result lists passing args likegt
and operations likeorderby
). Data Connect also generates queries for multi-table, relational operations with explicit names likeactors_on_movies
oractors_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
}
}
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
}
}
}
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 }
)
}
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?
- Learn how to call your queries and mutations from an automatically-generated web SDK, Android SDK, iOS SDK, and Flutter SDK.