Implement Data Connect queries

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

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

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

• Deployable queries are those you implement to call from client apps, with API endpoints you define. You bundle them into a connector deployed to the server. Data Connect tooling generates client SDKs based on your API. Deployed queries aren't protected by IAM policy, so be sure to secure them using the Data Connect @auth directive.
• Ad hoc administrative queries are run from privileged environments to read data. You can create and execute them in the Firebase console or in local development environments using our Data Connect VS Code extension.

This guide takes a deeper look at deployable queries.

Features of Data Connect queries

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

But with Data Connect's extensions to GraphQL, you can implement advanced queries for faster, more efficient apps:

• Use key scalars returned by many operations to simplify repeated operations on records
• Perform queries in the course of a multi-step mutation operations to look up data, saving lines of code and round trips to the server.

Use generated fields to build queries

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

Essential elements of a query

Data Connect queries are GraphQL queries with Data Connect extensions. Just as with a regular GraphQL query, you can define an operation name and a list of GraphQL variables.

Data Connect extends GraphQL queries with customized directives like @auth.

So the following query has:

• A query type definition
• A ListMoviesByGenre operation (query) name
• A single query argument, here a $genre variable of String type
• A single directive, @auth.
• A single field, movies.

query ListMoviesByGenre($genre: String!) @auth(level: PUBLIC) {
  movies(where: { genre: { eq: $genre } }) {
    id
    title
  }
}

Every query argument requires a type declaration, a built-in like String, or a custom, schema-defined type like Movie.

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

Key scalars in queries

But first, a note about key scalars.

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

You retrieve key scalars as a response returned by most auto-generated read fields, 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 }
}
    

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

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

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

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.

Write basic queries

You can start writing queries to get individual records from your database, or list 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 the auto-generated movie field.

query GetMovieById($id: UUID!) @auth(level: PUBLIC) {
  movie(id: $id) {
    id
    title
    imageUrl
    genre
  }
}
    

{
  "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 the Movies table, your query will make use of the auto-generated movies field, and your implementation might look like the following.

query ListMovies @auth(level: PUBLIC) {
  movies {
    id
    title
    imageUrl
    genre
  }
}
    

{
  "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"
      }
    ]
  }
}
    

Use orderBy, limit and offset operators

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

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

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

query MoviesTop10 {
  movies(orderBy: [{ rating: DESC }], limit: 10) {
    # graphql: list the fields from the results to return
    title
  }
}
    

{
  "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-20 ordered by rating.

query Movies11to20 {
  movies(orderBy: [{ rating: DESC }], limit: 10, offset: 10) {
    # graphql: list the fields from the results to return
    title
  }
}
    

{
  "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, 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 dataset, you use aliases to distinguish the sub-queries.

Here is a query where an expression uses the aliases mostPopular and leastPopular.

query ReviewPopularitySpread($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 order operations.

Filter with where with orderBy operators

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

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

Filter by testing for null values

You can test for null values using the isNull operator.

query ListMoviesWithoutDescription {
  movies(where: { description: { isNull: true }}) {
    id
    title
  }
}
    

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

For more operators, see the input objects types reference guide.

Filter with value comparisons

You can use operators like lt (less than) and ge (greater than or equal) to compare values in your queries.

query ListMoviesByRating($minRating: Int!, $maxRating: Int!) {
  movies(where: { rating: { ge: $minRating, lt: $maxRating }}) {
    id
    title
  }
}
    

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

Filter with includes and excludes operators for array fields

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

The following example illustrates the includes operator.

Data Connect supports includesAll, excludes, excludesAll and more. Review all such operators for integers, strings, dates and other data types in the _ListFilter headings of the reference documentation.

query ListMoviesByTag($tag: String!) {
  movies(where: { tags: { includes: $tag }}) {
    # graphql: list the fields from the results to return
    id
    title
  }
}
    

{
  "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, 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}}) {...}
}

Filter with _or, _and, _not operator logic

Use _or for more complex logic. Data Connect also supports _and and _not operators.

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

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

Write relational 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, that is, 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 read fields.

Remember to review the sample schema.

Many to one

Now look at a query to illustrate _on_ syntax.

query MyReviews @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.

query GetMovieMetadata($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 might retrieve actors for a specified movie.

query MoviesActors($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 on role to retrieve actors and associated movies in mainActors and supportingActors results. Since this is many-to-many, _via_ syntax is used.

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

{
  "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. With aggregate 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 over calculating them client side:

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

Example schema for aggregates

In this section, we'll switch to a storefront example schema, which is a good for explaining how to use aggregates:

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

Simple aggregates

_count for all fields

The simplest aggregate field is _count: it returns how many rows match your query. For each field in your type, Data Connect generates corresponding aggregate fields depending on the field type.

query CountProducts {
  products {
    _count
  }
}

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

All fields have a <field>_count field, which counts how many rows have a non-null value in that field.

query CountProductsWithExpirationDate {
  products {
    expirationDate_count
  }
}

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

_min, _max, _sum, and _avg for numeric fields

Numeric fields (int, float, int64) also have <field>_min, <field>_max, <field>_sum, and <field>_avg.

query NumericAggregates {
  products {
  quantityInStock_max
  price_min
  price_avg
  quantityInStock_sum
  }
}

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

_min and _max for dates and timestamps

Date and timestamp fields have <field>_min and <field>_max.

query DateAndTimeAggregates {
  products {
  expirationDate_max
  expirationDate_min
  }
}

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

Distinct

The distinct argument lets you get all unique values for a field (or combination of fields). For example:

query ListDistinctManufacturers {
  products(distinct: true) {
    manufacturer
  }
}

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

You can also use the distinct argument on aggregate fields to instead aggregate the distinct values. For example:

query CountDistinctManufacturers {
  products {
    manufacturer_count(distinct: true)
  }
}

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

Grouped aggregates

You perform a grouped aggregate by selecting a mix of aggregate and non-aggregate fields on a type. This groups together all matching rows that have the same value for the non-aggregate fields, and calculate the aggregate fields for that group. For example:

query MostExpensiveProductByManufacturer {
  products {
  manufacturer
  price_max
  }
}

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

having and where with grouped aggregates

You can also use the having and where argument to only return groups that meet a provided criteria.

• having lets you filter groups by their aggregate fields

• where lets you filter the rows based on non-aggregate fields.

query FilteredMostExpensiveProductByManufacturer {
  products(having: {price_max: {ge: 2.99}}) {
  manufacturer
  price_max
  }
}

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

Aggregates across tables

Aggregate fields can be used in concert with generated one-to-many relationship fields to answer complex questions about your data. Here is a modified schema, with separate table, Manufacturer, we can use in examples:

  type Product @table {
    name: String!
    manufacturer: Manufacturer!
    quantityInStock: Int!
    price: Float!
    expirationDate: Date
  }

  type Manufacturer @table {
    name: String!
    headquartersCountry: String!
  }

Now we can use aggregate fields to do things like find how many products a manufacturer makes:

query GetProductCount($id: UUID) {
  manufacturers {
    name
    products_on_manufacturer {
      _count
    }
  }
}

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

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

There are many situations in which you might want to read your database during execution of a mutation to lookup and verify existing data before performing, for example, inserts or updates. These options save round trip operations and hence costs.

Data Connect supports this functionality. See multi-step operations.

Next steps

You may be interested in:

• Generating queries for your apps using AI assistance tools
• Authorizing your queries per the authorization guide
• Calling queries from your client code for web, iOS, Android and Flutter.