EN
Node.js - PostgreSQL BETWEEN clause
0 points
In this article, we would like to show you how to use SQL BETWEEN 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 (fromID, toID) => {
12
const query = `SELECT * FROM "users"
13
WHERE "id" BETWEEN $1 AND $2`;
14
try {
15
await client.connect(); // gets connection
16
const { rows } = await client.query(query, [fromID, toID]); // sends queries
17
console.log(rows);
18
} catch (error) {
19
console.error(error.stack);
20
} finally {
21
await client.end(); // closes connection
22
}
23
};
24
25
fetchUsers(2, 4); // id >= 2 AND id <= 4
Result:
xxxxxxxxxx
1
[
2
{ id: 2, name: 'Chris', role: 'moderator' },
3
{ id: 3, name: 'Kate', role: 'user' },
4
{ id: 4, name: 'Denis', role: 'moderator' }
5
]
Note:
BETWEEN clause is used very often for dates.
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');