Build a CRUD API with Remix, Prisma, and Postgres
Tags: tutorials, remix
Published: 2023-04-02 07:18:10

---
title: Build a CRUD API with Remix, Prisma, and Postgres
published: true
date: 2023-02-19 12:16:16 UTC
tags: Tutorials,Remix
canonical_url: https://codesource.io/build-a-todo-api-with-remixjs-and-prisma/
---

 ![Build a CRUD API with Remix, Prisma, and Postgres](https://codesource.io/content/images/2023/02/Build-a-CRUD-API-with-Remix--Prisma--and-Postgres--1-.png)

In this tutorial, we will learn how to build a CRUD API with Remix, Prisma, Docker, and Postgres, where we will talk about how we can use Remix, Prisma, and [Postgres](https://codesource.io/build-a-crud-application-in-golang-with-postgresql/) together to create powerful web applications.

### **The Project - Todo API**

This will be a Todo App API where the user can add a new `Todo` , update, and also delete a `Todo`. At the end of this tutorial, we will have a fully functional `Todo` JSON API.

### Tools Needed:

- npm (node)
- Docker
- Vscode or any other code editor of choice
- Insomnia or any other API testing software of choice e.g Postman

### **Set up Our Remix Project**

We'll start by creating our Remix project using the below command:

```
npx create-remix@latest 
```

Then, name your app, use the basic, run remix App server, and choose typescript.

```
? Where would you like to create your app? remix-todo
? What type of app do you want to create? Just the basics
? Where do you want to deploy? Choose Remix App Server if you're unsure; it's easy to change deployment targets. Remix App Server
? TypeScript or JavaScript? TypeScript
? Do you want me to run `npm install`? Yes
```

### Run and Test the App

```
npm run dev
```

you should see something like this:

```
Remix App Server started at http://localhost:3000
```

visit`http://localhost:3000`on your browser, you'll see the Remix welcome page.

#### More about Remix Routes

A basic remix file has a loader, action, and the JSX element, let's see this sample `$postId.tsx` file.

```js
import type {
  ActionArgs,
  LoaderArgs,
} from "@remix-run/node"; // or cloudflare/deno
import { useParams } from "@remix-run/react";

export const loader = async ({ params }: LoaderArgs) => {
  console.log(params.postId);
};

export const action = async ({ params }: ActionArgs) => {
  console.log(params.postId);
};

export default function PostRoute() {
  const params = useParams();
  console.log(params.postId);
}
```

_$postId.tsx_

Basically, we have a loader, action, and the JSX element

- Loader: they autoload with the page.
- Action: Does something when there's an action
- JSX Element: handles the JSX directly

**CRUD - Create, Read, Update, Delete**

CRUD is the acronym for CREATE, READ, UPDATE and DELETE.

These terms describe the four essential operations for creating and managing persistent data elements, mainly in relational and NoSQL databases.

A little more about CRUD:

- Create: This is adding a resource to a database or an application. e.g. adding a new Todo to our Todo App.
- Read: This is reading a resource from our database or application e.g. getting a Todo from the database
- Update: This is updating a resource from our database or application e.g. updating a Todo from the database
- Delete: This is deleting a resource from our database or application e.g. deleting a Todo from the database

We will do everything explained here in this tutorial.

### **Add CRUD to our App using Prisma - Postgres**

1. **Setup prisma and Postgres**

First, we will start by installing Prisma and PrismaClient that we will use to access the postgres database from the Remix Application.

Run the below commands from the project folder terminal:

```bash
npm install --save-dev prisma
npm install @prisma/client
```

**2. Initialize our prisma with postgresql**

Then, by running the below code we will initialize Prisma with a ostgres provider, so it can generate all the needed files for our application,

```bash
npx prisma init --datasource-provider postgresql
```

It's possible to use another database of choice, Prisma works well with other databases like MySQL, SQLite, Mongo, Cockroach, etc. replace `postgresql` with any database you want to use, however, to continue with this tutorial, you will need to continue with Postgres.

**3. Add schema to Prisma**

We'll add our Todo schema to the `prisma/schema.prisma` file, this file was generated for us when ran the `npx prisma init` code above.

Add this line to the last line.

```tsx
model Todo {
  id Int @id @default(autoincrement())
  title String
  content String?
  done Boolean @default(false)
}
```

**4. Push schema to Postgres**

To push our schema to the Postgres server, we will need to start our local postgres server using docker by downloading or copying [this](https://raw.githubusercontent.com/marcusbello/remix-crud/master/docker-compose.yml) docker-compose file to your project folder and running the below code.

```bash
docker-compose up
```

This will start our Postgres server with the default user, password, and database set to `postgres` then we will edit `.env` file, make the `DATABASE_URL` equal to `postgresql://postgres:postgres@localhost:5432/postgres?schema=public` , if you edited the config in the docker file, make changes to `DATABASE_URL` too.

Run the below command to push the schema to Postgres and confirm our connection:

```bash
npx prisma db push
```

Optional: you can check your Postgres database for tables, Prisma has already created our table in the database, I use tablePlus.

Important: Let's Add a global database to our remix application, so we will not be creating a new instance of Prisma client for every call.

create a file `app/utils/db.server.ts`

```tsx
import { PrismaClient } from "@prisma/client";

let db: PrismaClient;

declare global {
    var __db: PrismaClient | undefined;
}

// this is needed because in development we don't want to restart
// the server with every change, but we want to make sure we don't
// create a new connection to the DB with every change either.
if (process.env.NODE_ENV === "production") {
    db = new PrismaClient();
} else {
    if (!global.__db) {
        global.__db = new PrismaClient();
    }
    db = global.__db;
}

export { db };
```

### CRUD implementation

Before diving into other CRUD operations, we'll start by using creating a `/todos` route, then we will give this route all the CRUD control. But first, we will make this route return the list of all todos in our database.

create a `app/routes/todos.tsx` and copy this code:

```tsx
import {useLoaderData} from "@remix-run/react";
import { db } from "~/utils/db.server";

export const loader = async () => {
    return json({
        todos: await db.todo.findMany(),
    });
};

export default function TodosRoute() {
    const data = useLoaderData<typeof loader>();

    return JSON.stringify(data);
}
```

In the above code we have, I created a loader that imports `db` from the utils, then we used the PrismaClient `findMany` function to get the list of todo from the database.

Then I used an exported function `TodosRoute()` to return the JSON result mapped to the `/todos` route.

check the URL  `http://localhost:3000/todos`, you should get an empty todo list at this point.

- Let's add the CRUD functionality to our Todo App, we'll start with Create, we will the HTTP `POST` method to get data and create resources with the data, let's add this code to our `app/routes/todos.tsx`

```tsx
export async function action({ request }: ActionArgs) {
    // read body
    const body = await request.json()
    let data: Prisma.TodoCreateInput

    if (request.method === 'POST') {
        data = {
            title: body.title,
            content: body.content,
            done: false
        }
        await db.todo.create({
            data: data,
        });
        console.log("added new todo")

        return redirect('/todos');
    }
}
```

By getting the body parsed in our request and checking the HTTP method used on the`/todos`URL, we serialized the body and sent it to Prisma create, so it will add the new todo to the database

- To Create a read for this App I will use the `$` operator to handle the URL and create a special handler for my todo by creating a `app/routes/todo/$todoId.tsx`.

```tsx
import type {LoaderArgs} from "@remix-run/node";
import {json} from "@remix-run/node";
import { useLoaderData} from "@remix-run/react";
import { db } from "~/utils/db.server";

export const loader = async ({ params }: LoaderArgs) => {
    const todo = await db.todo.findUnique({
        where: { id: parseInt(params.todoId as string, 10) },
    });
    if (!todo) {
        throw new Error("Todo not found");
    }
    return json({ todo });
};

export default function TodoRoute() {
    const data = useLoaderData<typeof loader>();

    return JSON.stringify(data);
}
```

We are creating a loader that will use the `findUnique()` that takes an Id, then uses a where `Id=Id` to match a record from the database, this is another Prisma function that finds one unique item that matches the conditions parsed as Args, this function can return a not found if the item does not exist which we have handled for our API.

Using `$` in the name will make us have control on `/todo/$todoId` due to how routes are designed in a Remix app. this return the todo which has the id parsed in the body as its id.

- For Update, in this app, we are only updating the `done` field of our Todo model, no need to update other information, to do this we will add another block of code to our action function in the `app/routes/todos.tsx` that uses the HTTP `PUT` method.

```tsx
if (request.method === 'PUT') {
        const id = body.id
        const done = body.done

        await db.todo.update({
            where: {id: parseInt(id as string, 10)},
            data: {done: done},
        });

        return redirect('/todos');
    }
```

With this we are going to get the request that has a JSON body consisting of id and done status, we then used Prisma update to update the data in our database.

- Delete, to delete a resource is straightforward, it deletes resources from the database, let's add the code, open `app/routes/todos.tsx`, add after the Update code.

```tsx
if (request.method === 'DELETE') {
        const id = body.id

        await db.todo.delete({
            where: {id: parseInt(id as string, 10)},
        });

        return redirect('/todos');
    }
```

We are using the `DELETE` HTTP method, we will be able to delete a resource by sending its body in the request.

Since we are done with the CRUD operations, let's restart our application and test with insomnia.

### **Test with Insomnia**

We will use Insomnia to test our JSON application by using HTTP POST, GET, PUT, and DELETE to access the Todo API.

Let's start by adding a new Todo by sending a POST  request to `http://localhost:3000/todos`, and add our Todo as the JSON body.

See the below image.

![Build a CRUD API with Remix, Prisma, and Postgres](https://codesource.io/content/images/2023/02/remix-tutorial---remixjs-todo-app---Add-Todo.jpg)
_Creating a Todo_

This will create a new Todo on the App by saving it to our Postgres database.

Next, is reading a Todo from the data store by adding its ID in the URL like `http://localhost:3000/todo/1` , see the image below

![Build a CRUD API with Remix, Prisma, and Postgres](https://codesource.io/content/images/2023/02/remix-tutorial---remixjs-todo-app---Todo-List.jpg)
_getting a Todo_

Let's try to update a Todo's status by changing it's `done` from false to true, to do this we will send a PUT request to our todos URL, we will then send the Todo ID and the `done` status we want to update with the request as a data.

See the image below.

![Build a CRUD API with Remix, Prisma, and Postgres](https://codesource.io/content/images/2023/02/remix-tutorial---remixjs-todo-app---Update-Todo.jpg)
_updating a todo_

Lastly, we will learn how to delete a Todo, we will use HTTP method DELETE on the todos URL of the app, by sending the Todo ID we want to delete as part of the request, the app will match the ID with the one in our database, then delete it.

See the image below.

![Build a CRUD API with Remix, Prisma, and Postgres](https://codesource.io/content/images/2023/02/remix-tutorial---remixjs-todo-app---Delete-Todo.jpg)
_deleting a todo_

I have uploaded the Insomnia test data if you want to use my test data, download it by using the link below and import it to your Insomnia.

[link to test data for Insomnia](https://raw.githubusercontent.com/marcusbello/remix-crud/master/Insomnia_remix_todo_test.json)

### **Conclusion**

We are at the end, in this tutorial we were able to build a Todo JSON API, using Remix, Prisma, and Postgres, we had two routes dedicated to our API, and we were able to Create, Read, Update and Delete a Todo, and we can also see all our todos at once.

The full code can be found on [GitHub.com](https://github.com/marcusbello/remix-crud)