API with Node.js, Express and Prisma

Get the Books Table of content

Presentation layer

Database

One of the fundamental aspects of applications is data persistence. If, for some reason, the application shuts down, data should not be lost. It should provide the possibility to back up data and offer mechanisms for its recovery. Everything described above is covered by a database management system.

There are many database options, ranging from open-source, free use, and paid, as well as SQL and NoSQL, among others. Each comes with its specific characteristics. The selection of one or more will always depend on the type of application being designed.

PostgreSQL

Here you will use the PostgreSQL database manager, which is open-source, free to use, and quite popular. As its name suggests, it is an SQL-type database; hence, resources will be associated with tables and each entity to a record.

On the official PostgreSQL website, you can find installers for various operating systems.

PostgreSQL can be managed from the Terminal or the pgAdmin graphical interface, where you can create users, databases, tables, and information.

Usually, once the database is installed, the default username and password are both postgres, and the default database name is also postgres, along with port 5432 where the process runs.

Some cloud services offer PostgreSQL as a service, which will be needed when publishing the API on a server or service.

Prisma

Prisma is an ORM (Object Relational Mapping) that simplifies interaction with the database from the application via an intermediary layer. It offers a set of methods for performing the most common operations.

Another important feature is ensuring the independence of the selected database type. if in the future you would want to migrate to another database, it would only involve changing the database provider type and, if necessary, making specific adjustments to the models, but not to the source code of your application.

It’s recommended to install the Prisma extension for Visual Studio Code so every time you save a .prisma file, it will adjust the format and, additionally, suggest and autocomplete text when working with files of this type:

More information:

Connect Prisma and the Database

The first step is to install Prisma as a development dependency and the Prisma client as a project dependency:

npm install prisma --save-dev
npm install @prisma/client

Then, initialize it with the selected provider, in this case, PostgreSQL:

npx prisma init --datasource-provider postgresql

You’ll need a URL to indicate which database to connect to. Add the variable with its respective values in the .env file:

PORT=3000
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/checklistdb

It’s also important to place the same variables in the .env.example file with sample values.

As you can see, the connection protocol will be postgresql; the username and password will be postgres; the address of the local machine will be localhost; the port will be 5432; and the name of the database will be checklistdb.

Create a module that will be responsible for managing operations related to connecting and disconnecting from the database within its respective directory:

touch app/database.js

Use the following content:

// app/database.js

import { PrismaClient } from '@prisma/client';

export const prisma = new PrismaClient();

export const connect = async function () {
  await prisma.$connect();
  console.log('Database connected');
};

export const disconnect = async function () {
  await prisma.$disconnect();
  console.log('Database disconnected');
};

In the previous code snippet:

  1. The library @prisma/client is imported.
  2. A new instance of Prisma is created and exported to be used throughout the application.
  3. Functions named connect and disconnect are exported, which will handle the database connection and disconnection, respectively.

There’s no need to specify the database connection details in the connection function, since Prisma adopts best practices and automatically fetches this information from the DATABASE_URL environment variable.

Now, in the main file, add the module, set up the configuration, and connect to the database:

// index.js

import http from 'http';

import { configuration } from './app/config.js';
import { app } from './app/index.js';
import { connect } from './app/database.js';

const { port } = configuration.server;

// Connect to the database
connect();

// Create Web Server
const server = http.createServer(app);

server.listen(port, () => {
  console.log(`Server running at port: ${port}`);
});

Since changes were made to the configuration files, the server needs to be restarted to apply these changes.

Now, the main file retains its primary responsibility, which is to initiate all services related to the database connection and the web server.

More information:

Presentation layer