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 .

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 .

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

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 scalarsare 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-manyrelationship, 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 ).

Handle one-to-one relationships in a MovieMetadata type

Now, keep track of movie directors, as well as set up a one-to-onerelationship 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.

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

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

 query 
  
 GetMovies 
 ( 
 $myYear 
 : 
  
 Int 
 !) 
  
 { 
  
 movies 
 ( 
 where 
 : 
  
 { 
  
 year 
 : 
  
 { 
  
 eq 
 : 
  
 $myYear 
  
 } 
  
 }) 
  
 { 
  
 title 
  
 } 
 } 

  
 query 
  
 GetActorsOnMovie 
 ( 
 $myKey 
 : 
  
 Movie_Key 
 !) 
  
 { 
  
 actors_on_movies 
 ( 
 where 
 : 
  
 { 
  
 movie 
 : 
  
 { 
  
 key 
 : 
  
 { 
  
 eq 
 : 
  
 $myKey 
  
 } 
  
 } 
  
 }) 
  
 { 
  
 actor 
  
 { 
  
 name 
  
 } 
  
 } 
  
 } 

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 .

More advanced schema concepts

Enumeration fields

Data Connect supports enumeration fields that map to PostgreSQL enumerated types. Enums let you quickly define a list of static, predefined values with a specific order.

To add an enum to your schema, declare the enum and its predefined values, then reference it in your type.

  enum 
  
 AspectRatio 
  
 { 
  
 ACADEMY 
  
 WIDESCREEN 
  
 ANAMORPHIC 
  
 IMAX 
  
 " 
 No 
  
 information 
  
 available 
  
 on 
  
 aspect 
  
 ratio 
 " 
  
 UNAVAILABLE 
 } 
 type 
  
 Movie 
  
 @table 
  
 { 
  
 title 
 : 
  
 String 
 ! 
  
  
 genre 
 : 
  
 String 
  
 description 
 : 
  
 String 
  
 originalAspectRatio 
 : 
  
 AspectRatio 
 ! 
  
 @default 
 ( 
 value 
 : 
  
 WIDESCREEN 
 ) 
  
 otherAspectRatios 
 : 
  
 [ 
 AspectRatio 
 !] 
  
 tags 
 : 
  
 [ 
 String 
 ] 
  
 rating 
 : 
  
 Float 
  
 imageUrl 
 : 
  
 String 
 ! 
  
 releaseYear 
 : 
  
 Int 
 } 
 

In the Movie type, we added an enum field originalAspectRatio for the aspect ratio in which the movie was filmed, and another field otherAspectRatios for a list of other available aspect ratios.

Manage changes to enumeration fields

You can add new values to your enum, but the order of the enum list is very meaningful, so insert your new values wisely. The only fully backwards-compatible change to an enum is adding a new value to the end of the list of value. Notably, inserting a new value between previously-published enums or reordering existing values changes the relative ordering when relative operators such as "less than" are used in queries. Removing or renaming values is always a backwards-incompatible change.

You should never reorder the values in the enum value list; the ordering is important as it changes how filtering is applied.

Adjustments to enum values should be done carefully so as not to break older versions of your operation or client code. When removing or renaming an enum value, be sure there are no remaining instances in your current database.

Using your enum fields in operations and in client code

Now that you've added an enum field to your schema, you can use this field in queries and client code.

Learn more about writing queries using enums, and about how to write client to permit adjustments to your enums starting in the queries guide .

Other advanced 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:) .

• 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

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

You may be interested in:

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