Building a full stack app with Remix & Drizzle ORM: Project setup

Introduction

In this tutorial series, we'll explore building a full stack application using Remix and Drizzle ORM. Remix is a powerful web framework built on React, emphasizing performance and developer experience.
Drizzle ORM not only serves as a performant alternative to Prisma but also excels in serverless and edge environments, making it an optimal choice for efficient database interactions in those contexts. With its broad compatibility, Drizzle ORM seamlessly connects to a variety of SQL databases, ranging from popular options like PlanetScale, Neon Serverless and Cloudflare D1.

Credit for inspiring this tutorial series goes Sabin Adams, whose insightful tutorial series served as a valuable source of inspiration for this project.

Overview

Please note that this tutorial assumes a certain level of familiarity with React.js, Node.js, and working with ORMs. In this tutorial we will be -

• Bootstraping the Remix project and add Tailwind css.
• Setting up Drizzle ORM creating schemas for our tables.
• Creating and running migrations using Drizzle Kit.

All the code for this tutorial can be found here. I would also encourage you to play around with the deployed version.

Step 1: Bootstrap Project

To create a new remix project, from your command line run -

npx create-remix kudos-remix-drizzle

The CLI will ask a few questions -

What type of app do you want to create ?  Just the basics
Where do want to deploy the app ? Vercel
Typescript or Javascript ? Typescript
Do you want to run npm install ? Yes

I would also recommend you initialize git in your project and push the code to GitHub. We will be using Tailwind CSS for styling our components, to setup Tailwind in your remix project, I highly recommend you follow this simple and detailed guide.

Step 2: Setup Drizzle ORM

We will be using postgres as our database so along with drizzle we need to install a postgres client, from your terminal run -

npm install drizzle-orm postgres

For our application we need 2 tables users & kudos. Under app folder create a new folder drizzle, under it create a new folder called schemas. Now under app/schemas/drizzle create a new file users.db.server.ts. Make sure you add the .server extension to your filename, Remix won't include such files in the client build they will only be used on the server side.
Under users.db.server.ts paste the table schema -

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

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  firstName: varchar("first_name").notNull(),
  lastName: varchar("last_name").notNull(),
  profileUrl: varchar("profile_url"),
  email: varchar("email").notNull(),
  password: text("password").notNull(),
});

export type User = InferModel<typeof users>;

export type NewUser = InferModel<typeof users, "insert">;

export type UserProfile = Pick<User, "firstName" | "lastName" | "profileUrl">;

With Drizzle ORM, you can effortlessly define table schemas using simple JavaScript syntax, as demonstrated in the code. Additionally, it is easy to create types leveraging the power of TypeScript to enforce strong typing and enhance code readability. Under schemas folder create kudos.db.server.ts -

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

import type { KudoStyle } from "~/utils/constants";
import { users } from "./users.db.server";

const defaultStyle: KudoStyle = {
  backgroundColor: "red",
  textColor: "white",
  emoji: "thumbsup",
};

export const kudos = pgTable("kudos", {
  id: uuid("id").primaryKey().defaultRandom(),
  message: text("message").notNull(),
  style: json("style").notNull().default(defaultStyle).$type<KudoStyle>(),
  createdAt: timestamp("created_at", { mode: "string" }).defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "string" }).defaultNow(),
  authorId: uuid("author_id")
    .references(() => users.id, {
      onDelete: "cascade",
    })
    .notNull(),
  recipientId: uuid("recipient_id")
    .references(() => users.id, {
      onDelete: "cascade",
    })
    .notNull(),
});

export type Kudo = InferModel<typeof kudos>;

export type NewKudo = InferModel<typeof kudos, "insert">;

• In the kudos table, the authorId and recipientId columns serve as foreign keys that establish a relationship with the users table, linking each kudos entry to the corresponding author and recipient user. Drizzle makes it very easy to declare these references.
• By defining the style column as a JSON type with a default value and inferring a custom type KudoStyle using Drizzle ORM, we can leverage Drizzle's robust type system to ensure type safety and facilitate seamless handling of JSON data within our application.
• To use timestamp columns as strings instead of the default Date type, we simply set the mode to string in Drizzle, allowing us to handle timestamps as strings in our application.

We need to create some constant values for handling the styles of our Kudos, like the bgColor, textColor, emoji. Under app create a utils folder, inside it create a constants.ts file -

export const textColorMap = {
  red: "text-red-400",
  green: "text-green-400",
  blue: "text-blue-400",
  white: "text-white",
  yellow: "text-yellow-300",
};

export const backgroundColorMap = {
  red: "bg-red-400",
  green: "bg-green-400",
  blue: "bg-blue-400",
  white: "bg-white",
  yellow: "bg-yellow-300",
};

export const emojiMap = {
  thumbsup: "👍",
  party: "🎉",
  handsup: "🙌🏻",
};

export const colorEnum = ["red", "green", "blue", "white", "yellow"] as const;
export const emojiEnum = ["thumbsup", "party", "handsup"] as const;

export type KudoStyle = {
  backgroundColor: keyof typeof backgroundColorMap;
  textColor: keyof typeof textColorMap;
  emoji: keyof typeof emojiMap;
};

Now that we have defined the schema for our tables, we can establish a connection to our PostgreSQL database and create a database client using Drizzle ORM. Under app/drizzle create a new file config.db.server.ts -

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

if (!process.env.DATABASE_URL) {
  throw new Error("environment variable: DATABASE_URL is missing.");
}

const queryClient = postgres(process.env.DATABASE_URL);
export const db = drizzle(queryClient);

Make sure you add the DATABASE_URL in your .env file which you should create in the root of the project. The format for postgres connection string is - postgresql://user:password@hostname:port/dbname
With our table schemas set up and the connection established to our PostgreSQL database, we are now ready to generate migrations.

Step 3: Creating and running migrations

In the previous section, we created our table schemas using Drizzle ORM. Now, leveraging the power of Drizzle Kit, we can utilize its functionality to automatically read our defined schema and generate SQL migrations. These migrations will capture the changes made to our schema, allowing us to apply them to our PostgreSQL database seamlessly. This automated process ensures that our database stays in sync with our schema definition.

npm install -D drizzle-kit

To generate migrations using Drizzle Kit, we need to provide the necessary configuration. We can do this by creating a drizzle.config.json file in the root of our project. This configuration file will specify the location of our schema and where the migration files should be generated -

{
  "schema": "./app/drizzle/schemas/*",
  "out": "./migrations"
} 

To generate migrations, simply run npx drizzle-kit generate:pg from your terminal. This command will read the configuration file and automatically create a migrations folder. In order to execute these generated SQL migrations against our database, we need to create a drizzle-migrate.js file in the project's root directory

require("dotenv/config")

if (process.env.NODE_ENV === "production") return;

const { drizzle } = require("drizzle-orm/postgres-js");
const { migrate } = require("drizzle-orm/postgres-js/migrator");
const postgres = require("postgres");

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

migrate(db, { migrationsFolder: "migrations" })
.then(() => {
  console.log("Migrations ran successfully")
  process.exit();
})
.catch((error) => {
  console.log("Error running migrations", error)
  process.exit(1);
})

From the command line run node drizzle-migrate.js and check your database all our tables should be created.
To simplify the process, we can add the migration generation command and the command to run the migrations in the package.json file. This will make it more convenient for us to execute these commands. We can include the following scripts in the scripts section of package.json:

"migrations:generate": "drizzle-kit generate:pg",
"migrations:up": "node drizzle-migrate.js"

With these scripts defined, we can now run npm run migrations:generate to generate the migrations based on our schema, and then execute npm run migrations:up to apply those migrations to our postgres database.

Conclusion

In this tutorial, we successfully bootstrapped our Remix project with Tailwind CSS, set up table schemas using Drizzle ORM, generated and executed migrations for our PostgreSQL database. In the next tutorial, we will focus on setting up the folder structure and creating the necessary React components for our project. All the code for this tutorial can be found here. Until next time PEACE!