Skip to content
Alchemy
GitHubXDiscordRSS

PlanetScale Postgres

This guide shows how to create and manage PlanetScale PostgreSQL databases with automated Drizzle migrations and deploy a Cloudflare Worker to interact with the database using Alchemy.

  1. Install dependencies

    Add the required dependencies to your project:

    Terminal window
    bun add drizzle-orm postgres
    bun add -D drizzle-kit

  2. Set credentials

    Create a PlanetScale account and get your API credentials. Add them to your .env file:

    PLANETSCALE_ORG_ID=your_organization_id
    PLANETSCALE_API_TOKEN=your_api_token
    CLOUDFLARE_ACCOUNT_ID=your_cloudflare_account_id
    CLOUDFLARE_API_TOKEN=your_cloudflare_api_token

  3. Define schema

    Create your PostgreSQL database schema:

    src/schema.ts
    import { pgTable, timestamp, uuid, varchar } from "drizzle-orm/pg-core";
    

    export const users = pgTable("users", {
      id: uuid().primaryKey().defaultRandom(),
      email: varchar({ length: 255 }).notNull(),
      password: varchar({ length: 255 }).notNull(),
      createdAt: timestamp().notNull().defaultNow(),
      updatedAt: timestamp().notNull().defaultNow(),
    });

  4. Configure Drizzle

    Add Drizzle configuration and scripts:

    drizzle.config.ts
    import { defineConfig } from "drizzle-kit";
    

    export default defineConfig({
      out: "./drizzle",
      schema: "./src/schema.ts",
      dialect: "postgresql",
      dbCredentials: {
        url: process.env.DATABASE_URL!,
      },
    });
    package.json
    {
      "scripts": {
        "db:generate": "drizzle-kit generate",
        "db:migrate": "drizzle-kit migrate",
        "db:studio": "drizzle-kit studio"
      }
    }

  5. Create Worker code

    Create the Cloudflare Worker that will interact with your database:

    src/index.ts
    import { drizzle } from "drizzle-orm/postgres-js";
    import postgres from "postgres";
    import { users } from "./schema";
    

    interface Env {
      HYPERDRIVE: Hyperdrive;
    }
    

    export default {
      async fetch(request: Request, env: Env): Promise<Response> {
        const sql = postgres(env.HYPERDRIVE.connectionString);
        const db = drizzle(sql);
    

        if (request.method === "GET") {
          const allUsers = await db.select().from(users);
          return Response.json(allUsers);
        }
    

        if (request.method === "POST") {
          const { email, password } = await request.json();
          const newUser = await db
            .insert(users)
            .values({ email, password })
            .returning();
          return Response.json(newUser[0]);
        }
    

        return new Response("Method not allowed", { status: 405 });
      },
    };

  6. Create your infrastructure

    Create alchemy.run.ts to provision PlanetScale PostgreSQL resources and deploy your Worker:

    /// <reference types="@types/node" />
    

    import alchemy from "alchemy";
    import { Hyperdrive, Worker } from "alchemy/cloudflare";
    import { Exec } from "alchemy/os";
    import { Database, Role } from "alchemy/planetscale";
    

    const app = await alchemy("planetscale-postgres");
    

    // Create the PostgreSQL database
    const database = await Database("Database", {
      name: "sample-database",
      organizationId: alchemy.env.PLANETSCALE_ORG_ID,
      clusterSize: "PS_10",
      kind: "postgresql",
    });
    

    // Create a database role with postgres privileges
    const role = await Role("Role", {
      database,
      branch: database.defaultBranch,
      inheritedRoles: ["postgres"],
    });
    

    // Create Hyperdrive configuration for connection pooling
    const hyperdrive = await Hyperdrive("Hyperdrive", {
      origin: role.connectionUrl,
      caching: { disabled: true },
    });
    

    // Generate Drizzle migrations
    await Exec("DrizzleGenerate", {
      command: "bun run db:generate",
      env: {
        DATABASE_URL: role.connectionUrl,
      },
      memoize: {
        patterns: ["drizzle.config.ts", "src/schema.ts"],
      },
    });
    

    // Apply migrations to the database
    await Exec("DrizzleMigrate", {
      command:
        process.platform === "win32"
          ? `cmd /C "bun run db:migrate || if %ERRORLEVEL%==9 exit 0 else exit %ERRORLEVEL%"`
          : `sh -c 'bun run db:migrate || ( [ $? -eq 9 ] && exit 0 ); exit $?'`,
      env: {
        DATABASE_URL: role.connectionUrl,
      },
      memoize: {
        patterns: ["drizzle.config.ts", "drizzle/*.sql"],
      },
    });
    

    // Deploy the Cloudflare Worker
    export const worker = await Worker("Worker", {
      entrypoint: "src/index.ts",
      compatibility: "node",
      bindings: {
        HYPERDRIVE: hyperdrive,
      },
    });
    

    // Start Drizzle Studio in local development
    if (app.local) {
      Exec("DrizzleStudio", {
        command: "bun run db:studio",
        env: {
          DATABASE_URL: role.connectionUrl,
        },
      });
    }
    

    console.log(worker.url);
    

    await app.finalize();

  7. Deploy your stack

    Run alchemy.run.ts to deploy:

    Terminal window
    bun alchemy deploy

    It should log your Worker URL:

    Terminal window
    https://worker.your-subdomain.workers.dev

  8. Test your API

    Test your deployed Worker:

    Terminal window
    # Create a user
    curl -X POST https://worker.your-subdomain.workers.dev \
      -H "Content-Type: application/json" \
      -d '{"email": "test@example.com", "password": "password123"}'
    

    # Get all users
    curl https://worker.your-subdomain.workers.dev

  9. (Optional) Tear down

    Clean up all resources created by this stack:

    Terminal window
    bun alchemy destroy