Alex Patterson for CodingCatDev

Posted on Oct 28 • Originally published at codingcat.dev

Firebase SQL with Data Connect

#webdev #javascript #beginners #podcast

Original: https://codingcat.dev/podcast/firebase-sql-with-data-connect

Introduction: Why Do We Need Firebase Data Connect?

Let’s be real: Firebase has been a go-to for fast app launches for years, but what happens when your app's logic gets complex, your data relationships grow hairy, or you want join-heavy reports, analytics, and powerful search? Google heard the pain points:

“I need relational queries and joins.”
“Can I finally use full text search, not just basic keyword matching?”
“Can my AI agents interact with my database, securely and conveniently?”
“Can I swap between secure APIs and developer-friendly tools on the fly?”

You’re not alone. That’s exactly why Firebase Data Connect was born.

By the end of this article, you’ll understand:

How Data Connect blends SQL (PostgreSQL) power with Firebase’s cloud-native ease.
Where it beats Firestore—and when you should stick with Firestore instead.
How to build secure APIs, type-safe SDKs, and connect your data to AI agents like Gemini.
How to use full text search and vector search (semantic matching) like a pro.
Practical steps to get started—even locally with emulators!

Meet Tyler Crowe & The Birth of Data Connect

Tyler Crowe isn’t just a Firebase PM—he’s been the brains behind Firebase Auth, AppCheck, and now Data Connect. In our season 5 podcast, Tyler explained:

"We built Data Connect under a single premise. You write the query, we do the rest."

The real motivation? Thousands of developers pining for the best of both worlds: Firebase’s developer joy… plus the relational might of SQL.

Want to go deeper into Tyler's journey? Listen to the full podcast episode.

What Is Firebase Data Connect?

In a nutshell: Data Connect is a fully managed, cloud-native SQL database service—built on Cloud SQL (hello, PostgreSQL!)—and deeply integrated into Firebase.

If you’ve ever wrangled cloud infrastructure, worried about scaling, or cursed at manual security configs, Data Connect is your new best friend.

Why is Data Connect Important?

Relational Power: Complex queries, joins, and analytics—finally possible!
No Infrastructure Headaches: Just click, deploy, and go. You never touch a server.
Secure by Design: Auth, AppCheck, and access directives built in.
AI-Ready: Schema and queries can be generated by AI agents like Gemini.
Future Proof: Roadmap includes support for other data sources beyond Cloud SQL.

Highlights

Built on Cloud SQL PostgreSQL
Type-Safe SDKs auto-generated for Android, iOS, and web
Integrated with Firebase Auth and AppCheck
Out-of-the-box support for Full Text Search and Vector Search
Agentic workflows for AI agents and smart queries

Want a more hands-on intro? Check out our beginner’s guide to Firebase.

Data Connect vs Firestore: The Relational Revolution

Let’s break down a classic developer question:

“Should I stick with Firestore, or do I need Data Connect?”

Why This Matters

Firestore (or the classic Realtime Database) was built for real-time sync, offline-first experiences, and mobile scale. But… when your data needs get complex—think relational joins, analytics, extensions—Firestore just isn’t enough.

Data Connect Brings You:

The ease of Firebase SDKs
Pure SQL power (JOINs, aggregation, constraints)
Deep API and security control
Analytics and full text search at scale

Firestore is still perfect for:

Real-time sync
Offline-first mobile apps
Simple, single-table queries

Not sure which is best for you? Check out our full breakdown: Data Connect vs Firestore.

Type-Safe SDKs and Secure API Endpoints

Here’s the developer dream: define a query, get a secure API endpoint—and a fully typed SDK that plugs right into your frontend.

Why Is Type Safety So Powerful?

Type-safe SDKs cut debugging hassles, let your IDE catch mistakes before runtime, and speed up development with auto-completion.

How Data Connect Does It

When you save a query/mutation, Data Connect generates an SDK for your chosen platform. That SDK ensures type safety, authentication, and converts your calls into secure, backend-executed SQL.

Example: Querying Movie Reviews in TypeScript

import { queryReviewsForMovie } from './sdk/movieReviews';
const reviews = await queryReviewsForMovie({ movieID: 'MOVIE123' });

This fetches reviews securely, using the backend’s query definition and type validation.

AI Agents & Schema Generation with Gemini

Here’s one for the future: Let AI design your database. With Gemini, Data Connect enables schema and query generation straight from natural language.

Why Does This Matter?

Instead of fiddling with table structures, just describe your app in plain English and watch Gemini build a solid schema—saving you hours.

Example Workflow

Describe Your App
- "I need a movie review app with reviews, movies, actors, and users."
Gemini Generates SQL

# Movies
# TODO: Fill out Movie table
type Movie
  @table {
  id: UUID! @default(expr: "uuidV4()")
  title: String!
  imageUrl: String!
  releaseYear: Int
  genre: String
  rating: Float
  description: String
  tags: [String]
}

# Movie Metadata
# Movie - MovieMetadata is a one-to-one relationship
# TODO: Fill out MovieMetadata table
type MovieMetadata
  @table {
  # @ref creates a field in the current table (MovieMetadata)
  # It is a reference that holds the primary key of the referenced type
  # In this case, @ref(fields: "movieId", references: "id") is implied
  movie: Movie! @ref
  # movieId: UUID <- this is created by the above @ref
  director: String
}

# 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
# TODO: Fill out Actor table
type Actor @table {
  id: UUID!
  imageUrl: String!
  name: String! @col(name: "name", dataType: "varchar(30)")
}

# Join table for many-to-many relationship for movies and actors
# The 'key' param signifies the primary key(s) of this table
# In this case, the keys are [movieId, actorId], the generated fields of the reference types [movie, actor]
# TODO: Fill out MovieActor table
type MovieActor @table(key: ["movie", "actor"]) {
  # @ref creates a field in the current table (MovieActor) that holds the primary key of the referenced type
  # In this case, @ref(fields: "id") is implied
  movie: Movie!
  # movieId: UUID! <- this is created by the implied @ref, see: implicit.gql

  actor: Actor!
  # actorId: UUID! <- this is created by the implied  @ref, see: implicit.gql

  role: String! # "main" or "supporting"
}

# Users
# Suppose a user can leave reviews for movies
# user-reviews is a one to many relationship, movie-reviews is a one to many relationship, movie:user is a many to many relationship
# TODO: Fill out User table
type User
  @table {
  id: String! @col(name: "auth_uid")
  username: String! @col(dataType: "varchar(50)")
  # The following are generated from the @ref in the Review table
  # reviews_on_user
  # movies_via_Review
}

# Join table for many-to-many relationship for users and favorite movies
# TODO: Fill out FavoriteMovie table
type FavoriteMovie
  @table(name: "FavoriteMovies", singular: "favorite_movie", plural: "favorite_movies", key: ["user", "movie"]) {
  # @ref is implicit
  user: User!
  movie: Movie!
}

# Reviews
# TODO: Fill out Review table
type Review @table(name: "Reviews", key: ["movie", "user"]) {
  id: UUID! @default(expr: "uuidV4()")
  user: User!
  movie: Movie!
  rating: Int
  reviewText: String
  reviewDate: Date! @default(expr: "request.time")
}

Tweak & Refine On the Fly
- Add constraints, relationships, or new features (“Let’s add a chat!”).

This isn’t just a parlor trick—schema quality is rock solid. Want to try it? Play with Gemini in this hands-on codelab.

Building Your First Data Connect App

Let’s roll up our sleeves. Setting up Data Connect is designed to be painless.

Why Building Should Be Instant

No one wants to spend hours on infrastructure just to test an idea. Data Connect delivers with fast onboarding, ephemeral databases for prototyping, and instant querying.

Step-by-Step Setup

Start a New Service in Studio Open Firebase Studio.
Design Schema with Gemini or Wizard Let Gemini handle heavy lifting, or design schema manually.
Deploy to Cloud SQL Choose ephemeral (for prototyping) or persistent DB.
Seed & Query Instantly Input demo data, run live queries, modify as needed.

Get inspired by our movie review app walkthrough.

Browsing Data: Table vs JSON Views

Data Connect gives you classic SQL table views and old-school JSON blobs.

Table View: Rows and columns (easy for relational reasoning)
JSON View: Structured for mutation/debugging

Tip: Vector embeddings (for AI search) show as massive numeric fields—expand for details.

Lightning-Fast Full Text Search

Trust me, this feature is a game-changer. Powered by PostgreSQL, you can search text in ways that Firestore could only dream about.

Why Full Text Search?

Users expect robust search (not just basic string matching)
Multi-field queries (title + description, for example)
Natural language and advanced operators (AND, OR, NOT)

How to Enable Full Text Search

Annotate fields in your schema:

table movies {title TEXT @searchable,description TEXT @searchable,...}

Then craft your query:

query searchMovies($query: String!) {movies_search(query: $query) {...MovieOverview}}

Modes

Plain: General terms
Phrase: Exact phrase
Query: Operators for advanced search (love -potion gets results with “love” but not “potion”)

Check out how full text search works in detail.

Vector Search: Semantic Search for Real Apps

Let’s take your search to the next level—semantic matching with vector embeddings powered by Vertex AI.

Why Go Semantic?

Full text search is great, but what if users type synonyms or phrases? Vector search returns results that mean the same thing, not just match words.

How It Works

Data (titles, descriptions) is fed to an embedding model (text-embedding-gecko-005)
Embeddings live in a vector field (often 768 dimensions!)
At query time, your search input is vectorized and compared for cosine similarity

Example: Vector Search Query

query searchMovies($query: String!) {movies_vectorSearch(embed: $query, model: "text-embedding-gecko-005", topK: 10) {titledescriptionscore}}

This finds “Love Potion” when you search “Love Tonic”—because meaning matters!

Defining Queries & Mutations: Making Your API

Here’s the “internals”: you define your queries and mutations (“operations”). Every operation is tracked with security, parameters, and API surface exposure.

Example: Add a Review Mutation

mutation addReview($movieId: String!, $userId: String!, $review: String!) {addReview(movieId: $movieId, userId: $userId, review: $review) {idreview}}

This gives frontend devs type safety and full API clarity.

Generated SDKs: Streamlining Frontend Integration

Once you’ve built your backend, integrating it is instant just hit “Download SDK” and roll into any modern frontend. Or have it build out your own connector.

Example:

// Example using a React component
import React, { useState, useEffect } from 'react';
import { initializeApp } from 'firebase/app';
import { getFirebaseConfig } from './firebase-config'; // Your Firebase config
import { SearchMovies } from '@your-project-name/your-connector-name'; // Generated SDK import

function MovieSearch() {
  const [searchTerm, setSearchTerm] = useState('');
  const [movies, setMovies] = useState([]);

  useEffect(() => {
    initializeApp(getFirebaseConfig());
  }, []);

  const handleSearch = async () => {
    try {
      const result = await SearchMovies({ query: searchTerm });
      setMovies(result.movies_search);
    } catch (error) {
      console.error("Error searching movies:", error);
    }
  };

  return (
    <div>
      <input
        type="text"
        value={searchTerm}
        onChange={(e) => setSearchTerm(e.target.value)}
        placeholder="Search movies..."
      />
      <button onClick={handleSearch}>Search</button>

      <div>
        {movies.map((movie) => (
          <div key={movie.id}>
            <h3>{movie.title} ({movie.releaseYear})</h3>
            <p>{movie.genre}</p>
            <p>{movie.description}</p>
          </div>
        ))}
      </div>
    </div>
  );
}

export default MovieSearch;

Fully typed SDKs for React, Next.js, Vue, Angular, and more!
AI agents (like Gemini) plug in seamlessly.

Security: Auth, Row-Level Access, and AppCheck

Security isn’t just a checkbox—it’s part of every query and mutation in Data Connect.

Why Query-Level Security?

Instead of hand-writing complex security rules at the collection/table level, Data Connect lets you lock down each operation directly.

Key Directives:

@user: Requires authentication
@public: Open access
Expression-based (e.g. admin role, claims)

Example: Secure Mutation Directive

mutation addReview($uid: String!) @auth(uid != nil && auth.provider != 'anonymous') {...}

Row-level security: operations filter access using user IDs, roles, and more
AppCheck compatibility—even for APIs like Stripe!

Local Development and Emulators

Yes—you can develop and test everything locally!

Emulator spins up with PG Lite
Seed and mutate data with demo apps in VS Code
Test all queries, mutations, operations, and security

Start Data Connect emulator:

firebase emulators:start --only data-connect

GraphQL Everywhere: Why It Matters

It’s official: Data Connect’s API is GraphQL-powered. Why should you care?

Why GraphQL?

Ask for just what you need: No overfetching or underfetching
Joins, fragments, reusable types: Compose powerful queries quickly
Strong types: IDE/codegen support, easy refactoring
Security at query layer: Fewer surprises

Example: Homepage Query With Fragments

query homepage {newReleases { ...MovieOverview }topMovies { ...MovieOverview }recentReviews { ...ReviewFragment }movie { ...MovieOverview }}

Learning curve? Sure! But flexibility and power make it worthwhile.

Data Connect vs Firestore: How to Choose

Here’s Tyler Crowe’s advice:

“If you need relational joins, heavy analytics, or want to use Postgres extensions—pick Data Connect.

If you need real-time sync, offline support, or fast mobile-first stuff, Firestore is your bread and butter.”

Quick Guide

Firestore:

Real-time sync, offline support
Massive scale
Simple queries
Mobile-first

Data Connect:

Complex queries, analytics
Relational schemas
Joins/aggregations
Full text & vector search
Web or cross-platform workloads

Still unsure? Ask Gemini right in Studio:

"Should I use Data Connect or Firestore for my app?"

What’s Next: Features Coming Soon

The Data Connect team is just getting started. Look out for:

RAW SQL queries (not just views!)
More advanced backend caching/performance tuning
Custom MCP server support for agentic workflows
Multi-data source (not limited to Cloud SQL)
Deeper AI integrations

Stay updated via the CodingCat.dev blog.

Conclusion & Further Learning

There’s never been a better time to be a Firebase developer. Data Connect bridges the world of enterprise-grade SQL and developer-friendly, cloud-native workflows. Building secure apps, powerful queries, and integrating AI is finally within reach—whether you’re launching a startup or scaling to millions.

Key Takeaways:

Data Connect brings SQL, security, and AI together in Firebase.
You can use type-safe SDKs and GraphQL-powered APIs without fighting your infrastructure.
Full text and vector search make your data "smarter" than ever.
Security and access controls are baked in, not bolted on.

🚀 Subscribe to youtube.com/codingcatdev for weekly web dev, Firebase, and AI content!

Now, I’d love to hear from you:

What would you build with Firebase Data Connect? What’s your dream feature or integration?

Drop your ideas in the comments, tweet @CodingCatDev

Happy codingcatting for perfect peeps ✨

Subscriptions

Are you looking to boost your products reach? Have Alex create a video for you https://codingcat.dev/sponsorships

References & More Links

Note:

All code samples and workflows described here are for educational/demo purposes. Please consult the Firebase documentation for production best practices and latest updates.

This post is part of our deep dive series on Firebase. Major thanks to Tyler Crowe and the Google engineering team for moving web development into the AI-powered future!