While actors can serve as a complete database solution, they can also complement your existing databases. For example, you might use actors to handle frequently-changing data that needs real-time access, while keeping less frequently accessed data in your traditional database.

Actors can be used with common SQL databases, such as PostgreSQL and MySQL.

​
Libraries

To facilitate interaction with SQL databases, you can use either ORM libraries or raw SQL drivers. Each has its own use cases and benefits:

  • ORM Libraries: Type-safe and easy way to interact with your database

  • Raw SQL Drivers: Direct access to the database for more flexibility

​
Hosting Providers

There are several options for places to host your SQL database:

​
Example

Here’s a basic example of how you might set up a connection to a PostgreSQL database using the pg library:

actor.ts
import { actor } from "actor-core";
import { Pool } from "pg";

// Create a database connection pool
const pool = new Pool({
  user: "your_db_user",
  host: "localhost",
  database: "your_db_name",
  password: "your_db_password",
  port: 5432,
});

// Create the actor
const databaseActor = actor({
  state: {
    // Local state if needed
    lastQueryTime: 0
  },
  
  // Initialize any resources
  onStart: (c) => {
    console.log("Database actor started");
  },
  
  // Clean up resources if needed
  onShutdown: async (c) => {
    await pool.end();
    console.log("Database connections closed");
  },
  
  // Define actions
  actions: {
    // Example action to fetch data from database
    fetchData: async (c) => {
      try {
        const result = await pool.query("SELECT * FROM your_table");
        c.state.lastQueryTime = Date.now();
        return result.rows;
      } catch (error) {
        console.error("Error fetching data:", error);
        throw new Error("Failed to fetch data");
      }
    },
    
    // Example action to insert data into database
    insertData: async (c, data) => {
      try {
        await pool.query(
          "INSERT INTO your_table (column1, column2) VALUES ($1, $2)", 
          [data.value1, data.value2]
        );
        
        c.state.lastQueryTime = Date.now();
        return { success: true };
      } catch (error) {
        console.error("Error inserting data:", error);
        throw new Error("Failed to insert data");
      }
    }
  }
});

export default databaseActor;

​
With Drizzle ORM

Here’s an example using Drizzle ORM for more type-safe database operations:

actor.ts
import { actor } from "actor-core";
import { drizzle } from "drizzle-orm/node-postgres";
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";
import { Pool } from "pg";

// Define your schema
const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  createdAt: timestamp("created_at").defaultNow()
});

// Create a database connection
const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

// Initialize Drizzle with the pool
const db = drizzle(pool);

// Create the actor
const userActor = actor({
  state: {
    // Actor state (frequently accessed data can be cached here)
    userCache: {}
  },
  
  actions: {
    // Get a user by ID
    getUser: async (c, userId) => {
      // Check cache first
      if (c.state.userCache[userId]) {
        return c.state.userCache[userId];
      }
      
      // Query the database
      const result = await db.select().from(users).where(eq(users.id, userId));
      
      if (result.length === 0) {
        throw new Error(`User ${userId} not found`);
      }
      
      // Cache the result
      c.state.userCache[userId] = result[0];
      return result[0];
    },
    
    // Create a new user
    createUser: async (c, userData) => {
      const result = await db.insert(users).values({
        name: userData.name,
        email: userData.email
      }).returning();
      
      // Broadcast the new user event
      c.broadcast("userCreated", result[0]);
      
      return result[0];
    }
  }
});

export default userActor;