Drizzle ORM is a TypeScript ORM that keeps you close to SQL. You define tables in TypeScript, query them with a type-safe builder, and manage schema changes with Drizzle Kit migrations.
How does the integration work?
The Postgres with Drizzle function template serves a todo REST API from an Appwrite native PostgreSQL database. It's written in TypeScript, defines a todos table with Drizzle, and connects with the postgres.js driver with prepared statements turned off, so it works through the connection pooler.
| Route | Purpose |
|---|---|
GET / | Lists all todos |
POST / | Creates a todo from a JSON body with a title of 1 to 255 characters |
GET /:id | Returns one todo |
PATCH /:id | Updates the title or completed value of a todo |
DELETE /:id | Deletes a todo |
Use it as a template for any API that keeps its data in PostgreSQL and its schema in Drizzle migrations.
Native PostgreSQL databases run on Appwrite Cloud and are available on paid plans. See billing and plan requirements.
Prerequisites
- An Appwrite Cloud project on a paid plan.
- Node.js and Git on your machine, to run the migrations.
How to implement
To implement the Drizzle integration, there are several steps you must complete:
Step 1: Create a PostgreSQL database
In your Appwrite project, go to Databases and click Create database. Under Choose database type, select PostgreSQL from the Native databases group, name the database, pick a specification, and click Create database. Wait until it's ready.
Open the database and click Credentials to copy its connection string from the DSN tab. The string uses the direct port, 5432. The connection pooler listens on port 6432 of the same host, so the pooled string is the same with the port changed.
Step 2: Create the Appwrite Function
In the Appwrite Console, go to Functions, open the Templates tab, search for Postgres with Drizzle, and click Create from template. Under Template variables, set DATABASE_URL to the pooled connection string, on port 6432.
Choose Connect your repository so the template's code, including its Drizzle migrations, lives in a repository you can clone. Then click Create and deploy.
Step 3: Apply the migrations
The function doesn't create tables. Clone the new repository, then apply the migrations in its drizzle/ folder from your machine. Run them over the direct connection on port 5432, since schema changes expect a session-level connection:
npm installDATABASE_URL="postgresql://admin:<PASSWORD>@db-<HASH>.<REGION>.appwrite.center:5432/<DATABASE>?sslmode=require" npm run db:migrateWhen you change src/schema.ts later, run npm run db:generate to create a new migration, apply it with npm run db:migrate, and push the code to deploy the function again.
Step 4: Test the API
Copy the function's domain from the Domains tab and create a todo:
curl -X POST https://<FUNCTION_DOMAIN> \ -H "Content-Type: application/json" \ -d '{"title": "Write the docs"}'The function responds with 201 and the new todo. List, update, and delete todos with the other routes:
curl https://<FUNCTION_DOMAIN>
curl -X PATCH https://<FUNCTION_DOMAIN>/1 \ -H "Content-Type: application/json" \ -d '{"completed": true}'
curl -X DELETE https://<FUNCTION_DOMAIN>/1You can also check the rows in the database's SQL editor in the Console.
The template allows anyone to execute the function, so anyone who finds its domain can read, change, and delete your todos. Before you store real data, replace Any in the function's execute access with narrower roles, such as Users, and call the function with an Appwrite SDK instead of its domain. To keep using the domain, check a JWT from each caller in your code instead.
Read more about Drizzle and Appwrite Databases
If you would like to learn more about Drizzle and Appwrite Databases, we have some resources that you should visit:
