We’ll see how to interact with a Postgres database from Node.js. To achieve it, we’ll use the node-postgres package (pg on npm). It provices a simple JavaScript interface between Postgres and Node.js.
These same steps can be followed to connect Postgres and Express.js.
Set up and installation
In case you don’t have postgres installed, you can follow this tutorial.
If you don’t have a node project, initialize one by executing the following command from a new folder.
$ npm init -y
Then, install the node-postgres package.
$ npm install pg
Initializing the database in postgres
We’ll create a simple database for this tutorial. The command-line instructions for this section are specific to Ubuntu. In case you have a different OS, you might need to use slightly different commands.
Run psql as postgres user.
$ sudo -u postgres psql
Create a new user with a secure password.
CREATE USER my_user WITH PASSWORD '12356';
Create a new database and set the user above as the owner.
CREATE DATABASE db WITH OWNER='my_user';
Exit psql with the \q command and connect to the database as the newly created user. You will need to create an OS user with the same username if you don’t have one (see here).
$ sudo -u my_user psql db
Create a new table with some dummy data.
CREATE TABLE my_table( id int, name text, age int ); INSERT INTO my_table(id, name, age) VALUES (1, 'foo', 25), (2, 'bar', 30), (3, 'john', 45);
We can verify the values were correctly inserted with:
table my_table;
Configure node-postgres
Now, we will start the JavaScritp manipulation in a file called db.js. The interaction with postgres is achieved via a Pool object from the node-postgres package. In simple terms, a Pool is a collection of Clients, where each Client is a single connection to the postgres database.
While a single Client would suffice for this simple tutorial, for both performance and ease of use, we’ll use Pool instead of Client.
To initialize our Pool, we pass it a configuration object with the following parameters to connect it to the database:
// db.js
const { Pool } = require('pg');
const pool = new Pool({
user: "map",
password: "map",
database: "map",
host: "localhost",
port: 5432,
});
Since we’re connection to the database locally, we set the host to localhost. The port is the one used by postgres, which is 5432 by default. You can determine which port postgres uses by running pg_lsclusters from the command terminal.
Performing queries
At this stage, we’re ready to query our database.
Let’s insert a new row to our table. The corresponding SQL command is:
INSERT INTO my_table VALUES (4, 'Nick', 50);
We just need to pass that query as a string to the pool.query method.
pool.query("INSERT INTO my_table VALUES (4, 'Nick', 50)");
Since it’s an asynchronous process, we need to wrap it inside an async function.
// db.js
const insert = async () => {
try {
await pool.query("INSERT INTO my_table VALUES (4, 'Nick', 50)");
console.log("success");
} catch (err) {
console.error(err);
}
};
We can test it, by loading our script to node.
$ node db.js > .load db.js > insert() > success
We can verify our table has been updated with psql:
$ sudo -u my_user psql db > table my_table;
Parameterized queries
We can create parameterized queries by passing as a second argument an array with the query parameters.
const insertParam = async (id, name, age) => {
try {
await pool.query("INSERT INTO my_table VALUES ($1, $2, $3)", [
id,
name,
age,
]);
console.log("success");
} catch (err) {
console.error(err);
}
};
The $x portions of the string will be substituted with the corresponding items of the parameters array.
Once again, we test it as follows.
$ node > .load db.js > insertParam(5, "Jamie", 21) > success
We can pass the parameters directly using JavaScript data types. node-postgres will cast them to the corresponding postgres types. Additionally, node-postgres will automatically sanitize the parameters to avoid SQL injection.
If you are passing parameters to your queries you will want to avoid string concatenating parameters into the query text directly. This can (and often does) lead to sql injection vulnerabilities. node-postgres supports parameterized queries, passing your query text unaltered as well as your parameters to the PostgreSQL server where the parameters are safely substituted into the query with battle-tested parameter substitution code within the server itself.
node-postgres docs
SQL Injection test
As a fun experiment, let’s try sql injection with the function we’ve created.
First, let’s create insertUnsafe to compare the results.
// DO NOT USE
const insertUnsafe = async (id, name, age) => {
try {
await pool.query(`INSERT INTO my_table VALUES (${id}, ${name}, ${age})`);
console.log("success");
} catch (err) {
console.error(err);
}
};
It’s very similar to insertParam, except that it inserts the arguments directly into the query string.
Let’s reload db.js and execute the following:
> insertParam(5, "'Jamie', 1); TRUNCATE my_table; SELECT repeat('a'", 1)
> success
We can verify that the weird text passed as the second argument will be safely introduced to my_table.
Now, let’s try the same with insertUnsafe (CAUTION: my_table data will be wiped out).
> insertUnsafe(5, "'Jamie', 1); TRUNCATE my_table; SELECT repeat('a'", 1)
success
And let’s check the contents of my_table now…
Fetching data from the database
As a final application, let’s see how to recover data from the database.
We can do so using pool.query as before.
const getRow = async (id) => {
try {
const res = await pool.query("SELECT * FROM my_table WHERE id=$1", [id]);
console.log(res.rows[0]);
} catch (err) {
console.error(err);
}
};
This time, we save the results of the query in a variable res. The results of the query will be in the res.rows property that stores an array with the results. To each row of the result corresponds and object with a property for each column.
$ node
> .load db.js
> getRow(1)
> { id: 1, name: 'foo', age: 25 }
Finally, to gracefully terminate the connection with the database, we call pool.end().
Source code used for this tutorial can be found here.