Firebase Data Connect lets you create connectors for your PostgreSQLinstances managed with Google Cloud SQL. These connectors are combinations of aqueries and mutations for using your data from your schema.

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

TheGet started guide introduced a moviereview app schema for PostgreSQL.

That guide also introduced both deployable and ad hoc administrativeoperations, including queries.

• Deployable queries are those you implement to call from clientapps, with API endpoints you define. You bundle them into aconnectordeployed to the server.Data Connect tooling generates client SDKsbased on your API. Deployed queries aren't protected by IAM policy, so besure to secure them using theData Connect@auth directive.
• Ad hoc administrative queries are run from privileged environments toread data. You can create and execute them in theFirebase console or inlocal development environments using our Data Connect VS Code extension.

This guide takes a deeper look atdeployable queries.

Features ofData Connect queries

Data Connect lets you perform basic queries in all the waysyou'd expect given a PostgreSQL database.

But withData Connect's extensions to GraphQL, you can implementadvanced queries for faster, more efficient apps:

• Usekey scalars returned by many operations to simplify repeatedoperations on records
• Perform queries in the course of a multi-step mutation operations to look updata, saving lines of code and round trips to the server.

typeUser@table{id:String!@default(expr:"auth.uid")username:String!@col(dataType:"varchar(50)")}typeReview@table(name:"Reviews",key:["movie","user"]){user:User!movie:Movie!rating:IntreviewText:StringreviewDate:Date!@default(expr:"request.time")}typeMovie@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:Stringrating:Int@col(name:"rating")description:String@col(name:"description")tags:[String]@col(name:"tags")}typeMovieMetadata@table(name:"MovieMetadata"){movie:Movie!@refdirector:String@col(name:"director")}extendtypeMovie{movieMetadata:MovieMetadatamovieMetadatas_on_movie:MovieMetadata}typeMovieActors@table(key:["movie","actor"]){movie:Movie!actor:Actor!}extendtypeMovieActors{movieId:UUID!actorId:UUID!}extendtypeMovie{movieActors:[MovieActors!]!actors:[Actor!]!actors_via_MovieActors:[Actor!]!}extendtypeActor{movieActors:[MovieActors!]!movies:[Movie!]!movies_via_MovieActors:[Movie!]!}

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

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

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

Essential elements of a query

Data Connect queries are GraphQL queries with Data Connect extensions. Justas with a regular GraphQL query, you can define an operation name and a listof GraphQL variables.

Data Connect extends GraphQL queries with customizeddirectives like@auth.

So the following query has:

• Aquery type definition
• AListMoviesByGenre operation (query) name
• A single query argument, here a$genre variable ofString type
• A single directive,@auth.
• A single field,movies.

queryListMoviesByGenre($genre:String!)@auth(level:PUBLIC){movies(where:{genre:{eq:$genre}}){idtitle}}

Every query argument requires a type declaration, a built-in likeString, or acustom, schema-defined type likeMovie.

This guide will look at the signature of increasingly complex queries. You'llend by introducing powerful, concise relationship expressions you can use tobuild your deployable queries.

Key scalars in queries

But first, a note about key scalars.

Data Connect defines a specialkey scalar to represent primarykeys of each table, identified by {TableType}_Key. It is a JSON object ofprimary key values.

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

Singular automatic queries, likemovie in our running example, support a keyargument that accepts a key scalar.

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

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

{"data":{"movie":{"title":"Example Movie Title"}}}

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

{#…"variables":{"myKey":{"id":"xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx"}}}

Thanks to custom scalar parsing, aMovie_Key can also be constructed using theobject syntax, which may contain variables. This is mostly useful when you wantto break individual components into different variables for some reason.

Write basic queries

You can start writing queries to get individual records from your database, orlist records from a table with the option to limit and order results.

Retrieve individual records

The simplest query gets a single record by ID. Your query will use theauto-generatedmovie field.

queryGetMovieById($id:UUID!)@auth(level:PUBLIC){movie(id:$id){idtitleimageUrlgenre}}

{"data":{"movie":{"id":"some-uuid","title":"Example Movie Title","imageUrl":"https://example.com/movie.jpg","genre":"Action"}}}

Retrieve all records in a table

To retrieve a subset of fields for the full list of movies from theMoviestable, your query will make use of the auto-generatedmovies field, and yourimplementation might look like the following.

queryListMovies@auth(level:PUBLIC){movies{idtitleimageUrlgenre}}

{"data":{"movies":[{"id":"some-uuid","title":"Example Movie Title","imageUrl":"https://example.com/movie.jpg","genre":"Action"},{"id":"another-uuid","title":"Another Movie Title","imageUrl":"https://example.com/another-movie.jpg","genre":"Comedy"}]}}

UseorderBy,limit andoffset operators

Naturally, listing all records from a table has limited usefulness.

You can order and perform pagination on results. These arguments are acceptedbut not returned in results.

Here, the query gets the titles of the top 10 movies by rating.

queryMoviesTop10{movies(orderBy:[{rating:DESC}],limit:10){# graphql: list the fields from the results to returntitle}}

{"data":{"movies":[{"title":"Top Movie 1"},{"title":"Top Movie 2"},{"title":"Top Movie 3"}// ... other 7 movies]}}

You might have a use case for fetching rows from an offset, like movies 11-20ordered by rating.

queryMovies11to20{movies(orderBy:[{rating:DESC}],limit:10,offset:10){# graphql: list the fields from the results to returntitle}}

{"data":{"movies":[{"title":"Movie 11"},{"title":"Movie 12"},{"title":"Movie 13"}// ... other 7 movies]}}

Use aliases in queries

Data Connect supports GraphQL aliasing in queries. With aliases, yourename the data that is returned in a query's results. A singleData Connect query can apply multiple filters or other queryoperations in one efficient request to the server, effectively issuing several"sub-queries" at once. To avoid name collisions in the returned dataset, youuse aliases to distinguish the sub-queries.

Here is a query where an expression uses the aliasesmostPopular andleastPopular.

queryReviewPopularitySpread($genre:String){mostPopular:review(first:{where:{genre:{eq:$genre}},orderBy:{popularity:DESC}}),leastPopular:review(last:{where:{genre:{eq:$genre}},orderBy:{popularity:DESC}})}

{"data":{"mostPopular":[{"popularity":9}],"leastPopular":[{"popularity":1}]}}

Use query filters

Data Connect queries map to all common SQL filters and orderoperations.

Filter withwhere withorderBy operators

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

queryMovieByTopRating($genre:String){mostPopular:movies(where:{genre:{eq:$genre}},orderBy:{rating:DESC}){# graphql: list the fields from the results to returnidtitlegenredescription}}

{"data":{"mostPopular":[{"id":"some-uuid","title":"Example Movie Title","genre":"Action","description":"A great movie"}]}}

Filter by testing for null values

You can test fornull values using theisNull operator.

queryListMoviesWithoutDescription{movies(where:{description:{isNull:true}}){idtitle}}

{"data":{"movies":[{"id":"some-uuid","title":"Example Movie Title"},{"id":"another-uuid","title":"Another Movie Title"}]}}

For more operators, see theinput objects types reference guide.

Filter with value comparisons

You can use operators likelt (less than) andge (greater than or equal)to compare values in your queries.

queryListMoviesByRating($minRating:Int!,$maxRating:Int!){movies(where:{rating:{ge:$minRating,lt:$maxRating}}){idtitle}}

{"data":{"movies":[{"id":"some-uuid","title":"Example Movie Title"},{"id":"another-uuid","title":"Another Movie Title"}]}}

Filter withincludes andexcludes operators for array fields

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

The following example illustrates theincludes operator.

Data Connect supportsincludesAll,excludes,excludesAll andmore. Review all such operators for integers, strings, dates and other datatypes in the_ListFilter headings of the reference documentation.

queryListMoviesByTag($tag:String!){movies(where:{tags:{includes:$tag}}){# graphql: list the fields from the results to returnidtitle}}

{"data":{"movies":[{"id":"some-uuid","title":"Example Movie Title"}]}}

Filter with string operations and regular expressions

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

queryMoviesTitleSearch($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}}){...}}

Filter with_or,_and,_not operator logic

Use_or for more complex logic.Data Connect also supports_andand_not operators.

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

{"data":{"movies":[{"title":"Movie Title 1"},{"title":"Movie Title 2"}]}}

Write relational queries

Data Connect queries can access data based on the relationships amongtables. You can use the object (one-to-one) or array (one-to-many) relationshipsdefined in your schema to make nested queries, that is, fetch data for one typealong with data from a nested or related type.

Such queries use magicData Connect_on_ and_via syntax ingenerated read fields.

Remember to review thesample schema.

Many to one

Now look at a query to illustrate_on_ syntax.

queryMyReviews@auth(level:USER){user(key:{id_expr:"auth.uid"}){reviews:reviews_on_user{movie{name}rating}}}

{"data":{"user":{"reviews":[{"movie":{"name":"Movie Title"},"rating":5}]}}}

One to one

You can write a one-to-one query using_on_ syntax.

queryGetMovieMetadata($id:UUID!)@auth(level:PUBLIC){movie(id:$id){movieMetadatas_on_movie{director}}}

{"data":{"movie":{"movieMetadatas_on_movie":{"director":"Some Director"}}}}

Many to many

Many-to-many queries use_via_ syntax. A many-to-many query mightretrieve actors for a specified movie.

queryMoviesActors($id:UUID!)@auth(level:USER){movie(id:$id){actors:actors_via_MovieActors{name}}}

{"data":{"movie":{"actors":[{"name":"Actor Name"}]}}}

But we can write a more complex query, using aliases, to filter based onroleto retrieve actors and associated movies inmainActors andsupportingActors results. Since this is many-to-many,_via_ syntax is used.

queryGetMovieCast($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}}}

{"data":{"movie":{"mainActors":[{"name":"Main Actor Name"}],"supportingActors":[{"name":"Supporting Actor Name"}]},"actor":{"mainRoles":[{"title":"Main Role Movie Title"}],"supportingRoles":[{"title":"Supporting Role Movie Title"}]}}}

Aggregation queries

What are aggregates, and why use them?

Aggregate fields let you perform calculations on a list of results. Withaggregate 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 overcalculating them client side:

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

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

queryCountProducts{products{_count}}

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

queryCountProductsWithExpirationDate{products{expirationDate_count}}

{"products":[{"expirationDate_count":3}]}

queryNumericAggregates{products{quantityInStock_maxprice_minprice_avgquantityInStock_sum}}

{"products":[{"quantityInStock_max":20,"price_min":1.99,"price_avg":3.6566666666666666,"quantityInStock_sum":35}]}

queryDateAndTimeAggregates{products{expirationDate_maxexpirationDate_min}}

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

queryListDistinctManufacturers{products(distinct:true){manufacturer}}

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

queryCountDistinctManufacturers{products{manufacturer_count(distinct:true)}}

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

queryMostExpensiveProductByManufacturer{products{manufacturerprice_max}}

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

queryFilteredMostExpensiveProductByManufacturer{products(having:{price_max:{ge:2.99}}){manufacturerprice_max}}

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

typeProduct@table{name:String!manufacturer:Manufacturer!quantityInStock:Int!price:Float!expirationDate:Date}typeManufacturer@table{name:String!headquartersCountry:String!}

queryGetProductCount($id:UUID){manufacturers{nameproducts_on_manufacturer{_count}}}

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

You can queryenumeration fields in your schema.

Assume our enumeration is implemented as:

enumAspectRatio{ACADEMYWIDESCREENANAMORPHICIMAX"Noinformationavailableonaspectratio"UNAVAILABLE}

For example, you can list all movies formatted in your preferred aspect ratio.

queryListMoviesByAspectRatio($aspectRatio:AspectRatio!)@auth(level:PUBLIC){movies(where:{originalAspectRatio:{eq:$aspectRatio}}){idtitleimageUrlreleaseYeargenreratingtagsdescriptionotherAspectRatios}}

Filter enumeration fields

Enums have the standard filtering actions available:eq,ne,ge,gt,lt,le,innin, as well asincludes,excludes,includesAll andexcludesAll for lists.

Inorder, the greater and less than operators use the order of the enumvalues for meaningful execution. Values at the beginning of the enumare smaller than values at the end of the enum definition.

For example, the followingwhere clause would return all movies withratio less than the input aspect ratio.

• In the first example, if$aspectRatio were IMAX, then movies withratio matching ACADEMY, WIDESCREEN, and ANAMORPHIC would be selected.
• In the second example, only movies available in both ACADEMY and WIDESCREENwould be returned.

movies(where:{originalAspectRatio:{lt:$aspectRatio}})movies(where:{otherAspectRatios:{includesAll:[ACADEMY,WIDESCREEN]}})

Perform aggregation queries on enumeration fields

Only_count is supported for enums.

The following query counts the total number of movies of each aspect ratio.

queryMovieCountByOriginalAspectRatio@auth(level:PUBLIC){movies{_countoriginalAspectRatio}}

Call enumeration queries from client code

Learn how to use enumeration queries from clients, and how to design clientcalls to maximize your flexibility in managing enumerations, in the SDK guidesforiOS,Android,webandFlutter.

Write advanced queries: usequery fields to read data in multi-step operations

There are many situations in which you might want to read your databaseduring execution of a mutation to lookup and verify existing data beforeperforming, for example, inserts or updates. These options save round tripoperations and hence costs.

Data Connect supports this functionality.Seemulti-step operations.

Next steps

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

You may be interested in:

• Generating queries for your apps usingAI assistance tools
• Authorizing your queries per theauthorization guide
• Calling queries from your client code forweb,iOS,AndroidandFlutter.