How to Create a CRUD API Using Node, PostgresSQL, and Express
A CRUD API manages data through four basic database operations: create, read, update, and delete. You can create a simple CRUD API with just Express and a PostgreSQL database.
Start by creating an Express server to which you’ll connect PostgreSQL. Then, create the CRUD functions and hook them up to API endpoints. Once you’ve done so, you’ll be able to connect Node to PostgreSQL and perform database queries on each route.
Prerequisites for Building the API
To follow along with this tutorial, you should:
Create an Express Server
Tocreate an Express server, begin by creating a new directory and entering it:
Then initialize npm:
This command will generate apackage.jsonfile in the notes folder. Finally, install Express.
Create a new file calledindex.jsand add the following code.
This will create a new server listening at port 3000.
Create a PostgreSQL Database
Execute the following command in the psql command prompt to create a PostgreSQL database called notedb.
Run this command tolist all the Postgres databasesand check that you created the notedb database:
Connect to the Database
First things first,connect your Node application to the PostgreSQL server. You can use the node-Postgres module.
Run the following to install it via npm:
As good practice, connect to the database in a separate file.
Create a new file calleddb.jsand add the following.
Here, you are exporting the connection string which you’ll use to communicate to the database. Note you are reading the database connection settings from a config file calleddbConfig.js. Therefore, create dbConfig.js and add the following data to it.
Remember to replace the database details with your own local values.
Create a PostgreSQL Table
In the psql command prompt, create a table called notes. To start with, connect to the notedb database using the \c command.
Next, create a table in the connected database using the CREATE TABLE command.
This table is quite simple. It only has an ID which is an autogenerated primary key and a text field called note.
Create a Note
Instead of performing the CRUD operations in the routes, create functions that will interact with the database in a separate file.
Create a helper file calledhelper.jsand import the connection object from db.js.
Use the following code to create the createNote() function.
This function first checks if the request body included a note. If the note is absent, it throws an error.
To create a note, the function uses the INSERT clause. It returns a JSON object containing a null error message and a success message if successful.
Get All Notes
To get all the notes from the table, use the SELECT * clause.
getNotes() sends the notes array in the response object if the query is successful.
Get Note by ID
The API will also have an endpoint that returns a note by ID. In helper.js, add a getNoteById() function.
This function will return a JSON object containing the note and an error object.
Update Note by ID
To update a note, you need a note and the ID of that note. You will get the note from the request body and the ID from the URL.
The updateNoteById() function uses the UPDATE clause to update an existing note with a new note.
This function returns a success message if the table is updated and an error message if it’s not.
Delete Note by ID
To delete a note by ID from the table use the following code.
Now that you have created all the CRUD functions, export them.
In helper.js, add the following.
You will import them inindex.jswhen creating the API endpoints.
Create API Routes
The final step is to create API endpoints inindex.jsfor each of the CRUD operations.
Begin by importing the helper.js file.
Next, create each endpoint.
A REST API to Update Your Database
Once you’re done, you could check that your API works by using a REST client like Postman, or by writing unit tests. You should be able to ensure that the five endpoints all work as you expect.
You can also inspect and manage the data you added to the database using the pgAdmin tool. It’s a GUI application that makes it easy to perform database administration and interact with PostgreSQL servers.
Configuring a database is hard, but managing it is even harder. Here’s how to install pgAdmin on Ubuntu to manage your PostgreSQL database.
Turn these settings on, and your iPhone will be so much better than before.
Obsidian finally feels complete.
Unlock a world of entertainment possibilities with this clever TV hack.
Tor spoiled me forever.
My foolproof plan is to use Windows 10 until 2030, with the latest security updates.
