EN
Node.js - PostgreSQL ORDER BY
3 points
In this article, we would like to show you how to use SQL ORDER BY in Node.js with Postgress.
Note: at the end of this article you can find database preparation SQL queries.
In this section you can find example that orders results using A-Z
direction.
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 (orderBy) => {
12
const query = `SELECT *
13
FROM "users"
14
ORDER BY $1`; // it abbreviated form to: ORDER BY $1 ASC
15
try {
16
await client.connect(); // creates connection
17
const { rows } = await client.query(query, [orderBy]); // 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('name');
Result:
xxxxxxxxxx
1
[
2
{ id: 2, name: 'Chris', role: 'moderator' },
3
{ id: 4, name: 'Denis', role: 'moderator' },
4
{ id: 1, name: 'John', role: 'admin' },
5
{ id: 3, name: 'Kate', role: 'user' }
6
]
In this section you can find example that orders results using Z-A
direction.
xxxxxxxxxx
1
const { Client } = require('pg');
2
3
const client = new Client({
4
host: '127.0.0.1',
5
user: 'postgres',
6
database: 'my_database',
7
password: 'password',
8
port: 5432,
9
});
10
11
const fetchUsers = async (orderBy) => {
12
const query = `SELECT *
13
FROM "users"
14
ORDER BY $1 DESC`;
15
try {
16
await client.connect(); // gets connection
17
const { rows } = await client.query(query, [orderBy]); // 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('id');
Result:
xxxxxxxxxx
1
[
2
{ id: 4, name: 'Denis', role: 'moderator' },
3
{ id: 3, name: 'Kate', role: 'user' },
4
{ id: 2, name: 'Chris', role: 'moderator' },
5
{ id: 1, name: 'John', role: 'admin' }
6
]
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');