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
- Where should we create your new project? => ./my-remix-app
- Initialize a new git repository? => Yes
- 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.
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.
Top comments (0)