SQL database

import * as gensx from "@gensx/core";
import { useDatabase } from "@gensx/storage";
 
const QueryTeamStats = gensx.Component("QueryTeamStats", async ({ team }) => {
  // Get access to a database (creates it if it doesn't exist)
  const db = await useDatabase("baseball");
 
  // Execute SQL queries with parameters
  const result = await db.execute("SELECT * FROM players WHERE team = ?", [
    team,
  ]);
 
  // Access query results
  console.log(result.columns); // Column names
  console.log(result.rows); // Data rows
  console.log(result.rowsAffected); // Number of rows affected
 
  return result.rows;
});

const InitializeDatabase = gensx.Component("InitializeDatabase", async () => {
  const db = await useDatabase("baseball");
 
  // Create table if it doesn't exist
  await db.execute(`
      CREATE TABLE IF NOT EXISTS baseball_stats (
        player TEXT,
        team TEXT,
        position TEXT,
        at_bats INTEGER,
        hits INTEGER,
        runs INTEGER,
        home_runs INTEGER,
        rbi INTEGER,
        batting_avg REAL
      )
    `);
 
  // Check if data already exists
  const result = await db.execute("SELECT COUNT(*) FROM baseball_stats");
  const count = result.rows[0][0] as number;
 
  if (count === 0) {
    // Insert sample data
    await db.execute(`
        INSERT INTO baseball_stats (player, team, position, at_bats, hits, runs, home_runs, rbi, batting_avg)
        VALUES
          ('Marcus Bennett', 'Portland Pioneers', '1B', 550, 85, 25, 32, 98, 0.312),
          ('Ethan Carter', 'San Antonio Stallions', 'SS', 520, 92, 18, 24, 76, 0.298)
      `);
  }
 
  return "Database initialized";
});

import * as gensx from "@gensx/core";
import { useDatabase } from "@gensx/storage";
import { generateText } from "@gensx/vercel-ai";
import { openai } from "@ai-sdk/openai";
import { tool } from "ai";
import { z } from "zod";
 
// Create a tool that executes SQL queries
const queryTool = tool({
  description: "Execute a SQL query against the baseball database",
  parameters: z.object({
    query: z.string().describe("The SQL query to execute"),
  }),
  execute: async ({ query }) => {
    const db = await useDatabase("baseball");
    const result = await db.execute(query);
    return JSON.stringify(result, null, 2);
  },
});
 
// SQL Copilot component that answers questions using SQL
const SqlCopilot = gensx.Component("SqlCopilot", ({ question }) => {
  return generateText({
    messages: [
      {
        role: "system",
        content: `You are a SQL assistant. The database has a baseball_stats table with
          columns: player, team, position, at_bats, hits, runs, home_runs, rbi, batting_avg.
          Use the execute_query tool to run SQL queries.`,
      },
      { role: "user", content: question },
    ],
    model: openai("gpt-4o-mini"),
    tools: { execute_query: queryTool },
    maxSteps: 10,
  });
});

const TransferFunds = gensx.Component(
  "TransferFunds",
  async ({ fromAccount, toAccount, amount }: TransferFundsInput) => {
    const db = await useDatabase("banking");
 
    try {
      // Execute multiple statements as a transaction
      const result = await db.batch([
        {
          sql: "UPDATE accounts SET balance = balance - ? WHERE account_id = ?",
          params: [amount, fromAccount],
        },
        {
          sql: "UPDATE accounts SET balance = balance + ? WHERE account_id = ?",
          params: [amount, toAccount],
        },
      ]);
 
      return { success: true, rowsAffected: result.rowsAffected };
    } catch (error) {
      return { success: false, error: error.message };
    }
  },
);

const SetupUserSystem = gensx.Component("SetupUserSystem", async () => {
  const db = await useDatabase("users");
 
  // Execute a SQL script with multiple statements
  await db.executeMultiple(`
      CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
      );
 
      CREATE TABLE IF NOT EXISTS user_preferences (
        user_id TEXT PRIMARY KEY,
        theme TEXT DEFAULT 'light',
        notifications BOOLEAN DEFAULT 1,
        FOREIGN KEY (user_id) REFERENCES users(id)
      );
 
      CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
    `);
 
  return "User system set up successfully";
});

const MigrateDatabase = gensx.Component(
  "MigrateDatabase",
  async ({ version }) => {
    const db = await useDatabase("app_data");
 
    if (version === "v2") {
      // Run migrations with foreign key checks disabled
      await db.migrate(`
        ALTER TABLE products ADD COLUMN category TEXT;
        CREATE TABLE product_categories (
          id INTEGER PRIMARY KEY,
          name TEXT NOT NULL,
          description TEXT
        );
      `);
 
      return "Database migrated to v2";
    }
 
    return "No migration needed";
  },
);

const DataAnalyst = gensx.Component(
  "DataAnalyst",
  async ({ query }: DataAnalystInput) => {
    // Initialize the database with the baseball stats
    await InitializeDatabase();
 
    // Use the SQL Copilot to answer the question
    return await SqlCopilot({ question: query });
  },
);

const UserPreferences = gensx.Component(
  "UserPreferences",
  async ({ userId, action, data }: UserPreferencesInput) => {
    const db = await useDatabase("user_data");
 
    if (action === "get") {
      const result = await db.execute(
        "SELECT * FROM preferences WHERE user_id = ?",
        [userId],
      );
      return result.rows.length > 0 ? result.rows[0] : null;
    } else if (action === "set") {
      await db.execute(
        "INSERT OR REPLACE INTO preferences (user_id, settings) VALUES (?, ?)",
        [userId, JSON.stringify(data)],
      );
      return { success: true };
    }
  },
);

const DataCollector = gensx.Component(
  "DataCollector",
  async ({ source }: DataCollectorInput) => {
    const db = await useDatabase("workflow_data");
    // Collect data from source and store in database
    // ...
    return { success: true };
  },
);
 
const DataAnalyzer = gensx.Component(
  "DataAnalyzer",
  async ({ query }: DataAnalyzerInput) => {
    const db = await useDatabase("workflow_data");
    // Analyze data from database
    // ...
    return { results: "..." };
  },
);