Neon serverless driver installation
Install the Neon serverless driver using: npm install @neondatabase/serverless
drizzle-orm/setup
326 notes in this subject, read out of this brain and free to use. This is page 4 of 6.
Drizzle schema definition for Encore
Create a schema.ts file to define tables using drizzle-orm/pg-core. Example: export a pgTable called "users" with columns: id (serial, primary key), name (text, not null), email (text, unique, not null), and createdAt (timestamp, default now, not null).
Install Drizzle ORM and drizzle-kit for Encore
To add Drizzle to an existing Encore project, install drizzle-orm@rc as a dependency and drizzle-kit@rc as a dev dependency using npm.
Drizzle ORM setup with Encore backend framework
To set up Drizzle ORM with Encore, create a database.ts file using Encore's SQLDatabase and initialize Drizzle with the connection string. Encore automatically provisions a PostgreSQL database locally using Docker and in the cloud when deployed. Set the migration source to "drizzle" in the SQLDatabase configuration to use Drizzle's migration format.
Define Encore database in database.ts
Example database configuration for Encore with Drizzle: Import SQLDatabase from encore.dev/storage/sqldb, drizzle from drizzle-orm/node-postgres, and your schema. Create a new SQLDatabase with name "mydb" and configure migrations with path: "migrations" and source: "drizzle". Export the initialized Drizzle ORM instance with the database connection string and schema.
Create Encore project with Drizzle pre-configured
Run 'encore app create my-app --example=ts/drizzle' to create a new Encore project with Drizzle already configured. This provides a starter template with database, schema, and configuration files set up.
Drizzle connection with node-postgres for Bun
To connect Drizzle ORM to PostgreSQL using the pg (node-postgres) driver in Bun, import drizzle from drizzle-orm/node-postgres and initialize with the DATABASE_URL: import { drizzle } from "drizzle-orm/node-postgres"; export const db = drizzle(process.env.DATABASE_URL!);
Drizzle ORM setup with Bun and PostgreSQL
To set up Drizzle ORM with Bun runtime and PostgreSQL, install drizzle-orm@rc, drizzle-kit@rc as dev dependencies, and the pg package with @types/pg. Create a db.ts file that imports drizzle from drizzle-orm/node-postgres and initializes it with process.env.DATABASE_URL. Create a schema.ts file using drizzle-orm/pg-core to declare tables with pgTable(). Create a drizzle.config.ts file with defineConfig specifying schema, out, dialect as postgresql, and dbCredentials with the DATABASE_URL.
PostgreSQL table schema declaration example with Drizzle
Example of declaring PostgreSQL tables with Drizzle: ```typescript import * as p from "drizzle-orm/pg-core"; export const usersTable = p.pgTable("users", { id: p.serial().primaryKey(), name: p.text().notNull(), age: p.integer().notNull(), email: p.text().notNull().unique(), }); export const postsTable = p.pgTable("posts", { id: p.serial().primaryKey(), title: p.text().notNull(), content: p.text().notNull(), userId: p .integer() .notNull() .references(() => usersTable.id, { onDelete: "cascade" }), createdAt: p.timestamp().notNull().defaultNow(), updatedAt: p .timestamp() .notNull() .$onUpdate(() => new Date()), }); export type InsertUser = typeof usersTable.$inferInsert; export type SelectUser = typeof usersTable.$inferSelect; export type InsertPost = typeof postsTable.$inferInsert; export type SelectPost = typeof postsTable.$inferSelect; ```
Setting DATABASE_URL environment variable in .env for local Drizzle development
Create a .env file in the project root with DATABASE_URL set to the PostgreSQL public connection string from Railway. Example: DATABASE_URL=postgresql://postgres:password@region.railway.app:port/railway. This .env file is for local development only. When deploying to Railway, configure DATABASE_URL separately in the Railway dashboard using a service reference variable.
PostgreSQL timestamp with defaultNow() and $onUpdate() in Drizzle
In Drizzle PostgreSQL schema, use p.timestamp().notNull().defaultNow() to create a timestamp column that defaults to the current time, and p.timestamp().notNull().$onUpdate(() => new Date()) to create a timestamp that updates to the current date on every update.
Type inference from Drizzle tables with $inferInsert and $inferSelect
Drizzle tables support type inference for insert and select operations using $inferInsert and $inferSelect. Example: export type InsertUser = typeof usersTable.$inferInsert; export type SelectUser = typeof usersTable.$inferSelect;
Inferring insert and select types from tables
Use typeof tableName.$inferInsert to create a TypeScript type for insert operations and typeof tableName.$inferSelect to create a type for select operations. These inferred types match the table schema.
Drizzle ORM connection to Neon Postgres using neon-http
Create a db.ts file that imports drizzle from 'drizzle-orm/neon-http' and the neon function from '@neondatabase/serverless'. Initialize the neon client with the DATABASE_URL environment variable, then export the drizzle database instance created with the neon client.
Configure import_map.json for Netlify Edge Functions
Create an import_map.json file in the root of your project with the following content to enable Deno imports in Edge Functions: { "imports": { "drizzle-orm/": "https://esm.sh/drizzle-orm/", "@neondatabase/serverless": "https://esm.sh/@neondatabase/serverless" } }
Test Netlify Edge Functions locally
Run 'netlify dev' to start the Netlify dev server locally. The first run will suggest configuring VS Code to use Edge Functions. Answer yes to configure it, which creates settings.json in the .vscode directory. Navigate to the route in your browser (e.g., /user) to test your Edge Function.
Deploy Netlify Edge Functions
Run 'netlify init' to initialize a new Netlify project, then 'netlify env:import .env' to import environment variables into Netlify. Run 'netlify deploy' for a draft deployment or 'netlify deploy --prod' for production deployment. Navigate to the deployed website URL with the route (e.g., /user) to access your Edge Function.
Drizzle ORM with Netlify Edge Functions and Neon setup overview
This tutorial demonstrates how to use Drizzle ORM with Netlify Edge Functions and Neon Postgres database. It covers installation of required packages (drizzle-orm, drizzle-kit, dotenv, and optionally @netlify/edge-functions), configuring the database connection, creating table schemas, setting up Drizzle config, applying schema changes, connecting to the database within Edge Functions, testing locally, and deploying to Netlify.
Install packages for Drizzle with Netlify Edge Functions
Install drizzle-orm@rc, drizzle-kit@rc as a dev dependency, and dotenv package. If using Node.js v20.6.0 or later, dotenv is not needed because Node.js natively supports .env files. Optionally install @netlify/edge-functions to import types for the Context object.
Configure netlify.toml for Drizzle Edge Functions
Create a netlify.toml file in the root of your project with the following configuration to use import_map.json and route requests to Edge Functions: [functions] deno_import_map = "./import_map.json" [[edge_functions]] path = "/user" function = "user"
Connect Drizzle ORM to Neon database in Netlify Edge Functions
To connect Drizzle ORM to Neon in an Edge Function, import the neon client from @neondatabase/serverless and create a drizzle instance: const sql = neon(Netlify.env.get("DATABASE_URL")!); const db = drizzle({ client: sql }); Then use the db object to execute queries like db.select().from(usersTable).
Drizzle packages in Netlify Edge Functions are for schema setup only
The installed packages (drizzle-orm, drizzle-kit, dotenv, @netlify/edge-functions) are used only for creating tables, setting up the Drizzle config file, and applying database changes during development. These packages do not affect the code running inside Netlify Edge Functions. Instead, an import_map.json file is used to import the necessary packages for the Edge Functions.
Create Netlify Edge Functions directory structure
Create a netlify/edge-functions directory in the root of your project to store Edge Functions. Create a function file (e.g., user.ts) in the netlify/edge-functions directory. The function receives Request and Response objects which are in global scope and a Context object from @netlify/edge-functions.
Drizzle ORM schema declaration for PostgreSQL with Netlify
Create a schema.ts file in netlify/edge-functions/common directory. Use pgTable, serial, text, and integer from drizzle-orm/pg-core. Example: pgTable('users_table', { id: serial('id').primaryKey(), name: text('name').notNull(), age: integer('age').notNull(), email: text('email').notNull().unique() })
Connect Drizzle ORM to database in Netlify Edge Functions
Use postgres-js driver to connect. Import drizzle from 'drizzle-orm/postgres-js' and postgres from 'postgres'. Create a queryClient with postgres(Netlify.env.get('DATABASE_URL')!). Then create the db instance with drizzle({ client: queryClient }). Access environment variables in Edge Functions using Netlify.env.get().
import_map.json configuration for Netlify Edge Functions
Create import_map.json in the root of your project with imports for drizzle-orm and postgres. Use ESM CDN URLs: { "imports": { "drizzle-orm/": "https://esm.sh/drizzle-orm/", "postgres": "https://esm.sh/postgres" } }
netlify.toml configuration for Edge Functions
Create netlify.toml in the root of your project. Set [functions] deno_import_map to point to import_map.json. Add [[edge_functions]] section with path and function name. Example: [functions] deno_import_map = "./import_map.json", [[edge_functions]] path = "/user", function = "user"
Supabase connection with Drizzle ORM
Create a new Supabase project via dashboard or database.new. Find Project connect details by clicking Connect in the top bar and copy the URI from the Transaction pooler section. Replace the password placeholder with your actual database password. Store this as DATABASE_URL environment variable.
Test Netlify Edge Functions locally
Run 'netlify dev' to start the Netlify development server. The first run will prompt to configure VS Code for Edge Functions. If red underlines appear in VS Code, try restarting the Deno Language Server. Test the function by opening the browser and navigating to the route configured in netlify.toml.
Deploy Netlify project with Drizzle ORM
Run 'netlify init' to initialize a new Netlify project. Run 'netlify env:import .env' to import environment variables. Run 'netlify deploy' for draft deployment or 'netlify deploy --prod' for production deployment. Access the deployed edge function at the configured route.
Netlify Edge Functions setup with Drizzle ORM
Create a netlify/edge-functions directory in the root of your project to store Edge Functions. Create a function file (e.g., user.ts) in this directory. Use import_map.json to import necessary packages for Edge Functions, as the dev dependencies are only used for creating tables and migrations, not for code running inside Netlify Edge Functions.
Setup DATABASE_URL environment variable in Supabase Edge Function
For production deployment, use DATABASE_URL environment variable instead of SUPABASE_DB_URL. Find the connection string in the dashboard by clicking Connect and copying the URI from the Transaction pooler section. Set the environment variable using 'supabase secrets set DATABASE_URL=<CONNECTION_STRING>'.
postgres client configuration for Transaction pool mode
When creating a postgres client for use with Supabase's Transaction pooler, use { prepare: false } option to disable prefetch, as prefetch is not supported for Transaction pool mode.
Each Supabase Edge Function is independent
In the Deno ecosystem, each Supabase Edge Function should be treated as an independent project with its own set of dependencies and configurations. Maintain separate configuration files (deno.json, .npmrc, or import_map.json) within each function's directory, even if it means duplicating some configurations.
Drizzle with Supabase Edge Functions setup overview
This tutorial demonstrates how to use Drizzle ORM with Supabase Edge Functions. Prerequisites include the latest Supabase CLI, Drizzle ORM and Drizzle Kit (both @rc versions), and Docker Desktop for local development.
Create schema.ts for Supabase Edge Functions
Create a schema.ts file in the src directory and declare a table schema using drizzle-orm/pg-core imports. The schema file is used to generate migrations for the database.
Example schema.ts with usersTable for Supabase
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() })
Initialize Supabase project locally
Run 'supabase init' command to create a new Supabase project folder locally. This creates a supabase directory with a config.toml file.
Create Supabase Edge Function
Run 'supabase functions new [FUNCTION_NAME]' to create a new Edge Function. For example, 'supabase functions new drizzle-tutorial' creates a new folder in supabase/functions/drizzle-tutorial with index.ts, deno.json, and .npmrc files.
Setup deno.json imports for Edge Function
In the deno.json file within the Edge Function directory (supabase/functions/drizzle-tutorial/deno.json), add imports for drizzle-orm and postgres. Each function should have its own deno.json file with necessary imports as separate functions are independent projects.
Example deno.json for Edge Function with Drizzle
{ "imports": { "drizzle-orm/": "npm:/drizzle-orm/", "postgres": "npm:postgres" } }
Connect Drizzle ORM to Supabase database in Edge Function
Import drizzle from drizzle-orm/postgres-js and postgres. Get the connection string from Deno.env.get("SUPABASE_DB_URL"). Create a postgres client with prepare: false to disable prefetch as it is not supported for Transaction pool mode. Then initialize drizzle with the client.
Example Drizzle ORM connection in Supabase Edge Function
import { drizzle } from "drizzle-orm/postgres-js"; import postgres from "postgres"; const connectionString = Deno.env.get("SUPABASE_DB_URL")!; const client = postgres(connectionString, { prepare: false }); const db = drizzle({ client });
Test Edge Function locally
Run 'supabase functions serve --no-verify-jwt' to test the function locally. Navigate to the route in the browser (e.g., /drizzle-tutorial) to see the results.
Link local Supabase project to hosted project
Create a new Supabase project in the dashboard or at database.new(). Copy the Reference ID from project settings and run 'supabase link --project-ref=<REFERENCE_ID>' to link the local development project to a hosted Supabase project.
Push schema changes to hosted Supabase project
Run 'supabase db push' to push schema changes from the local project to the hosted Supabase project.
Deploy Supabase Edge Function
Deploy an Edge Function by running 'supabase functions deploy [FUNCTION_NAME] --no-verify-jwt'. For example, 'supabase functions deploy drizzle-tutorial --no-verify-jwt'. After deployment, access the function using the URL of the deployed project and the route (e.g., /drizzle-tutorial).
Supabase project with Drizzle comes with Postgres database
Every Supabase project includes a full PostgreSQL database that can be connected to using Drizzle ORM.
Project file structure for Supabase and Drizzle
Standard structure includes: src/db/index.ts for database connection, src/db/schema.ts for table definitions, supabase/migrations directory for generated migration files, supabase/migrations/meta directory for _journal.json and snapshot files, .env for environment variables, drizzle.config.ts in project root, package.json, and tsconfig.json.
Drizzle ORM with Supabase setup - connection
To connect Drizzle ORM to Supabase, create a file at src/db/index.ts. Import config from dotenv, drizzle from drizzle-orm/postgres-js, and postgres from postgres package. Call config() with path to .env file, create a postgres client with process.env.DATABASE_URL, and export a drizzle instance: export const db = drizzle({ client }).
Supabase table schema with Drizzle - complete example
Create schema.ts file in src/db directory. Import column types from drizzle-orm/pg-core. Define usersTable with pgTable containing: id (serial, primary key), name (text, not null), age (integer, not null), email (text, not null, unique). Define postsTable with: id (serial, primary key), title (text, not null), content (text, not null), userId (integer, not null, foreign key to usersTable.id with onDelete cascade), createdAt (timestamp, not null, default now), updatedAt (timestamp, not null, with $onUpdate returning new Date). Export inferred types: InsertUser, SelectUser, InsertPost, SelectPost.
Supabase connection string from database settings
Navigate to Database Settings in Supabase dashboard and copy the URI from the Connection String section. Enable connection pooling. Replace the password placeholder with actual database password. Add this to DATABASE_URL environment variable in .env or .env.local file.
Turso database creation with CLI
Create a new Turso database using the command `turso db create <DATABASE_NAME>`. View database information with `turso db show <DATABASE_NAME>`.
Drizzle ORM supports libSQL driver natively
Drizzle ORM has native support for the libSQL driver and mirrors popular SQLite-like query methods including `all`, `get`, `values`, and `run`.
Turso ORM connection setup with libSQL
To connect Drizzle ORM to Turso, create a `drizzle` instance using the libSQL driver. Import `drizzle` from `'drizzle-orm/libsql'` and pass a connection object with `url` (the Turso connection URL) and `authToken` (the Turso authentication token) from environment variables.
Turso prerequisite packages
To use Drizzle ORM with Turso, install: drizzle-orm, drizzle-kit, dotenv (for environment variable management), @libsql/client (the Turso client library), and Turso CLI.
Turso environment variables
Store Turso connection credentials in `.env` or `.env.local` file with two variables: `TURSO_CONNECTION_URL` (the database connection URL) and `TURSO_AUTH_TOKEN` (the authentication token created via `turso db tokens create`).
SQLite schema for Turso with Drizzle
Use `sqliteTable` from `'drizzle-orm/sqlite-core'` to define tables for Turso. Columns can be defined with `integer`, `text`, and other SQLite column types. Support foreign key references with `.references()`, unique constraints with `.unique()`, default values with `.default()`, and computed values with `.$onUpdate()`.
Infer TypeScript types from Turso tables
Use `typeof table.$inferInsert` and `typeof table.$inferSelect` to generate TypeScript types for insert and select operations on a table.
Turso authentication token creation
Generate an authentication token for a Turso database using `turso db tokens create <DATABASE_NAME>`. This token is required for Drizzle ORM connection.