Setting up Postgres on Vercel and Building Simple Next.js CRUD Application

Photo by Pixabay: https://www.pexels.com/photo/asphalt-balance-blur-close-up-268018/

Since May 2023, Vercel offers a free tier Postgres database from their hosting service. Vercel offers a hassle-free deployment experience for developers. With its intuitive user interface and straightforward configuration, deploying applications is really easy. Whether you’re working with Next.js, React, Vue.js, or any other popular frameworks, Vercel seamlessly integrates with them, providing a smooth development workflow.

In this blog post, we will walk through the process for setting up a free-tier Postgres database on Vercel.com and leverage it to build a simple CRUD (Create, Read, Update, Delete) application using Next.js. So, let’s jump in and unlock the potential of these technologies to create full-stack applications.

To keep it simple, this example will not take advantage of Next.js 13.4.x App directory features and the data fetching will be done on the client side.

Source Files

GitHub

Add a Postgres Database on Vercel

If you haven’t already signed up with Vercel, please register at https://vercel.com/signup. Otherwise, if you already have a Postgres database set up and just want to connect your Next.js application to it, then skip this section.

Create a new Database

Select Postgres

Get The Environment Variables Information

Once the new database is created, click on the database instance listed under the storage search field (below).

Vercel.com has a built in feature to generate the environment variables for you.

Add this to you environment .env.local and .env.production files.

Add the Database to a Project

In order for your Next.js application to have access to the database when deployed, you have to pair the project to the database.

Click on Projects in the left menu.

Select a project you want to connect the database to and that’s it. Your project will be able to connect to it without minimal configuration.

Create Example Blog Post Table

Create a table labeled BlogPosts

CREATE TABLE BlogPosts (
	Id serial PRIMARY KEY,
	Title VARCHAR ( 80 ) UNIQUE NOT NULL,
	Body VARCHAR ( 500 ) NOT NULL,
	Created_on TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Insert Records

Insert 4 records into BlogPosts table.

INSERT INTO BlogPosts (Title, Body) VALUES ('Blog Post 1', 'Some text about the blog post')
INSERT INTO BlogPosts (Title, Body) VALUES ('Blog Post 2', 'Some text about the blog post')
INSERT INTO BlogPosts (Title, Body) VALUES ('Blog Post 3', 'Some text about the blog post')
INSERT INTO BlogPosts (Title, Body) VALUES ('Blog Post 4', 'Some text about the blog post')

A little About Postgres Database

Basic Postgres SQL queries.

QueryDescription
CREATE TABLE posts (likes INT);Create a new table in the database
SELECT * FROM posts;Get all posts in the database
SELECT * FROM posts WHERE likes > ${likes};Get all posts with more than 100 likes
INSERT INTO posts (likes) VALUES (${likes});Insert a new post into the database
UPDATE posts SET likes = 200;Update all posts to have 200 likes
DELETE FROM posts;Delete all posts in the database

Advantages of using Postgres

Multiple Datatype Support
Great Concurrency Control
Supports Non Relational Data like JSON
Advanced SQL Functions
Supports ACID (atomicity, consistency, isolation, durability)
Open Source Free

Next.js Project

The section below will go over how to build a simple Next.js CRUD application.

Libraries Installed

Snippet from the package.json. Included are the Material UI libraries which are optional.

"@emotion/cache": "^11.10.5",
"@emotion/react": "^11.10.6",
"@emotion/server": "^11.10.0",
"@emotion/styled": "^11.10.6",
"@mui/icons-material": "^5.11.11",
"@mui/material": "^5.11.15",
"@next/font": "^13.2.4",
"@vercel/postgres": "^0.1.3",
"axios": "^1.4.0",
"next": "^13.2.4",
"prop-types": "^15.8.1",
"react": "^18.2.0",
"react-dom": "^18.2.0"

Next.js Environment Variables

Copy and paste the environment variables from Get The Environment Variables Information section. This is for ‘./.env.local’ and ‘./.env.production’ files at the root of your project.

SITE_URL=http://localhost:3008
POSTGRES_URL=
POSTGRES_PRISMA_URL=
POSTGRES_URL_NON_POOLING=
POSTGRES_USER=
POSTGRES_HOST=
POSTGRES_PASSWORD=
POSTGRES_DATABASE=

Next.js API CRUD Operations

First create four API handlers under ‘./pages/api/’ and test the http://localhost:3000/api/getallrecords with Postman or another API development and testing tool.

Get All Records

Code for ‘./pages/api/getallblogpost.js’

import { sql } from '@vercel/postgres';

export default async function handler(req, res) {
  console.log('Handler');
  try {
    const { rows } = await sql`SELECT * FROM blogposts;`
   
    res.status(200).json(rows);

  } catch(error) {
    console.error(error);
  } 
}

Add Record

Adds a record and returns the current records with ‘RETURNING *’ at the end of the SQL statement.

Optional – Adding ‘RETURNING *’ at the end of the SQL statement returns the columns from the updated rows. * is for all columns but you can specify columns.
Example: RETURNING title, body;

Code for ‘./pages/api/addblogpost.js’

import { sql } from '@vercel/postgres';

export default async function handler(req, res) {
  const method = req.method;
  const { title, body } = JSON.parse(req.body);
  
  try {
   
    const { rows } = await sql`INSERT INTO blogposts (title, body) VALUES (${title}, ${body});`
        
    res.status(200).json(rows);

  } catch(error) {
    console.error(error);
  }
  
}

Delete Record

Deletes a record and returns the current records with ‘RETURNING *’ at the end of the SQL statement.

Code for ‘./pages/api/deleteblogpost.js’

import { sql } from '@vercel/postgres';

export default async function handler(req, res) {
  const method = req.method;
  const { id } = JSON.parse(req.body);
  console.error('handler', id, method, req.body);
  try {
   
    const { rows } = await sql`DELETE FROM blogposts WHERE ID = ${id};`
        
    res.status(200).json(rows);

  } catch(error) {
    console.error(error);
  }
  
}

Update Row

Updates a row and returns the current records with ‘RETURNING *’ at the end of the SQL statement.

Code for ‘./pages/api/updateblogpost.js’

import { sql } from '@vercel/postgres';

export default async function handler(req, res) {
  const method = req.method;
  const { id, title, body } = JSON.parse(req.body);
  
  try {
   
    const { rows } = await sql`UPDATE blogposts SET title = ${title}, body = ${body} WHERE id = ${id};`        
    res.status(200).json(rows);
    
  } catch(error) {
    console.error(error);
  }
}

Next.js UI – CRUD Components

Table Component using MUI

This component uses a simple MUI table that displays ID, Title , Body, and Posted Date. First column has an action button to edit a record and the last column shows a delete button.

Code for ‘./components/blog/TableBlogPosts.tsx’

import * as React from "react";
import Table from "@mui/material/Table";
import TableBody from "@mui/material/TableBody";
import TableCell from "@mui/material/TableCell";
import TableContainer from "@mui/material/TableContainer";
import TableHead from "@mui/material/TableHead";
import TableRow from "@mui/material/TableRow";
import Paper from "@mui/material/Paper";
import IconButton from "@mui/material/IconButton";
import DeleteIcon from "@mui/icons-material/Delete";
import DialogUpdateBlogPost from "@/components/postgres/DialogUpdateBlogPost";

export default function TableBlogPosts(props) {
  const { blogData, handleRemoveRecord } = props;
  const [blogDataState, setBlogDataState] = React.useState(blogData);
  const [isDataLoaded, setIsDataLoaded] = React.useState(false);
  console.log("TableBlogPosts", props);
  React.useEffect(() => {
    console.log("TableBlogPosts", props);
    setBlogDataState(props.blogData);
    setTimeout(() => {
      setIsDataLoaded(true);
    }, 500);
  }, [props]);

  return (
    <>
      {isDataLoaded && (
        <TableContainer component={Paper}>
          <Table sx={{ minWidth: 650 }} size="small" aria-label="a dense table">
            <TableHead>
              <TableRow>
                <TableCell align="left">ID</TableCell>
                <TableCell align="left">Title</TableCell>
                <TableCell align="left">Body</TableCell>
                <TableCell align="left">Posted On</TableCell>
                <TableCell align="left">Action</TableCell>
              </TableRow>
            </TableHead>
            <TableBody>
              {blogDataState.map((row) => (
                <TableRow
                  key={row.name}
                  sx={{ "&:last-child td, &:last-child th": { border: 0 } }}
                >
                  <TableCell align="center">
                    <DialogUpdateBlogPost
                      data={row}
                      handleUpdateRecord={props.handleUpdateRecord}
                    />
                  </TableCell>
                  <TableCell align="left">{row.id}</TableCell>
                  <TableCell align="left">{row.title}</TableCell>
                  <TableCell align="left">{row.body}</TableCell>
                  <TableCell align="left">{row.created_on}</TableCell>
                  <TableCell align="left">
                    <IconButton onClick={(e) => handleRemoveRecord(row.id)}>
                      <DeleteIcon sx={{ color: "red", fontSize: 22 }} />
                    </IconButton>
                  </TableCell>
                </TableRow>
              ))}
            </TableBody>
          </Table>
        </TableContainer>
      )}
    </>
  );
}

Table Loading Component using MUI Skeleton (Optional)

Using MUI <Skeleton/> to show loading records.

Code for ‘./components/blog/TableBlogLoading.tsx’

import * as React from "react";
import Skeleton from "@mui/material/Skeleton";
import Stack from "@mui/material/Stack";

export default function TableBlogLoading() {
  return (
    <Stack spacing={1}>
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
      <Skeleton variant="rectangular" height={40} />
    </Stack>
  );
}

Edit Form Component using MUI Dialog

This component appears in the first column of the <TableBlogPosts/> as a button. The button opens a form dialog with the record data.

Code for ‘./components/blog/DialogUpdateBlogPost.tsx’

import { useState } from "react";
import Button from "@mui/material/Button";
import TextField from "@mui/material/TextField";
import Dialog from "@mui/material/Dialog";
import DialogActions from "@mui/material/DialogActions";
import DialogContent from "@mui/material/DialogContent";
import DialogContentText from "@mui/material/DialogContentText";
import DialogTitle from "@mui/material/DialogTitle";
import { IconButton, Stack } from "@mui/material";
import EditIcon from "@mui/icons-material/Edit";

export default function DialogUpdateBlogPost(props: any) {  
  const [open, setOpen] = useState<boolean>(false);
  const [data, setData] = useState<any>(props.data);
  const [title, setTitle] = useState(props.data.title);
  const [body, setBody] = useState(props.data.body);

  const handleClickOpen = () => {
    setOpen(true);
  };

  const handleClose = () => {
    setOpen(false);
  };

  return (
    <div>
      <IconButton onClick={handleClickOpen}>
        <EditIcon color="primary" />
      </IconButton>
      <Dialog
        open={open}
        onClose={handleClose}
        fullWidth={true}
        maxWidth={"md"}
      >
        <DialogTitle>
          Edit <i style={{ color: "blue" }}>{data.title}</i>
        </DialogTitle>
        <DialogContent>
          <DialogContentText></DialogContentText>
          <Stack spacing={2} direction="column">
            <TextField
              type="text"
              id="standard-basic"
              label="Title"
              value={title}
              onChange={(e) => setTitle(e.target.value)}
            />
            <TextField
              type="text"
              multiline
              rows={4}
              id="standard-basic"
              label="Body"
              value={body}
              onChange={(e) => setBody(e.target.value)}
            />
          </Stack>
        </DialogContent>
        <DialogActions>
          <Button onClick={handleClose}>Cancel</Button>
          <Button
            onClick={(e) =>
              props.handleUpdateRecord(props.data.id, title, body)
            }
          >
            Save
          </Button>
        </DialogActions>
      </Dialog>
    </div>
  );
}

Next.js UI – Page With CRUD Functions

This page renders the <TableBlogPosts /> and <TableBlogLoading />components and has the functions to handle the CRUD events.

Code for ‘./pages/blog/index.jsx’

import { useEffect, useState } from "react";
import { Button, Grid, Stack, TextField } from "@mui/material";
import axios from "axios";
import TableBlogPosts from "@/components/postgres/TableBlogPosts";
import TableBlogLoading from "@/components/postgres/TableBlogLoading";
import useSWR from 'swr';

const fetcher = (url) => fetch(url).then((res) => res.json());

export default function Index() {
  const [queryData, setQueryData] = useState([]);
  const [isDataLoaded, setIsDataLoaded] = useState(false);
  const [value, setValue] = useState(0);
  const [title, setTitle] = useState("");
  const [body, setBody] = useState("");

  const { data, error } = useSWR('api/getblogposts', fetcher);


  useEffect(() => {
    fetchData();  
  }, []);


  //* F E T C H   D A T A
  const fetchData = async () => {
    setIsDataLoaded(false);
    //const conn = mysql.createConnection({yourHOST/USER/PW/DB});
    const url = "http://localhost:3007/api/getblogposts";
    axios
      .get(url)
      .then(function (response) {
        // handle success
        console.log(response);
      })
      .catch(function (error) {
        // handle error
        console.log(error);
      })
      .finally(function () {
        // always executed
      });

    fetch("http://localhost:3007/api/getblogposts")
      .then((response) => response.json())
      .then((data) => {
        console.log("fetchData", data);
        setQueryData(data);
        setIsDataLoaded(true);
      });
  };


  //* A D D   R E C O R D
  const handleAddRecord = async () => {
    setIsDataLoaded(false);
    fetch("http://localhost:3007/api/addblogpost", {
      method: "POST",
      body: JSON.stringify({ title: title, body, body }),
    }).then((response) => {
      console.log("Then Response", response);
      fetchData();
    });
  };

  //* U P D A T E   R E C O R D
  const handleUpdateRecord = async (id, title, body) => {
    console.log("handleUpdateRecord", id, title, body);
    setIsDataLoaded(false);
    fetch("http://localhost:3007/api/updateblogpost", {
      method: "POST",
      body: JSON.stringify({ id: id, title: title, body: body }),
    }).then((response) => {
      console.log("Then Response", response);
      fetchData();
    });
  };

  //* R E M O V E   R E C O R D
  const handleRemoveRecord = async (id) => {
    console.log("handleRemoveRecord", id);
    setIsDataLoaded(false);
    fetch("http://localhost:3007/api/deleteblogpost", {
      method: "POST",
      body: JSON.stringify({ id: id }),
    }).then((response) => {
      console.log("Then Response", response);
      fetchData();
    });
  };

  return (
    <div>
      <Grid container spacing={2}>
        <Grid item xs={12} sm={6}>
          <Stack spacing={2}>
            <TextField
              type="text"
              id="standard-basic"
              label="Title"
              value={title}
              onChange={(e) => setTitle(e.target.value)}
            />
            <TextField
              type="text"
              multiline
              rows={4}
              id="standard-basic"
              label="Body"
              value={body}
              onChange={(e) => setBody(e.target.value)}
            />
            <Button variant="contained" onClick={() => handleAddRecord()}>
              Add
            </Button>
          </Stack>
        </Grid>
      </Grid>
      {!isDataLoaded && <TableBlogLoading />}
      {isDataLoaded && (
        <TableBlogPosts
          blogData={queryData}
          handleUpdateRecord={handleUpdateRecord}
          handleRemoveRecord={handleRemoveRecord}
        />
      )}
    </div>
  );
}

Conclusion

In conclusion, setting up a free tier Postgres database on Vercel and building a simple CRUD application with Next.js opens up a world of possibilities for developers. By leveraging the features available on Vercel with Next.js and Postgres, we can create dynamic and interactive web applications with a robust database like PostgreSQL. The convenience of Vercel’s free tier offering allows us to easily deploy and host our applications, making the development process extremely simple. Whether you’re a seasoned developer or just starting your journey, this combination of technologies provides a solid foundation for building scalable and efficient applications. So, why wait? Start exploring the endless possibilities of Next.js and Vercel’s free tier Postgres database today and unlock the potential of your web development projects!

Previous Article
Next Article