{"id":4143,"date":"2023-11-04T23:14:05","date_gmt":"2023-11-04T23:14:05","guid":{"rendered":"http:\/\/localhost:10003\/how-to-use-postgresql-with-node-js-and-express-js\/"},"modified":"2023-11-05T05:47:59","modified_gmt":"2023-11-05T05:47:59","slug":"how-to-use-postgresql-with-node-js-and-express-js","status":"publish","type":"post","link":"http:\/\/localhost:10003\/how-to-use-postgresql-with-node-js-and-express-js\/","title":{"rendered":"How to Use PostgreSQL with Node.js and Express.js"},"content":{"rendered":"
In this tutorial, we will learn how to use PostgreSQL with Node.js and Express.js to build a web application. PostgreSQL is a powerful, open-source object-relational database system, while Node.js is a JavaScript runtime environment that allows us to run JavaScript on the server-side. Express.js is a fast and minimalist web application framework that runs on top of Node.js.<\/p>\n
By the end of this tutorial, we will have a basic understanding of how to connect to a PostgreSQL database, perform CRUD (Create, Read, Update, Delete) operations, and handle errors in a Node.js and Express.js application.<\/p>\n
Before we begin, make sure you have the following installed:<\/p>\n
Let’s start by setting up a new Node.js and Express.js project.<\/p>\n
mkdir node-postgresql\ncd node-postgresql\n<\/code><\/pre>\n\n- Initialize a new Node.js project using the
npm init<\/code> command.<\/li>\n<\/ol>\nnpm init -y\n<\/code><\/pre>\nThis will generate a package.json<\/code> file in your project directory.<\/p>\n\n- Install the required dependencies for our project, which include
express<\/code>, pg-promise<\/code>, and dotenv<\/code>.<\/li>\n<\/ol>\nnpm install express pg-promise dotenv\n<\/code><\/pre>\nNow that we have set up our project, let’s move on to connecting it to a PostgreSQL database.<\/p>\n
Connecting to the PostgreSQL Database<\/h2>\n
To connect to a PostgreSQL database from our Node.js and Express.js application, we will use the pg-promise<\/code> library. pg-promise<\/code> is a PostgreSQL driver for Node.js that provides a higher-level API for interacting with the database.<\/p>\n\n- Create a new file called
db.js<\/code> in the root of your project.<\/li>\n<\/ol>\ntouch db.js\n<\/code><\/pre>\n\n- Open the
db.js<\/code> file and import the required dependencies.<\/li>\n<\/ol>\n\/\/ db.js\n\nconst pgp = require('pg-promise')();\nrequire('dotenv').config();\n\n\/\/ Database configuration\nconst config = {\n host: process.env.DB_HOST,\n port: process.env.DB_PORT,\n database: process.env.DB_NAME,\n user: process.env.DB_USER,\n password: process.env.DB_PASSWORD\n};\n\n\/\/ Create a new database connection\nconst db = pgp(config);\n\nmodule.exports = db;\n<\/code><\/pre>\nIn the above code, we import the pg-promise<\/code> and dotenv<\/code> packages. pg-promise<\/code> is initialized, and a new database connection is created using the configuration details provided in the .env<\/code> file (which we will create shortly).<\/p>\n\n- Create a new file called
.env<\/code> in the root of your project.<\/li>\n<\/ol>\ntouch .env\n<\/code><\/pre>\n\n- Open the
.env<\/code> file and add the following environment variables.<\/li>\n<\/ol>\nDB_HOST=localhost\nDB_PORT=5432\nDB_NAME=mydatabase\nDB_USER=postgres\nDB_PASSWORD=mypassword\n<\/code><\/pre>\nReplace the placeholder values with your PostgreSQL database configuration details.<\/p>\n
\n- Now, we can import the database connection in our main
app.js<\/code> file.<\/li>\n<\/ol>\n\/\/ app.js\n\nconst express = require('express');\nconst db = require('.\/db');\n\nconst app = express();\n\n\/\/ Express.js app configuration\n\n\/\/ ...\n\n\/\/ Start the server\napp.listen(3000, () => {\n console.log('Server started on port 3000');\n});\n<\/code><\/pre>\nIn the above code, we import the db<\/code> connection object from the db.js<\/code> file and start the Express.js server.<\/p>\nWith the connection set up, we can now move on to performing CRUD operations on our PostgreSQL database.<\/p>\n
Performing CRUD Operations<\/h2>\n
In this section, we will create a simple RESTful API using Express.js to perform CRUD operations on our PostgreSQL database.<\/p>\n
\n- Create a new file called
routes.js<\/code> in the root of your project.<\/li>\n<\/ol>\ntouch routes.js\n<\/code><\/pre>\n\n- Open the
routes.js<\/code> file and add the following code.<\/li>\n<\/ol>\n\/\/ routes.js\n\nconst express = require('express');\nconst router = express.Router();\nconst db = require('.\/db');\n\n\/\/ Create a new record\nrouter.post('\/records', async (req, res, next) => {\n try {\n const { name, age } = req.body;\n const record = await db.none('INSERT INTO records(name, age) VALUES($1, $2)', [name, age]);\n res.json({\n message: 'Record created successfully'\n });\n } catch (error) {\n next(error);\n }\n});\n\n\/\/ Get all records\nrouter.get('\/records', async (req, res, next) => {\n try {\n const records = await db.any('SELECT * FROM records');\n res.json(records);\n } catch (error) {\n next(error);\n }\n});\n\n\/\/ Get a single record by ID\nrouter.get('\/records\/:id', async (req, res, next) => {\n try {\n const { id } = req.params;\n const record = await db.one('SELECT * FROM records WHERE id = $1', id);\n res.json(record);\n } catch (error) {\n next(error);\n }\n});\n\n\/\/ Update a record by ID\nrouter.put('\/records\/:id', async (req, res, next) => {\n try {\n const { id } = req.params;\n const { name, age } = req.body;\n const record = await db.none('UPDATE records SET name = $1, age = $2 WHERE id = $3', [name, age, id]);\n res.json({\n message: 'Record updated successfully'\n });\n } catch (error) {\n next(error);\n }\n});\n\n\/\/ Delete a record by ID\nrouter.delete('\/records\/:id', async (req, res, next) => {\n try {\n const { id } = req.params;\n const record = await db.none('DELETE FROM records WHERE id = $1', id);\n res.json({\n message: 'Record deleted successfully'\n });\n } catch (error) {\n next(error);\n }\n});\n\nmodule.exports = router;\n<\/code><\/pre>\nIn the above code, we define various routes using the Express.js Router<\/code> middleware. Each route corresponds to a specific CRUD operation on our PostgreSQL database.<\/p>\n\n- Open the
app.js<\/code> file and import the routes.js<\/code> file.<\/li>\n<\/ol>\n\/\/ app.js\n\nconst express = require('express');\nconst db = require('.\/db');\nconst routes = require('.\/routes');\n\nconst app = express();\n\n\/\/ Express.js app configuration\n\n\/\/ ...\n\n\/\/ Routes\napp.use('\/', routes);\n\n\/\/ Start the server\napp.listen(3000, () => {\n console.log('Server started on port 3000');\n});\n<\/code><\/pre>\nBy adding app.use('\/', routes);<\/code>, we are telling Express.js to use the routes defined in the routes.js<\/code> file for all paths starting with \/<\/code>.<\/p>\n\n- Start the application.<\/li>\n<\/ol>\n
node app.js\n<\/code><\/pre>\nNow that our application is running, we can test our endpoints using a tool like curl<\/code> or an API testing platform like Postman.<\/p>\n\n- To create a new record, send a POST request to `http:\/\/localhost:3000\/records` with the following JSON payload:<\/li>\n<\/ul>\n
curl -X POST -H \"Content-Type: application\/json\" -d '{\n \"name\": \"John Smith\",\n \"age\": 30\n}' http:\/\/localhost:3000\/records\n<\/code><\/pre>\n\n- To fetch all records, send a GET request to `http:\/\/localhost:3000\/records`:<\/li>\n<\/ul>\n
curl http:\/\/localhost:3000\/records\n<\/code><\/pre>\n\n- To fetch a single record by ID, send a GET request to `http:\/\/localhost:3000\/records\/{id}`:<\/li>\n<\/ul>\n
curl http:\/\/localhost:3000\/records\/1\n<\/code><\/pre>\n\n- To update a record by ID, send a PUT request to `http:\/\/localhost:3000\/records\/{id}` with the updated JSON payload:<\/li>\n<\/ul>\n
curl -X PUT -H \"Content-Type: application\/json\" -d '{\n \"name\": \"John Doe\",\n \"age\": 35\n}' http:\/\/localhost:3000\/records\/1\n<\/code><\/pre>\n\n- To delete a record by ID, send a DELETE request to `http:\/\/localhost:3000\/records\/{id}`:<\/li>\n<\/ul>\n
curl -X DELETE http:\/\/localhost:3000\/records\/1\n<\/code><\/pre>\nCongratulations! You have successfully connected to a PostgreSQL database, performed CRUD operations, and handled errors in a Node.js and Express.js application.<\/p>\n
Conclusion<\/h2>\n
In this tutorial, we learned how to use PostgreSQL with Node.js and Express.js to build a web application. We started by setting up a new Node.js and Express.js project, and we connected it to a PostgreSQL database using the pg-promise<\/code> library.<\/p>\nWe then created a simple RESTful API to perform CRUD operations on our PostgreSQL database, and we tested the endpoints using curl<\/code> or Postman.<\/p>\nFeel free to explore more features of PostgreSQL, Node.js, and Express.js to enhance your web application.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will learn how to use PostgreSQL with Node.js and Express.js to build a web application. PostgreSQL is a powerful, open-source object-relational database system, while Node.js is a JavaScript runtime environment that allows us to run JavaScript on the server-side. Express.js is a fast and minimalist web Continue Reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[1454,1453,1456,455,531,1455,19,1452,1451],"yoast_head":"\nHow to Use PostgreSQL with Node.js and Express.js - Pantherax Blogs<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n