Schema migration with Neon Postgres and Drizzle ORM
Set up Neon Postgres and run migrations for your TypeScript project using Drizzle ORM
Drizzle is a TypeScript-first ORM that connects to all major databases and works across most Javascript runtimes. It provides a simple way to define database schemas and queries in an SQL-like dialect and tools to generate and run migrations.
This guide shows how to use Drizzle
with the Neon
Postgres database in a Typescript project. We'll create a simple Node.js application with Hono.js
and demonstrate the full workflow of setting up and working with your database using Drizzle
.
Prerequisites
To follow along with this guide, you will need:
- A Neon account. If you do not have one, sign up at Neon. Your Neon project comes with a ready-to-use Postgres database named
neondb
. We'll use this database in the following examples. - Node.js and npm installed on your local machine. We'll use Node.js to build and test the application locally.
Setting up your Neon database
Initialize a new project
- Log in to the Neon Console and navigate to the Projects section.
- Select a project or click the
New Project
button to create a new one.
Retrieve your Neon database connection string
Navigate to the Connection Details section to find your database connection string. It should look similar to this:
Keep your connection string handy for later use.
note
Neon supports both direct and pooled database connection strings, which can be copied from the Connection Details widget on your Neon Project Dashboard. A pooled connection string connects your application to the database via a PgBouncer connection pool, allowing for a higher number of concurrent connections. However, using a pooled connection string for migrations can lead to errors. For this reason, we recommend using a direct (non-pooled) connection when performing migrations. For more information about direct and pooled connections, see Connection pooling.
Setting up the TypeScript application
Create a new Hono.js project
We'll create a simple catalog, with API endpoints that query the database for authors and a list of their books. Run the following command in your terminal to set up a new project using Hono.js
:
This initiates an interactive CLI prompt to set up a new project. To follow along with this guide, you can use the following settings:
To use Drizzle and connect to the Neon database, we also add the drizzle-orm
and drizzle-kit
packages to our project, along with the Neon serverless
driver library.
Add the DATABASE_URL
environment variable to your .env
file, which you'll use to connect to our Neon database. Use the connection string that you obtained from the Neon Console earlier:
Test that the starter Hono.js
application works by running npm run dev
in the terminal. You should see the Hello, Hono!
message when you navigate to http://localhost:3000
in your browser.
Set up the database schema
Now, we will define the schema for the application using the Drizzle
ORM. Create a new schema.ts
file in your src
directory and add the following code:
The code defines two tables: authors
, which will contain the list of all the authors, and books
, which will contain the list of books written by the authors. Each book is associated with an author using the authorId
field.
To generate a migration to create these tables in the database, we'll use the drizzle-kit
command. Add the following script to the package.json
file at the root of your project:
Then, run the following command in your terminal to generate the migration files:
This command generates a new folder named drizzle
containing the migration files for the authors
and books
tables.
Run the migration
The generated migration file is written in SQL and contains the necessary commands to create the tables in the database. To apply these migrations, we'll use the Neon serverless driver and helper functions provided by the drizzle-orm
library.
Create a new migrate.ts
in your src
directory and add the following code:
The drizzle-orm
package comes with an integration for Neon
, which allows us to run the migrations using the migrate
function. Add a new script to the package.json
file that executes the migration.
You can now run the migration script using the following command:
You should see the Migration completed
message in the terminal, indicating that the migration was successful.
Seed the database
To test the application works, we need to add some example data to our tables. Create a new file at src/seed.ts
and add the following code to it:
This script inserts some seed data into the authors
and books
tables. Add a new script to the package.json
file that runs the seeding program.
Run the seed script using the following command:
You should see the Seeding completed
message in the terminal, indicating that the seed data was inserted into the database.
Implement the API endpoints
Now that the database is set up and populated with data, we can implement the API to query the authors and their books. Replace the existing src/index.ts
file with the following code:
This code sets up a simple API with two endpoints: /authors
and /books/:authorId
. The /authors
endpoint returns a list of all the authors, and the /books/:authorId
endpoint returns a list of books written by the specific author with the given authorId
.
Run the application using the following command:
This will start a Hono.js
server at http://localhost:3000
. Navigate to http://localhost:3000/authors
and http://localhost:3000/books/1
in your browser to check that the API works as expected.
Migration after a schema change
To demonstrate how to execute a schema change, we'll add a new column to the authors
table, listing the country of origin for each author.
Generate the new migration
Modify the code in the src/schema.ts
file to add the new column to the authors
table:
Now, we can run the following command to generate a new migration file:
This command generates a new migration file in the drizzle
folder, with the SQL command to add the new column to the authors
table.
Run the migration
Run the migration script using the following command:
You should see the Migration completed
message in the terminal, indicating it was successful.
Verify the schema change
To verify that the schema change was successful, run the application using the following command:
You can navigate to http://localhost:3000/authors
in your browser to check that each author entry has a country
field, currently set to null
.
Conclusion
In this guide, we set up a new TypeScript project using Hono.js
and Drizzle
ORM and connected it to a Neon
Postgres database. We created a schema for the database, generated and ran migrations, and implemented API endpoints to query the database.
Source code
You can find the source code for the application described in this guide on GitHub.
Resources
For more information on the tools used in this guide, refer to the following resources:
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.