Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

使用 Firebase SQL Connect 构建实时交易应用 (Web)

1. 准备工作

在此 Codelab 中，您将 Firebase SQL Connect 与 Cloud SQL 数据库集成，以构建 Friendly Exchange，这是一个实时表情符号股票市场 Web 应用。

完成的应用展示了高级 SQL Connect 功能，包括：

原生 SQL：使用 _execute 和 _select 安全地执行复杂的数据操纵语言 (DML) 语句和通用表表达式 (CTE)。
SQL 视图：使用 @view 指令创建由动态 Postgres 查询支持的严格的类型安全 GraphQL 对象。
实时订阅：使用 @refresh 触发器使前端界面保持同步。
原子事务：使用 @transaction 和 @check 链接多个操作并验证状态。
（可选） 地理空间和向量搜索：利用 PostGIS 和 pgvector 查找用户坐标附近的趋势资产并执行语义搜索。
（可选） 自定义解析器：将自定义 Cloud Run 逻辑连接到 GraphQL 架构以生成 AI 交易头条新闻。

前提条件

您需要对 JavaScript/TypeScript、React 和基本 SQL 语法有扎实的了解。

学习内容

如何使用原生 SQL 来弥合声明式 GraphQL 和原始 PostgreSQL 逻辑之间的差距。
如何将 Postgres 扩展程序（例如 PostGIS）直接集成到数据库查询中。
如何使用原子 @transaction 块强制执行复杂逻辑。
如何为排行榜和统计信息创建类型安全的 @views。
如何使用 @refresh 设置实时订阅。

所需条件

Git
Visual Studio Code
安装 Node.js
采用随用随付 Blaze 定价方案的 Firebase 项目（自定义解析器和 Vertex AI 所需）。

2. 设置您的开发环境

此阶段将指导您设置前端并配置 Cloud SQL 实例以使用高级功能。

克隆项目代码库并安装应用所需的依赖项：

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

使用 Visual Studio Code 打开克隆的文件夹，然后安装 Firebase SQL Connect Visual Studio 扩展程序 。
在终端中，确保 Firebase CLI 完全是最新的（这是使用 @refresh 和原生 SQL 等新功能所必需的）：

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

（选择 Hosting、Authentication 和 SQL Connect）。

生成 SQL Connect SDK：运行以下命令：

firebase dataconnect:sdk:generate

将 Web 应用连接到 Firebase 项目：使用 Firebase 控制台在 Firebase 项目中注册 Web 应用：
1. 打开您的项目，然后点击添加应用 （选择 Web 图标）。
2. 暂时忽略 SDK 设置和配置设置，但请务必复制生成的 firebaseConfig 对象。
3. 在代码编辑器中打开 lib/firebase.tsx，然后将现有占位符替换为您刚刚复制的配置：

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

运行开发服务器：

npm run dev

3. 查看起始代码库

在本部分中，您将探索应用起始代码库的关键区域。虽然您将从头开始编写架构和查询，但了解前端如何连接以与 SQL Connect 交互会很有帮助。

文件夹和文件结构

`dataconnect/` 目录

此文件夹包含后端定义，包括数据库结构到应用允许运行的特定 SQL 查询的所有内容。

schema/schema.gql：您将在此处使用标准 GraphQL 类型定义基本 Postgres 表。
schema/views.gql：您将在此处使用 @view 指令定义复杂的只读 SQL 视图（例如排行榜）。
friendly-exchange/queries.gql 和 mutations.gql：您的“连接器”。您将在此处定义应用允许的确切查询和原生 SQL（_execute、_select）。
dataconnect.yaml：用于决定 SDK 生成和 Cloud SQL 部署设置的配置文件。

目录`lib/`

包含应用逻辑、身份验证以及与 Firebase SQL Connect SDK 的交互。

firebase.tsx：处理 Firebase 应用、Auth 和 SQL Connect 实例的初始化。
ExchangeService.tsx：这是 React 组件和数据库之间的桥梁。它将生成的 SDK 函数（例如 buyStock 或 sellStock）封装在标准异步函数中，以处理错误捕获、业务逻辑和 Toast 通知。

生成的 SDK

在 SQL Connect 中编写查询或变更时，VS Code 扩展程序会自动生成强类型 SDK。在此项目中，前端直接从 @dataconnect/generated 导入这些函数。

4. 为表情符号交易定义架构

在本部分中，您将定义交易应用中关键实体之间的结构和关系。User、Emoji、StockOwnership、Event 和 PriceHistory 等实体会映射到数据库表，并使用 Firebase SQL Connect 和 GraphQL 架构指令建立关系。

此架构到位后，您的应用将能够处理从执行买入/卖出交易和更新全局排行榜到映射本地地理空间趋势的所有事务。

核心实体和关系

Emoji：包含符号、名称、价格和趋势等关键详细信息，应用使用这些信息来显示市场。
User：跟踪交易者的个人资料、可用积分（货币）和地理坐标，以进行本地雷达扫描。
关系： StockOwnership 联接表准确跟踪特定用户拥有的特定表情符号的股份数量。Event 和 PriceHistory 类型充当不可变的账本，记录市场影响和历史价格点随时间的变化。

设置 User 表

User 类型定义系统中的交易者，跟踪其余额、角色和物理位置以进行地理空间查询。

将以下代码段复制并粘贴到 dataconnect/schema/schema.gql 文件中：

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

重点小结：

id：使用 @default(expr: "auth.uid") 直接绑定到 Firebase Authentication。这可确保数据库身份和 Auth 身份安全地 1:1 对应，防止用户伪造 ID。
points：用于交易的虚拟货币，新用户默认为 100.0。

设置 Emoji 表

Emoji 类型定义要交易的主要资产，包括用于标准文本搜索的字段。

将此代码段复制并粘贴到 dataconnect/schema/schema.gql 文件中：

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

重点小结：

name 和 description：使用 @searchable 指令优化这些列以进行标准全文搜索。

设置 StockOwnership 表

StockOwnership 类型是一个连接表，用于处理用户及其拥有的表情符号之间的多对多关系。将此代码段复制并粘贴到 dataconnect/schema/schema.gql 文件中：

# 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: ["user", "emoji"]：创建复合主键。用户不能为同一表情符号拥有两个单独的记录；它强制执行每对的唯一性。
隐式引用：通过直接引用 User 和 Emoji 类型，SQL Connect 会在后台自动生成外键 userId: String! 和 emojiId: UUID!。

设置 Event 和 PriceHistory 表

这些类型表示应用的账本，准确记录发生了什么以及价格如何变化。将最终代码段复制并粘贴到 dataconnect/schema/schema.gql 文件中：

# 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")
}

重点小结：

createdAt 和 recordedAt：使用 @default(expr: "request.time") 自动设置为数据库事务发生的确切时间。这可防止客户端操纵时间戳。

自动生成的字段和默认值

该架构依赖于 @default(expr: "uuidV4()") 和 @default(expr: "auth.uid") 等表达式来自动生成唯一 ID 并强制执行所有权，而无需客户端应用提供这些 ID。

5. 检索市场和用户数据

在本部分中，您将向数据库中插入模拟市场数据，然后实现连接器（查询）和 TypeScript 代码，以在整个 Web 应用中调用这些连接器。最后，您的应用将能够直接从数据库中动态提取和显示实时表情符号市场、用户个人资料和排行榜。

插入模拟市场和用户数据

在 VSCode 中，打开 dataconnect/seed.gql。
确保 Firebase SQL Connect 扩展程序中的模拟器正在运行（或 Cloud SQL 实例已连接）。
您应该会在文件顶部看到运行（本地） 或运行（生产环境） CodeLens 按钮。点击此按钮可将模拟表情符号数据和初始价格历史记录插入到数据库中。
检查 SQL Connect 执行终端，确认数据已成功添加。

实现基本查询

首先，我们来查询您在架构中定义的标准表。

打开 dataconnect/friendly-exchange/queries.gql。
添加以下查询以检索信息中心数据、用户个人资料和基本价格历史记录：

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

重点小结：

emojis() / events(): 自动生成的 GraphQL 查询字段，用于直接从表中提取数据。
id_expr: "auth.uid"：通过提取与当前经过身份验证的 Firebase 用户的令牌匹配的用户个人资料来确保访问安全。
_on_：允许直接访问具有外键关系的关联类型的字段。stockOwnerships_on_user 在一个查询中提取用户的整个投资组合。
insecureReason：将操作公开给 PUBLIC 时是必需的。它明确记录了为什么此数据在未经身份验证的情况下可以安全地公开。

创建类型安全的 SQL 视图

在编写自定义 SQL 之前，务必了解 Firebase SQL Connect 处理查询的不同方式：

标准 GraphQL：最适合基本 CRUD 和具有严格端到端类型安全性的简单关系。
SQL 视图 (@view)：最适合只读的复杂 SQL（例如使用窗口函数的排行榜），您仍希望将严格的类型安全 GraphQL 对象返回给客户端。
原生 SQL (_execute / _select)：最适合直接执行 DML、CTE 或 PostGIS 扩展程序。您将严格的编译时类型检查换成了最大的执行时灵活性（返回动态 JSON）。

为了构建排行榜和折线图，我们需要计算移动平均值并对用户进行排名。这是 @view 的用例。

打开 dataconnect/schema/views.gql。
添加以下视图以计算服务器上的必要统计信息：

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

现在，打开 dataconnect/friendly-exchange/queries.gql 并替换 TODO 以从新视图中提取数据：

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

重点小结

@view：在服务器上封装复杂的数据库逻辑，同时保持客户端代码的严格类型。SQL Connect 将 @view 类型中的 GraphQL 字段映射到 SELECT 语句返回的列。
只读：视图没有主键，无法直接更改。
查询生成：请注意，topTraders() 和 emojiSparklines() 的工作方式与查询标准表完全相同。

实现搜索查询

SQL Connect 会为架构中标记有 @searchable 指令的任何字段自动生成标准搜索查询。

将以下查询添加到 dataconnect/friendly-exchange/queries.gql 以启用全文搜索：

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

重点小结

emojis_search：自动生成的查询字段，因为您已将 @searchable 应用于 Emoji 架构中的 name 和 description 字段。

生成 SDK

由于您已在 GraphQL 文件中定义了新的查询和视图，因此必须运行 SDK 生成器，以便 TypeScript 前端可以安全地使用它们。

打开终端并运行：

firebase dataconnect:sdk:generate

在 Web 应用中集成查询

Firebase SQL Connect 编译器会根据您的 .gql 文件生成 SDK。由于此应用旨在成为实时应用，因此您将在多个组件中使用 subscribe 方法以及生成的查询引用。

将以下文件中的空 useEffect 块替换为以下逻辑：

1. 首页 (

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. 用户个人资料组件

app/profile/page.tsx

，更新钩子：

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();
  }, []);

对于 components/FloatingMenu.tsx，还要将手动 const { data } 对象替换为生成的钩子：

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]);

重点小结

getUserProfileRef / getDashboardDataRef: 自动生成的函数，用于准备要执行的 GraphQL 查询，保留表和视图定义的严格类型。
subscribe：监听查询的 SQL Connect SDK 方法。目前，它只是在组件挂载时提取数据，但在后续步骤中，我们将升级后端以在数据库发生更改时自动触发此函数！

市场面板 (components/MarketPanel.tsx)：同样，在 MarketPanel 组件 (components/MarketPanel.tsx) 中，您可以替换 TODO 以同时调用多个查询来构建侧边栏。

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();
  }, []);

排行榜页面 (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();
  }, []);

表情符号模态框 (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();
  }, []);

看看实际运用情况

重新加载 Web 应用以查看实际查询。首页和侧边栏现在会显示表情符号列表，直接从 PostgreSQL 数据库提取数据。

6. 处理用户更新和市场交易

在本部分中，您将使用 Firebase Authentication 实现用户登录功能，以在 Firebase SQL Connect 中 upsert 用户个人资料（例如显示名称和物理位置）。您还将使用 SQL Connect 的 @transaction 和 @check 指令安全地执行原子多步市场事件。

实现用户和位置连接器

打开 dataconnect/friendly-exchange/mutations.gql。通过添加以下变更来处理创建、更新和查找用户，替换 TODO：

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

重点小结

id_expr: "auth.uid": 这使用 auth.uid，它由 Firebase Authentication 令牌直接提供。通过在服务器端评估此值，您可以确保用户只能更新自己的个人资料数据，从而增加一层牢不可破的安全性。

使用`@transaction` 链接逻辑

接下来，您将实现一个“做市商”，管理员可以触发该做市商来模拟随机市场活动。由于这需要同时更新表情符号的价格、记录事件和更新系统的股票所有权，因此我们需要原子事务。

将此变更添加到 mutations.gql 文件中：

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

重点小结

@transaction：确保所有数据库操作（upsert 股票、更新表情符号价格、记录事件）要么一起成功，要么一起失败。
@check：在继续之前评估条件的指令。在这里，它会检查经过身份验证的用户的 role 是否为 'ADMIN'。如果用户只是标准 'USER'，则整个事务将被拒绝并回滚。
@redact：防止在响应载荷中将查询结果（例如用户角色检查）返回给客户端，从而保持事务响应的简洁性。

生成 SDK

由于您已在 GraphQL 文件中定义了新的变更，因此必须运行 SDK 生成器，以便 TypeScript 前端可以调用它。

打开终端并运行：

firebase dataconnect:sdk:generate

在 Web 应用中集成变更

在 Web 应用中，您将这些生成的 SDK 变更封装在标准异步函数中，以处理错误捕获和界面通知。

打开 lib/ExchangeService.tsx 并查看封装函数。将 TODO 块替换为以下实现：

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);

重点小结：

onGraphRequest：一个专门的 Firebase Functions 封装容器，用于将 Cloud Function 映射到 SQL Connect 自定义解析器架构。
args：从 GraphQL 变更传递的参数会自动在此处进行类型化和提取，以注入到 Gemini 提示中。

将变更添加到连接器

现在自定义解析器逻辑已存在，通过应用的连接器公开它，以便前端可以调用它。

打开 dataconnect/friendly-exchange/mutations.gql 并添加变更：

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

部署并生成 SDK

由于自定义解析器通过 Cloud Functions 运行，因此您必须将函数部署到 Google Cloud，端点才能变为活跃状态。

打开终端并部署函数：

firebase deploy --only functions

部署后，生成前端 SDK 以包含新的 AI 变更：

firebase dataconnect:sdk:generate

在 Web 应用中集成 AI 解析器

我们来设置一下，以便任何交易 10 股或更多股的交易都会触发突发新闻提醒！

打开 lib/ExchangeService.tsx。首先，确保您在顶部导入了 generateTradeHeadline 和 triggerEvent：

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

接下来，向下滚动到 executeBuyStock 函数的底部，并在函数结束之前将 TODO 替换为 AI 触发器块：

// ... (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(),
    });
  }
};

在 executeSellStock 函数的底部执行完全相同的操作：

// ... (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(),
    });
  }
};

看看实际运用情况

重新加载 Web 应用。
确保您已登录并且有足够的货币。
选择一个表情符号并一次购买10 股或更多股 。
查看信息中心右侧的全球市场行情显示器。几秒钟内，您就会看到一个自定义的、由 Gemini 生成的讽刺新闻头条！

使用 Firebase SQL Connect 构建实时交易应用 (Web) 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

1. 准备工作

前提条件

学习内容

所需条件

2. 设置您的开发环境

3. 查看起始代码库

文件夹和文件结构

dataconnect/ 目录

目录lib/

生成的 SDK

4. 为表情符号交易定义架构

核心实体和关系

设置 User 表

设置 Emoji 表

设置 StockOwnership 表

设置 Event 和 PriceHistory 表

自动生成的字段和默认值

5. 检索市场和用户数据

插入模拟市场和用户数据

实现基本查询

创建类型安全的 SQL 视图

实现搜索查询

生成 SDK

在 Web 应用中集成查询

看看实际运用情况

6. 处理用户更新和市场交易

实现用户和位置连接器

使用@transaction 链接逻辑

生成 SDK

在 Web 应用中集成变更

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

将变更添加到连接器

部署并生成 SDK

在 Web 应用中集成 AI 解析器

看看实际运用情况

使用 Firebase SQL Connect 构建实时交易应用 (Web)

`dataconnect/` 目录

目录`lib/`

使用`@transaction` 链接逻辑

Add `@refresh` triggers to your queries