Testing the limits of Supabase + Postgres in a serverless environment

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

ALTER TABLE users DISABLE ROW LEVEL SECURITY;

INSERT INTO users (name, email)
SELECT
    'User ' || gs AS name,
    'user' || gs || '@example.com' AS email
FROM generate_series(1, 100000) AS gs;

import express from "express";
import { createClient } from "@supabase/supabase-js";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { pgTable, serial, text } from "drizzle-orm/pg-core";

const app = express();
const port = 3000;

const supabase = createClient(
  "http://127.0.0.1:54321",
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0"
);

const client = postgres(
  "postgresql://postgres:postgres@127.0.0.1:54322/postgres"
);

const usersTable = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
});
export const db = drizzle(client, {
  schema: {
    usersTable,
  },
});

app.get("/", (req, res) => {
  res.send("Hello World!");
});

app.get("/supabasejs", async (req, res) => {
  try {
    const { data, error } = await supabase.from("users").select().limit(100);
    if (error) return res.status(400).send(error.message);
    res.json(data);
  } catch (e) {
    console.log(e);
    res.status(500).send(e.message);
  }
});

app.get("/drizzle", async (req, res) => {
  try {
    const users = await db.query.usersTable.findMany({
      limit: 100,
    });
    res.json(users);
  } catch (e) {
    console.log(e);
    res.status(500).send(e.message);
  }
});

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`);
});

import { createClient } from "@supabase/supabase-js";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { pgTable, serial, text } from "drizzle-orm/pg-core";

const usersTable = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
});
export default {
  async fetch(request, env, ctx): Promise<Response> {
    const url = new URL(request.url);
    if (url.pathname === "/supabasejs") {
      const supabase = createClient(
        "http://127.0.0.1:54321",
        "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0",
      );
      const { data, error } = await supabase.from("users").select().limit(100);
      if (error) return new Response(error.message, { status: 500 });

      return new Response(JSON.stringify(data), {
        headers: {
          "content-type": "application/json",
        },
      });
    } else if (url.pathname === "/drizzle") {
      const client = postgres(
        "postgresql://postgres:postgres@127.0.0.1:54322/postgres",
      );

      const db = drizzle(client, {
        schema: {
          usersTable,
        },
      });
      const users = await db.query.usersTable.findMany({
        limit: 100,
      });
      return new Response(JSON.stringify(users), {
        headers: {
          "content-type": "application/json",
        },
      });
    }
    return new Response("Hello World!");
  },
} satisfies ExportedHandler<Env>;

import { stdout, stderr } from "node:process";
import autocannon, { printResult } from "autocannon";

function print(result) {
  const printedResult = printResult(result);
  stdout.write(printedResult);
  return printedResult;
}

const urls = [
  "http://localhost:8787/supabasejs",
  "http://localhost:8787/drizzle",
  "http://localhost:3000/supabasejs",
  "http://localhost:3000/drizzle",
];

async function loadTest(url) {
  return new Promise((resolve, reject) => {
    const instance = autocannon(
      {
        url,
        connections: 100,
        pipelining: 100,
        duration: 60,
      },
      (err, result) => {
        if (err) {
          reject(err);
        } else {
          const printedResult = print(result);
          resolve({ result, printedResult, responseCounts });
        }
      },
    );

    let completedRequests = 0;
    const responseCounts = {
      "2xx": 0,
      "3xx": 0,
      "4xx": 0,
      "5xx": 0,
      other: 0,
    };

    instance.on("response", (client, statusCode) => {
      completedRequests += 1;
      if (statusCode >= 200 && statusCode < 300) {
        responseCounts["2xx"] += 1;
      } else if (statusCode >= 300 && statusCode < 400) {
        responseCounts["3xx"] += 1;
      } else if (statusCode >= 400 && statusCode < 500) {
        responseCounts["4xx"] += 1;
      } else if (statusCode >= 500 && statusCode < 600) {
        responseCounts["5xx"] += 1;
      } else {
        responseCounts.other += 1;
      }

      console.log(`Completed Requests for ${url}: ${completedRequests}`);
      console.log(
        `2xx: ${responseCounts["2xx"]}, 3xx: ${responseCounts["3xx"]}, 4xx: ${responseCounts["4xx"]}, 5xx: ${responseCounts["5xx"]}, other: ${responseCounts.other}`,
      );
    });
  });
}

async function runTests() {
  const results = [];
  for (const url of urls) {
    console.log(`Running load test for ${url}`);
    try {
      const result = await loadTest(url);
      results.push(result);
      console.log(`Completed load test for ${url}\n`);
    } catch (error) {
      stderr.write(`Error during load test for ${url}: ${error.message}\n`);
    }
  }
  summarizeResults(results);
}

function summarizeResults(results) {
  console.log("\nSummary of Results:\n");
  results.forEach(({ result, printedResult, responseCounts }) => {
    console.log(`Results for ${result.url}:`);
    console.log(printedResult);
    console.log(`Response Codes Summary for ${result.url}:`);
    console.log(
      `2xx: ${responseCounts["2xx"]}, 3xx: ${responseCounts["3xx"]}, 4xx: ${responseCounts["4xx"]}, 5xx: ${responseCounts["5xx"]}, other: ${responseCounts.other}`,
    );
    console.log("\n");
  });
}

runTests().catch(console.error);