How to create a PostgreSQL database with docker-compose and manipulate with drizzle ORM.

Source: Author Image

The article does not dig deeper into the theoretical aspects. It focuses on how docker is practically used in a certain use case, creating a PostgreSQL database and use of drizzle ORM to control and manipulate the database.

Create a PostgreSQL database using docker-compose

Docker Compose is a tool that makes creating and running multi-container applications easier. It automates the process of managing several Docker containers simultaneously, such as a website frontend, API, and database service.

Here we are going to create a PostgreSQL database. There are different ways to define a container. I used a “docker-compose.yml” file to create the container.

Docker Compose allows you to define your application’s containers as code inside a “YAML” file in your project directory. Once you’ve created your file (normally named docker-compose.yml). You can start all your containers (called “services”) with a single Compose command (docker-compose up — build).

Let's try

⦿ Download and install docker desktop into your machine.

⦿ Create a Next.js project (Here I create a next project that supports for demonstration purposes)

npx create-next-app@latest <Your project name>
cd <Your project name>

⦿ Create a “docker-compose.yml” file inside the root directory of the project. Copy and paste the following code snippet.

services: # Define the services to be run by Docker Compose
  db: # Name of the service, in this case, a PostgreSQL database
    image: postgres # Use the official PostgreSQL image from Docker Hub
    ports:
      - "5432:5432" # Map port 5432 on the host to port 5432 in the container, allowing access to the database from the host
    volumes:
      - pgdata:/var/lib/postgresql/data # Mount a named volume 'pgdata' to persist data in the specified directory inside the container
    environment: # Set environment variables for the PostgreSQL container. These variables configure the PostgreSQL database.


      - POSTGRES_DB=test_db # The name of the database to be created
      - POSTGRES_USER=postgres # The username for the PostgreSQL database
      - POSTGRES_PASSWORD=postgres # The password for the PostgreSQL database user

volumes: # Define named volumes that can be used by services
  pgdata: # Named volume 'pgdata' used to persist PostgreSQL data outside the container

⦿ Run “docker-compose up — build” command in the terminal.

Then the docker container for the PostgreSQL database started building. After finishing the build process, when you open the docker desktop. Your container shows running like this.

The running container, Source: Author Image

Manipulate database using Drizzle ORM

Drizzle ORM is a TypeScript ORM (Object-Relational Mapping) library that supports various SQL databases, including PostgreSQL. It allows you to define your database schema, perform queries, and manipulate data using TypeScript, which provides type safety and autocompletion features.

The following packages are required for drizzle.

npm i drizzle-orm postgres
npm i -D drizzle-kit

Let's see how the manipulating process happens

⦿ Create a “schema.ts” file inside the project src directory. Copy and paste the code snippet.

// Import necessary types and functions from Drizzle ORM for PostgreSQL
import { serial, varchar } from "drizzle-orm/pg-core";
import { pgTable } from "drizzle-orm/pg-core";

// Define the 'employees' table schema using Drizzle ORM
export const employees = pgTable("employees", {
  // 'id' column: A serial (auto-incrementing) primary key
  id: serial("id").primaryKey(),

  // 'employeeName' column: A VARCHAR column for storing employee names, with a NOT NULL constraint
  employeeName: varchar("employee_name").notNull(),

  // 'employeeRole' column: A VARCHAR column for storing employee roles, with a NOT NULL constraint
  employeeRole: varchar("employee_role").notNull(),

  // 'employeeDepartment' column: A VARCHAR column for storing employee department names, with a NOT NULL constraint
  employeeDepartment: varchar("employee_department").notNull(),
});

In the context of Drizzle ORM, a schema file defines the structure of your database tables and their relationships using TypeScript. This file allows you to describe your database schema in a type-safe way. Here, I define a table for my database as “employees”.

⦿ Create a .env file inside the root directory of the project and define the “DATABASE_URL” as follows.

DATABASE_URL=postgres://postgres:postgres@localhost:5432/test_db

This “DATABASE_URL” is related to this project. These environmental variables come from “docker-compose.yml” file, which we used to build the docker container. Drizzle ORM required these credentials to get access to the created database.

⦿ Create the “drizzle.config.ts” file inside the root directory of the project, copy and paste the following code.

import * as dotenv from "dotenv";
import type { Config } from "drizzle-kit";

dotenv.config();

export default {
  schema: "./modules/**/schema.ts",
  out: "./.drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
} as Config;

drizzle.config.tsProvides the configuration for Drizzle ORM, including schema file locations, output paths, database dialect, and connection credentials. This is essential for setting up Drizzle ORM to know where to find schemas and how to connect to the database.

⦿ Create a folder as “db” in the project, inside that folder create an “index.ts” file, copy and paste the following code.

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { employees } from "../../../modules/employees/schema";


const pool = new Pool({
  connectionString: "postgres://postgres:postgres@localhost:5432/test_db",
});

const db = drizzle(pool, {
  schema: {
    employees,
   
  },
});

export { db };

db/index.tsInitializes and exports a Drizzle ORM instance with a PostgreSQL connection pool and schema definitions. This file is crucial for actual database operations in the application, allowing the application to perform type-safe queries and mutations on the specified tables.

⦿ Run “npx drizzle-kit push” command in the terminal

This command will apply schema changes to your database. Now you can check whether the changes applied to the database.

Run “npx drizzle-kit migrate” in the terminal

This command let you apply migrations stored in your migrations folder also you can get the migration file by running “npx drizzle-kit generate” command in the terminal.

Now you can control and manipulate the database.

⦿ Finally, Run “npx drizzle-kit studio” in the terminal

The command is used to launch Drizzle Kit Studio, a graphical user interface (GUI) for interacting with your database schema and migrations managed by Drizzle ORM. This tool provides a visual representation of your database schema and offers an interface to manage migrations and other database-related tasks.

The Drizzle Studio, Source: Author Image

This is how you can use docker and the drizzle ORM to create and manipulate a PostgreSQL database.

➡ GitHub Repository

➡Visit Drizzle.team for more info

➡Docker of Beginners