Node.js / PostgreSQL - list sizes of databases

In this article, we would like to show you how to list the sizes of PostgreSQL databases using Node.js.

Quick solution:

const { Client } = require('pg');

const client = new Client({
    host: '127.0.0.1',
    user: 'postgres',
    password: 'password',
    port: 5432,
});

const printDatabasesSizes = async () => {
    const query = `
        SELECT 
            "d"."datname" AS "database_name",
            pg_size_pretty(pg_database_size("d"."datname")) AS "database_size"
        FROM "pg_database" AS "d"
    `;
    try {
        await client.connect();                      // creates connection
        const { rows } = await client.query(query);  // sends query
        console.table(rows);
    } catch (error) {
        console.error(error.stack);
        return false;
    } finally {
        await client.end();                          // closes connection
    }
};

printDatabasesSizes();

Example result: 

┌─────────┬─────────────────┬───────────────┐
│ (index) │  database_name  │ database_size │
├─────────┼─────────────────┼───────────────┤
│    0    │   'postgres'    │   '7965 kB'   │
│    1    │    'dirask'     │   '8245 kB'   │
│    2    │   'template1'   │   '7753 kB'   │
│    3    │   'template0'   │   '7753 kB'   │
│    4    │ 'postgres_test' │   '7753 kB'   │
│    5    │  'my_database'  │   '7753 kB'   │
│    6    │  'm_database'   │   '7753 kB'   │
│    7    │  'database3'    │   '7753 kB'   │
│    8    │ 'my_datadbase'  │   '7753 kB'   │
└─────────┴─────────────────┴───────────────┘

Native SQL query (used in the above example):

SELECT 
    "d"."datname" AS "database_name",  
    pg_size_pretty(pg_database_size("d".datname)) AS "database_size"
FROM "pg_database" AS "d";