↑ ↓ navigate↵ selectesc close

Aggregate Fields for Data Connect in Firebase: Web Architecture & Implementation Guide

CodingCat.dev tutorial cover image
Alex Patterson
Alex Patterson

For years, choosing Firebase meant trading away relational SQL joins, strict foreign keys, and pgvector similarity search in favor of NoSQL document denormalization. Following the announcement of Aggregate Fields for Data Connect by Andrea Wu on the Official Firebase Blog (2025-04-24), here is our hands-on CodingCat.dev breakdown of what actually shipped, how it works under the hood, and how we wire it into production TypeScript, Next.js, Astro, SvelteKit, React, and Angular applications. Note: Firebase Data Connect has been renamed to SQL Connect. Learn more in the SQL Connect announcement.

The Mental Model: Why Aggregate Fields for Data Connect Matters for Web Devs

As a Google Developer Expert (GDE) for Firebase and Senior Solutions Engineer at CodingCat.dev, I evaluate every Firebase update through one lens: does it reduce boilerplate while keeping our runtime security and cloud bills predictable? Firebase Data Connect bridges that gap by pairing a managed Google Cloud SQL for PostgreSQL instance with a declarative GraphQL schema compiler that automatically generates type-safe TypeScript, React, and Angular SDKs alongside server-enforced @auth directives.

AJ Pro-Tip from Alex: Keep your authorization logic inside your Data Connect GraphQL operations using @auth(level: USER) and server-evaluated expressions like auth.uid and request.time rather than trusting client-supplied user IDs in query variables.

How It Works: Technical Breakdown & Implementation

When you have a lot of data, you might need to perform calculations on a list of results. Firebase Data Connect already supports @view for you to write SQL to do these, but In this update, the Firebase team announced that you can now use aggregate fields to do the same calculations.

For each field in your schema, Data Connect generates corresponding aggregate fields depending on the field type. These are the supported aggregates, and this blog post dives deeper into each one: _count: returns the number of rows that match your query.

  • All fields have a <field>_count field, which counts how many rows have a non-null value in that field.
  • Numeric fields (int, float, int64) have <field>_min, <field>_max, <field>_sum, and <field>_avg.
  • Date and timestamp fields have <field>_min and <field>_max.
  • distinct argument: get all unique values for a field (or combination of fields). You can also use distinct on aggregate fields to instead aggregate the distinct values.
  • Grouped aggregates: selecting a mix of aggregate and non-aggregate fields on a type applies the aggregate fields to split by each distinct combination of values for non-aggregate fields. where and having arguments return only groups that meet the provided criteria. where lets you filter rows based on non-aggregate fields and having lets you filter groups by aggregate fields.

Movie Example App

To see how each of these would have been written with @view versus how much less code you can now write using aggregates, we’ll use the running movie review app example you might already know from the documentation and the Everything Data Connect deep-dive blog series. We also cover some cases in which you still need to use @view for more complex aggregates.

As a review, these are the relevant tables:

type
  id: UUID! @col(name: "movie_id") @default(expr: "uuidV4()")
  title: String!
  releaseYear: Int
  genre: String @col(dataType: "varchar(20)")
  rating: Int
  description: String
}

type
  user: User!
  movie: Movie!
  rating: Int
  reviewText: String
  reviewDate: Date! @default(expr: "request.time")
}

Count

For the movie app, let’s say you want to show the number of movie titles available in the app.

type
    SELECT COUNT(*) FROM movie AS movies_count
""") {
  moviesCount: Int @col(name: "movies_count")
}

query MovieCount @
    movieCount: 
        moviesCount
    }
 }

Instead, you can use the _count aggregate field, which returns the number of rows that match your query.

query MovieCount {
  movies: {
    _count
  }
}

Calling the query returns the number of rows in the Movie table, giving you the number of movies in your database.

You eliminate the need to define a @view in the type, and you directly use an aggregate field to get the value your app needs. This goes for all aggregate fields, and just for brevity, the rest of this blog post will not cover the query needed to use the @view defined in the type, because those queries will look very similar to the one shown in this example.

Now that you know how many movies your database has, you may want to find out how many movies in your database have a rating. This way, you can gauge if it’s worth the effort to create an incentive structure for users to give ratings and write reviews for non-rated movies.

type
    (SELECT COUNT(rating) FROM movie) AS ratings_count
""") {
  moviesCount: Int @col(name: "ratings_count")
}

All fields have a <field>_count field, which counts how many rows have a non-null value in that field. Using the <field>_count field, the above code becomes:

query RatingsCountView {
  movies: {
    rating_count
  }
}

Numeric Fields

You’re next interested in seeing the oldest and newest movies in your database by release year, along with the average release year of all of the movies. This way, you know if your database is biased towards newer or older movies.

type
    (SELECT MIN(releaseYear) FROM movie) AS min_release_year
    (SELECT MAX(releaseYear) FROM movie) AS max_release_year
    (SELECT AVG(releaseYear) FROM movie) AS avg_release_year
""") {
  minReleaseYear: Int @col(name: "min_release_year")
  maxReleaseYear: Int @col(name: "max_release_year")
  avgReleaseYear: Int @col(name: "avg_release_year")
}

All fields regardless of type have <field>_count as mentioned above. Numeric fields (int, float, int64) have additional fields: <field>_min, <field>_max, <field>_sum, and <field>_avg.

For this example here, you can use <field>_min, <field>_max, and <field>_avg:

query OldestAndNewestMovies {
  movies: {
   releaseYear_min
   releaseYear_max
   releaseYear_avg
  }
}

Dates and Timestamps

You’re intrigued by oldest and newest, so next, you’re curious about finding the oldest and newest reviews.

type
    SELECT MIN(reviewDate) as min_review_date, MAX(reviewDate) as max_review_date FROM review
""") {
  minReviewDate: Date @col(name: "min_review_date")
  maxReviewDate: Date @col(name: "max_review_date")
}

Date and timestamp fields have <field>_min and <field>_max, so using those:

query DateAndTimeMinMax {
  reviews: {
   reviewDate_min
   reviewDate_max
  }
}

Distinct

You’re done fiddling around with years and dates, and as a potential movie buff, you want to find all of the unique movie genres to see if there are any categories you don’t know about!

type
    SELECT DISTINCT genre FROM movies AS genres
""") {
  genresList: [String] @col(name: "genres")
}

The distinct argument gets all unique values for a field or combination of fields:

query ListDistinctGenres {
  movies
    genre
  }
}

Less code again! You might be seeing a trend here…

You notice a long list of unique movie genres, and of course as a coder, you can’t manually count them! So of course, you write some code to see how many there are.

type
    SELECT COUNT(DISTINCT genre) FROM review AS genres_count
""") {
  genresCount: Int @col(name: "genres_count")
}

You can also use distinct on aggregate fields to instead aggregate the distinct values:

query CountDistinctGenres {
  movies: {
    genre_count
  }
}

WHERE clause You know that in public media, horror movies are often controversial. Do people like them or not like them? Well, you happen to have all of this data on movies, so you figure out the answer once and for all by finding the average rating of horror movies.

type
    SELECT AVG(rating) as average_rating FROM movies 
    WHERE genre
    GROUP
"""
  avgRating
}

You can use where to filter rows before they’re aggregated:

query FilteredBestRated {
  movies
   rating_avg
  }
}

Are you reading my mind? “Less code yet again” - absolutely!

Grouped Aggregates

Now that you know the average rating for horror movies, you’re wondering how that ranks in genres generally, so you look for the average rating per genre. You need to group all the genres together, then find the average per each genre.

type
    SELECT genre, AVG(rating) as average_rating FROM movies
    GROUP BY genre
""") {
  genre: String? @col(name: "genre")
  rating_avg: Float @col(name: "average_rating")
}

Grouped aggregates let you select a mix of aggregate and non-aggregate fields on a type, and it will apply the aggregate fields to split by each distinct combination of values for non-aggregate fields. Using grouped aggregates:

query AvgRatingPerGenre {
  reviews {
   genre
   rating_avg
  }
}

# If there are genres a, b, and c, an example result is: 
[{movie: {genre: a}, rating_avg: 
 {movie: {genre: b}, rating_avg: 
 {movie: {genre: c}, rating_avg:

HAVING clause

You want to know which genres are especially popular, so you look for genres with an average rating of above 85.

type
    SELECT genre, AVG(rating) as average_rating FROM movies 
    HAVING(AVG(rating) > 85) 
    GROUP BY genre
""") {
  genre: String? @col(name: "genre")
  rating_avg: Float @col(name: "average_rating")
}

having lets you filter groups by aggregate fields:

query HighlyRatedGenres {
  reviews
   genre
   rating_avg
  }
}

# If there are genres x, y, and z, an example result is: 
[{genre: x, rating_avg: 
 {genre: y, rating_avg: 
 {genre: z, rating_avg:

We can hear you chanting “less code, less code, hip hip hooray!”

Cross-Table Aggregates

You then ponder how your favorite movie’s rating is. Do you really want to know though, what if it’s low? After much deep consideration, you decide to go ahead with finding the answer.

type
   SELECT m.title as title, AVG(r.rating) as average_rating
   FROM review as r
   JOIN movies as m on review.movie
   WHERE
"""
  genre
  rating_avg
}

query 
    movie
        title
        reviews_on_movie {
        rating_avg
        }
    }
}

It took some time and extra brainpower to write that SQL in the view, but using aggregates, hopefully it uses less:

query 
    movie
        title
        reviews_on_movie {
            rating_avg
        }
    }
}

Complex Aggregates

Although aggregate fields are great for performing a lot of calculations you could need in your app, you may still need to use @view and SQL for complex or nested aggregates. For example, if you wanted to find the average number of reviews for a movie in each genre:

type
    SELECT genre, AVG(COUNT(r)) as average_number_of_reviews
    FROM movies as m
    JOIN reviews as r on r.movie 
    GROUP
"""
  genre
  averageReviewCount
}

Benefits of Aggregates

Aside from the much less code you can write using aggregates over a view and SQL, aggregates are performed server side. This 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

Production Web Architecture & Security Checklist

Whether we are deploying on Firebase App Hosting, Cloudflare Workers, or Vercel, shipping SQL features to production requires defense-in-depth across three layers:

  • Enforce Firebase App Check & Attestation: Protect your web endpoints, Cloud Functions, Data Connect GraphQL resolvers, and Firebase AI Logic calls against unauthorized replay or billing abuse using Firebase App Check with reCAPTCHA Enterprise.
  • Validate Locally with the Firebase Emulator Suite: Run deterministic integration tests against local Firestore, Auth, Functions, and Data Connect PostgreSQL emulators before promoting schema or rule changes to production.
  • Keep Server & Client Boundaries Type-Safe: Use the modular Firebase Web SDK and generated TypeScript bindings so SSR frameworks like Next.js, Astro, SvelteKit, and Angular serialize state cleanly without hydration flickers.

Conclusion

Accelerate your full-stack web and AI development by pairing Aggregate Fields for Data Connect with strict Security Rules, Firebase App Check, and local emulator testing. Come tell us on X (@CodingCatDev) and LinkedIn (CodingCat.dev) how you are using Firebase in your stack, and subscribe to the CodingCat.dev YouTube Channel so you don't miss what we build next!

Watch Our Related Firebase Video Tutorial on YouTube

Want to see how we architect and debug Firebase web applications live? Watch our related CodingCat.dev session Firebase SQL with Data Connect below, and make sure to Subscribe to CodingCat.dev on YouTube for weekly full-stack web and AI deep dives:

Resources, Official Docs & Related CodingCat.dev Guides

Frequently Asked Questions (FAQ)

What are the key capabilities introduced in Aggregate Fields for Data Connect?

Aggregate Fields for Data Connect (announced 2025-04-24 by the Firebase team) enhances the platform for Firebase, Google Cloud, Web Dev, SQL, TypeScript workloads. Note: Firebase Data Connect has been renamed to SQL Connect. Learn more in the SQL Connect announcement. Developers can review the primary announcement on the Firebase Blog alongside our implementation patterns and production checklist above.

How do web developers use Aggregate Fields for Data Connect with Next.js, SvelteKit, React, or Angular?

Web developers can integrate these features using the modular Firebase JavaScript/TypeScript SDK, Firebase Data Connect web bindings, and Firebase App Hosting. Always pair client-side SDK initialization with Firebase App Check and strict Security Rules, and test locally with the Firebase Local Emulator Suite before deploying.

Where can I watch video tutorials on Firebase Web and AI development?

You can watch our hands-on Firebase video walkthroughs on the CodingCat.dev YouTube Channel, including Firebase SQL with Data Connect, and follow us on X (@CodingCatDev) for weekly full-stack tutorials.