DEV Community

Cover image for Remix with PostgreSQL and Prisma from Local Environment Setup to Deploy to Fly.io
Yuichi Nabeshima
Yuichi Nabeshima

Posted on

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


Enter fullscreen mode Exit fullscreen mode
  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


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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



docker compose up -d


Enter fullscreen mode Exit fullscreen mode

Install Prisma

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



npm install prisma --save-dev


Enter fullscreen mode Exit fullscreen mode

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



npx prisma init --datasource-provider postgresql


Enter fullscreen mode Exit fullscreen mode

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"


Enter fullscreen mode Exit fullscreen mode

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

Image description

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")
+ }


Enter fullscreen mode Exit fullscreen mode

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

Then, execute migration.



npx prisma migrate dev --name init


Enter fullscreen mode Exit fullscreen mode

Now you can add the table to the database.

Connect local PostgreSQL to DBeaver

Start DBeaver and click the icon on the top left.

Image description

Select PostgreSQL.
Image description

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

Image description

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.

Image description

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.

Image description

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

Image description

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

Image description

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

Image description

sql



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


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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>
    </>
  );
}



Enter fullscreen mode Exit fullscreen mode

Please start dev server with below command.



npm run dev


Enter fullscreen mode Exit fullscreen mode

It is now displayed on the screen created data.

Image description

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.

Image description

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



brew install flyctl


Enter fullscreen mode Exit fullscreen mode

Let's deploy it!!



fly launch


Enter fullscreen mode Exit fullscreen mode

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.

Image description

Please select development for PostgreSQL as well.

Image description

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.

Image description

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]


Enter fullscreen mode Exit fullscreen mode

Create new role(user).



CREATE ROLE dbeaver_user WITH PASSWORD 'newpassword';


Enter fullscreen mode Exit fullscreen mode

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;


Enter fullscreen mode Exit fullscreen mode

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



\l


Enter fullscreen mode Exit fullscreen mode

Image description

Terminate the DB ssh connection.



exit


Enter fullscreen mode Exit fullscreen mode

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



fly proxy 5432 -a [YOUR_DB_APP_NAME]


Enter fullscreen mode Exit fullscreen mode

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

Image description

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');


Enter fullscreen mode Exit fullscreen mode

It was displayed successfully!🎉
That's it!

Image description

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



fly deploy


Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)