Drizzle with Supabase Edge Functions

Initialize a new Supabase project

Create a new Supabase project in a folder on your local machine:

supabase init

It will create supabase folder with config.toml file:

└── supabase
    └── config.toml

If you are using Visual Studio Code, follow the Supabase documentation to setup settings for Deno.

Create a new Edge Function

Run the supabase functions new [FUNCTION_NAME] command to create a new Edge Function:

supabase functions new drizzle-tutorial

It will create a new folder with the function name in the supabase/functions directory:

└── supabase
    └── functions
    │   └── drizzle-tutorial
    │   │   └── index.ts ## Your function code

Setup imports

Create a import_map.json file in the supabase/functions directory and add import for Drizzle ORM:

{
  "imports": {
    "drizzle-orm": "npm:drizzle-orm@0.30.10",
    "drizzle-orm/": "npm:/drizzle-orm@0.30.10/"
  }
}

Learn more in the documentation.

Create a table

Create a schema.ts file in the supabase/functions/common directory and declare a table schema:

import { pgTable, serial, text, integer } from "drizzle-orm/pg-core";

export const usersTable = pgTable('users_table', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age').notNull(),
  email: text('email').notNull().unique(),
})

Setup Drizzle config file

Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.

Create a drizzle.config.ts file in the root of your project and add the following content:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./supabase/functions/common/schema.ts",
  out: "./supabase/migrations",
  dialect: "postgresql",
});

In this tutorial we will use Drizzle kit to generate migrations for our schema.

Generate migrations

Run the drizzle-kit generate command to generate migrations:

npx drizzle-kit generate

It will create a new migration file in the supabase/migrations directory:

Apply migrations

To apply migrations and start the Supabase local development stack, run the following command:

supabase start

Alternatively, you can apply migrations using the migrate() helper, available for every supported driver. Learn more about this migration process in the documentation.

Connect Drizzle ORM to your database

Create a db.ts file in the supabase/common directory and set up your database configuration:

import postgres from "npm:postgres";
import { drizzle } from "drizzle-orm/postgres-js";

const connectionString = Deno.env.get("SUPABASE_DB_URL")!;

// Disable prefetch as it is not supported for "Transaction" pool mode
const client = postgres(connectionString, { prepare: false });
export const db = drizzle(client);

SUPABASE_DB_URL is default environment variable for the direct database connection. Learn more about managing environment variables in Supabase Edge Functions in the documentation.

Test your code locally

import { db } from "../common/db.ts";
import { usersTable } from "../common/schema.ts";

Deno.serve(async (req) => {
  await db.insert(usersTable).values({
    name: "Alice",
    email: "email",
    age: 25
  })
  const data = await db.select().from(usersTable);

  return new Response(
    JSON.stringify(data),
    { headers: { "Content-Type": "application/json" } },
  );
});

Run the following command to test your function locally:

supabase functions serve --no-verify-jwt

Navigate to the route (e.g. /drizzle-tutorial) in your browser:

[
  {
    "id": 1,
    "name": "Alice",
    "age": 25,
    "email": "email"
  }
]

Link your local project to a hosted Supabase project

You can create new Supabase project in the dashboard or by following this link.

Copy the Reference ID and use it to link your local development project to a hosted Supabase project by running the following command:

supabase link --project-ref <REFERENCE_ID>

Push your schema changes to the hosted Supabase project by running the following command:

supabase db push

Setup environment variables

Navigate to Database Settings and copy the URI from the Connection String section. Make sure to use connection pooling and Transaction mode to connect to your database. Remember to replace the password placeholder with your actual database password.

Read more about Connection Pooler in the documentation.

Run the following command to set the environment variable:

supabase secrets set DATABASE_URL=<CONNECTION_STRING>

Update the db.ts file in the supabase/functions/common directory to use the DATABASE_URL environment variable:

// imports

const connectionString = Deno.env.get("DATABASE_URL")!;

// code

Learn more about managing environment variables in Supabase Edge Functions in the documentation.

Deploy your function

Deploy your function by running the following command:

supabase functions deploy drizzle-tutorial --no-verify-jwt