Remix and Database integration
Tags: react, javascript, webdev, remix
Published: 2021-11-27 08:38:06

## Table Of Contents
  * <a href="#tldr" target="_self">TL;DR Source and Demo</a>
  * <a href="#preface" target="_self">Preface</a>
  * <a href="#app-overview" target="_self">App Overview</a>
  * <a href="#prerequisites" target="_self">Prerequisites</a>
  * <a href="#supabase-setup" target="_self">Supabase Setup</a>
  * <a href="#create-remix-project" target="_self">Create remix project</a>
  * <a href="#integration-prerequisites" target="_self">Integration Prerequisites</a>
  * <a href="#optional-chores" target="_self">Optional Chores</a>
  * <a href="#fetch-all" target="_self">Fetch All Words</a>
  * <a href="#fetch-one" target="_self">Fetch Word Details</a>
  * <a href="#delete-one" target="_self">Delete a Word</a>
  * <a href="#create-one" target="_self">Add a Word</a>
  * <a href="#edit-one" target="_self">Update Word Details</a>
  * <a href="#state-transition" target="_self">useTransition Hook</a>
  * <a href="#extra-demo" target="_self">CRUD operations without JavaScript</a>
  * <a href="#conclusion" target="_self">Conclusion</a>
  
> ## TL;DR <a name="tldr"></a>
> Link to the [source code](https://github.com/codegino/playground/tree/part1/remix-supabase-crud)
> 
> Here's a live [demo](https://playground-4jgaymeeo-codegino.vercel.app/words)
>
> UPDATE: After [implementing authentication](https://dev.to/codegino/remix-and-supabase-authentication-205i), the link above has only view access.
>
> Here's the [demo with authentication](https://playground-iirrt7a6j-codegino.vercel.app)



## Preface <a name="tldr"></a>

This post will be the first part of the series on how I will create the entire application. So I will start with a boring introduction about the motivation for this series.

<br />

I have chosen [Supabase](https://supabase.com/) to store my data as it allows me to focus on the Frontend part due to Supabase's easy-to-use API. You can use any provider of your choice, or you can even create your custom backend.

This part of the series will focus on how to use Remix for CRUD operations.

<br />  

### Basic overview of the app <a name="app-overview"></a>

The Vocabulary section will consist of lists of words that are publicly available and a protected admin route to perform a CRUD operation.

Here are the properties we need for each `word`:
- name: the word itself
- type: the type of the word (noun, verb, adjective, etc.)
- definitions: an array of definitions
- sentences: an array of how I would use the word in a sentence

<br />

### Prerequisites if you want to follow along <a name="prerequisites"></a>

- Make sure you have [node](https://nodejs.org/en/) installed
- Basic understanding of [React](https://reactjs.org/)
- Basic `cli` knowledge
- [Supabase](https://supabase.com/) account

> If you are not yet familiar with Remix, I suggest checking first my previous [blog post](https://dev.to/codegino/getting-started-with-remix-10o0) about it, or refer to their [documentation](https://remix.run/docs/en/v1).

<br />


### Create a Supabase project <a name="supabase-setup"></a>

> If you want to use another provider, you can skip to this part <a href="#create-remix-project" target="_self">Create the Remix project</a>

Refer to their official [documentation](https://supabase.com/docs/guides/with-react#create-a-project) on how to create a Supabase project.

<br />


After creating your account, go to SQL Editor tab and execute the queries below:

<Img src="https://i.imgur.com/ROXHoHe.png" height={446} width={769}/>

<br />

#### Create words table

```sql
CREATE TABLE words (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar NOT NULL,
  definitions varchar ARRAY NOT NULL,
  sentences varchar ARRAY NOT NULL,
  type varchar NOT NULL
);
```

<br />

#### Add a new word
```sql
INSERT INTO words
  (name, type, definitions, sentences)
VALUES
  ('hello', 'noun', ARRAY['used as a greeting'], ARRAY['Hello world.']);
```

<br />

In the Table Editor tab, you should see the new entry.
<Img src="https://i.imgur.com/hfVMubg.png" height={395} width={1395}/>

<br />

#### Add anonymous access

```sql
ALTER TABLE words ENABLE ROW LEVEL SECURITY;

CREATE POLICY "anon_select" ON public.words FOR SELECT USING (
  auth.role() = 'anon'
);

CREATE POLICY "anon_insert" ON public.words FOR INSERT WITH CHECK (
  auth.role() = 'anon'
);

CREATE POLICY "anon_update" ON public.words FOR UPDATE USING (
  auth.role() = 'anon'
);

CREATE POLICY "anon_delete" ON public.words FOR DELETE USING (
  auth.role() = 'anon'
);
```

Lastly, in `Authentication/Policies` tab, should be seeing this.

<Img src="https://i.imgur.com/bzbYep0.png" height={621} width={1109}/>

<br />
<br />

### Create a Remix project <a name="create-remix-project"></a>

<br />

#### Installation

```sh
npx create-remix@latest
cd [whatever you named the project]
```

#### Cleaning up

```sh
# Remove demo files
rm -rf app/routes/demos app/styles/demos

# We'll recreate this files later
rm app/routes/index.tsx app/root.tsx
```

#### Re-create file `root.tsx` file under `app` folder.


```tsx
// app/root.tsx
import {LiveReload,Meta,Outlet,Scripts,ScrollRestoration,useCatch} from "remix";

export default function App() {
  return (
    <Document>
      <Layout>
        <Outlet />
      </Layout>
    </Document>
  );
}

function Document({
  children,
  title,
}: {
  children: React.ReactNode;
  title?: string;
}) {
  return (
    <html lang="en">
      <head>
        <meta charSet="utf-8" />
        <meta name="viewport" content="width=device-width,initial-scale=1" />
        {title ? <title>{title}</title> : null}
        <Meta />
      </head>
      <body>
        {children}
        <ScrollRestoration />
        <Scripts />
        {process.env.NODE_ENV === "development" && <LiveReload />}
      </body>
    </html>
  );
}

function Layout({children}: React.PropsWithChildren<{}>) {
  return (
    <main>{children}</main>
  );
}

export function CatchBoundary() {
  let caught = useCatch();

  let message;
  switch (caught.status) {
    case 404:
      message = <p>This is a custom error message for 404 pages</p>
      break;
    // You can customize the behavior for other status codes
    default:
      throw new Error(caught.data || caught.statusText);
  }

  return (
    <Document title={`${caught.status} ${caught.statusText}`}>
      <Layout>
        <h1>
          {caught.status}: {caught.statusText}
        </h1>
        {message}
      </Layout>
    </Document>
  );
}
```

#### Re-create file `index.tsx` file under `app/routes` folder.


```tsx
// app/routes/index.tsx
export default function Index() {
  return (
    <div>
      <h1>Hello world</h1>
    </div>
  );
}
```

The mandatory `hello world` page is now ready.
<Img src="https://i.imgur.com/xVlFv2Q.jpg" height={308}/>

> Expect the design will be terrible, I will create a separate blog for styling.

<br />
<br />

### Integration Prerequisites <a name="integration-prerequisites"></a>
Install Supabase javascript library

> Not using Supabase? Skip to <a href="#fetch-all" target="_self">Fetch All Words</a>

<br />

```sh
npm install @supabase/supabase-js

OR

yarn add @supabase/supabase-js
```

<br />

### Create a Supabase client utility
The next step will allow us to create a Supabase client utility that we can use across the whole application.

<br />

#### Create a `.env` file to hold your Supabase credentials.

```.env
SUPABASE_ANON_KEY=YOUR_SUPABASE_ANON_KEY
SUPABASE_URL=YOUR_SUPABASE_URL
```

> Make sure to add `.env` in the `.gitignore` file.

#### Create a Supabase client utility for reusability


```ts
// libs/supabase-client.ts
import { createClient } from "@supabase/supabase-js";

const supabaseUrl = process.env.SUPABASE_URL as string;
const supabaseKey = process.env.SUPABASE_ANON_KEY as string;

export const supabase = createClient(supabaseUrl, supabaseKey);
```

<br />

### Chores before integration <a name="optional-chores"></a>

<br />

#### (OPTIONAL)Create type definition

> If you prefer not to use TypeScript, remove the declarations and usages of types, and change the file extensions from `.tsx` to `.jsx`.


```ts
// app/models/word.ts
export enum WordType {
  NOUN = "noun",
  VERB = "verb",
  ADJECTIVE = "adjective",
}

export type Word = {
  id: number;
  name: string;
  sentences: string[];
  definitions: string[];
  type: WordType;
};
```

<br />


#### (OPTIONAL) Redirect `/` to `/words`

As I plan to create multiple mini-apps in this project, I'll redirect `/` to `/words`, for now.
The code below will ensure we don't need to manually navigate to `/words` every time we open the root page.


```tsx
// app/routes/index.tsx
import { redirect } from "remix";

export function loader() {
  return redirect("/words", 308);
}
```

<Img height={179} width={480} src="https://media2.giphy.com/media/FUsn85EFKPI1H8Jd1y/giphy.gif"/>

<br />
<br />

## Integrating Supabase with Remix

### Create the words listing page <a name="fetch-all"></a>

> If you are not using Supabase, replace the Supabase API calls with your choice.

<br />

#### fetch data from Supabase using Remix's `loader`

```tsx
// app/routes/words.tsx
import type { LoaderFunction } from "remix";
import { supabase } from "~/libs/supabase-client";
import { Word } from "~/models/word";

export const loader: LoaderFunction = async () => {
  const { data: words } = await supabase
    .from<Word>("words")
    .select("id,name,type");

  // We can pick and choose what we want to display
  // This can solve the issue of over-fetching or under-fetching
  return words;
};
```

<br />

#### Create a React component to display the list of words 


```tsx
// app/routes/words.tsx
import { useLoaderData, Link } from "remix";

// export const loader ...

export default function Index() {
  const words = useLoaderData<Word[]>();

  return (
    <div>
      <h1>English words I learned</h1>
      <ul>
        {words.map((word) => (
          <li key={word.id}>
            <div>
              <Link to={`/words/${word.id}`}>
                {word.name} | {word.type}
              </Link>
            </div>
          </li>
        ))}
      </ul>
    </div>
  );
}
```

The code above will fetch the data from Supabase and display it in a list.
<Img src="https://i.imgur.com/rgrhQCu.jpg" height={426}/>

<br />

### Word details page <a name="fetch-one"></a>

<br />

#### Create a file named `$id.tsx` under `app/routes/words` folder.

<br />

##### Create the loader function


```tsx
// app/routes/words/$id.tsx
import type { LoaderFunction } from "remix";
import { supabase } from "~/libs/supabase-client";
import { Word } from "~/models/word";

export const loader: LoaderFunction = async ({ params }) => {
  const { data } = await supabase
    .from<Word>("words")
    .select("*")
    .eq("id", params.id as string)
    .single();

  return data;
};
```

<br />

##### Create the component


```tsx
// app/routes/words/$id.tsx
// import ...
import { /*other imports*/, useLoaderData } from "remix";

// export const loader ...

export default function Word() {
  const word = useLoaderData<Word>();

  return (
    <div>
      <h3>
        {word.name} | {word.type}
      </h3>
      {word.definitions.map((definition, i) => (
        <p key={i}>
          <i>{definition}</i>
        </p>
      ))}
      {word.sentences.map((sentence, i) => (
        <p key={i}>{sentence}</p>
      ))}
    </div>
  );
}
```

The image below shows that it still won't show even after creating the `/words/[id]` route.
<Img height={159} width={480} src="https://media2.giphy.com/media/tEykbQ44acZ7mzzJHz/giphy.gif"/>

<br />

##### Adding a router `Outlet`

<br />

We need to add an `Outlet` inside our Words Index component to fix the above issue.


```tsx
// app/routes/words.tsx
import {/*other imports*/, Outlet} from "remix";

// export const loader ...

export default function Index() {
  const words = useLoaderData<Word[]>();

  return (
    <div>
      <h1>English words I learned</h1>
      {/* To put the list and outlet side by side */}
      <div style={{ display: "flex", justifyContent: "space-between" }}>
        <ul>
          {words.map((word) => (
            <li key={word.id}>
              <Link to={`/words/${word.id}`}>
                {word.name} | {word.type}
              </Link>
            </li>
          ))}
        </ul>
        <Outlet /> {/* <-- this is where $id.tsx will render */}
      </div>
    </div>
  );
}
```

After clicking on a word, `$id.tsx route` will render on where we put the `Outlet`.
<Img height={238} width={480} src="https://media2.giphy.com/media/VkczpdMwHjywdt7gg3/giphy.gif"/>

<br />

### Delete a word entry

Since we're already on the `/words/$id` page, let's proceed with deletion first

<br />

#### Add a button to delete the word

```tsx
// app/routes/words/$id.tsx
import {/*other imports*/, Form} from "remix";

// export const loader ...

export default function Index() {
  // ...

  return (
    <div>
      {/* previous content */}
      <Form method="post">
        <input type="hidden" name="_method" value="delete" />
        <button type="submit">Delete</button>
      </Form>
    </div>
  );
}
```

The image shows a message that we did not define any action to handle the submit event.
<Img src="https://i.imgur.com/8IqhBuG.jpg" height={904} width={1389}/>

<br />

#### Delete the word in the database using Remix's `action`


```tsx
// app/routes/words/$id.tsx
import { /*other imports*/, redirect} from "remix";
import type {/*other imports*/, ActionFunction} from "remix";

export const action: ActionFunction = async ({ request, params }) => {
  const formData = await request.formData();

  if (formData.get("_method") === "delete") {
    await supabase
      .from<Word>("words")
      .delete()
      .eq("id", params.id as string);

    return redirect("/words");
  }
};

// ...rest of the code
```

After we click on the delete button, the word `hello` will be deleted from the database, and the page will redirect to the `/words` page.

<Img height={238} width={480} src="https://media2.giphy.com/media/5R5wyoHvJbeR88VoGY/giphy.gif"/>

<br />

##### Explanation:
- We created a form with a hidden input field named `_method` with value `delete`.
- When the submit button is clicked, the `action` handler will trigger in the server.
- Inside the `action` handler, we check if the `_method` is `delete`.
- If it is, we delete the word from the database.

##### Why go through all this trouble?

It just happens that this approach does not need any JavaScript to run(try it on your browser).
This means our app is interactive even before we load the JavaScript from the server.
 
<br />

### Add a new word entry <a name="create-one"></a>
Now we don't have anything on the list; let's create the route to handle creation.

<br />

#### Create a button in the `/words` route that will navigate to `/words/add`

```tsx
// app/routes/words.tsx
import { /*other imports*/ Form } from "remix";

// export const loader ...

export default function Index() {
  // const words ...

  return (
    <div>
      <h1>English words I learned</h1>
      <Form method="get" action={"/words/add"}>
        <button type="submit">Add new word</button>
      </Form>
      {/* previous contents */}
    </div>
  );
}
```

#### Create the add new word route

To avoid a `404` page, let's create the `/words/add` route.

<br />

##### Create the component


```tsx
// app/routes/words/add.tsx
import {Form} from "remix";
import { WordType } from "~/models/word";

export default function AddWord() {
  return (
    <Form method="post">
      <div>
        <label htmlFor="name">Word</label>
        <input id="name" name="name" type="text" placeholder="Word" required />
      </div>
      <div>
        <label htmlFor="type">Type</label>
        <select id="type" name="type" defaultValue={WordType.NOUN}>
          <option value={WordType.NOUN}>Noun</option>
          <option value={WordType.VERB}>Verb</option>
          <option value={WordType.ADJECTIVE}>Adjective</option>
        </select>
      </div>
      <div>
        <label htmlFor="sentence.1">Sentences</label>
        <textarea
          id="sentence.1"
          name="sentence"
          placeholder="Sentence"
          minLength={10}
        />
      </div>
      <div>
        <label htmlFor="definition.1">Definitions</label>
        <textarea
          id="definition.1"
          name="definition"
          placeholder="Definition"
          minLength={10}
        />
      </div>
      <button type="submit">Submit</button>
    </Form>
  );
}
```

The image below shows the form we created after clicking on the `Add new word` button.
<Img height={238} width={480} src="https://media2.giphy.com/media/AwKs6oyTQvg568x2bd/giphy.gif"/>

<br />

##### Add an action

To avoid the missing action error after clicking on the `Submit` button, let's add an action on the `words/add` route.


```tsx
// app/routes/words/add.tsx
import { /*other imports*/, redirect } from "remix";
import type { ActionFunction } from "remix";
import { supabase } from "~/libs/supabase-client";

export const action: ActionFunction = async ({ request }) => {
  const formData = await request.formData();

  const newWord = {
    name: formData.get("name"),
    type: formData.get("type"),
    sentences: formData.getAll("sentence"),
    definitions: formData.getAll("definition"),
  };

  const { data } = await supabase.from("words").insert([newWord]).single();

  return redirect(`/words/${data?.id}`);
};
```

After clicking on the `Submit` button, the word will be added to the database, and the page will redirect to the `/words/$id` page.
<Img height={238} width={480} src="https://media2.giphy.com/media/dYuz36okSZXIFjTFVs/giphy.gif"/>

<br />

> I'm not sure if you noticed, but we haven't used any JavaScript code on the Frontend, yet it could complete the intended task. We only used an HTML form as it was initially used to handle validations and perform the submission. This is what I like about Remix; we can focus on the web fundamentals to become a better Web developer instead of making some workarounds. 



<br />

### Edit a word details <a name="edit-one"></a>

Now, to handle the missing operation in our CRUD app, let's add the ability to modify an existing entry.

<br />

#### Create a file named `edit.$id.tsx` under `app/routes/words`

When we add a `.` between words, it will transform to `/` in the URL.
The above example will result in `words/edit/[id]`.

<br />

##### Create a form for editing the word

<br />

###### Refactoring
Since the edit form is very similar to the add form, we can reuse the same form with additional checks to determine if we are adding or editing.


```tsx
// app/components/WordForm.tsx
import { Form } from "remix";
import { Word, WordType } from "~/models/word";

export default function WordForm({ word }: { word?: Word }) {
  return (
    <Form method="post">
      <div>
        <label htmlFor="name">Word</label>
        <input
          id="name"
          name="name"
          type="text"
          placeholder="Word"
          required
          defaultValue={word?.name ?? ""}
          disabled={Boolean(word?.name)}
        />
      </div>
      <div>
        <label htmlFor="type">Type</label>
        <select
          id="type"
          name="type"
          defaultValue={word?.type ?? WordType.NOUN}
        >
          <option value={WordType.NOUN}>Noun</option>
          <option value={WordType.VERB}>Verb</option>
          <option value={WordType.ADJECTIVE}>Adjective</option>
        </select>
      </div>
      <div>
        {word?.sentences.map((sentence, i) => (
          <SentenceField index={i + 1} sentence={sentence} key={i} />
        )) ?? <SentenceField index={1} sentence={""} />}
      </div>
      <div>
        {word?.definitions.map((definition, i) => (
          <DefinitionField index={i + 1} definition={definition} key={i} />
        )) ?? <DefinitionField index={1} definition={""} />}
      </div>
      <button type="submit">Submit</button>
    </Form>
  );
}

const SentenceField = ({ index, sentence }) => (
  <div>
    <label htmlFor={`sentence.${index}`}>Sentence #{index}</label>
    <textarea
      id={`sentence.${index}`}
      name="sentence"
      defaultValue={sentence}
      placeholder={`Sentence #${index}`}
      minLength={10}
    />
  </div>
);

const DefinitionField = ({ index, definition }) => (
  <div>
    <label htmlFor={`definition.${index}`}>Definition #{index}</label>
    <textarea
      id={`definition.${index}`}
      name="definition"
      defaultValue={definition}
      placeholder={`Definition #${index}`}
      minLength={10}
    />
  </div>
);
```

<br />

That's a lot of code; however, we can reap the benefits of simplifying the code in `add.tsx` and `edit.$id.tsx`.

##### Update `routes/words/add.tsx`


```tsx
// app/routes/words/add.tsx
// other code...

export default function AddWord() {
  return <WordForm />;
}
```

##### Create `routes/words/edit.$id.tsx`


```tsx
// app/routes/words/edit.$id.tsx
import { useLoaderData } from "remix";
import WordForm from "~/components/WordForm";
import { Word } from "~/models/word";

export default function EditWord() {
  const data = useLoaderData<Word>();

  return <WordForm word={data} />;
}
```

Now, we have a reusable form. If we have to make a style change, we can update the `WordForm` component, reflecting the change on both routes. 

> NOTE: I'm extracting everything to `WordForm.tsx` since it is applicable in my use case.

##### Create a loader for the word details

In order for the edit form to be populated with the existing data, we need to create a loader.


```tsx
// app/routes/words/edit.$id.tsx
import { supabase } from "~/libs/supabase-client";
import type {LoaderFunction} from "remix";

export const loader: LoaderFunction = async ({ params }) => {
  const { data } = await supabase
    .from<Word>("words")
    .select("*")
    .eq("id", params.id as string)
    .single();

  return data;
};

// export const EditWord...
```

#### Create a button in the `/words/$id` page to edit a word


```tsx
// app/routes/words/$id.tsx

// ...

export default function Word() {
  // const word ...

  return (
    <div>
      {/* other code... */}
      <Form method="get" action={`/words/edit/${word.id}`}>
        <button type="submit">Edit</button>
      </Form>
    </div>
  );
}
```

The image below shows the pre-filled form depending on the content of `id` in the URL.
<Img height={289} width={480} src="https://media2.giphy.com/media/VWgIpSiHRlNmg2p4VT/giphy.gif"/>

<br />

##### Add an action handler

To handle the form submission, we need to add an action handler.


```tsx
// app/routes/words/edit$.id.tsx
import {/*other imports*/, redirect} from "remix";
import type {/*other imports*/, ActionFunction} from "remix";

export const action: ActionFunction = async ({ request, params }) => {
  const formData = await request.formData();
  const id = params.id as string;

  const updates = {
    type: formData.get("type"),
    sentences: formData.getAll("sentence"),
    definitions: formData.getAll("definition"),
  };

  await supabase.from("words").update(updates).eq("id", id);

  return redirect(`/words/${id}`);
};

// export const loader...

// export const EditWord...
```

After modifying some fields and clicking the submit button, the page will redirect to the `/words/$id` page with the updated data.
<Img height={289} width={480} src="https://media2.giphy.com/media/b2kgQRnUtoyU2ZGvRB/giphy.gif"/>

<br/>

### Add indicator for state transition  <a name="state-transition"></a>

By utilizing the `useTransition` hook, we can add or change something on the screen depending on the route's state.

// Routes you want to use `useTransition`
```tsx
import { useTransition } from "remix";

// Somewhere in the route's compnent
let transition = useTransition();

<div>... State: {transition.state}</div>
```

We can replace the text states below with global loading indicator, local component spinner, disabling elements, etc.

<Img height={328} width={480} src="https://media2.giphy.com/media/LLxoIxfuoudslYRGxM/giphy.gif"/>

### Extra Demo: CRUD operations without JavaScript <a name="extra-demo"></a>

Here's proof that we can perform the CRUD operations without using any JavaScript on the client-side(_as indicated by errors in the network tab_).
Take note that I also simulated a slower network connection, yet the performance is not that terrible.

<Img height={273} width={480} src="https://media2.giphy.com/media/m6aKzheArUJCZYmbae/giphy.gif"/>

> I'm not saying we should not use JavaScript on the client-side, as JavaScript can do some cool stuff to help with user experience.
> For instance, you might notice that the states were stuck in `idle`.



## Conclusion
So far, I'm having a positive experience with the framework. Of course, I'm still learning, but I'm enjoying the process. I'm starting to agree with the Remix team said that if we become better with Remix, we become better with the Web. Working with Remix allows me to refresh my HTML skills that are almost diminishing due to too much dependency on JavaScript. I'm looking forward to using more of their features in the next iteration of this app.

## What's next?

- [Styling](https://dev.to/codegino/remix-styling-using-vanilla-css-4g6b)
- [Authentication](https://dev.to/codegino/remix-and-supabase-authentication-205i)
- [Error handling](https://dev.to/codegino/how-to-handle-errors-gracefully-in-remix-1jkb)
- [SEO](https://dev.to/codegino/remix-search-engine-optimization-4da4)