EN
Node.js - PostgreSQL WHERE clause
0 points
In this article, we would like to show you how to use SQL WHERE clause in Node.js.
Note: at the end of this article you can find database preparation SQL queries.
xxxxxxxxxx
1
const { Client } = require('pg');
2
3
const client = new Client({
4
host: '127.0.0.1',
5
user: 'postgres',
6
database: 'database_name',
7
password: 'password',
8
port: 5432,
9
});
10
11
const fetchUsers = async (userName) => {
12
const query = `SELECT *
13
FROM "users"
14
WHERE "name" = $1`;
15
try {
16
await client.connect(); // gets connection
17
const { rows } = await client.query(query, [userName]); // sends queries
18
console.log(rows);
19
} catch (error) {
20
console.error(error.stack);
21
} finally {
22
await client.end(); // closes connection
23
}
24
};
25
26
fetchUsers('Chris'); // username
Result:
xxxxxxxxxx
1
[{ id: 2, name: 'Chris', role: 'moderator' }]
xxxxxxxxxx
1
const { Client } = require('pg');
2
3
const client = new Client({
4
host: '127.0.0.1',
5
user: 'postgres',
6
database: 'dirask',
7
password: 'password',
8
port: 5432,
9
});
10
11
const fetchUsers = async (userName, userRole) => {
12
const query = `SELECT *
13
FROM "users"
14
WHERE "name" = $1 OR "role" = $2`;
15
try {
16
await client.connect(); // gets connection
17
const { rows } = await client.query(query, [userName, userRole]); // sends queries
18
console.log(rows);
19
} catch (error) {
20
console.error(error.stack);
21
} finally {
22
await client.end(); // closes connection
23
}
24
};
25
26
fetchUsers('Chris', 'admin');
Result:
xxxxxxxxxx
1
[
2
{ id: 1, name: 'John', role: 'admin' },
3
{ id: 2, name: 'Chris', role: 'moderator' }
4
]
create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE "users" (
2
"id" SERIAL,
3
"name" VARCHAR(100) NOT NULL,
4
"role" VARCHAR(15) NOT NULL,
5
PRIMARY KEY ("id")
6
);
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO "users"
2
("name", "role")
3
VALUES
4
('John', 'admin'),
5
('Chris', 'moderator'),
6
('Kate', 'user'),
7
('Denis', 'moderator');