Building Next JS Applications with SQLite Database

Next.js is a popular React-based framework for building server-side rendered (SSR) and statically generated websites and applications. In this article, we'll explore how to build Next.js applications with SQLite database.

SQLite is a lightweight, self-contained database that doesn't require a separate server process, making it an ideal choice for Next.js applications.

To get started, we need to install the sqlite3 package using npm or yarn. This package provides a JavaScript interface to SQLite databases.

To set up Next.js with SQLite, you'll need to create an API folder structure. This involves creating an API folder and understanding how Next.js AppRouter works for API.

You can create an SQLite DB instance for your portfolio project by using the SQLite package. In your project root directory, run the command `npm install sqlite3` to install the required package.

To use the DB instance for CRUD operations, you'll need to create an SQLite DB instance and use it to interact with your tables. This involves using the `sqlite3` package to connect to the database and perform CRUD operations.

Here are the basic steps to set up Next.js with SQLite:

These steps will give you a solid foundation for using SQLite with Next.js.

Let's talk about creating a database schema for our Next.JS app using SQLite.

We'll create a table called "articles" to hold article-related information, including id, name, description, imageUrl, articleUrl, and slug.

To create the "articles" table, we can use a migration script in a separate file called migrations.ts.

This script will create the table automatically on our first API execution.

We can run this migration script in three ways:

1. By creating a button that triggers the migration action from the UI,

2. By manually invoking the migrate() function from the database.ts file,

3. By installing a package called ts-node and running the migrations with a package.json definition.

Here's a summary of the table structure we'll be creating:

To create a database instance for your Next.JS project, you'll need to create a file called `database.ts` in your `api` directory with the necessary code. This file will establish a connection to your SQLite database.

You'll want to use the `process.cwd()` method to get the root path of your project, as `__dirname` may not work in some Next.JS deployments.

When making API calls, keep in mind that SQLite is asynchronous, so you may need to wrap transactions in a Promise manually to avoid misinformation.

Here are the basic CRUD operations you'll need to perform on your database:

Remember to use the correct method for each operation, such as `req.json()` for getting the body of a request, and to send a Response in every branch of your API handler.

To connect to the database, you'll need to create a database file in your API directory. Create a file called database.ts with the following code:

```typescript

const dbPath = process.cwd();

```

This code uses the process.cwd() method to get the root path of your project, which is important for Next.JS deployments.

The dbPath variable is used to create a database instance, allowing you to access it when necessary. This is a crucial step in interacting with your database.

Here are the steps to create a database instance:

By following these steps, you'll be able to connect to your database and start interacting with it.

Making API calls can be a bit tricky, especially when working with asynchronous databases like SQLite.

To avoid writing boilerplate code multiple times, it's a good idea to wrap database transactions in a Promise manually.

This will help prevent misinformation from being passed back in the API before the database transaction actually closes.

You'll need to create and export API transaction functions, like GET and POST, to make your life easier.

For example, Next.JS requires you to name your index file as route.ts, and use a handler like so to perform a GET operation on a route.

To POST an article, you'll need to use req.json() to get the body, rather than req.body.

And, you'll need to send a Response in every branch, even if you're using Promise.then() and Promise.catch().

To avoid repetition, it's a good idea to create local status and respBody variables and return them after promise execution.

You can write a common Response.json() method to make things even easier, but it's up to you whether to customize it per request or not.

Prisma is the perfect companion for Next.js apps that need to work with a database.

Prisma supports pre-rendering pages at build time (SSG) or request time (SSR), making it a great fit for Next.js.

You can access your database with Prisma at build time using getStaticProps, at request time using getServerSideProps, or by separating the backend into a standalone server.

Prisma Accelerate can speed up database queries in Serverless or Edge environments, ensuring scalable connection pools and caching results at the Edge.

To integrate Prisma with Next.js, start by creating a TypeScript project and setting up Prisma ORM.

Create a project directory and initialize a TypeScript project using npm, then install the Prisma CLI as a development dependency.

Next, set up Prisma ORM with the init command of the Prisma CLI, configuring SQLite as your database and creating a schema.prisma file.

Run a migration to create your database tables with Prisma Migrate, executing the SQL migration file against the database and generating a tailored Prisma Client API.

You can also explore ready-to-run Prisma ORM examples in the prisma-examples repository on GitHub, which includes examples with Express, NestJS, GraphQL, and fullstack examples with Next.js and Vue.js.