Setting Up Your Database on AWS

This post covers the steps to use AWS instead of Vercel accounts mentioned at https://nextjs.org/learn/dashboard-app/setting-up-your-database to setup a PostgresSQL database for Next.js.

To use these steps first launch an EC2 instance and install nginx, PostgresSQL, and ssh with a security group that lets GitHub to access the server via SSH. I’ll provide a quick launch guide below.

For postgres, I altered customers:

ALTER TABLE public.customers
    ADD CONSTRAINT customers_email_key UNIQUE (email);
ALTER TABLE

Setting up an efficient workflow to deploy your Next.js app from Visual Studio Code on your iMac to your EC2 instance each time you push changes to GitHub involves a few interconnected systems: your local development environment, your GitHub repository, and your EC2 instance. I’ll guide you through the process of automating deployments using GitHub Actions, which can automate the deployment of your code to your EC2 server when you push to your GitHub repository.

Step 1: Prepare Your Local Development Environment

Visual Studio Code Setup

Install VS Code on your iMac if it’s not already installed (Download VS Code).
Install Necessary Extensions:

GitHub Pull Requests and Issues: To manage GitHub Pull Requests and Issues.
Remote – SSH: To connect and edit files directly on your EC2 instance if necessary.
ESLint, Prettier: For code linting and formatting (recommended for Next.js development).

Configure Your Project:

Ensure your Next.js project is properly set up in a GitHub repository.
Open your project in VS Code and make sure it’s configured with your build scripts and a .gitignore file that excludes node_modules, .env, and other sensitive files.

The server’s .env should look something like the following:

# Copy from .env.local on the Vercel dashboard
# https://nextjs.org/learn/dashboard-app/setting-up-your-database#create-a-postgres-database
POSTGRES_URL=postgresql://instacopilot_user:password@localhost:5432/instacopilot_db
POSTGRES_PRISMA_URL=postgresql://instacopilot_user:password@localhost:5432/instacopilot_db?schema=public
POSTGRES_URL_NON_POOLING=postgresql://instacopilot_user:password@localhost:5432/instacopilot_db?pool=false
POSTGRES_USER=instacopilot_user
POSTGRES_HOST=localhost
POSTGRES_PASSWORD=pwd
POSTGRES_DATABASE=instacopilot_db

# `openssl rand -base64 32`
AUTH_SECRET=some-secret
AUTH_URL=https://instacopilot.ai/api/auth
# Set the environment to production
NODE_ENV=production

Step 2: Set Up SSH Access to EC2 Instance

Ensure you can SSH into your EC2 instance from your iMac:

Generate an SSH Key (if not already done):

   ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

Follow the prompts and save it to a default or specific path.

Add Your SSH Public Key to EC2:

Copy your public key to your clipboard:
bash pbcopy < ~/.ssh/id_rsa.pub
Log into your EC2 instance and add this key to the ~/.ssh/authorized_keys file for your user.

Verify SSH Connection:

Ensure you can connect to your EC2 instance without issues:
bash ssh -i ~/.ssh/id_rsa your-ec2-user@ec2-instance-ip

Step 3: Automate Deployment Using GitHub Actions

Create a Workflow File:

In your GitHub repository, create a directory named .github/workflows if it doesn’t already exist.
Create a new file called main.yml in this directory.

Define Workflow:

Here’s a basic workflow file to deploy your Next.js app: name: Deploy Next.js App on

name: Deploy Next.js App

on:
  push:
    branches:
      - main

jobs:
  build:
    runs-on: ubuntu-latest

    steps:
    - uses: actions/checkout@v4

    - name: Set up Node.js
      uses: actions/setup-node@v4
      with:
        node-version: '22'  # Set this to your Node.js version

    - name: Install Dependencies
      run: npm install

    - name: Build
      run: npm run build

    - name: Deploy to EC2
      uses: appleboy/scp-action@master
      with:
        host: ${{ secrets.HOST }}
        username: ${{ secrets.USERNAME }}
        key: ${{ secrets.SSH_KEY }}
        port: '22'
       source: "*, .next, public, pages, styles, package.json, package-lock.json, next.config.js"
        target: "/var/www/instacopilot.ai/html"

Set GitHub Secrets:

Go to your repository on GitHub, click on Settings → Secrets, and add the following secrets:
- HOST: Your EC2 instance IP.
- USERNAME: Your EC2 username.
- SSH_KEY: Your private SSH key (paste the entire key file content here).

Configure nginx on EC2:

Make sure nginx is configured to serve files from /var/www/instacopilot.ai/html.
Adjust the server block in nginx to correctly point to your built Next.js app, typically the .next static files.

here is the config file

server {
    server_name instacopilot.ai www.instacopilot.ai;
    
location / {
    proxy_pass http://localhost:3000;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header Cookie $http_cookie;
    proxy_set_header Set-Cookie $http_set_cookie;
    proxy_cache_bypass $http_upgrade;
}

location /pgadmin {
    proxy_pass http://localhost:5050;
    proxy_http_version 1.1;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
        proxy_set_header X-Script-Name /pgadmin;

}


    location /static {
        alias /var/www/instacopilot.ai/html/.next/static;  # Serve static files directly
        expires 1y;
        access_log off;
        add_header Cache-Control "public";
    }

    error_page 404 /404.html;
    location = /40x.html {
    }

    error_page 500 502 503 504 /50x.html;
    location = /50x.html {
    }


    listen 443 ssl; # managed by Certbot
    ssl_certificate /etc/letsencrypt/live/instacopilot.ai/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/instacopilot.ai/privkey.pem; # managed by Certbot
    include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot


}

server {
    if ($host = www.instacopilot.ai) {
        return 301 https://$host$request_uri;
    } # managed by Certbot


    if ($host = instacopilot.ai) {
        return 301 https://$host$request_uri;
    } # managed by Certbot


    listen 80;
    server_name instacopilot.ai www.instacopilot.ai;
    return 404; # managed by Certbot

}

Step 4: Test Your Setup

Make a small change in your Next.js app, commit, and push to the main branch of your GitHub repository.
Check the Actions tab in GitHub to see the deployment process and ensure it completes successfully.
Verify by accessing https://instacopilot.ai to see the changes.

This setup provides a smooth and automated process for getting your Next.js application from your iMac to your EC2 instance visible to the public each time you push your changes.

Creating a dedicated deployuser for managing deployments on your EC2 instance is a best practice for several reasons:

Security Isolation: Using a separate user for deployment activities helps isolate permissions and reduce the risk of unauthorized access to administrative functions. The deployuser can be given only the necessary permissions to deploy and manage the application, without having broader access that your ec2-user might have.
Audit and Logging: It’s easier to track and audit deployment activities when they are associated with a specific user. This can be crucial for troubleshooting and understanding changes made to your production environment.
Minimizing Risk of Human Error: With a dedicated deployment user, you reduce the risk associated with manual operations impacting your production environment, as routine access would typically be done with a less privileged user.

Steps to Create and Use `deployuser`:

1. Create the `deployuser` on your EC2 instance:

Connect to your EC2 instance using SSH as ec2-user.
Add a new user called deployuser:
bash sudo adduser deployuser
Set permissions or add to groups according to the least privilege necessary for deployment tasks.

2. Setup SSH for `deployuser`:

Switch to the new user:
bash sudo su - deployuser
Create a .ssh directory:
bash mkdir ~/.ssh chmod 700 ~/.ssh
Create an authorized_keys file:
bash touch ~/.ssh/authorized_keys chmod 600 ~/.ssh/authorized_keys

3. Add Your Public SSH Key:

On your local machine, copy your SSH public key:
bash pbcopy < ~/.ssh/id_rsa.pub
Paste this key into the ~/.ssh/authorized_keys file of the deployuser on your EC2:
bash echo "your-public-ssh-key" >> ~/.ssh/authorized_keys

4. Verify SSH Access for `deployuser`:

Ensure you can connect to your EC2 instance using the new deployuser:
bash ssh -i ~/.ssh/id_rsa deployuser@ec2-instance-ip

By following these steps, you create a safer and more controlled deployment environment. This not only enhances security but also aligns with best practices for managing production servers. Using deployuser also simplifies role management and access controls, particularly if you later decide to automate more tasks or integrate more tools into your deployment process.

If you already have an SSH key pair stored at /Users/tony/.ssh/id_rsa. Here are your options:

Option 1: Overwrite the Existing Key

Warning: Overwriting your existing SSH key will replace it completely, which could disrupt any other systems or services that use the current key for authentication. If you choose to overwrite it:

Enter y when prompted to overwrite the file.
After overwriting, you’ll need to update any systems or services that previously used the old key with the new public key.

Option 2: Save the New Key Under a Different Name

To avoid overwriting the existing key, you can save the new key pair under a different name. This way, you keep your existing key pair intact and can use the new key pair specifically for your EC2 deployment:

When prompted for the file in which to save the key, enter a new file name, for example:

  /Users/tony/.ssh/id_rsa_ec2

This will create a new key pair (id_rsa_ec2 and id_rsa_ec2.pub) in your .ssh directory.

Next Steps After Generating the Key

If you created a new key:

Copy the New Public Key to Clipboard:

   pbcopy < ~/.ssh/id_rsa_ec2.pub

Add the New Public Key to EC2:

SSH into your EC2 instance as ec2-user or another user with sudo privileges.
Switch to the deployuser if you have set one up:
bash sudo su - deployuser
Append the new public key to the ~/.ssh/authorized_keys file:
bash echo "paste-your-public-key-here" >> ~/.ssh/authorized_keys

Verify SSH Access:

Try SSHing into the EC2 instance using the new key:
bash ssh -i ~/.ssh/id_rsa_ec2 deployuser@ec2-instance-ip

If you chose to overwrite:

Update all places where your old key was used with the new public key.
Verify SSH access with the new key as described above.

Choosing to create a new key under a different name is generally safer and more manageable, especially if you connect to multiple servers or services. This approach avoids potential disruptions in other setups.

Given that you have Node.js version 22.0.0 installed on your local machine, this is one of the newer versions of Node.js, which includes the latest features and security updates.

Next Steps:

Ensure Compatibility:
Make sure that your Next.js project is compatible with Node.js version 22.0.0. Most recent releases of Next.js should support this version, but it’s good to check any third-party dependencies for compatibility issues as well.
Update GitHub Actions Workflow:
Since you’re using Node.js version 22.0.0 locally, it’s a good practice to use the same version in your GitHub Actions workflow to maintain consistency between development and production environments. Update your GitHub Actions workflow file to reflect this:

   - name: Set up Node.js
     uses: actions/setup-node@v4
     with:
       node-version: '22'  # Updated to match your local Node.js version

This ensures that the environment where your code is built and tested in GitHub Actions mirrors your local development environment, reducing the likelihood of environment-specific bugs.

Check Node.js Version on EC2 Instance:
It’s important that the Node.js version on your EC2 instance is also compatible with your project. SSH into your EC2 instance and check the installed Node.js version:

   ssh -i ~/.ssh/id_rsa_instacopilot deployuser@52.207.29.86
   node -v

If the version is not 22.0.0 or if Node.js isn’t installed:

You can install or update it using NVM (Node Version Manager), which is especially useful if you need to manage multiple versions of Node.js:

curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.1/install.sh | bash
source ~/.bashrc  # Or ~/.zshrc or ~/.profile, depending on your shell
nvm install 22
nvm use 22
nvm alias default 22

Deployment Considerations:
Make sure your deployment scripts and processes are set up to use the correct Node.js version. This might include setting the Node.js version in any startup scripts or ensuring that any service managers (like pm2) are restarted to use the new Node.js version after an upgrade.

By aligning the Node.js versions across your local development environment, GitHub Actions, and your production environment on EC2, you minimize the risk of running into version-specific issues and ensure a smoother deployment process.

Install Development Tools on Amazon Linux 2023

Open SSH Connection:
- Connect to your EC2 instance if you aren’t already connected.
Update Your Package Manager:
- It’s a good practice to update your package manager to ensure all repositories and packages are up to date.bashCopy codesudo dnf update -y
Install Development Tools:
- Run the following command to install make and other essential development tools such as gcc, g++, and so on:bashCopy codesudo dnf groupinstall "Development Tools" -y
- This command installs a comprehensive set of tools required for building and compiling software.
Install Additional Dependencies:
- Some Node.js packages might require python3 or other specific libraries. Ensure you have python3 and its development headers:bashCopy codesudo dnf install python3 python3-devel -y
- If node-gyp or other build processes require additional specific libraries, you may need to install those as well.
Re-run npm install:
- Navigate back to your project’s root directory and try installing your npm packages again:bashCopy codecd /var/www/instacopilot.ai/html npm install

Run the app

Step 1: Install PM2

Once Node.js is set up, you can install PM2 globally using npm:

npm install pm2@latest -g

Step2: Start Your Application with PM2

Navigate to your application directory, where your Node.js app’s entry file is located (for example, app.js or server.js).

bashCopy codecd /path/to/your/nodejs/app
pm2 start npm --name "next-app" -- run start

Replace "next-app" with a name for your app in the PM2 process list.

Step 3: Configure PM2 to Auto-start at Boot

To ensure that your application starts automatically after a reboot, use the pm2 startup command, which sets up a startup script to launch PM2 and its managed processes on server boots:

pm2 startup

This command will generate a script and provide you with a command that you need to execute with superuser privileges. Run the command that pm2 startup outputs.

Step 4: Save the PM2 List

Save the current list of running applications to be resurrected after a reboot:

pm2 save

Step 5: Verify PM2 Operation

Check the status of your PM2-managed applications:

pm2 status

Conclusion

Now, PM2 is installed and configured to manage your application on Amazon Linux 2023. It will ensure that your Node.js application is kept alive continuously and restarts automatically if it crashes or the server reboots.

Instead of using “import { sql } from ‘@vercel/postgres’;” we can either install postgres and configure the database

npm install postgres
// Configure your database connection
const sql = postgres(process.env.POSTGRES_URL as string);

Or we can refactor the code to use “pg”. Here is the refactored seed.js

const { Pool } = require('pg');
const dotenv = require('dotenv');
const {
  invoices,
  customers,
  revenue,
  users,
} = require('../app/lib/placeholder-data.js');
const bcrypt = require('bcrypt');

dotenv.config();

const pool = new Pool({
  connectionString: process.env.POSTGRES_URL,
});

async function seedUsers() {
  const client = await pool.connect();
  try {
    await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);
    // Create the "users" table if it doesn't exist
    const createTable = await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL
      );
    `);

    console.log(`Created "users" table`);

    // Insert data into the "users" table
    const insertedUsers = await Promise.all(
      users.map(async (user) => {
        const hashedPassword = await bcrypt.hash(user.password, 10);
        return client.query(
          `
          INSERT INTO users (id, name, email, password)
          VALUES ($1, $2, $3, $4)
          ON CONFLICT (id) DO NOTHING;
        `,
          [user.id, user.name, user.email, hashedPassword],
        );
      }),
    );

    console.log(`Seeded ${insertedUsers.length} users`);

    return {
      createTable,
      users: insertedUsers,
    };
  } catch (error) {
    console.error('Error seeding users:', error);
    throw error;
  } finally {
    client.release();
  }
}

async function seedInvoices() {
  const client = await pool.connect();
  try {
    await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

    // Create the "invoices" table if it doesn't exist
    const createTable = await client.query(`
      CREATE TABLE IF NOT EXISTS invoices (
        id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
        customer_id UUID NOT NULL,
        amount INT NOT NULL,
        status VARCHAR(255) NOT NULL,
        date DATE NOT NULL
      );
    `);

    console.log(`Created "invoices" table`);

    // Insert data into the "invoices" table
    const insertedInvoices = await Promise.all(
      invoices.map((invoice) =>
        client.query(
          `
          INSERT INTO invoices (customer_id, amount, status, date)
          VALUES ($1, $2, $3, $4)
          ON CONFLICT (id) DO NOTHING;
        `,
          [invoice.customer_id, invoice.amount, invoice.status, invoice.date],
        ),
      ),
    );

    console.log(`Seeded ${insertedInvoices.length} invoices`);

    return {
      createTable,
      invoices: insertedInvoices,
    };
  } catch (error) {
    console.error('Error seeding invoices:', error);
    throw error;
  } finally {
    client.release();
  }
}

async function seedCustomers() {
  const client = await pool.connect();
  try {
    await client.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

    // Create the "customers" table if it doesn't exist
    const createTable = await client.query(`
      CREATE TABLE IF NOT EXISTS customers (
        id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        image_url VARCHAR(255) NOT NULL
      );
    `);

    console.log(`Created "customers" table`);

    // Insert data into the "customers" table
    const insertedCustomers = await Promise.all(
      customers.map((customer) =>
        client.query(
          `
          INSERT INTO customers (id, name, email, image_url)
          VALUES ($1, $2, $3, $4)
          ON CONFLICT (id) DO NOTHING;
        `,
          [customer.id, customer.name, customer.email, customer.image_url],
        ),
      ),
    );

    console.log(`Seeded ${insertedCustomers.length} customers`);

    return {
      createTable,
      customers: insertedCustomers,
    };
  } catch (error) {
    console.error('Error seeding customers:', error);
    throw error;
  } finally {
    client.release();
  }
}

async function seedRevenue() {
  const client = await pool.connect();
  try {
    // Create the "revenue" table if it doesn't exist
    const createTable = await client.query(`
      CREATE TABLE IF NOT EXISTS revenue (
        month VARCHAR(4) NOT NULL UNIQUE,
        revenue INT NOT NULL
      );
    `);

    console.log(`Created "revenue" table`);

    // Insert data into the "revenue" table
    const insertedRevenue = await Promise.all(
      revenue.map((rev) =>
        client.query(
          `
          INSERT INTO revenue (month, revenue)
          VALUES ($1, $2)
          ON CONFLICT (month) DO NOTHING;
        `,
          [rev.month, rev.revenue],
        ),
      ),
    );

    console.log(`Seeded ${insertedRevenue.length} revenue`);

    return {
      createTable,
      revenue: insertedRevenue,
    };
  } catch (error) {
    console.error('Error seeding revenue:', error);
    throw error;
  } finally {
    client.release();
  }
}

async function main() {
  await seedUsers();
  await seedCustomers();
  await seedInvoices();
  await seedRevenue();

  await pool.end();
}

main().catch((err) => {
  console.error(
    'An error occurred while attempting to seed the database:',
    err,
  );
});

To ensure next.js features work on production you must add async headers to next.config.js as follows

/** @type {import('next').NextConfig} */
module.exports = {
  async headers() {
    return [
      {
        source: "/:path*{/}?",
        headers: [
          {
            key: "X-Accel-Buffering",
            value: "no",
          },
        ],
      },
    ];
  },
};

We’ll need to also run

npm i use-debounce

To implement the feature where users with the “user” role see invoices and related data corresponding to them, we need to make some modifications to the database schema and the code. Specifically, we’ll need to add a user_id column to the invoices table to link invoices to specific users. Then we’ll adjust the code to filter invoices based on the logged-in user’s id.

Step 1: Modify the Database Schema

We need to add a user_id column to the invoices table and create the necessary foreign key constraint.

Here are the PostgreSQL ALTER TABLE queries to make these changes:

-- Add user_id column to customers table
ALTER TABLE customers
ADD COLUMN user_id uuid;

-- Create foreign key constraint
ALTER TABLE customers
ADD CONSTRAINT customers_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

Step 1: Prepare Your Local Development Environment

Visual Studio Code Setup

Step 2: Set Up SSH Access to EC2 Instance

Step 3: Automate Deployment Using GitHub Actions

Step 4: Test Your Setup

Steps to Create and Use `deployuser`:

1. Create the `deployuser` on your EC2 instance:

2. Setup SSH for `deployuser`:

3. Add Your Public SSH Key:

4. Verify SSH Access for `deployuser`:

Option 1: Overwrite the Existing Key

Option 2: Save the New Key Under a Different Name

Next Steps After Generating the Key

If you created a new key:

If you chose to overwrite:

Next Steps:

Install Development Tools on Amazon Linux 2023

Run the app

Step 1: Install PM2

Step2: Start Your Application with PM2

Step 3: Configure PM2 to Auto-start at Boot

Step 4: Save the PM2 List

Step 5: Verify PM2 Operation

Conclusion

Step 1: Modify the Database Schema

About The Author

Tony Leonard

Recent Posts

Recent Comments

Setting Up Your Database on AWS

Step 1: Prepare Your Local Development Environment

Visual Studio Code Setup

Step 2: Set Up SSH Access to EC2 Instance

Step 3: Automate Deployment Using GitHub Actions

Step 4: Test Your Setup

Steps to Create and Use deployuser:

1. Create the deployuser on your EC2 instance:

2. Setup SSH for deployuser:

3. Add Your Public SSH Key:

4. Verify SSH Access for deployuser:

Option 1: Overwrite the Existing Key

Option 2: Save the New Key Under a Different Name

Next Steps After Generating the Key

If you created a new key:

If you chose to overwrite:

Next Steps:

Install Development Tools on Amazon Linux 2023

Run the app

Step 1: Install PM2

Step2: Start Your Application with PM2

Step 3: Configure PM2 to Auto-start at Boot

Step 4: Save the PM2 List

Step 5: Verify PM2 Operation

Conclusion

Step 1: Modify the Database Schema

About The Author

Tony Leonard

Related Posts

Documentation Tools

Software Development with AWS

Interaction Design Basics

Automated Testing

Recent Posts

Recent Comments

Steps to Create and Use `deployuser`:

1. Create the `deployuser` on your EC2 instance:

2. Setup SSH for `deployuser`:

4. Verify SSH Access for `deployuser`: