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.jsonnpm i drizzle-orm @libsql/client dotenv
npm i -D drizzle-kit tsxCreate a .env file in the root of your project and add your database connection variable:
DB_FILE_NAME=For example, if you want to create an SQLite database file in the root of your project for testing purposes, you need to use file: before the actual filename, as this is the format required by LibSQL, like this:
DB_FILE_NAME=file:local.dbYou can check the LibSQL docs for more info.
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: 'sqlite',
dbCredentials: {
url: process.env.DB_FILE_NAME!,
},
});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:
CREATE TABLE `users_table` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`name` text NOT NULL,
`age` integer NOT NULL,
`email` text NOT NULL UNIQUE
);Pull your database schema:
npx drizzle-kit pullThe 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:
// table schema generated by introspection
import {
sqliteTable,
uniqueIndex,
integer,
text,
} from "drizzle-orm/sqlite-core";
export const usersTable = sqliteTable(
"users_table",
{
id: integer().primaryKey({ autoIncrement: true }).notNull(),
name: text().notNull(),
age: integer().notNull(),
email: text().notNull(),
},
(table) => [
uniqueIndex("users_table_email_unique").on(table.email)
]
);Learn more about introspection in the documentation.
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.
├ 📂 drizzle
│ ├ 📂 meta
│ ├ 📜 migration.sql
│ ├ 📜 relations.ts ────────┐
│ └ 📜 schema.ts ───────────┤
├ 📂 src │
│ ├ 📂 db │
│ │ ├ 📜 relations.ts <─────┤
│ │ └ 📜 schema.ts <────────┘
│ └ 📜 index.ts
└ …Create a index.ts file in the src directory and initialize the connection:
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';
const db = drizzle(process.env.DB_FILE_NAME!);If you need to provide your existing driver:
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';
const client = createClient({ url: process.env.DB_FILE_NAME! });
const db = drizzle({ client });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/libsql';
import { eq } from 'drizzle-orm';
import { usersTable } from './db/schema';
const db = drizzle(process.env.DB_FILE_NAME!);
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();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
npx tsx src/index.tsWe 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:
bun src/index.tsIf you don’t have bun installed, check the Bun installation docs
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:
// table schema generated by introspection
import {
sqliteTable,
uniqueIndex,
integer,
text,
} from "drizzle-orm/sqlite-core";
export const usersTable = sqliteTable(
"users_table",
{
id: integer().primaryKey({ autoIncrement: true }).notNull(),
name: text().notNull(),
age: integer().notNull(),
email: text().notNull(),
phone: text(),
},
(table) => [
uniqueIndex("users_table_email_unique").on(table.email)
]
);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 pushRead more about the push command in documentation.
Alternatively, you can generate migrations using the drizzle-kit generate command and then apply them using the drizzle-kit migrate command:
Generate migrations:
npx drizzle-kit generateApply migrations:
npx drizzle-kit migrateRead more about migration process in documentation.
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/libsql';
import { eq } from 'drizzle-orm';
import { usersTable } from './db/schema';
const db = drizzle(process.env.DB_FILE_NAME!);
async function main() {
const user: typeof usersTable.$inferInsert = {
name: 'John',
age: 30,
email: '[email protected]',
phone: '123-456-7890',
};
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;
phone: string | null;
}[]
*/
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();