In the world of software development, efficient data management is one of the important aspects. As applications grow in complexity and scale, so do the challenges associated with interacting with databases. Writing raw SQL queries is not only hard but also error prone. Object-Relational Mapping (ORM), a paradigm-shifting approach that revolutionizes how developers work with databases. This abstraction layer not only simplifies the development process but also enhances productivity by allowing developers to focus more on application logic rather than the intricacies of SQL queries and database schemas. In this blog post, we explore the world of ORM, exploring its significance, and spotlighting Drizzle ORM and integrate it with nextjs application using supabase postgres databse.

What is ORM?

ORM stands for Object-Relational Mapping. It is a programming technique that enables developers to work with relational databases using an object-oriented approach. In traditional database interactions operations are handled with SQL queries. But in ORM we write the queries using programming language which will handle the database operations like SQL queries.

This approach simplifies database interactions by allowing developers to work with database entities as if they were regular objects in their programming language. This abstraction layer eliminates the need for writing repetitive SQL queries and manual mapping between database tables and application objects. By providing a higher-level interface, ORM enhances productivity, reduces development time, and promotes cleaner, more maintainable code.

Examples of Popular ORM Libraries and Frameworks:

• Hibernate - Java

• SQLAlchemy - Python

• Drizzle - TS/JS

• Prisma - TS/JS

Why ORM is Important and Helpful

Challenges of Traditional Database Interaction Methods:

• Writing raw SQL query is hard and error prone.

• No type safety when writing queries.

• Hard to maintain the large codebase.

• Prone to SQL injections. (Reference)

ORM addresses these challenges by providing a higher-level abstraction for database management. By mapping database tables to application objects, ORM frameworks eliminate the need for developers to write boilerplate SQL code manually. Instead, developers can work with domain-specific objects and use familiar object-oriented programming paradigms to interact with the database. ORM frameworks handle the translation of object-oriented operations (such as CRUD operations) into corresponding SQL queries, as well as the mapping of query results back to application objects. Also they provide inbuilt security for SQL injections.

Introducing DRIZZLE ORM: 💦

Drizzle ORM is a headless TypeScript/JavaScript ORM. It is a library and a collection of complementary opt-in tools. Drizzle lets you build your project the way you want, without interfering with your project or structure. Using Drizzle you can define & manage database schemas in TypeScript, access your data in a SQL-like or relational way, and take advantage of opt in tools. Drizzle is a library which is lightweight, performant, typesafe, flexible and serverless-ready.

Adding drizzle to Next projects.

Step 1: Create a Next App.

npx create-next-app@latest

Step 2: Get a postgres database URL.

Here I am using Supabase postgres.

• Go to dashboard > Click on New project

  

• Add your project name. Copy the password and hit create new project.

• 

  Wait for the project to setup then click on connect

• 

  Go to orm tab and select drizzle from the menu

• 

  Add the database url to .env file in the project.

  ./.env

DATABASE_URL=<Your postgres databse url from supabse>

Step 3: Install postgres, Drizzle, and Drizzle Kit.

npm i postgres
npm i drizzle-orm
npm i -D drizzle-kit # It is a dev dependency.

Step 4: Create Drizzle Config.

./drizzle.config.ts

import "@/lib/loadEnv";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./lib/schema/*",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Step 5: Create config file for loading environments variables.

./lib/loadEnv.ts add this file inside the lib folder

import { loadEnvConfig } from "@next/env";

const projectDir = process.cwd();
loadEnvConfig(projectDir);

Step 6: Create Schemas.

./lib/schema/index.ts

// Export all the schemas of your project from this file.
export * from './store'
export * from './billboards'

Create ./lib/schema/store.ts for defining the table structure and relations of the store.

import {
    pgTable,
    text,
    timestamp,
    serial,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { billboards } from ".";

export const store = pgTable("store", {
    id: serial("id").primaryKey(),
    name: text("name").notNull(),
    userId: text("userId").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at"),
})

export const storeRelations = relations(store, ({ many }) => ({
    billboards: many(billboards)
}))

export type Store = typeof store.$inferSelect

./lib/schema/billboard.ts

import {
    pgTable,
    text,
    timestamp,
    serial,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { store } from ".";

export const billBoards = pgTable("billboards", {
    id: serial("id").primaryKey(),
    // defining foreign key
    storeId: integer("store_id").notNull().references(() => store.id),
    label: text("label").notNull(),
    imageUrl: text("image_url").notNull(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at")
})

export const billBoardsRelations = relations(billboards, ({ one }) => ({
    store: one(store, {
        fields: [billboards.storeId],
        references: [store.id]
    })
}))

export type BillBoard = typeof billBoards.$inferSelect

Step 7: Creating DB connection.

import "@/lib/loadEnv";

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from '@/lib/schema'

const connectionString = process.env.DATABASE_URL!

const client = postgres(connectionString, {prepare: false})

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

Step 8: Creating scripts.

./package.json

// Add this to package.json
{
  "scripts": {
    "generate": "drizzle-kit generate:pg", // Genrated table definations in SQL and puts in ./drizzle folder.
    "push": "drizzle-kit push:pg", // Pushes the tables to the databse.
  }
}

Run these 2 commands.

npm run generate
npm run push

Step 9: Creating the helper functions.

./lib/actions.ts

• To get all stores:

import { db } from '@/lib/db';
import { store } from '@/lib/schema';

export async function fetchStores() {
    try {
        const res = await db.select().from(store);
        return {
            success: true,
            data: res
        };
    } catch (error) {
        console.log("fetch Stores",error);
        return {
            success: false;
            data: error
        }
    }
}

• To get all stores with certain condition:

import { db } from '@/lib/db';
import { store } from '@/lib/schema';
import { eq } from 'drizzle-orm';

export async function fetchUserStores(userId: string) {
    try {
        const res = await db.select().from(store).where(eq(store.userId, userId));
        return {
            success: true,
            data: res
        };
    } catch (error) {
        console.log("fetch User Stores",error);
        return {
            success: false;
            data: error
        }
    }
}

Note:

There is one more syntax in drizzle for querying:

import { db } from '@/lib/db'

export const fetchAllStoredWithBillboards(storeId: number) {
    const res = await db.query.store.findMany({
         with: {
            billBoards: true
         },
         where: (category, { eq }) => eq(store.id, storeId),
    })
}

with will populate the billboards data associated with the store . To make use of with we need to define the relation between the tables.

If you dont want to use the with functionality no need to write the relations for the table.

• We can use this function to fetch the all store in the database. This will be more easier and help full than writing a api endpoint.

• I prefer writing actions instead of API endpoint unless we have to expose some api endpoints.

• This method is easy to write and provides type safety without having to use any other library, and also we can call this function in client components as well.

Checkout my project E-hippo for more reference on how I used drizzle orm in my project. No go and try Drizzle ORM in your project. I am sure you will love it.

Thanks ♥

References:

• https://www.fullstackbook.com/blog/nextjs-drizzle-orm-postgresql-vercel-tutorial

• https://orm.drizzle.team/

Checkout my previous blog: https://hashnode.com/post/clt32gxn9000d08jwhvna050p