How I built my portfolio using Next.JS and SQLite DB — [Part 2]

Building API routes and using SQLite as a database with Next.JS

Hello again fellow devs! Welcome to Part 2 of building your portfolio using Next.JS and SQLite DB! In this part, let us discuss about the API structures and SQLite for a database of our portfolio app.

We will be diving deeper into —

1. First steps of creating an API folder structure and how Next.JS AppRouter works for API.
2. How to create an SQLite DB instance for our portfolio project.
3. How to use this DB instance to C.R.U.D our tables.

So far, we have a very basic installation of out Next.JS app that just serves the standard Next.JS starter page and an admin/login page for dashboard authentication. If you haven’t already read through it, I recommend checking it out here —

How I built my portfolio using Next.JS and SQLite DB — [Part 1]

A part of my thought process for my portfolio has always been that I don’t want to rely on modifying the code base every time I want to make changes in the project, every time I write an article or want to update some static text. That is also one of the few reasons I chose to go the Next.JS route — just so I can have a quick API setup to manage data via a dashboard. In light of that, let us look at SQLite and it’s integration with Next.JS for a simple set of tables for our portfolio.

First off, in Next.JS 14, we have chosen AppRouter over PageRouter which means to say we now have an app directory that we will have to adhere to in order for the AppRouter to work. So, we will start by creating an api folder inside our app folder like in the image below.

API folder to contain our routes

All our API related code (backend code) will reside inside this folder. AppRouter for APIs work pretty much the same as it does for our client code — just the difference being instead of a page.tsx, we will be having a route.ts file for the index files.

1. Installing requirements

Let us first start by installing the required packages for SQLite database in our Next.JS packages. We need two packages to be installed — sqlite and sqlite3. To install them, run the following command in your project root directory —

yarn add sqlite sqlite3

2. Connect to the database

Now that the dependencies are installed, let us first create a database file that will create a db instance and lets us access it when necessary. Create a file called database.ts in your api directory with below code —

// /src/app/api/database.ts

import path from "path";
import sqlite3 from "sqlite3";

const dbPath = path.join(process.cwd(), "profile.db");
export const db = new sqlite3.Database(
 dbPath,
 sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE,
 (err) => {
  if (err) {
   console.error(err.message);
  }
  console.log("Connected to the profile database.");
 }
);

Things to notice here are the dbPath variable which is making use of process.cwd(). This method gets you the root path of your project and is specifically important because there are instances in Next.JS deployments where your much generally used __dirname fails to fetch a proper path.

I will be naming my database as profile.db but you can choose a name that suits your requirement better. This profile.db will be created in your root directory as seen in the image above.

3. Creating tables

Since we are learning this through an article on Medium (which I also wish to showcase on my portfolio), let us, just to test this through, create our first table articles that would hold our article related information —
id, name, description, imageUrl, articleUrl, slug.

You can read / copy-paste below code right after export const db function to create a database and the table automatically on our very first API execution —

// /src/app/api/migrations.ts

import { db } from "./database";

export const migrate = () => {
  db.serialize(() => {
   db.run(
    `
      CREATE TABLE IF NOT EXISTS articles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        imageUrl TEXT NOT NULL,
        articleUrl TEXT NOT NULL,
        slug TEXT UNIQUE NOT NULL
      );
    `,
    (err: Error) => {
     if (err) {
      console.error(err.message);
     }
     console.log("articles table created successfully.");
    }
   );
  });
}

Note that this block of code is in a separate file called migrations.ts. We do not want to run this every time. There are 3 ways to run this.

1. You can have a button trigger action from UI where you can wrap this in a function and give it a migrate handler — /src/app/api/migrate/route.ts file and under the GET handler you can invoke this such that you can just fire a GET request to run the migrations.
2. You can just manually invoke migrate() from the database.ts file where on every instance of db, this migration will be run.
3. You can install a package called ts-node that can run typescript files and have a package.json definition like "migrate": "ts-node src/app/api/migrations.ts to run the migrations executing yarn migrate.

4. Making API calls

With this, we have created our database and our table. Now, I am pretty sure we are not going to have only one table and route and as a result, we will need to write the C.R.U.D operations to the db multiple times. There is a bit of a concern with SQLite where since it is asynchronous in nature, most of the times it will result in misinformation passed back in the API before the db transaction actually closes. To circumvent this, we actually need to wrap the transaction in a Promise manually. This is a boilerplate that we want to avoid writing multiple times, so let us create and export these API transaction functions like so — (I will focus on writing for GET and POST for this article, but you can write it for your PATCH and DELETE as well)

// /src/app/api/database.ts

export const apiGet = async (query: string) => {
 return await new Promise((resolve, reject) => {
  db.all(query, (err: Error, row) => {
   if (err) {
    console.log(err);
    return reject(err);
   }
   return resolve(row);
  });
 });
};

export const apiPost = async (query: string, values: string[]) => {
 return await new Promise((resolve, reject) => {
  db.run(query, values, function (err) {
   if (err) {
    console.log(err);
    reject(err);
   }
   resolve(null);
  });
 });
};

With our db connection and common code setup completed, let us move on to writing our very first GET and POST APIs for our atricles table.

If you look at Next.JS documentation under RouteHandlers, you will notice that we will have to name our index file as route.ts. So for our articles, we will have to create a route.ts file under /api/articles. Also, Next.JS now follows an approach of directly naming our handlers by the method that is intended to be executed. Like for example, if you want to perform a GET operation on the route, you would use a handler like so —

// /src/app/api/articles/route.ts

export async function GET(req: Request, res: Response) {}

Let us first look at the POST API right? Because we need to create an article before we can GET the data 😅. Below is the code to POST an article and save it in our profile.db

// /src/app/api/articles/route.ts

import { apiPost } from "../database";

export async function POST(req: Request, res: Response) {
 const body = await req.json();
 const { name, description, imageUrl, articleUrl, slug } = body;

 const query = `
    INSERT INTO articles(name, description, imageUrl, articleUrl, slug)
    VALUES(?, ?, ?, ?, ?)
  `;
 const values = [name, description, imageUrl, articleUrl, slug];

 let status, respBody;
 await apiPost(query, values)
  .then(() => {
   status = 200;
   respBody = { message: "Successfully created article" };
  })
  .catch((err) => {
   status = 400;
   respBody = err;
  });
 return Response.json(respBody, {
  status,
 });
}

Here are a few Gotchas with this snipp —

If you are used to Node.JS, you would have used req.body. Well, it does not exist! We will be getting the body by doing req.json() OR you can extend your Request and Response to NextApiRequest and NextApiResponse. Read more about it in the Next.JS documentation.

Next.JS RouteHandler requires you to send a Response in every branch. I know what you are thinking — can’t you just send a Response in the Promise.then() and Promise.catch() block? That generally covers the branches — BUT NO. you will still get an error! To circumvent that, it is easier to create a local status, respBody variables and assign it in the then() and catch() block and return it after promise execution. Saves a lot of time and repeatition.

You can write a common Response.json() method that takes a JS Object and the status code — that is an improvement I will leave to you as I personally like to keep it customisable per request.

Now you can try hitting postman POST requests to http://localhost:3000/api/articles with the necessary body object and see that an entry is created in the database!

Let us then implement a GetAll method that would output the stored entries in a JSON format —

// /src/app/api/articles/route.ts

import { apiGet } from "../database";

export async function GET(req: Request, res: Response) {
 const query = `
    SELECT * from articles
  `;

 let status, body;
 try {
  await apiGet(query)
   .then((res) => {
    status = 200;
    body = res;
   })
   .catch((err: Error) => {
    status = 400;
    body = { error: err };
   });
  return Response.json(body, {
   status,
  });
 } catch (error: any) {
  console.error(error.message);
  return Response.json(
   { error: error },
   {
    status: 400,
   }
  );
 }
}

Most part of the implementation is the same as it is for POST. A quick Gotcha here would be

There are instances where you would be looking for a getById kind of an implementation. For this, you would have to implement the logic in the same function by accessing the {params} from the request and writing an if condition to conditionally add a WHERE id=? section to your query string. An implementation of this will be used later in our project. You could also create a route like /api/articles/[slug]/rojute.ts where you can have a complete CRUD for a single article. At this point, since we are just going to redirect to Medium, we won’t dig deeper into it.

Now accessing the link http://localhost:3000/api/articles in your browser itself will display all articles entries in the db that you have created with the above POST calls.

This concludes a very basic SQLite DB creation, GET and POST API implementation using SQLite DB in your Next.JS app. PATCH and DELETE are no different from the above implementation, so I will be skipping them in the article.

I will let you implement this and play around with the documentation and modifications to your heart’s content before we move on to a basic authentication system in our app — which will be the focus of our Part 3 article 😉.

Until then my fellow devs! May the code be with you ✋ peace out and CHEERS!!

Next Part —

How I built my portfolio using Next.JS and SQLite DB — [Part 3]