Drizzle ORM

Overview

Drizzle ORM is a modern TypeScript ORM that differentiates itself through a "SQL-first" approach, aiming to provide a lightweight and type-safe layer over relational databases. Unlike some traditional ORMs that abstract away SQL entirely, Drizzle encourages developers to think in SQL while offering the convenience and type safety of TypeScript. This design philosophy targets developers who prefer direct control over their SQL queries but still want the benefits of an ORM, such as schema definition, migrations, and a fluent query builder.

The project consists of two primary components: Drizzle ORM itself, which provides the core query building and schema definition capabilities, and Drizzle Kit, a companion tool for managing database migrations and schema introspection. Drizzle ORM supports a range of popular relational databases, including PostgreSQL, MySQL, and SQLite, and integrates with various database drivers. Its architecture is designed to be highly performant by generating efficient SQL queries and minimizing abstraction overhead.

Drizzle ORM is particularly well-suited for server-side applications built with modern JavaScript and TypeScript. Its strong emphasis on type safety at compile time helps prevent common database-related errors, improving developer experience and code reliability. It is often chosen by teams that prioritize performance, type safety, and a clear understanding of the underlying SQL operations. For projects where developers want to maintain control over their database schemas and SQL, Drizzle ORM provides a balance between raw SQL and ORM convenience. The project's open-source nature, as defined by the Open Source Definition criteria, means it is freely available for use and modification, fostering community contributions.

Developers transitioning from other ORMs like TypeORM or Prisma might find Drizzle's approach refreshing due to its explicit SQL focus. While Prisma offers a powerful type-safe client and schema migration system, Drizzle ORM provides a more direct mapping to SQL, which can be beneficial for complex queries or performance-sensitive applications. Similarly, compared to TypeORM's extensive decorators and more opinionated architecture, Drizzle offers a more functional and less intrusive way to interact with databases, reducing boilerplate and increasing clarity in query construction.

Key features

• Type-safe schema definition: Define database schemas using TypeScript, generating types for tables, columns, and relations that are checked at compile time.
• SQL-like query builder: Construct complex SQL queries using a fluent API that closely mirrors SQL syntax, supporting SELECT, INSERT, UPDATE, DELETE, JOIN, WHERE, and more.
• Database driver agnosticism: Works with various database drivers for PostgreSQL (e.g., node-postgres, pg-promise, vercel/postgres), MySQL (e.g., mysql2), and SQLite (e.g., better-sqlite3, libsql).
• Drizzle Kit for migrations: Provides tools for generating and running database migrations based on schema changes, as well as introspecting existing database schemas.
• Relation support: Define one-to-one, one-to-many, and many-to-many relationships between tables, allowing for eager and lazy loading of related data.
• JSON support: Comprehensive handling of JSON data types in databases, including querying and manipulating JSON fields.
• Transactions: Support for atomic database transactions, ensuring data consistency and integrity.
• Prepared statements: Utilizes prepared statements for improved performance and protection against SQL injection vulnerabilities.
• Open Source: Fully open source under the MIT license, available for free use and modification.

Pricing

Drizzle ORM is an open-source project and is entirely free to use. There are no paid tiers, commercial licenses, or feature restrictions. The project's development is community-driven, with contributions from individuals and companies. Users can access all features and updates without any cost.

For detailed information, refer to the Drizzle ORM official documentation.

Common integrations

• PostgreSQL: Integrates with various PostgreSQL drivers such as node-postgres, pg-promise, and Vercel's @vercel/postgres. For specific setup, consult the Drizzle PostgreSQL getting started guide.
• MySQL: Compatible with MySQL drivers like mysql2. Refer to the Drizzle MySQL integration documentation for implementation details.
• SQLite: Works with SQLite drivers including better-sqlite3 and libsql. The Drizzle SQLite setup instructions provide guidance.
• Next.js/Remix: Frequently used in server-side rendered applications built with frameworks like Next.js and Remix due to its TypeScript focus and performance characteristics. Remix data access patterns often align with Drizzle's approach.
• Cloudflare Workers: Drizzle ORM can be deployed in serverless environments, including Cloudflare Workers, by utilizing compatible database drivers and adapters.
• Vercel Postgres: Seamless integration with Vercel's managed Postgres service, leveraging the @vercel/postgres package.

Alternatives

• Prisma: A next-generation ORM that provides a type-safe database client and powerful migration tools, emphasizing developer experience and full-stack type safety.
• TypeORM: A popular ORM for TypeScript and JavaScript, supporting multiple databases and offering features like ActiveRecord and DataMapper patterns.
• Kysely: A type-safe SQL query builder for TypeScript, focusing on providing a highly ergonomic and extensible way to build SQL queries with full type inference.
• Mikro-ORM: A TypeScript ORM with a focus on developer experience, supporting DataMapper and Unit of Work patterns, and offering strong type inference.
• Aiohttp ORM: An asynchronous ORM for Python, often used with the aiohttp web framework, providing an alternative for Python-based applications.

Getting started

To begin using Drizzle ORM, you typically install the core package along with a database-specific driver. The following example demonstrates setting up Drizzle ORM with PostgreSQL and the node-postgres driver, defining a simple schema, and performing an insertion.

First, install the necessary packages:

npm install drizzle-orm pg
npm install -D drizzle-kit typescript @types/node

Next, define your database schema in a file like src/schema.ts:

import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  email: varchar('email', { length: 256 }).unique(),
});

Then, create your Drizzle configuration file (e.g., drizzle.config.ts) for Drizzle Kit:

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

Run your first migration with Drizzle Kit:

npx drizzle-kit generate:pg
npx drizzle-kit migrate

Finally, connect to your database and perform a query (e.g., src/index.ts):

import { drizzle } from 'drizzle-orm/node-postgres';
import { Client } from 'pg';
import { users } from './schema';

async function main() {
  const client = new Client({
    connectionString: process.env.DATABASE_URL,
  });
  await client.connect();

  const db = drizzle(client);

  // Insert a new user
  const insertedUsers = await db.insert(users).values({
    fullName: 'Jane Doe',
    email: '[email protected]',
  }).returning();

  console.log('Inserted users:', insertedUsers);

  // Select all users
  const allUsers = await db.select().from(users);
  console.log('All users:', allUsers);

  await client.end();
}

main().catch(console.error);

Ensure you have a .env file with DATABASE_URL set to your PostgreSQL connection string. This basic setup demonstrates defining a schema, running migrations, and interacting with the database using Drizzle ORM's type-safe query builder. For more advanced features and database-specific configurations, refer to the Drizzle ORM getting started documentation.