Design Data Connect schemas

With Firebase Data Connect, you design a GraphQL schema that represents the data model required for your application. Data Connect converts this schema to the Cloud SQL for PostgreSQL instance that backs your app. You then author queries and mutations to interface with the backend and bundle these operations into connectors for using your data from client code.

Data Connect offers AI tooling to help you design and implement your schemas. This guide introduces important concepts of schema design to support and complement your standard and AI-assisted workflows when you start developing an app, and beyond.

Note: Follow the complete series on building Data Connect schemas and connectors, which covers:

• Schema development (this guide)
• Query development
• Mutation development

The Get started guide introduced a movie review app schema for PostgreSQL.

This guide develops that schema further and provides a SQL listing equivalent to the final movie review app schema.

The schema for a movie review app

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

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

In Data Connect, you'll define GraphQL types to define the shape of the data your clients can query and manipulate. When you write your schema, your types are translated to Cloud SQL for PostgreSQL tables, most often in a direct relationship between GraphQL types and database tables, though other mappings are possible. This guide shows some examples from basic to more advanced.

Define a basic Movie type

You can start with a Movie type.

The schema for Movie 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.

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

Store important user data automatically in a User type

Your app will keep track of users, so you need a User type.

The @default directive is especially useful in this case. The id field here can automatically grab the user's ID from authentication: note the use of @default(expr: "auth.uid") in the following sample.

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

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

Key scalars and server values

Before looking more at the movie review app, it's important to 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").

Handle many-to-many relationships in Actor and MovieActor types

With users handled, you can get back to modeling movie data.

Next, you want actors to star in your movies.

The Actor table is pretty straightforward.

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

If you want actors to be in multiple movies and movies to have multiple actors, you'll need a "join table."

The MovieActor table handles the many-to-many relationship, and its primary key is a combination of [movie, actor] (the foreign key fields from movie and actor).

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

When you define a SQL relationship on the table with a foreign key constraint, Data Connect automatically generates the corresponding field on the other side. You don't need to define the reverse mapping field (e.g., from Actor back to MovieActor).

Note: Relations are defined on one side, not both! Data Connect generates names with underscores (like actors_on_movies and actors_via_actormovie) to avoid clashes with your own field names.

Handle one-to-one relationships in a MovieMetadata type

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

You can use the @ref directive to customize foreign key constraints:

• @ref(fields) specifies which foreign key fields to use.
• @ref(references) specifies the fields referenced in the target table (defaults to the primary key, but @unique fields work too). This is a more advanced option; Data Connect can often infer this for you.

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
}

Use fields generated from your schema to build operations

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

Tip: To discover the generated fields while building operations, use the query editor in the Firebase console, or our Visual Studio Code extension.

Assume your schema contains a Movie type and an associated Actor type. Data Connect generates movie, movies, actors_on_movies fields, and more.

Note: These query fields let you pass a key scalar type, here a Movie_Key, to identify records. These are created based on your schema.

Query with the
movie field

The movie field represents a single record in the Movie table.

Use this field to query a single movie by its key. query GetMovie($myKey: Movie_Key!) { movie(key: $myKey) { title } }

Query with the
movies field

The movies field represents a list of records in the Movie table.

Use this field to query multiple movies, for example, all movies with a given year. query GetMovies($myYear: Int!) { movies(where: { year: { eq: $myYear } }) { title } }

Query with the
actors_on_movies field

The actors_on_movies field represents a list of records that connect Actor and Movie tables. Use this field to query all actors associated with a given movie.

Use this field to query all actors associated with a given movie. query GetActorsOnMovie($myKey: Movie_Key!) { actors_on_movies(where: { movie: { key: { eq: $myKey } } }) { actor { name } } }

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

With this in mind, you can read how to implement operations using these fields in the guide to implementing queries and guide to implementing mutations.

Note: See the quickstart guide or local quickstart guide for more on how to discover auto-generated fields.

More advanced schema concepts

To move beyond basic but useful types and relationships, refer to examples in the reference documentation.

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.

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

Next steps

Note: Follow the complete series on building Data Connect connectors, which covers:

• Schema development (this guide)
• Query development
• Mutation development

You may be interested in:

• Generating schemas for your apps using AI assistance tools
• Reviewing the syntax reference documentation.