Build a Realtime Trading App with Firebase SQL Connect (web)

1. Before you begin

In this codelab, you'll integrate Firebase SQL Connect with a Cloud SQL database to build Friendly Exchange, a realtime emoji stock market web app.

The completed app showcases advanced SQL Connect features, including:

Native SQL: Execute complex Data Manipulation Language (DML) statements and Common Table Expressions (CTEs) securely using _execute and _select.
SQL Views: Create strict, type-safe GraphQL objects backed by dynamic Postgres queries using the @view directive.
Realtime Subscriptions: Keep your frontend UI in sync using @refresh triggers.
Atomic Transactions: Chain multiple operations and validate state using @transaction and @check.
(Optional) Geospatial & Vector Search: Leverage PostGIS and pgvector to find trending assets near a user's coordinates and perform semantic searches.
(Optional) Custom Resolvers: Connect custom Cloud Run logic to your GraphQL schema to generate AI trade headlines.

Prerequisites

You'll need a solid understanding of JavaScript/TypeScript, React, and basic SQL syntax.

What you'll learn

How to use Native SQL to bridge the gap between declarative GraphQL and raw PostgreSQL logic.
How to integrate Postgres extensions such as PostGIS directly into your database queries.
How to enforce complex logic using atomic @transaction blocks.
How to create type-safe @views for leaderboards and statistics.
How to set up realtime subscriptions using @refresh.

What you'll need

Git
Visual Studio Code
Install Node.js
A Firebase project on the pay-as-you-go Blaze pricing plan (required for Custom Resolvers and Vertex AI).

2. Set up your development environment

This stage guides you through setting up the frontend and configuring your Cloud SQL instance for advanced features.

Clone the project repository and install the required dependencies for the app:

git clone https://github.com/firebaseextended/codelab-dataconnect-web
cd codelab-dataconnect-web
git switch emoji-init
npm install

Open the cloned folder using Visual Studio Code and install the Firebase SQL Connect Visual Studio Extension.
In your terminal, ensure your Firebase CLI is completely up to date (this is necessary for new features like @refresh and Native SQL):

npm uninstall -g firebase-tools
npm install -g firebase-tools
firebase login
firebase use your-project-id
firebase init

(Select Hosting, Authentication, and SQL Connect).

Generate SQL Connect SDKs: Run the command:

firebase dataconnect:sdk:generate

Connect your web app to your Firebase project: Register your web app in your Firebase project using the Firebase console:
1. Open your project, and then click Add App (select the Web icon).
2. Ignore the SDK setup and configuration setup for now, but make sure to copy the generated firebaseConfig object.
3. Open lib/firebase.tsx in your code editor and replace the existing placeholder with the configuration you just copied:

const firebaseConfig = {
  apiKey: "API_KEY",
  authDomain: "PROJECT_ID.firebaseapp.com",
  projectId: "PROJECT_ID",
  storageBucket: "PROJECT_ID.firebasestorage.app",
  messagingSenderId: "SENDER_ID",
  appId: "APP_ID"
};

Run the development server:

npm run dev

3. Review the starter codebase

In this section, you'll explore key areas of the app's starter codebase. While you'll be writing the schema and queries from scratch, it's helpful to understand how the frontend is wired to interact with SQL Connect.

Folder and file structure

The `dataconnect/` directory

This folder contains your backend definition—everything from your database structure to the specific SQL queries your app is allowed to run.

schema/schema.gql: Where you will define your base Postgres tables using standard GraphQL types.
schema/views.gql: Where you will define complex, read-only SQL views (like leaderboards) using the @view directive.
friendly-exchange/queries.gql & mutations.gql: Your "connectors". This is where you will define the exact queries and Native SQL (_execute, _select) allowed by your app.
dataconnect.yaml: The configuration file that dictates SDK generation and Cloud SQL deployment settings.

The `lib/` directory

Contains the application logic, authentication, and interaction with the Firebase SQL Connect SDK.

firebase.tsx: Handles the initialization of your Firebase app, Auth, and SQL Connect instance.
ExchangeService.tsx: This is the bridge between your React components and the database. It wraps the generated SDK functions (like buyStock or sellStock) in standard async functions to handle error catching, business logic, and toast notifications.

The Generated SDK

When you write a query or mutation in SQL Connect, the VS Code extension automatically generates a strongly-typed SDK. In this project, the frontend imports these functions directly from @dataconnect/generated.

4. Define a schema for your emoji exchange

In this section, you'll define the structure and relationships between the key entities in the trading application. Entities such as User, Emoji, StockOwnership, Event, and PriceHistory are mapped to database tables, with relationships established using Firebase SQL Connect and GraphQL schema directives.

Once this schema is in place, your app will be ready to handle everything from executing buy/sell transactions and updating global leaderboards to mapping local geospatial trends.

Core entities and relationships

Emoji: Holds key details like the symbol, name, price, and trend, which the app uses to display the market.
User: Tracks the trader's profile, available points (currency), and geographic coordinates for local radar scanning.
Relationships: The StockOwnership join table tracks exactly how many shares a specific user owns of a particular emoji. The Event and PriceHistory types serve as immutable ledgers, logging market impacts and historical price points over time.

Set up the User table

The User type defines a trader in the system, keeping track of their balance, role, and physical location for geospatial queries.

Copy and paste the following code snippet into your dataconnect/schema/schema.gql file:

# Users
# user-stockOwnership is a one-to-many relationship, user-events is a one-to-many relationship
# Utilizes the Firebase Auth uid expression as the primary key
type User @table {
  id: String! @default(expr: "auth.uid")
  username: String!
  profileImage: String
  role: String! @default(value: "USER")
  points: Float! @default(value: 100.0)
  city: String @default(value: "Las Vegas")
  latitude: Float @default(value: 36.1699)
  longitude: Float @default(value: -115.1398)
}

Key Takeaways:

id: Binds directly to Firebase Authentication using @default(expr: "auth.uid"). This ensures the database identity and the Auth identity are securely 1:1, preventing users from spoofing IDs.
points: The virtual currency used to trade, defaulting to 100.0 for new users.

Set up the Emoji table

The Emoji type defines the main asset being traded, including fields for standard text search.

Copy and paste this code snippet into your dataconnect/schema/schema.gql file:

# Emojis
# emoji-stockOwnership is a one-to-many relationship, emoji-priceHistory is a one-to-many relationship
# Implements @searchable directives for full-text search
type Emoji @table {
  id: UUID! @default(expr: "uuidV4()")
  symbol: String!
  name: String! @searchable
  tags: [String!]
  description: String! @searchable
  currentPrice: Float! @default(value: 10.0)
  trend: Float! @default(value: 0.0)
}

Key Takeaways:

name & description: Uses the @searchable directive to optimize these columns for standard full-text search.

Set up the StockOwnership table

The StockOwnership type is a join table that handles many-to-many relationships between users and the emojis they own. Copy and paste this snippet into your dataconnect/schema/schema.gql file:

# Join table for many-to-many relationship between users and emojis
# The 'key' param signifies the primary key(s) of this table
# In this case, the keys are [user, emoji], the generated fields of the reference types
type StockOwnership @table(key: ["user", "emoji"]) {
  user: User!
  emoji: Emoji!
  shares: Int! @default(value: 0)
}

Key Takeaways:

key: ["user", "emoji"]: Creates a composite primary key. A user cannot have two separate records for the same emoji; it enforces uniqueness per pair.
Implicit References: By referencing the User and Emoji types directly, SQL Connect automatically generates the foreign keys userId: String! and emojiId: UUID! behind the scenes.

Set up the Event and PriceHistory tables

These types represent the ledger of the application, logging exactly what happened and how prices changed. Copy and paste the final snippets into your dataconnect/schema/schema.gql file:

# Events
# Event-User is a many-to-one relationship, Event-Emoji is a many-to-one relationship
# Evaluates the createdAt timestamp purely on the server side using the request.time expression
type Event @table {
  id: UUID! @default(expr: "uuidV4()")
  user: User!
  emoji: Emoji!
  impact: Float!
  description: String!
  createdAt: Timestamp! @default(expr: "request.time")
}

# Price History
# PriceHistory-Emoji is a many-to-one relationship
type PriceHistory @table {
  id: UUID! @default(expr: "uuidV4()")
  emoji: Emoji!
  price: Float!
  recordedAt: Timestamp! @default(expr: "request.time")
}

Key Takeaways:

createdAt & recordedAt: Automatically set to the exact time the database transaction occurs using @default(expr: "request.time"). This prevents clients from manipulating timestamps.

Auto-generated fields and defaults

The schema relies on expressions like @default(expr: "uuidV4()") and @default(expr: "auth.uid") to automatically generate unique IDs and enforce ownership without requiring the client application to supply them.

5. Retrieve market and user data

In this section, you will insert mock market data into your database, then implement the connectors (queries) and the TypeScript code to call these connectors across the web application. By the end, your app will be able to dynamically fetch and display the live emoji market, user profiles, and leaderboards directly from the database.

Insert mock market and user data

In VSCode, open dataconnect/seed.gql.
Ensure the emulators in the Firebase SQL Connect extension are running (or your Cloud SQL instance is connected).
You should see a Run (local) or Run (Production) CodeLens button at the top of the file. Click this to insert the mock emoji data and initial price histories into your database.
Check the SQL Connect Execution terminal to confirm that the data was added successfully.

Implement basic queries

First, let's query the standard tables you defined in your schema.

Open dataconnect/friendly-exchange/queries.gql.
Add the following queries to retrieve the dashboard data, user profiles, and basic price histories:

# Get dashboard data including top emojis by price and recent market events
query GetDashboardData
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojis(orderBy: [{ currentPrice: DESC }]) {
    id
    symbol
    name
    description
    currentPrice
    trend
  }
  events(orderBy: [{ createdAt: DESC }], limit: 15) {
    id
    description
    impact
    createdAt
    user {
      username
      profileImage
    }
    emoji {
      symbol
    }
  }
}

# Get current authenticated user profile and their stock ownership using auth.uid
query GetUserProfile @auth(level: USER) {
  user(id_expr: "auth.uid") {
    points
    username
    profileImage
    role
    stockOwnerships_on_user {
      shares
      emoji {
        id
        symbol
        currentPrice
        name
      }
    }
    city
    latitude
    longitude
  }
}

# Get price history for a specific emoji ordered by time
query GetPriceHistory($emojiId: UUID!, $limit: Int)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  priceHistories(
    where: { emojiId: { eq: $emojiId } }
    orderBy: [{ recordedAt: ASC }]
    limit: $limit
  ) {
    price
    recordedAt
  }
}

Key Takeaways:

emojis() / events(): GraphQL query fields generated automatically for fetching data directly from your tables.
id_expr: "auth.uid": Secures access by fetching the user profile that matches the current authenticated Firebase user's token.
_on_: Allows direct access to fields from an associated type that has a foreign key relationship. stockOwnerships_on_user fetches the user's entire portfolio in one query.
insecureReason: Required when exposing operations to PUBLIC. It explicitly documents why this data is safe to expose without authentication.

Create type-safe SQL Views

Before writing custom SQL, it's important to understand the different ways Firebase SQL Connect handles queries:

Standard GraphQL: Best for basic CRUD and naïve relations with strict end-to-end type safety.
SQL Views (@view): Best for read-only, complex SQL (like leaderboards using window functions) where you still want a strict, type-safe GraphQL object returned to the client.
Native SQL (_execute / _select): Best for executing DML, CTEs, or PostGIS extensions directly. You trade strict compile-time typing for maximum execution-time flexibility (returns dynamic JSON).

To build our leaderboards and sparkline charts, we need to calculate moving averages and rank users. This is a use case for @view.

Open dataconnect/schema/views.gql.
Add the following views to calculate the necessary statistics on the server:

# Rank users on a leaderboard based on their total net worth
type TopTrader
@view(
  sql: """
  SELECT
    u.id,
    u.username,
    u.profile_image,
    (u.points + COALESCE(SUM(so.shares * e.current_price), 0)) AS net_worth,
    RANK() OVER (ORDER BY (u.points + COALESCE(SUM(so.shares * e.current_price), 0)) DESC) AS rank
  FROM "user" u
  LEFT JOIN stock_ownership so ON u.id = so.user_id
  LEFT JOIN emoji e ON so.emoji_id = e.id
  WHERE u.id != 'system_market_maker'
  GROUP BY u.id, u.username, u.profile_image, u.points
  """
) {
  id: String
  username: String
  profileImage: String
  netWorth: Float
  rank: Int
}

# Identify the top shareholder (whale) for each emoji and their total ownership percentage
type EmojiWhaleStat
  @view(
    sql: """
    WITH total_shares AS (
      SELECT emoji_id, SUM(shares) AS total_supply
      FROM stock_ownership WHERE shares > 0 GROUP BY emoji_id
    ),
    ranked_holders AS (
      SELECT
        so.emoji_id, u.username AS whale_username, u.profile_image AS whale_profile_image,
        so.shares AS whale_shares, ts.total_supply,
        ROUND((so.shares::DECIMAL / NULLIF(ts.total_supply, 0)) * 100, 2) AS whale_percentage,
        RANK() OVER (PARTITION BY so.emoji_id ORDER BY so.shares DESC) AS holder_rank
      FROM stock_ownership so
      JOIN "user" u ON u.id = so.user_id
      JOIN total_shares ts ON ts.emoji_id = so.emoji_id
      WHERE so.shares > 0
    )
    SELECT emoji_id, whale_username, whale_profile_image, whale_shares, total_supply, whale_percentage
    FROM ranked_holders WHERE holder_rank = 1
    """
  ) {
  emojiId: UUID
  whaleUsername: String
  whaleProfileImage: String
  whaleShares: Int
  totalSupply: Int
  whalePercentage: Float
}

# Calculate the moving average of historical prices for each emoji
type EmojiHistoryStat
  @view(
    sql: """
    SELECT
      emoji_id, price, recorded_at,
      AVG(price) OVER (PARTITION BY emoji_id ORDER BY recorded_at ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as moving_average
    FROM price_history
    """
  ) {
  emojiId: UUID
  price: Float
  recordedAt: Timestamp
  movingAverage: Float
}

# Combine recent price updates and major news events into a single chronological feed
type TickerFeed
  @view(
    sql: """
    WITH latest_prices AS (
      SELECT emoji_id, MAX(recorded_at) as last_trade_time
      FROM price_history GROUP BY emoji_id
    )
    SELECT
      'PRICE' as type, e.symbol, e.name, e.current_price, e.trend,
      '' as description, lp.last_trade_time as event_time
    FROM emoji e JOIN latest_prices lp ON e.id = lp.emoji_id
    UNION ALL
    SELECT
      'NEWS' as type, e.symbol, '' as name, 0 as current_price, 0 as trend,
      ev.description, ev.created_at as event_time
    FROM event ev JOIN emoji e ON ev.emoji_id = e.id
    """
  ) {
  type: String
  symbol: String
  name: String
  currentPrice: Float
  trend: Float
  description: String
  eventTime: Timestamp
}

# Retrieve the 15 most recent price points for each emoji to render sparkline charts
type EmojiSparkline
  @view(
    sql: """
    WITH RankedPrices AS (
      SELECT
        emoji_id, price, recorded_at,
        ROW_NUMBER() OVER(PARTITION BY emoji_id ORDER BY recorded_at DESC) as rn
      FROM price_history
    )
    SELECT emoji_id, price, recorded_at
    FROM RankedPrices WHERE rn <= 15 ORDER BY recorded_at ASC
    """
  ) {
  emojiId: UUID
  price: Float
  recordedAt: Timestamp
}

Now, open dataconnect/friendly-exchange/queries.gql and replace the TODOs to fetch data from your new views:

# Get emoji whale statistics to identify top shareholders from emojiWhaleStats view
query GetEmojiWhaleStats
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojiWhaleStats {
    emojiId
    whaleUsername
    whaleProfileImage
    whaleShares
    totalSupply
    whalePercentage
  }
}

# Get historical price and moving average stats for a specific emoji from emojiHistoryStats view
query GetEmojiHistoryStats($emojiId: UUID!)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojiHistoryStats(
    where: { emojiId: { eq: $emojiId } }
    orderBy: [{ recordedAt: ASC }]
    limit: 50
  ) {
    price
    movingAverage
    recordedAt
  }
}

# List top traders ordered by rank from topTraders view
query GetTopTraders
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  topTraders(orderBy: [{ rank: ASC }]) {
    id
    username
    profileImage
    netWorth
    rank
  }
}

# Get chronological market ticker feed of recent events from tickerFeeds view
query GetChronologicalTicker
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  tickerFeeds(orderBy: [{ eventTime: DESC }], limit: 30) {
    type
    symbol
    name
    currentPrice
    trend
    description
    eventTime
  }
}

# Get simple price points for rendering emoji sparkline charts from emojiSparklines view
query GetEmojiSparklines
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojiSparklines {
    emojiId
    price
    recordedAt
  }
}

Key Takeaways

@view: Encapsulates complex database logic on the server while keeping your client-side code strictly typed. SQL Connect maps the GraphQL fields on your @view type to the columns returned by your SELECT statement.
Read-only: Views do not have primary keys and cannot be mutated directly.
Query Generation: Notice how topTraders() and emojiSparklines() work exactly the same as querying a standard table.

Implement search queries

SQL Connect automatically generates standard search queries for any fields marked with the @searchable directive in your schema.

Add the following query to dataconnect/friendly-exchange/queries.gql to enable full-text search:

# Search emojis using full-text search query
query SearchEmojis($query: String)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojis_search(query: $query) {
    id
    symbol
    name
    description
    currentPrice
    trend
  }
}

Key Takeaways

emojis_search: An auto-generated query field created because you applied @searchable to the name and description fields in the Emoji schema.

Generate the SDK

Because you have defined new queries and views in your GraphQL files, you must run the SDK generator so your TypeScript frontend can use them safely.

Open your terminal and run:

firebase dataconnect:sdk:generate

Integrate queries in the web app

The Firebase SQL Connect compiler generates SDKs based on your .gql files. Because this is designed to be a realtime app, you will use the subscribe method alongside the generated query references across multiple components.

Replace the empty useEffect blocks in the following files with the logic below:

1. Home Page (

app/page.tsx

)

import { subscribe } from "@firebase/data-connect";
import {
  getDashboardDataRef,
  searchEmojisRef,
  getChronologicalTickerRef,
  getUserProfileRef,
} from "@dataconnect/generated";

// Inside the Home component:
  useEffect(() => {
    // Subscribe to realtime updates for the main market dashboard data including top emojis and recent events
    const unsubscribe = subscribe(
      getDashboardDataRef(),
      (res) => {
        if (res.data) setDashboardData(res.data);
        setIsDashboardLoading(false);
      },
      (err) => {
        console.error("Dashboard Realtime Error:", err);
        setIsDashboardLoading(false);
      },
    );
    return () => unsubscribe();
  }, [user]);

  useEffect(() => {
    // Subscribe to a realtime chronological ticker feed combining recent price updates and major news events
    const unsubscribe = subscribe(
      getChronologicalTickerRef(),
      (res) => {
        if (res.data) setTickerData(res.data);
      },
      (err) => console.error("Ticker Realtime Error:", err),
    );
    return () => unsubscribe();
  }, []);

  useEffect(() => {
    if (loading || !user) return;
    // Subscribe to realtime updates for the authenticated user's profile and stock ownership
    const unsubscribe = subscribe(
      getUserProfileRef(),
      (res) => {
        if (res.data) setProfileData(res.data);
      },
      (err) => console.error("Profile Error:", err),
    );
    return () => unsubscribe();
  }, [user, loading]);

  useEffect(() => {
    if (!debouncedSearch) {
      setSearchData(null);
      return;
    }
    
    // Subscribe to realtime full-text search results for emojis based on user input
    const unsubscribe = subscribe(
      searchEmojisRef({ query: debouncedSearch }),
      (res) => {
        if (res.data) setSearchData(res.data.emojis_search);
        setIsSearchLoading(false);
      },
      (err) => {
        console.error("Text Search Error:", err);
        setIsSearchLoading(false);
      },
    );

    return () => unsubscribe();
  }, [debouncedSearch]);

2. User Profile Components

app/profile/page.tsx

, update the hooks:

import { subscribe } from "@firebase/data-connect";
import { getUserProfileRef } from "@dataconnect/generated";

useEffect(() => {
  // Subscribe to realtime updates for the authenticated user's profile and stock ownership
  const unsubscribe = subscribe(
    getUserProfileRef(),
    (res) => {
      if (res.data) {
        setData(res.data);
      }
      setIsLoading(false);
    },
    (err) => {
      console.error("Profile Realtime Error:", err);
      setIsLoading(false);
    },
  );
  return () => unsubscribe();
}, []);

components/NavBar.tsx

 useEffect(() => {
    // Subscribe to realtime updates for the authenticated user's profile and stock ownership
    const unsub = subscribe(
      getUserProfileRef(),
      (res) => {
        if (res.data) setData(res.data);
      },
      (err) => console.error("Navbar Balance Realtime Error:", err),
    );
    return () => unsub();
  }, []);

For components/FloatingMenu.tsx, also replace the manual const { data } object with the generated hook:

const { data, refetch: refetchDashboard } = useGetDashboardData();

 useEffect(() => {
    if (!user) return;
    // Subscribe to realtime updates for the authenticated user's profile
    const unsub = subscribe(getUserProfileRef(), (res) => {
      if (res.data) {
        setProfileData(res.data);
        setOptimisticRole(null);
      }
    });
    return () => unsub();
  }, [user]);

Key Takeaways

getUserProfileRef / getDashboardDataRef: Auto-generated functions that prepare the GraphQL queries for execution, preserving the strict types defined by your tables and views.
subscribe: A SQL Connect SDK method that listens to the query. Right now it simply fetches data when the component mounts, but in a later step, we will upgrade the backend to trigger this function automatically whenever the database changes!

Market Panel (components/MarketPanel.tsx): Similarly, in the MarketPanel component (components/MarketPanel.tsx), you can replace the TODOs to call multiple queries simultaneously to build the sidebar.

import { subscribe } from "@firebase/data-connect";
import { getDashboardDataRef, getEmojiSparklinesRef } from "@dataconnect/generated";

// Inside the MarketPanel component:
  useEffect(() => {
    // Subscribe to realtime updates for the main market dashboard data including top emojis and recent events
    const unsub = subscribe(
      getDashboardDataRef(),
      (res) => {
        if (res.data) setData(res.data);
      },
      (err) => console.error("Market Panel Realtime Error:", err)
    );
    return () => unsub();
  }, []);

  useEffect(() => {
    // Subscribe to realtime price history updates to render emoji sparkline charts
    const unsub = subscribe(
      getEmojiSparklinesRef(),
      (res) => {
        if (res.data?.emojiSparklines) {
          setSparklineRawData(res.data.emojiSparklines);
        }
      },
      (err) => console.error("Global Sparklines Error:", err)
    );
    return () => unsub();
  }, []);

Leaderboard Page (app/leaderboard/page.tsx)

import { subscribe } from "@firebase/data-connect";
import { getTopTradersRef } from "@dataconnect/generated";

// Inside the Leaderboard component:
  useEffect(() => {
    // Subscribe to realtime updates for the global leaderboard ranking top traders by net worth
    const unsubscribe = subscribe(
      getTopTradersRef(),
      (res) => {
        if (res.data) setData(res.data);
        setIsLoading(false);
      },
      (err) => {
        console.error("Leaderboard Realtime Error:", err);
        setIsLoading(false);
      },
    );
    return () => unsubscribe();
  }, []);

Emoji Modal (components/EmojiModal.tsx)

import { subscribe } from "@firebase/data-connect";
import {
  getEmojiHistoryStatsRef,
  getEmojiWhaleStatsRef,
} from "@dataconnect/generated";

// Inside the EmojiModal component:
  useEffect(() => {
    if (!emoji?.id) return;

    setStatsLoading(true);
    // Subscribe to realtime historical price and moving average statistics for the selected emoji
    const unsub = subscribe(
      getEmojiHistoryStatsRef({ emojiId: emoji.id }),
      (res) => {
        if (res.data) setStatsData(res.data);
        setStatsLoading(false);
      },
      (err) => {
        console.error("History Realtime Error:", err);
        setStatsLoading(false);
      },
    );
    return () => unsub();
  }, [emoji?.id]);

  useEffect(() => {
    // Subscribe to realtime whale statistics to identify the top shareholder for the selected emoji
    const unsub = subscribe(
      getEmojiWhaleStatsRef(),
      (res) => {
        if (res.data) setWhaleData(res.data);
      },
      (err) => console.error("Whale Realtime Error:", err),
    );
    return () => unsub();
  }, []);

See it in action

Reload your web app to see the queries in action. The homepage and sidebar now display the list of emojis, fetching data directly from your PostgreSQL database.

6. Handle user updates and market transactions

In this section, you'll implement user sign-in functionality using Firebase Authentication to upsert user profiles (like their display name and physical location) in Firebase SQL Connect. You'll also use SQL Connect's @transaction and @check directives to securely execute an atomic, multi-step market event.

Implement user and location connectors

Open dataconnect/friendly-exchange/mutations.gql. Replace the TODOs by adding the following mutations to handle creating, updating, and locating users:

# Upserts a user record using the Firebase Auth uid expression as the primary key
# Upsert (update or insert) a user's profile information
mutation UpsertUser($username: String!, $profileImage: String!)
@auth(level: USER) {
  user_upsert(
    data: {
      id_expr: "auth.uid"
      username: $username
      profileImage: $profileImage
    }
  )
}

# Update a user's role
mutation UpdateUserRole($role: String!) @auth(level: USER) {
  user_update(key: { id_expr: "auth.uid" }, data: { role: $role })
}

# Update a user's location
mutation UpdateUserLocation(
  $city: String!
  $latitude: Float!
  $longitude: Float!
) @auth(level: USER) {
  user_update(
    key: { id_expr: "auth.uid" }
    data: { city: $city, latitude: $latitude, longitude: $longitude }
  )
}

# Trigger a new market event for an emoji
mutation TriggerEvent(
  $emojiId: UUID!
  $impact: Float!
  $description: String!
  $now: Timestamp!
) @auth(level: USER) {
  event_insert(
    data: {
      userId_expr: "auth.uid"
      emojiId: $emojiId
      impact: $impact
      description: $description
      createdAt: $now
    }
  )
}

Key Takeaways

id_expr: "auth.uid": This uses auth.uid, which is provided directly by Firebase Authentication token. By evaluating this server-side, you ensure that a user can only ever update their own profile data, adding an unbreakable layer of security.

Chain logic with `@transaction`

Next, you'll implement a "Market Maker" that an admin can trigger to simulate random market activity. Because this requires updating an emoji's price, logging an event, and updating the system's stock ownership all at once, we need an atomic transaction.

Add this mutation to your mutations.gql file:

# Execute a market maker trade to adjust emoji price and shares
mutation MarketMakerTrade(
  $emojiId: UUID!
  $priceImpact: Float!
  $shareDelta: Int!
  $eventDesc: String!
  $newPrice: Float!
)
@auth(
  level: USER
  insecureReason: "This operation is safe to expose to any user."
)
@transaction {
  query @redact {
    user(key: { id_expr: "auth.uid" })
      @check(
        expr: "this != null && this.role == 'ADMIN'",
        message: "Access Denied: You must have the ADMIN role to deploy the Market Maker bot."
      ) {
      role
    }
  }
  stockOwnership_upsert(
    data: {
      userId: "system_market_maker"
      emojiId: $emojiId
      shares_update: { inc: $shareDelta }
    }
  )
  emoji_update(
    id: $emojiId
    data: { currentPrice_update: { inc: $priceImpact }, trend: $priceImpact }
  )
  event_insert(
    data: {
      userId: "system_market_maker"
      emojiId: $emojiId
      impact: $priceImpact
      description: $eventDesc
    }
  )
  priceHistory_insert(data: { emojiId: $emojiId, price: $newPrice })
}

Key Takeaways

@transaction: Ensures all database operations (upserting stock, updating emoji price, logging the event) either succeed together or fail together.
@check: A directive that evaluates a condition before proceeding. Here, it checks if the authenticated user's role is 'ADMIN'. If the user is just a standard 'USER', the entire transaction is rejected and rolls back.
@redact: Prevents the query results (like the user's role check) from being returned to the client in the response payload, keeping the transaction response clean.

Generate the SDK

Because you have defined new mutations in your GraphQL files, you must run the SDK generator so your TypeScript frontend can call it.

Open your terminal and run:

firebase dataconnect:sdk:generate

Integrate mutations in the web app

In your web app, you will wrap these generated SDK mutations in standard async functions to handle error catching and UI notifications.

Open lib/ExchangeService.tsx and review the wrapper functions. Replace the TODO blocks with the following implementations:

import {
  upsertUser,
  updateUserLocation,
  marketMakerTrade,
  updateUserRole,
  triggerMarketCrash,
} from "@dataconnect/generated";

// Upsert (update or insert) a user's profile information and log the event
export const executeUpsertUser = async (
  username: string,
  profileImage: string,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<void> => {
  logEvent("UPSERT_USER_MUTATION", { username });
  await upsertUser({ username, profileImage });
};

// Update a user's role and log the event
export const executeUpdateRole = async (
  role: string,
  logEvent: (key: LogEventKey, params?: any) => void
): Promise<void> => {
  logEvent("UPDATE_USER_ROLE_MUTATION", { role });
  await updateUserRole({ role });
};

// Update a user's city and geographic coordinates
export const executeUpdateLocation = async (
  city: string,
  latitude: number,
  longitude: number,
): Promise<void> => {
  await updateUserLocation({ city, latitude, longitude });
};

// Execute a random market maker trade and adjust an emoji's stock price
export const executeManualBotTrade = async (
  randomEmoji: any,
  username: string,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<{ isBuy: boolean; tradeAmount: number }> => {
  logEvent("MARKET_MAKER_TRADE");
  const isBuy = Math.random() > 0.5;
  const tradeAmount = Number((Math.random() * (10 - 2) + 2).toFixed(2));

  await marketMakerTrade({
    emojiId: randomEmoji.id,
    priceImpact: isBuy ? tradeAmount : -tradeAmount,
    shareDelta: isBuy ? 10 : -10,
    eventDesc: `Admin ${username} triggered market event: ${randomEmoji.symbol} went ${isBuy ? "up" : "down"} by $${tradeAmount.toFixed(2)}.`,
    newPrice: Math.max(0.01, randomEmoji.currentPrice + (isBuy ? tradeAmount : -tradeAmount)),
  });

  return { isBuy, tradeAmount };
};

Triggering upsert on login: In app/src/components/Navbar.tsx, you can see how executeUpsertUser is called immediately after Firebase Authentication successfully signs a user in via Google Popup. This guarantees the SQL Connect database is synced with Firebase Auth.

See it in action

Now, click the Sign In button in the navbar. You can sign in using Firebase Authentication. After signing in:

Navigate to your Profile and test out the Auto-Locate button. When you click Update Coordinates, the UpdateUserLocation mutation will execute.
Open the Floating Control Panel (the purple icon in the bottom right corner).
Click USER and switch your authorization level to ADMIN.
Click Trigger random market activity. Because your role is now 'ADMIN', the @check directive passes, the @transaction executes, and you will instantly see the market prices update across your application!

7. Advanced operations with Native SQL

In this section, you will use Native SQL to execute complex Data Manipulation Language (DML) statements and leverage PostgreSQL-specific extensions.

While standard GraphQL and @views are ideal for strictly-typed CRUD and read-only operations, Native SQL provides execution-time flexibility. It allows you to use Common Table Expressions (CTEs) to chain multiple updates in a single database round-trip, and lets you query native PostgreSQL extensions directly.

Enable the PostGIS extension

Before we write geospatial queries, you need to enable the PostGIS extension on your Cloud SQL database.

Navigate to the Google Cloud Console.
Go to Cloud SQL -> select your provisioned instance -> click Cloud SQL Studio.
Log into your database and execute the following command:

CREATE EXTENSION IF NOT EXISTS postgis;

Implement Native SQL Queries

Let's use Native SQL to find trending emojis near the user's physical location, and to calculate the top emojis per city using complex ranking.

Open dataconnect/friendly-exchange/queries.gql.
Add the following Native SQL queries using the _select field:

# Get top trending emojis partitioned by user city using native SQL
query GetTopEmojisByCity
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  cityTrends: _select(
    sql: """
    WITH city_shares AS (
      SELECT
        u.city,
        AVG(u.latitude) as latitude,
        AVG(u.longitude) as longitude,
        e.id as emoji_id,
        e.symbol,
        e.name,
        SUM(so.shares) as total_shares,
        RANK() OVER (PARTITION BY u.city ORDER BY SUM(so.shares) DESC) as rank
      FROM stock_ownership so
      JOIN "user" u ON so.user_id = u.id
      JOIN emoji e ON so.emoji_id = e.id
      WHERE u.city IS NOT NULL AND u.latitude IS NOT NULL AND so.shares > 0
      GROUP BY u.city, e.id, e.symbol, e.name
    )
    SELECT city, latitude, longitude, emoji_id, symbol, name, total_shares
    FROM city_shares
    WHERE rank = 1
    ORDER BY city ASC
    """
    params: []
  )
}

# Get trending emojis within a geographic radius using native SQL and PostGIS extension
query GetTrendingEmojisNearMe(
  $userLng: Float!
  $userLat: Float!
  $radiusMeters: Float!
)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  regionalTrends: _select(
    sql: """
    SELECT
      e.id,
      e.symbol,
      e.name,
      e.current_price,
      e.trend,
      COUNT(so.shares)   AS regional_holders,
      SUM(so.shares)     AS regional_shares
    FROM emoji e
    JOIN stock_ownership so ON so.emoji_id = e.id
    JOIN "user" u            ON u.id = so.user_id
    WHERE u.latitude  IS NOT NULL
      AND u.longitude IS NOT NULL
      AND so.shares > 0
      AND ST_DWithin(
        ST_MakePoint(u.longitude, u.latitude)::geography,
        ST_MakePoint($1, $2)::geography,
        $3
      )
    GROUP BY e.id, e.symbol, e.name, e.current_price, e.trend
    ORDER BY regional_shares DESC
    LIMIT 10
    """
    params: [$userLng, $userLat, $radiusMeters]
  )
}

Key Takeaways

_select: Executes a Data Query Language (DQL) statement returning a JSON array ([Any]).
ST_DWithin: A native PostGIS function that calculates distances on a sphere. Native SQL allows you to use this without mapping complex geometry types into your GraphQL schema.
params: Variables like $userLng are bound to the SQL string via positional parameters ($1, $2, $3), preventing SQL injection.

Implement Native SQL Mutations

When a user buys or sells a stock, the system must validate their funds, deduct the cost, add the shares, update the global emoji price, and log the history. Doing this across multiple standard mutations could lead to race conditions. Instead, we can use a CTE (WITH) to do this atomically in one Native SQL execution.

Open dataconnect/friendly-exchange/mutations.gql and replace the TODOs with the following Native SQL mutations:

# Buy shares of an emoji stock
mutation BuyStock($emojiId: UUID!, $amount: Int!, $isDiscounted: Boolean!)
@auth(level: USER) {
  buyStock: _execute(
    sql: """
    WITH validated_params AS (
      SELECT
        $1::uuid AS emoji_id,
        $2::int AS amount,
        $3::boolean AS is_discounted,
        $4::text AS user_id
    ),
    target_emoji AS (
      SELECT
        e.id,
        (e.current_price * (CASE WHEN vp.is_discounted THEN 0.5 ELSE 1.0 END) * vp.amount) AS total_cost
      FROM emoji e
      CROSS JOIN validated_params vp
      WHERE e.id = vp.emoji_id
        AND vp.amount > 0
        AND vp.amount <= 100
    ),
    deduct_funds AS (
      UPDATE "user" u
      SET points = u.points - te.total_cost
      FROM target_emoji te, validated_params vp
      WHERE u.id = vp.user_id AND u.points >= te.total_cost
      RETURNING u.id
    ),
    upsert_ownership AS (
      INSERT INTO stock_ownership (user_id, emoji_id, shares)
      SELECT vp.user_id, vp.emoji_id, vp.amount
      FROM validated_params vp
      WHERE EXISTS (SELECT 1 FROM deduct_funds)
      ON CONFLICT (user_id, emoji_id) DO UPDATE
      SET shares = stock_ownership.shares + EXCLUDED.shares
      RETURNING stock_ownership.emoji_id
    ),
    update_emoji AS (
      UPDATE emoji e
      SET
        current_price = GREATEST(0.01, e.current_price + (e.current_price * 0.01 * vp.amount)),
        trend = GREATEST(0.01, e.current_price + (e.current_price * 0.01 * vp.amount)) - e.current_price
      FROM validated_params vp
      WHERE e.id = vp.emoji_id AND EXISTS (SELECT 1 FROM deduct_funds)
      RETURNING e.id, e.current_price, e.trend
    )
    INSERT INTO price_history (id, emoji_id, price, recorded_at)
    SELECT gen_random_uuid(), ue.id, ue.current_price, NOW()
    FROM update_emoji ue;
    """
    params: [$emojiId, $amount, $isDiscounted, { _expr: "auth.uid" }]
  )
}

# Sell shares of an emoji stock
mutation SellStock($emojiId: UUID!, $amount: Int!) @auth(level: USER) {
  sellStock: _execute(
    sql: """
    WITH validated_params AS (
      SELECT
        $1::uuid AS emoji_id,
        $2::int AS amount,
        $3::text AS user_id
    ),
    target_emoji AS (
      SELECT
        e.id,
        (e.current_price * vp.amount) AS total_revenue,
        GREATEST(0.01, e.current_price * POWER(0.99, vp.amount)) AS new_price
      FROM emoji e
      CROSS JOIN validated_params vp
      WHERE e.id = vp.emoji_id
        AND vp.amount > 0
        AND vp.amount <= 100
    ),
    check_shares AS (
      SELECT so.user_id
      FROM stock_ownership so
      CROSS JOIN validated_params vp
      WHERE so.user_id = vp.user_id
        AND so.emoji_id = vp.emoji_id
        AND so.shares >= vp.amount
    ),
    add_funds AS (
      UPDATE "user" u
      SET points = u.points + te.total_revenue
      FROM target_emoji te, validated_params vp
      WHERE u.id = vp.user_id AND EXISTS (SELECT 1 FROM check_shares)
      RETURNING u.id
    ),
    update_ownership AS (
      UPDATE stock_ownership so
      SET shares = so.shares - vp.amount
      FROM validated_params vp
      WHERE so.user_id = vp.user_id
        AND so.emoji_id = vp.emoji_id
        AND EXISTS (SELECT 1 FROM check_shares)
        AND EXISTS (SELECT 1 FROM add_funds)
    ),
    update_emoji AS (
      UPDATE emoji e
      SET
        current_price = te.new_price,
        trend = te.new_price - e.current_price
      FROM target_emoji te, validated_params vp
      WHERE e.id = vp.emoji_id
        AND EXISTS (SELECT 1 FROM check_shares)
        AND EXISTS (SELECT 1 FROM add_funds)
      RETURNING e.id, e.current_price, e.trend
    )
    INSERT INTO price_history (id, emoji_id, price, recorded_at)
    SELECT gen_random_uuid(), ue.id, ue.current_price, NOW()
    FROM update_emoji ue;
    """
    params: [$emojiId, $amount, { _expr: "auth.uid" }]
  )
}

Key Takeaways

_execute: Executes a Data Manipulation Language (DML) statement, such as UPDATE, INSERT, or DELETE.
Common Table Expressions (WITH): Each block in the CTE depends on the previous one. For example, add_funds will only execute if check_shares returns a result. This handles the complex conditions completely within Postgres.
Context Injection: { _expr: "auth.uid" } injects the authenticated user's ID into the query directly on the server, enforcing security.

Generate the SDK

Because you have defined new queries and mutations in your GraphQL files, you must run the SDK generator so your TypeScript frontend can call it.

Open your terminal and run:

firebase dataconnect:sdk:generate

Integrate Native SQL in the web app

Native SQL returns a flexible JSON payload rather than a strictly typed object. Because of this, it's essential to manually validate the returned data shape in your client code to handle the dynamic response.
Execute Trades: In lib/ExchangeService.tsx, we wrap the generated buyStock and sellStock SDKs. Notice how the return types buyResult and sellResult must be manually validated as arrays, because _execute returns dynamic JSON data based on your specific RETURNING clauses in the SQL strings.
Replace the empty executeBuyStock and executeSellStock functions with your original complete code:

import { buyStock, sellStock, generateTradeHeadline, triggerEvent } from "@dataconnect/generated";
import { LogEventKey } from "./InspectorContext";

// Execute a stock purchase, validating limits and potentially generating an AI news headline for large trades
export const executeBuyStock = async (
  emoji: any,
  amount: number,
  isDiscounted: boolean,
  user: any,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<void> => {
  const MAX_AMOUNT = 100;

  if (!Number.isInteger(amount) || amount <= 0 || amount > MAX_AMOUNT) {
    throw new Error(`Amount must be an integer between 1 and ${MAX_AMOUNT}.`);
  }
  const singleSharePrice = isDiscounted
    ? emoji.currentPrice * 0.5
    : emoji.currentPrice;
  const estimatedCost = singleSharePrice * amount;
  const estimatedImpact = emoji.currentPrice * 0.05 * amount;

  logEvent("BUY_STOCK_TRANSACTION", { amount, symbol: emoji.symbol });

  const response = await buyStock({
    emojiId: emoji.id,
    amount: amount,
    isDiscounted: isDiscounted,
  });

  const buyResult = response.data?.buyStock as any;

  if (
    !buyResult ||
    buyResult === 0 ||
    (Array.isArray(buyResult) && buyResult.length === 0)
  ) {
    throw new Error(
      "Transaction denied: Insufficient funds or price mismatch.",
    );
  }

  const actualCost = Array.isArray(buyResult)
    ? buyResult[0].actual_cost
    : estimatedCost;
  const actualImpact = Array.isArray(buyResult)
    ? buyResult[0].actual_impact
    : estimatedImpact;

// TODO: Optionally add a custom resolver to call AI to generate headline for this purchase
};

// Execute a stock sale, validating ownership and potentially generating an AI news headline for large trades
export const executeSellStock = async (
  emoji: any,
  amount: number,
  ownedShares: number,
  user: any,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<void> => {
  const MAX_AMOUNT = 100;

  if (!Number.isInteger(amount) || amount <= 0 || amount > MAX_AMOUNT) {
    throw new Error(`Amount must be an integer between 1 and ${MAX_AMOUNT}.`);
  }
  if (amount > ownedShares) {
    throw new Error(
      "INSUFFICIENT SHARES: You cannot sell more shares than you own.",
    );
  }

  const estimatedRevenue = emoji.currentPrice * amount;
  const dropRatePerShare = 0.05;
  const targetPrice =
    emoji.currentPrice * Math.pow(1 - dropRatePerShare, amount);
  const estimatedImpact = Math.max(0.01, targetPrice) - emoji.currentPrice;

  logEvent("SELL_STOCK_TRANSACTION", { amount, symbol: emoji.symbol });

  const response = await sellStock({
    emojiId: emoji.id,
    amount: amount,
  });

  const sellResult = response.data?.sellStock as any;

  if (
    !sellResult ||
    sellResult === 0 ||
    (Array.isArray(sellResult) && sellResult.length === 0)
  ) {
    throw new Error("Transaction denied: Insufficient shares.");
  }

  const actualRevenue = Array.isArray(sellResult)
    ? sellResult[0].actual_revenue
    : estimatedRevenue;
  const actualImpact = Array.isArray(sellResult)
    ? sellResult[0].actual_impact
    : estimatedImpact;
// TODO: Optionally add a custom resolver to call AI to generate headline for this sale

};

Query Geospatial Data (Local Radar): In app/src/components/LocalRadar.tsx, we subscribe to the getTrendingEmojisNearMeRef query. The dynamic JSON array from the _select execution maps directly to the UI list, utilizing PostGIS's distance calculations.

import { subscribe } from "@firebase/data-connect";
import { getTrendingEmojisNearMeRef } from "@dataconnect/generated";

// ... inside the component
  useEffect(() => {
    if (!location) return;

    setIsLoadingTrends(true);

    // Subscribe to realtime updates for trending emojis within a 50km radius
    const unsub = subscribe(
      getTrendingEmojisNearMeRef({
        userLat: location.lat,
        userLng: location.lng,
        radiusMeters: 50000, // 50km
      }),
      (res) => {
        if (res.data) setLocalData(res.data);
        setIsLoadingTrends(false);
      },
      (err) => {
        console.error("Local Radar Realtime Error:", err);
        setIsLoadingTrends(false);
      },
    );

    return () => unsub();
  }, [location?.lat, location?.lng]);

Query Geospatial Data (Global Assets Map): In app/src/app/map/page.tsx (the Insights Page), we use Native SQL's complex window functions (RANK() OVER) to find the single most popular emoji for every city in the database.

import { subscribe } from "@firebase/data-connect";
import { getTopEmojisByCityRef, getTrendingEmojisNearMeRef, getUserProfileRef } from "@dataconnect/generated";

// ... inside the component
  useEffect(() => {
    // Subscribe to realtime updates for the authenticated user's profile and stock ownership
    const unsub = subscribe(getUserProfileRef(), (res) => {
      if (res.data) setProfileData(res.data);
    });
    return () => unsub();
  }, []);

  useEffect(() => {
    // Subscribe to realtime updates for top trending emojis partitioned by user city
    const unsub = subscribe(getTopEmojisByCityRef(), (res) => {
      if (res.data) setCityData(res.data);
    });
    return () => unsub();
  }, []);

  useEffect(() => {
    setRadarLoading(true);
    // Subscribe to realtime updates for trending emojis within a specified geographic radius
    const unsub = subscribe(
      getTrendingEmojisNearMeRef({
        userLat: coords.lat,
        userLng: coords.lng,
        radiusMeters: radiusKm * 1000,
      }),
      (res) => {
        if (res.data) setRadarData(res.data);
        setRadarLoading(false);
      },
    );
    return () => unsub();
  }, [coords.lat, coords.lng, radiusKm]);

See it in action

In your browser, navigate to the Geo page from the top navigation bar.
If your location is correctly set in your Profile, the Global Top Assets map will ping the GetTopEmojisByCity native query to drop pins on cities with high trade volumes.
Click Scan Local Network. The Local Radar Scanner will ask for your browser's location and ping the GetTrendingEmojisNearMe native query, utilizing PostGIS to find the top assets specifically traded within 50km of your coordinates!
Navigate to the Home page or Profile page and purchase some assets to see your balance deduct and the emoji price update automatically via your atomic _execute queries.

8. Realtime subscriptions and caching

In the previous section, we used the subscribe() method in our React components to fetch data. While that successfully retrieved the initial state, a true stock exchange needs to feel alive. If another user buys a massive amount of emoji stock, your screen should update instantly.

This is where Firebase SQL Connect's Realtime features come in.

What is Realtime and how does it work?

Realtime support allows your application to receive proactive notifications from the server whenever data your app is using has been updated.

Here is the underlying mechanism:

Trigger (@refresh): You tell the SQL Connect backend which specific mutations should trigger a data refresh for a given query.
Broadcast: When one of those mutations executes (e.g., someone runs BuyStock), the server proactively broadcasts a realtime notification to any connected clients listening to that query.
Cache Update: When the notification arrives, the JS SDK treats it just like an ad-hoc query execution. The local cache is instantly updated with the new data.
UI Reactivity: The SDK automatically fires the onNext callbacks for all active subscribers, causing your React state to update and your UI to re-render "in real time".

Add `@refresh` triggers to your queries

To enable this on the backend, we need to add the @refresh directive to our queries.

Open dataconnect/friendly-exchange/queries.gql.
Update your existing queries by attaching @refresh directives for every market-altering mutation. For example, update GetDashboardData and GetUserProfile:

# Get dashboard data including top emojis by price and recent market events
query GetDashboardData
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
)
@refresh(onMutationExecuted: { operation: "BuyStock" })
@refresh(onMutationExecuted: { operation: "SellStock" })
@refresh(onMutationExecuted: { operation: "TriggerEvent" })
@refresh(onMutationExecuted: { operation: "MarketMakerTrade" }) {
  emojis(orderBy: [{ currentPrice: DESC }]) {
    id
    symbol
    name
    description
    currentPrice
    trend
  }
  events(orderBy: [{ createdAt: DESC }], limit: 15) {
    id
    description
    impact
    createdAt
    user {
      username
      profileImage
    }
    emoji {
      symbol
    }
  }
}

# Get current authenticated user profile and their stock ownership using auth.uid
query GetUserProfile
@auth(level: USER)
@refresh(onMutationExecuted: { operation: "BuyStock" })
@refresh(onMutationExecuted: { operation: "SellStock" })
@refresh(onMutationExecuted: { operation: "UpdateUserLocation" })
@refresh(onMutationExecuted: { operation: "UpdateUserRole" }) {
  user(id_expr: "auth.uid") {
    points
    username
    profileImage
    role
    stockOwnerships_on_user {
      shares
      emoji {
        id
        symbol
        currentPrice
        name
      }
    }
    city
    latitude
    longitude
  }
}

Key Takeaways

@refresh(onMutationExecuted: ...): Instructs the server to re-evaluate this query and push new data to subscribers whenever the specified mutation occurs.

Generate the SDK

Because you have defined new queries and mutations in your GraphQL files, you must run the SDK generator so your TypeScript frontend can call it.

Open your terminal and run:

firebase dataconnect:sdk:generate

Handle Realtime Subscriptions in the Web App

We already laid the groundwork for this in the previous section by using the subscribe method. Let's look closer at how the generated SDK handles this in React.

If you open app/src/app/page.tsx (the Home page), you will see the useEffect hook managing the dashboard data:

import { subscribe } from "@firebase/data-connect";
import { getDashboardDataRef } from "@dataconnect/generated";

// ... inside the component
  useEffect(() => {
    const queryRef = getDashboardDataRef();

    // The subscribe function registers the QueryRef and callbacks
    const unsubscribe = subscribe(
      queryRef,
      (res) => {
        // onNext: Fires initially, AND whenever a @refresh trigger occurs
        if (res.data) setDashboardData(res.data);
        setIsDashboardLoading(false);
      },
      (err) => {
        // onError: Handles any server or permission errors
        console.error("Dashboard Realtime Error:", err);
        setIsDashboardLoading(false);
      }
    );

    // onComplete/Cleanup: Unregisters the callbacks when the component unmounts
    return () => unsubscribe();
  }, [user]);

Key Takeaways

subscribe(queryRef, onNext, onError): Enables Realtime notifications for the specific QueryRef.
unsubscribe(): Calling subscribe returns a cleanup function. It is critical to return this in your useEffect so that when the component unmounts (e.g., the user navigates away), the subscription is canceled and memory leaks are prevented.
Caching Efficiency: If multiple components subscribe to the same query (like GetDashboardData), the SDK shares the cached result. When a Realtime notification arrives, the cache updates once, and all active subscribers are notified automatically.

See it in action

Because you've added @refresh to your backend and subscribe to your frontend, your app is now fully reactive.

Open your web app in two separate browser windows side-by-side.
In one window, purchase a few shares of an emoji.
Watch the second window—without refreshing the page, you will instantly see the emoji's price increase!

9. Conclusion

Congratulations, you've successfully built and deployed a realtime, highly complex trading platform directly on top of PostgreSQL using Firebase SQL Connect!

By utilizing SQL Connect, you were able to:

Define a strictly-typed GraphQL schema that maps directly to PostgreSQL.
Enforce granular, row-level security using Firebase Authentication and @auth directives.
Leverage advanced Native SQL to query geospatial data with PostGIS and write atomic market transactions via CTEs.
Make your entire application reactive using the @refresh directive for realtime subscriptions.
Seamlessly generate frontend SDKs to keep your client code synced with your database.

If you want to play with your own market data, feel free to insert your own mock emojis, locations, and pricing histories using the Firebase SQL Connect extension by mimicking the .gql seed files, or add them through the SQL Connect execution pane in VS Code.

10. Deploy to Cloud

Now that you've worked through the local development iteration, it's time to deploy your schema, data, and queries to the server. This can be done using the Firebase SQL Connect VS Code extension or the Firebase CLI.

Set up Firebase Authentication in your Firebase project

Set up Firebase Authentication with Google Sign-In.
(Optional) Allow domains for Firebase Authentication using the Firebase console (for example, http://127.0.0.1).
1. In the Authentication settings, go to Authorized Domains.
2. Click "Add Domain" and include your local domain in the list.

Enable required PostgreSQL Extensions

Because this app utilizes PostgreSQL extensions for vector search and location tracking, you must manually enable them on your provisioned Cloud SQL instance before deploying your schema.

Navigate to the Google Cloud Console.
Go to Cloud SQL -> select your provisioned instance -> click Cloud SQL Studio.
Log into your database and execute the following commands:

# Required for the Geo Map page
CREATE EXTENSION IF NOT EXISTS postgis;
# Required for Vector Search
CREATE EXTENSION IF NOT EXISTS "vector";
# Required for automatic Vector Search embedding generation
CREATE EXTENSION IF NOT EXISTS "google_ml_integration";

Build your web app for hosting

Back in VS Code, ensure you have placed your firebaseConfig variables in lib/firebase.tsx (as done in the setup section).

Next, guarantee that your frontend is using the latest generated hooks by running:

firebase dataconnect:sdk:generate

Then, build the React web app for hosting deployment:

npm run build

Deploy with the Firebase CLI

In dataconnect/dataconnect.yaml, ensure that your instance ID, database, and service ID match your actual Google Cloud project identifiers, and use the v1 specification:

specVersion: v1
serviceId: your-project-id-service
location: us-west4
schemas:
  - source: ./schema
    datasource:
      postgresql:
        database: your-project-id-database
        cloudSql:
          instanceId: your-project-id-instance
connectorDirs:
  - ./friendly-exchange

In your terminal, run the following command to deploy:

firebase deploy --only dataconnect,hosting

For updates or refactors, run this command to compare your schema changes:

firebase dataconnect:sql:diff

If the changes are acceptable, apply them with:

firebase dataconnect:sql:migrate

Your Cloud SQL for PostgreSQL instance will be updated with the final deployed schema and data. You should now be able to see your app live at your-project.web.app/.

Learn more

11. Optional: Vector search with Firebase SQL Connect (billing required)

In this section, you'll enable vector search in your emoji exchange using Firebase SQL Connect. This feature allows for semantic, content-based searches, such as finding emojis that match a vibe or concept using vector embeddings.

This step requires that you completed the last step of this codelab to deploy to Google Cloud.

Update the schema to include embeddings for a field

In dataconnect/schema/schema.gql, add the descriptionEmbedding field to your Emoji table. Replace your existing Emoji type with this updated version:

# Emojis
# emoji-stockOwnership is a one-to-many relationship, emoji-priceHistory is a one-to-many relationship
# Implements @searchable directives for full-text search
# Optional: implements Vector type for semantic search
type Emoji @table {
  id: UUID! @default(expr: "uuidV4()")
  symbol: String!
  name: String! @searchable
  tags: [String!]
  description: String! @searchable
  descriptionEmbedding: Vector @col(size: 768)
  currentPrice: Float! @default(value: 10.0)
  trend: Float! @default(value: 0.0)
}

Key Takeaways

descriptionEmbedding: Vector @col(size: 768): This field stores the semantic embeddings of your emoji descriptions, enabling vector-based content search in your app.

Add a vector search query

In dataconnect/friendly-exchange/queries.gql, add the following query to perform vector searches:

# Search emoji descriptions using Vertex AI embeddings
query VectorSearchEmojis($query: String!)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
)
@refresh(onMutationExecuted: { operation: "BuyStock" })
@refresh(onMutationExecuted: { operation: "SellStock" })
@refresh(onMutationExecuted: { operation: "TriggerEvent" })
@refresh(onMutationExecuted: { operation: "MarketMakerTrade" }) {
  emojis_descriptionEmbedding_similarity(
    compare_embed: { model: "text-multilingual-embedding-002", text: $query }
    method: COSINE
    within: 2
    limit: 15
  ) {
    id
    symbol
    name
    description
    currentPrice
    trend
    _metadata {
      distance
    }
  }
}

Key Takeaways:

compare_embed: Specifies the embedding model (text-multilingual-embedding-002) and the input text ($query) for comparison.
method: Specifies the similarity method (COSINE), measuring the cosine similarity between the vectors.
within: Limits the search to emojis with a distance of 2 or less, focusing on close content matches.

Generate the SDK

Because you have defined new queries and mutations in your GraphQL files, you must run the SDK generator so your TypeScript frontend can call it.

Open your terminal and run:

firebase dataconnect:sdk:generate

Activate Vertex AI and re-deploy

Follow the prerequisites guide to set up Vertex AI APIs from Google Cloud. This step is essential to support the embedding generation.
Re-deploy your schema to activate pgvector and vector search by running firebase deploy --only dataconnect or clicking "Deploy to Production" using the Firebase SQL Connect VS Code extension.

Populate the database with embeddings

Open the dataconnect folder in VS Code.
Click Run (Production) in optional_vector_seed.gql to populate your deployed database with the 768-dimensional embeddings for the emojis.

Implement the vector search function in your app

Now that the schema and query are set up, integrate the vector search into your app's frontend.

In app/src/app/page.tsx (your Home component), review the useEffect that listens to the search input and swaps dynamically between full-text search and vector search based on the user's selected searchMode:

import { subscribe } from "@firebase/data-connect";
import {
  getDashboardDataRef,
  searchEmojisRef,
  vectorSearchEmojisRef, // <-- Add this!
  getChronologicalTickerRef,
  getUserProfileRef,
} from "@dataconnect/generated";

// Inside Home component, find the search useeffect
useEffect(() => {
    if (!debouncedSearch) {
      setSearchData(null);
      return;
    }

    let unsubscribe: () => void;

    if (searchMode === "TEXT") {
      // Subscribe to realtime full-text search results for emojis based on user input
      unsubscribe = subscribe(
        searchEmojisRef({ query: debouncedSearch }),
        (res) => {
          if (res.data) setSearchData(res.data.emojis_search);
          setIsSearchLoading(false);
        },
        (err) => {
          console.error("Text Search Error:", err);
          setIsSearchLoading(false);
        },
      );
    } else {
      // Subscribe to realtime vector search results using semantic similarity for emojis based on user input
      unsubscribe = subscribe(
        vectorSearchEmojisRef({ query: debouncedSearch }),
        (res) => {
          if (res.data)
            setSearchData(res.data.emojis_descriptionEmbedding_similarity);
          setIsSearchLoading(false);
        },
        (err) => {
          console.error("Vector Search Error:", err);
          setIsSearchLoading(false);
        },
      );
    }

    return () => {
      if (unsubscribe) unsubscribe();
    };
  }, [debouncedSearch, searchMode]);

See it in action

Navigate to the search bar on your app's homepage. Type in abstract phrases like "happy", "nature", or "technology". Toggle the search mode from TEXT to VECTOR and notice how the results shift from exact string matches to contextual, semantic matches returned directly from Vertex AI and PostgreSQL!

12. Optional: Custom Resolvers with Vertex AI (billing required)

10:00

By writing Custom Resolvers, you can extend Firebase SQL Connect to support other data sources and combine them into your unified GraphQL schema. In this section, you'll write a Firebase Cloud Function that uses Vertex AI (Gemini) to generate a satirical financial news headline whenever a user makes a large trade, and expose that function through SQL Connect.

Initialize the custom resolver

Instead of creating all the boilerplate files manually, the Firebase CLI has a built-in generator for custom resolvers.

Open your terminal in the root of your project and run:

firebase init dataconnect:resolver

When prompted by the CLI:

Enter generateTradeHeadline as the name for your custom resolver.
Select TypeScript to generate the example implementation.

The CLI will automatically create a new dataconnect/schema_generateTradeHeadline/schema.gql file, initialize a functions directory with sample code, and link the resolver in your dataconnect.yaml configuration!

Define the custom resolver schema

Next, you need to define the exact shape of your custom endpoint using a GraphQL schema.

Open the newly generated dataconnect/schema_generateTradeHeadline/schema.gql file and replace its contents with the following code:

# Custom resolver fields can be defined on root Query and Mutation types.
type Mutation {
  # This field will be backed by your Cloud Function.
  generateTradeHeadline(
    emojiSymbol: String!
    emojiName: String!
    username: String!
    tradeAmount: Int!
    tradeCost: Float!
    tradeType: String!
  ): String!
}

Key Takeaways:

By placing this inside the root type Mutation, you are telling SQL Connect that this operation might have side-effects (like calling an AI API) rather than just reading data.

Implement the custom resolver logic

Next, implement your resolver using Cloud Functions. Under the hood, you are creating a GraphQL server; however, Cloud Functions provides a helper method, onGraphRequest, that handles the boilerplate so you only need to write the core logic.

Open your Firebase Functions file (functions/src/index.ts), which the CLI generated for you. Replace the entire file with the Gemini API implementation:

import { setGlobalOptions } from "firebase-functions";
import {
  FirebaseContext,
  onGraphRequest,
} from "firebase-functions/dataconnect/graphql";
import { initializeApp, getApps } from "firebase-admin/app";
import { GoogleGenAI } from "@google/genai";

setGlobalOptions({
  maxInstances: 10,
  region: "us-west4",
});

if (getApps().length === 0) {
  initializeApp();
}

const ai = new GoogleGenAI({
  vertexai: true,
  project: process.env.GCLOUD_PROJECT || "your-project-id",
  location: process.env.GCLOUD_LOCATION || "us-west4",
});

const headlineOpts = {
  // Points to the schema you defined earlier
  schemaFilePath: "dataconnect/schema_generateTradeHeadline/schema.gql",
  resolvers: {
    mutation: {
      // Generate a satirical financial news headline for a stock trade using Vertex AI
      async generateTradeHeadline(
        _parent: unknown,
        args: Record<string, unknown>,
        _contextValue: FirebaseContext,
        _info: unknown,
      ): Promise<string> {
        const {
          emojiSymbol,
          emojiName,
          username,
          tradeAmount,
          tradeCost,
          tradeType,
        } = args;

        try {
          const prompt = `You are a hype-driven, satirical financial news bot. 
          A user named '${username}' just executed a massive ${tradeType} of ${tradeAmount} shares of ${emojiSymbol} (${emojiName}) for $${tradeCost}. 
          Write a single, punchy, dramatic news headline (under 12 words) about this market move, use puns wherever possible, but don't round or exagerate the numbers. Include the asset symbol.`;
          
          const response = await ai.models.generateContent({
            model: "gemini-2.5-flash-lite",
            contents: prompt,
          });

          if (!response.text) {
            throw new Error("No text returned from Vertex AI");
          }

          return response.text.trim();
        } catch (error) {
          console.error("Vertex AI generation failed:", error);
          return `BREAKING: Massive ${tradeType} detected on ${emojiSymbol}! Market reacting.`;
        }
      },
    },
  },
};

export const generateTradeHeadline = onGraphRequest(headlineOpts);

Key Takeaways:

onGraphRequest: A specialized Firebase Functions wrapper that maps a Cloud Function to a SQL Connect Custom Resolver schema.
args: The arguments passed from the GraphQL mutation are automatically typed and extracted here to be injected into the Gemini prompt.

Add the mutation to your connector

Now that the custom resolver logic exists, expose it through your application's connector so the frontend can call it.

Open dataconnect/friendly-exchange/mutations.gql and add the mutation:

# Generate an AI headline for a stock trade
mutation GenerateTradeHeadline(
  $emojiSymbol: String!
  $emojiName: String!
  $username: String!
  $tradeAmount: Int!
  $tradeCost: Float!
  $tradeType: String!
)
@auth(
  level: USER
  insecureReason: "This operation is safe to expose to any authenticated user."
) {
  aiHeadline: generateTradeHeadline(
    emojiSymbol: $emojiSymbol
    emojiName: $emojiName
    username: $username
    tradeAmount: $tradeAmount
    tradeCost: $tradeCost
    tradeType: $tradeType
  )
}

Deploy and Generate the SDK

Because Custom Resolvers run via Cloud Functions, you must deploy your functions to Google Cloud for the endpoint to become active.

Open your terminal and deploy the function:

firebase deploy --only functions

Once deployed, generate the frontend SDK to include your new AI mutation:

firebase dataconnect:sdk:generate

Integrate the AI Resolver in the web app

Let's wire this up so that any trade of 10 or more shares triggers a breaking news alert!

Open lib/ExchangeService.tsx. First, make sure you import generateTradeHeadline and triggerEvent at the top:

import { 
  buyStock, 
  sellStock, 
  generateTradeHeadline, 
  triggerEvent 
} from "@dataconnect/generated";

Next, scroll down to the bottom of your executeBuyStock function and replace the TODO with the AI trigger block right before the function ends:

// ... (existing executeBuyStock code)

  const actualImpact = Array.isArray(buyResult)
    ? buyResult[0].actual_impact
    : estimatedImpact;

  if (amount >= 10 && user) {
    setTimeout(() => {
      logEvent("GENERATE_HEADLINE_RESOLVER");
    }, 2000);

    const headlineResult = await generateTradeHeadline({
      emojiSymbol: emoji.symbol,
      emojiName: emoji.name,
      username: user.displayName || "Anonymous Whale",
      tradeAmount: amount,
      tradeCost: actualCost.toFixed(2),
      tradeType: "BUY",
    });

    await triggerEvent({
      emojiId: emoji.id,
      impact: actualImpact.toFixed(2),
      description: `GEMINI REPORT: ${headlineResult.data?.aiHeadline}`,
      now: new Date().toISOString(),
    });
  }
};

Do the exact same thing at the bottom of the executeSellStock function:

// ... (existing executeSellStock code)

  const actualImpact = Array.isArray(sellResult)
    ? sellResult[0].actual_impact
    : estimatedImpact;

  if (amount >= 10 && user) {
    const headlineResult = await generateTradeHeadline({
      emojiSymbol: emoji.symbol,
      emojiName: emoji.name,
      username: user.displayName || "Anonymous Whale",
      tradeAmount: amount,
      tradeCost: actualRevenue.toFixed(2),
      tradeType: "SELL",
    });

    await triggerEvent({
      emojiId: emoji.id,
      impact: actualImpact.toFixed(2),
      description: `GEMINI REPORT: ${headlineResult.data?.aiHeadline}`,
      now: new Date().toISOString(),
    });
  }
};

See it in action

Reload your web app.
Ensure you are signed in and have enough currency.
Select an emoji and purchase 10 or more shares at once.
Watch the Global Market Ticker on the right side of your dashboard. Within a few seconds, you'll see a custom, Gemini-generated satirical news headline appear!

Build a Realtime Trading App with Firebase SQL Connect (web) Stay organized with collections Save and categorize content based on your preferences.

1. Before you begin

Prerequisites

What you'll learn

What you'll need

2. Set up your development environment

3. Review the starter codebase

Folder and file structure

The dataconnect/ directory

The lib/ directory

The Generated SDK

4. Define a schema for your emoji exchange

Core entities and relationships

Set up the User table

Set up the Emoji table

Set up the StockOwnership table

Set up the Event and PriceHistory tables

Auto-generated fields and defaults

5. Retrieve market and user data

Insert mock market and user data

Implement basic queries

Create type-safe SQL Views

Implement search queries

Generate the SDK

Integrate queries in the web app

See it in action

6. Handle user updates and market transactions

Implement user and location connectors

Chain logic with @transaction

Generate the SDK

Integrate mutations in the web app

See it in action

7. Advanced operations with Native SQL

Enable the PostGIS extension

Implement Native SQL Queries

Implement Native SQL Mutations

Generate the SDK

Integrate Native SQL in the web app

See it in action

8. Realtime subscriptions and caching

What is Realtime and how does it work?

Add @refresh triggers to your queries

Generate the SDK

Handle Realtime Subscriptions in the Web App

See it in action

9. Conclusion

10. Deploy to Cloud

Set up Firebase Authentication in your Firebase project

Enable required PostgreSQL Extensions

Build your web app for hosting

Deploy with the Firebase CLI

Learn more

11. Optional: Vector search with Firebase SQL Connect (billing required)

Update the schema to include embeddings for a field

Add a vector search query

Generate the SDK

Activate Vertex AI and re-deploy

Populate the database with embeddings

Implement the vector search function in your app

See it in action

12. Optional: Custom Resolvers with Vertex AI (billing required)

Initialize the custom resolver

Define the custom resolver schema

Implement the custom resolver logic

Add the mutation to your connector

Deploy and Generate the SDK

Integrate the AI Resolver in the web app

See it in action

Build a Realtime Trading App with Firebase SQL Connect (web)

The `dataconnect/` directory

The `lib/` directory

Chain logic with `@transaction`

Add `@refresh` triggers to your queries