Remix with PostgreSQL and Prisma from Local Environment Setup to Deploy to Fly.io

Hi there😊
I'm Yuichi, a CCTB student and a Frontend developer.

Remix is ​​a great framework and I'm really into it right now.

It is React framework but we can develop backend together, so it is very convenient.

I am going to introduce to create base of full stack app by the Remix with PostgreSQL and Prisma to deploy on Fly.io.
And I also try to reach connecting DBeaver both environment local and production.

Tools

• macOS Sonoma 14.1.2
• Docker Desktop 4.32.0
• Node 20.16.0
• DBeaver 24.1.3

Why Fly.io

Fly.io allows you to easily deploy the Remix apps and also provides PostgreSQL.
It has free plan, so it doesn't cost much to deploy a small project.

First, Install Remix

npx create-remix@latest

1. Where should we create your new project? => ./my-remix-app
2. Initialize a new git repository? => Yes
3. Install dependencies with npm? => Yes

cd ./my-remix-app

Create docker-compose.yml

Next, create docker-compose.yml on the project root for creating the PostgreSQL container for the local environment.

touch docker-compose.yml

docker-compose.yml

version: "3.7"
services:
  postgres:
    image: postgres:latest
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    ports:
      - "5432:5432"
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

Then, start the container.
Please start the Docker Desktop before.

docker compose up -d

Install Prisma

Install Prisma to manipulate data of the database from the app.

npm install prisma --save-dev

Please initialize prisma.
Executing the command below will create a prisma directory and .env file.

npx prisma init --datasource-provider postgresql

Then, Modify value of DATABASE_URL in .env to like below for connecting prisma to database.

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/postgres?schema=public"

This string will contain the value set in docker-compose.yml.

If the user name or password set in docker-compose.yml are different, please change your DATABASE_URL.

Migrate Database

Now that prism and the database are connected let's add a table to the database for testing.
Please edit schema.prisma under the prisma like below.

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

+ model Post {
+   id      Int      @id @default(autoincrement())
+   name    String?
+   body    String   @db.Text

+   @@map("post")
+ }

@@map is the characters you enter here will become the table name.

Then, execute migration.

npx prisma migrate dev --name init

Now you can add the table to the database.

Connect local PostgreSQL to DBeaver

Start DBeaver and click the icon on the top left.

Select PostgreSQL.

Enter postgres in the Password of red circle.
This Database, Username, and Password are set in docker-compose.yml.

It is successful if there are no errors.

Then, you can make sure the tables of database.
It is successful if the post table is displayed as shown in the image.

Create sample Data

Create data directly from DBeaver using SQL.

Right click on the table name post and select Generate SQL then select the INSERT.

Then a modal like the image will open, so please copy the SQL inside.

Click the SQL icon in the top left to open the SQL script and paste the SQL you copied.

Add test text to the copied SQL and execute it using the Execute button on the left.

sql

INSERT INTO public.post
(id, "name", body)
VALUES(nextval('post_id_seq'::regclass), 'Henly', 'I am Henly, it is test post.');

Creating test data is now complete.

Display database data on browser

Let's display the created test data on the screen.

Please install the @prisma/client for client.

npm install @prisma/client

Please rewrite /app/routes/_index.tsx as below.

import { type LoaderFunction, json } from "@remix-run/node";
import { PrismaClient } from "@prisma/client";
import { useLoaderData } from "@remix-run/react";

export const loader: LoaderFunction = async () => {
  const prisma = new PrismaClient();
  const post = await prisma.post.findFirst();

  return json(post);
};

export default function Index() {
  const postData = useLoaderData<typeof loader>();

  return (
    <>
      <h1>My app</h1>
      <h2>{postData?.name ? postData.name : 'loading...'}</h2>
      <h2>{postData?.body ? postData.body : 'loading...'}</h2>
    </>
  );
}

Please start dev server with below command.

npm run dev

It is now displayed on the screen created data.

Deploy app to Fly.io

Now that the setup in the local environment is complete, it's time to deploy it to the Fly.io.

First, register with Fly.io and register your credit card.
Select the $0 plan.

Next, install flyctl to use fly's CLI on your local machine.

brew install flyctl

Let's deploy it!!

fly launch

The following question will be displayed, please select yes.

Do you want to tweak these settings before proceeding? => y

Then, the browser will open, so please configure the app settings.

Please select the lowest CPU and memory for your web server.

Please select development for PostgreSQL as well.

Once the browser settings are complete, the command line will start working again.
The DB information will be displayed during the process, so don't forget to write it down.

The deployment has completed so also connect to the DB with DBeaver same as local.

First, connect to DB by SSH and create new user.

fly postgres connect -a [YOUR_DB_APP_NAME]

Create new role(user).

CREATE ROLE dbeaver_user WITH PASSWORD 'newpassword';

Then, grant privileges to the created user.
This time, we want to edit the database directly from DBeaver, so we will grant SUPERUSER permission.

ALTER ROLE dbeaver_user SUPERUSER LOGIN;

Use the command below to display a list of DBs and check the DB name.

\l

Terminate the DB ssh connection.

exit

When operating from local DBeaver, you need to run a proxy in the terminal.

fly proxy 5432 -a [YOUR_DB_APP_NAME]

Create a new connection in DBeaver using the database name, username, and password from earlier.

Let's create test data from SQL same as local environment.

INSERT INTO public.post
(id, "name", body)
VALUES(nextval('post_id_seq'::regclass), 'Yuichi', 'This is production');

It was displayed successfully!🎉
That's it!

If you want to fix it and deploy it again, you can deploy it with the following command.

fly deploy

Thank you for reading this long article😊
Have a good Remix life!✨

I am looking for a job

As of July 2024, I am looking for a job.
My areas of expertise are React, TypeScript, Remix, Next, PHP, and WordPress.
I'm currently a student, so I'm looking for a part-time position where I can work up to 20 hours a week.
I'm looking for a full-time job starting next August.