Learning: Setting up Neon Postgres and Drizzle ORM, Scarcom project

Next.js 14, App router based

Setting up PostgreSQL, Neon

.env

Database connection string, the only sensitive credential required, is stored as an environment variable within a .env file, which should be placed at root of the project directory.

Never commit .env files to Git. They hold sensitive secrets that should not be publicly accessible, particularly on GitHub.

// scarcom/.env
DATABASE_URL="postgresql://.."

It is crucial to understand and be aware that Next.js makes environment variables accessible only on the server-side by default. This design ensures that sensitive information is not inadvertently exposed in client-side. For variables intended for use within the browser, a NEXT_PUBLIC_ prefix must be added to the environment variable name in the .env file.

npm install postgres

The postgres library (npm install postgres) was installed over the Neon-specific neondatabase/serverless library. I believe this decision promotes portability. Using the widely adopted postgres library enables us to potentially simplifying migrating to a different database providers, including self-hosted PostgreSQL instances in the future, if such decision was to be made. This strategic choice aims to get rid of vendor lock-in. Although I do wonder if this really is the case or if some additonal configuration is required.

verify

Following the official Neon documentation's example for the Next.js app router, using the code associated with the postgres library, a connection to the Neon database was successfully established and verified. The initial connection verification code, implemented within main page as server component, is provided below:

// js
import postgres from "postgres";

async function getData() {
  const sql = postgres(process.env.DATABASE_URL, { ssl: "require" });
  const response = await sql`SELECT version()`;
  return response[0].version;
}

export default async function Page() {
  const data = await getData();
  return <>{data}</>;
}

Configuring Drizzle ORM

install libraries

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

The Drizzle ORM setup requires a configuration file, drizzle.config.js, which is to be located at the root of the project directory.

set drizzle.config.js

Setting up Drizzle requires a drizzle.config.js configuration file, which must be placed at the root of the project directory.

// scarcom/drizzle.config.js

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/lib/db-schema.js",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
});

The db-schema.js is to be created as shown in next section. Note that the location of db-schema.js and the name of schema file is upto developer preference.

set db-schema.js, users schema

// ./src/lib/db-schema.js

import { pgTable, text, uuid } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  password: text("password").notNull(),
});

create users table

After adding the above, run the command to create users table as following

npx drizzle-kit push

If you did and you found no errors, you can notice that a table has been added into your database. This means the configuration was successful. I was able to see the users table in neon dashboard.

not .env but .env.local

error encountered

Note: If database credentials are stored in a .env.local file instead of .env, please read this section carefully. Otherwise, you can skip ahead.

This is crucial to prevent the following error when the push command is run

> npx drizzle-kit push
Error: Either connection "url" or "host", "database" are required for PostgreSQL database connection

install dotenv library

npm install dotenv

drizzle.config.js

Apply the following changes to the drizzle.config.js, after making sure dotenv library is installed.

// scarcom/drizzle.config.js

import { configDotenv } from "dotenv";
import { defineConfig } from "drizzle-kit";

configDotenv({ path: "./.env.local" });

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/lib/db-schema.js",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
});

create users table

Now, you can run the command without any errors, to create the users table.

npx drizzle-kit push

configure db.js

// .src/lib/db.js

import postgres from "postgres";
import { drizzle } from "drizzle-orm/postgres-js";

const pool = postgres(process.env.DATABASE_URL, { max: 1 });
export const db = drizzle(pool);

The const db is what we use to be able to read and mutate our database at server side from now on.

verify

The initial connection verification code, implemented within main page as server component, is provided below:

import { db } from "@/lib/db";

async function getData() {
  const response = await db.execute("select version()");
  return response[0].version;
}

export default async function Page() {
  const data = await getData();
  return <>{data}</>;
}

The output on the app page is same as before, hence db.js configuration is verified.

References

1. Next.js 14, Nature of environment variables

2. Connect a Next.js application to Neon Postgresql

3. Drizzle with Neon Postgresql

4. Drizzle, Get Started Postgresql

5. Drizzle, Organize your schema files

6. Push command : drizzle-kit

7. Using drizzle with .env.local instead of .env

Misc

Previous blog post at, here. Project Github Repo at, here. Application live at, here.

Any suggestions and corrections that may push me towards becoming a better software engineer are always welcome.