Drizzle ORM Crash Course

#javascript #sql #programming #beginners

Introduction

Drizzle ORM is a simple, lightwiegt ORM for TypeScript. It is designed to be simple to use and easy to understand. It is designed to be used with MySQL databases, but can be easily extended to work with other databases.

Get Started

Drizzle ORM supports PostgreSQL, MySQL, and SQLite. This guide will focus on MySQL, but the concepts are the same for all databases.
You can reach out more details on Drizzle ORM

Installation

To install Drizzle ORM, you need to install the package from npm.

npm i drizzle-orm pg dotenv
npm i -D drizzle-kit tsx @types/pg

npm i drizzle-orm pg dotenv - This command installs the Drizzle ORM package, the pg package, and the dotenv package. The pg package is required for PostgreSQL support, but it is not required for MySQL or SQLite.

npm i -D drizzle-kit tsx @types/pg - This command installs the Drizzle Kit package, the tsx package, and the @types/pg package. The Drizzle Kit package is required for Drizzle ORM to work with TypeScript.

Configuration

To configure Drizzle ORM, you need to create a .env file in the root of your project. This file should contain the following information:

DATABASE_URL=your-database-url

Replace your-database-url with the URL of your database. For example, if you are using MySQL, the URL should look like this:

DATABASE_URL=mysql://username:password@localhost:3306/database

Setup drizzle config in your drizzle.config.ts file.

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!,
  },
});

Create a Table

To create a table in your database, you need to define a schema in a file called schema.ts. This file should contain the following information:

import { pgTable, unique, integer, varchar } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

export const users = pgTable(
  'users',
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity({
      name: 'users_id_seq',
      startWith: 1,
      increment: 1,
      minValue: 1,
      maxValue: 2147483647,
      cache: 1,
    }),
    name: varchar({ length: 255 }).notNull(),
    age: integer().notNull(),
    email: varchar({ length: 255 }).notNull(),
  },
  (table) => {
    return {
      usersEmailUnique: unique('users_email_unique').on(table.email),
    };
  }
);

We define a table called users with four columns: id, name, age, and email. The id column is an auto-incrementing primary key, and the name, age, and email columns are required.

Connect to the Database

Create a index.ts file in the src/db directory and initialize the connection to the database.

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(process.env.DATABASE_URL!);

We have already define the DATABASE_URL in the .env file. We are using the drizzle function to connect to the database.

Insert Data

To insert data into the users table, you can use the insert method on the db object.

import { users } from './schema';

const user = await db.insert(users).values({
  name: 'John Doe',
  age: 30,
  email: '
});

We are using the insert method on the db object to insert a new user into the users table. We are passing an object with the name, age, and email properties to the values method.

Query Data

To query data from the users table, you can use the select method on the db object.

import { users } from './schema';

const user = await db.select(users).where(users.id.eq(1)).execute();

We are using the select method on the db object to query a user from the users table. We are using the where method to filter the results based on the id column.

Update Data

To update data in the users table, you can use the update method on the db object.

import { users } from './schema';

const user = await db.update(users).set({ age: 31 }).where(users.id.eq(1)).execute();

We are using the update method on the db object to update a user in the users table. We are using the set method to set the age column to 31, and the where method to filter the results based on the id column.

Delete Data

To delete data from the users table, you can use the delete method on the db object.

import { users } from './schema';

const user = await db.delete(users).where(users.id.eq(1)).execute();

We are using the delete method on the db object to delete a user from the users table. We are using the where method to filter the results based on the id column.

Conclusion

Drizzle ORM is a simple, lightweight ORM for TypeScript. It is designed to be simple to use and easy to understand. It is designed to be used with MySQL databases, but can be easily extended to work with other databases. You can reach out more details on Drizzle ORM

Thank You

Thank you for reading this guide. I hope you found it helpful. If you have any questions or feedback, please feel free to reach out to me. I would love to hear from you.