Get Started with Drizzle and Bun:SQLite

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.

📦 <project root>  ├ 📂 drizzle  ├ 📂 src  │ ├ 📂 db  │ │ └ 📜 schema.ts  │ └ 📜 index.ts  ├ 📜 .env  ├ 📜 drizzle.config.ts  ├ 📜 package.json  └ 📜 tsconfig.json

Step 1 - Install required packages

npm i drizzle-orm npm i -D drizzle-kit @types/bun

yarn add drizzle-orm yarn add -D drizzle-kit @types/bun

pnpm add drizzle-orm pnpm add -D drizzle-kit @types/bun

bun add drizzle-orm bun add -D drizzle-kit @types/bun

Step 2 - Setup connection variables

Create a .env file in the root of your project and add your database connection variable:

DATABASE_URL=

Step 3 - Connect Drizzle ORM to the database

Create a index.ts file in the src directory and initialize the connection:

import 'dotenv/config'; import { drizzle } from 'drizzle-orm/bun-sql';  const db = drizzle(process.env.DATABASE_URL!);

import 'dotenv/config'; import { drizzle } from 'drizzle-orm/bun-sql';  // You can specify any property from the bun sql connection options const db = drizzle({ connection: { url: process.env.DATABASE_URL! }});

If you need to provide your existing driver:

import 'dotenv/config'; import { drizzle } from 'drizzle-orm/bun-sql'; import { SQL } from 'bun';  const client = new SQL(process.env.DATABASE_URL!); const db = drizzle({ client });

Step 4 - Create a table

Create a schema.ts file in the src/db directory and declare your table:

import { integer, pgTable, varchar } from "drizzle-orm/pg-core";  export const usersTable = pgTable("users", {  id: integer().primaryKey().generatedAlwaysAsIdentity(),  name: varchar({ length: 255 }).notNull(),  age: integer().notNull(),  email: varchar({ length: 255 }).notNull().unique(), });

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:

import 'dotenv/config'; import { defineConfig } from 'drizzle-kit';  export default defineConfig({  out: './drizzle',  schema: './src/db/schema.ts',  dialect: 'postgresql',  dbCredentials: {  url: process.env.DATABASE_URL!,  }, });

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:

npx drizzle-kit push

Read more about the push command in documentation.

npx drizzle-kit generate

npx drizzle-kit migrate

Step 7 - Seed and Query the database

Let’s update the src/index.ts file with queries to create, read, update, and delete users

import 'dotenv/config'; import { drizzle } from 'drizzle-orm/bun-sql'; import { eq } from 'drizzle-orm'; import { usersTable } from './db/schema';   const db = drizzle(process.env.DATABASE_URL!);  async function main() {  const user: typeof usersTable.$inferInsert = {  name: 'John',  age: 30,  email: '[email protected]',  };   await db.insert(usersTable).values(user);  console.log('New user created!')   const users = await db.select().from(usersTable);  console.log('Getting all users from the database: ', users)  /*  const users: {  id: number;  name: string;  age: number;  email: string;  }[]  */   await db  .update(usersTable)  .set({  age: 31,  })  .where(eq(usersTable.email, user.email));  console.log('User info updated!')   await db.delete(usersTable).where(eq(usersTable.email, user.email));  console.log('User deleted!') }  main();

Step 8 - Run index.ts file

To run a script with bun, use the following command:

bun src/index.ts