Get Started with Drizzle and PostgreSQL in existing project
This guide assumes familiarity with:
dotenv - package for managing environment variables - read here
tsx - package for running TypeScript files - read here
node-postgres - package for querying your PostgreSQL database - read here
Basic file structure
This is the basic file structure of the project. In the src/db directory, we have table definition in schema.ts. In drizzle folder there are sql migration file and snapshots.
Step 1 - Install node-postgres package
npm
yarn
pnpm
bun
Step 2 - Setup connection variables
Create a .env file in the root of your project and add your database connection variable:
tips
If you don’t have a PostgreSQL database yet and want to create one for testing, you can use our guide on how to set up PostgreSQL in Docker.
The PostgreSQL in Docker guide is available here. Go set it up, generate a database URL (explained in the guide), and come back for the next steps
Step 3 - 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:
Step 4 - Introspect your database
Drizzle Kit provides a CLI command to introspect your database and generate a schema file with migrations. The schema file contains all the information about your database tables, columns, relations, and indices.
For example, you have such table in your database:
Pull your database schema:
The result of introspection will be a schema.ts file, meta folder with snapshots of your database schema, sql file with the migration and relations.ts file for relational queries.
Here is an example of the generated schema.ts file:
Learn more about introspection in the documentation.
Step 5 - Transfer code to your actual schema file
We recommend transferring the generated code from drizzle/schema.ts and drizzle/relations.ts to the actual schema file. In this guide we transferred code to src/db/schema.ts. Generated files for schema and relations can be deleted. This way you can manage your schema in a more structured way.
Step 6 - Connect Drizzle ORM to the database
Create a index.ts file in the src/db directory and initialize the connection:
node-postgres
node-postgres with config
your node-postgres driver
Step 7 - Query the database
Let’s update the src/index.ts file with queries to create, read, update, and delete users
Step 8 - Run index.ts file
To run any TypeScript files, you have several options, but let’s stick with one: using tsx
You’ve already installed tsx, so we can run our queries now
Run index.ts script
npm
yarn
pnpm
bun
tips
We suggest using bun to run TypeScript files. With bun, such scripts can be executed without issues or additional
settings, regardless of whether your project is configured with CommonJS (CJS), ECMAScript Modules (ESM), or any other module format.
To run a script with bun, use the following command:
If you want to update your table schema, you can do it in the schema.ts file. For example, let’s add a new column phone to the users_table:
Step 10 - Applying changes to the database (optional)
You can directly apply changes to your database using the drizzle-kit push command. This is a convenient method for quickly testing new schema designs or modifications in a local development environment, allowing for rapid iterations without the need to manage migration files: