Building a full stack app with Remix & Drizzle ORM: Project setup
Tags: react, remix, drizzle, typescript
Published: 2023-06-28 16:21:07

### Introduction
In this tutorial series, we'll explore building a full stack application using [Remix](https://remix.run/) and [Drizzle ORM](https://orm.drizzle.team/). 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](https://twitter.com/sabinthedev), whose insightful [tutorial series](https://www.prisma.io/blog/fullstack-remix-prisma-mongodb-1-7D0BfTXBmB6r) 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](https://github.com/yaldram/kudos-remix-drizzle/tree/feat/drizzle-setup). I would also encourage you to play around with the [deployed version](https://kudos-remix-drizzle.vercel.app/). 

![home-screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dypetm2al0e1vi8wrexf.png)

### Step 1: Bootstrap Project
To create a new remix project, from your command line run - 
```bash
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](https://tailwindcss.com/docs/guides/remix).

### 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 - 
```bash
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 - 
```ts
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` - 
```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 - 
```ts
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` - 
```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.
```bash
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 -
```json
{
  "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
```js
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:
```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](https://github.com/yaldram/kudos-remix-drizzle/tree/feat/drizzle-setup). Until next time PEACE!