dotenv - package for managing environment variables - read here
tsx - package for running TypeScript files - read here
mysql2 - package for querying your MySQL database - read here
To use Drizzle with a MySQL database, you should use the mysql2 driver
According to the official website,
mysql2 is a MySQL client for Node.js with focus on performance.
Drizzle ORM natively supports mysql2 with drizzle-orm/mysql2 package.
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 mysql2 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 MySQL database yet and want to create one for testing, you can use our guide on how to set up MySQL in Docker.
The MySQL 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 - Connect Drizzle ORM to the database
Create a index.ts file in the src/db directory and initialize the connection:
mysql2
mysql2 with config
If you need a synchronous connection, you can use our additional connection API, where you specify a driver connection and pass it to the Drizzle instance.
Thereβre two ways you can connect to the MySQL with mysql2 driver, either single client connection or a pool.
Client connection
Pool connection
IMPORTANT
For the built in migrate function with DDL migrations we and drivers strongly encourage you to use single client connection.
For querying purposes feel free to use either client or pool based on your business demands.
Step 4 - Create a table
Create a schema.ts file in the src/db directory and declare your table:
Step 5 - 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 6 - Applying changes to the database
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:
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: