Using Drizzle ORM with Nextjs and Sqlite

Drizzle is comparatively simple and performant and easy to use Typescript ORM when compared to other ORMs like Prisma. It is a light weight and has zero dependencies. 

I encountered a problem wile using Prisma, invoking the DB object in multiple locations, it shows some warning. This can be solved using global export as per the official page. Drizzle has no such problem.

Let's configure the Drizzle on Nextjs. 

First, we need the dependencies, the CLI and the ORM itself. Drizzle has zero dependencies, so no worries.

pnpm add drizzle-orm better-sqlite3pnpm add -D drizzle-kit

The Schema

//./db/Schema.ts

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const todoTable = sqliteTable('todos', {

    id: integer('id').primaryKey(),

    title: text('title').notNull(),

    description: text('description'),

  });

Drizzle configuration

//./drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({

  dialect: "sqlite", // "mysql" | "sqlite" | "postgresql"

  dbCredentials: {

    url: './db/sqlite.db', // 👈 this could also be a path to the local sqlite file

  },

  schema: "./db/schema/index.ts",

  out: "./drizzle",

});

Export the DB object

//./db/index.ts

import { drizzle } from 'drizzle-orm/better-sqlite3';

import Database from 'better-sqlite3';

import * as schema from "./schema/schema";

const sqlite = new Database('./db/sqlite.db');

export const db = drizzle(sqlite,{schema,});

I stored all my Schema and Database files on root folder named db and place the configuration on root of the project.

One of the mistakes I made is that specify the path of the database as Database('./sqlite.db'); in the Export db. Since I placed the Sqlite file in the db folder. It should be Database('./db/sqlite.db');

In the next step, run generate and migrate which will automatically create the database using better-sqlite3 driver. For ease of use add few drizzle scripts on package.json file as follows.

  "scripts": {

   "generate": "drizzle-kit generate",

    "migrate": "drizzle-kit migrate",

    "studio": "drizzle-kit studio",

    "db:push": "drizzle-kit push"

    ....

Using the database

By using the exported db object, can interact with the Sqlite db.  

import { db } from "../../db";

export default async function Home() {

  const todos = await db.query.todoTable.findFirst()

...