The PostgreSQL Schema in NodeJs

Written by Data Pilot

November 18, 2019

NodeJS
PostgreSQL

Introduction the PostgreSQL schema in NodeJs

An SQL schema works like a blueprint, mapping the structure of a database table. SQL schemas help keep data organized, structured and predictable and are useful for maintaining data integrity. Various permissions can also be applied to SQL schemas. This tutorial will explain how to use Node’s pg client library for PostgreSQL schema node management.

Postgres schema errors while executing SQL commands

Following is a short list of some of the schema error codes that can occur:

25007: schema_and_data_statement_mixing_not_supported 3F000: invalid_schema_name 42P06: duplicate_schema 42P15: invalid_schema_definition

Syntax error codes

A syntax error, as shown below, can occur if SQL statements aren’t written properly:

42000: syntax_error_or_access_rule_violation 42601: syntax_error

Prerequisites to accessing a PostgreSQL schema in NodeJs

To access PostgreSQL schema node, Postgres and a PostgreSQL role with access to a database must be installed. Execute the psql -V command to return the currently installed version of PostgreSQL in the PSQL command-line interface. Execute the following bash command to enter into PSQL while connecting to a database name:

1	psql some_database

Node must be installed on the machine or server that has access to Postgres. The Node Package Manager, or NPM, must also be installed on the server executing the JavaScript.

Install the pg module for the NodeJs PostgreSQL client

If not currently installed, execute the following command to install the ‘pg’ client for the Node project directory:

1	npm install pg

Run the npm init command to initialize Node in the directory. Complete all of the cues so Node will be able to create the package.json file for the project.

Create a Node script for the PostgreSQL schema RESTful API calls

Create a new JavaScript file (.js) for Node to execute inside the project directory. Edit the file using an IDE, like Sublime or VS Code, that has JavaScript syntax support.

Setup the constants for the PostgreSQL credentials

Declare some constants, as shown below, at the beginning of the script for the Postgres values:

1
2
3
4
5
6
7
8
9
10

const Pool = require("pg").Pool;

// Declare a constant for the schema name
const schemaName = "myschema";

// Declare global array for the Postgres schema names
var pgSchemas = [];

// Declare a constant for the Postgres ROLE
const postgresRole = "objectrocket";

NOTE: Make certain to change the values to match the PostgreSQL database and user roles.

Create a Pool instance of the pg client library for Postgres

As shown in the following code, declare an instance of the Pool() method library. Make certain to pass the postgresRole string to the user parameter:

1
2
3
4
5
6
7

const pool = new Pool({
user: postgresRole,
host: "localhost",
database: "some_database",
password: "1234",
port: "5432"
});

NOTE: Again, be sure to change the values to match the PostgreSQL database and user roles.

Create a JavaScript array for the error codes

The following array will store the error codes mentioned above, along with their respective names, so the codes can be logged to the console while getting an API response from Postgres:

1
2
3
4
5
6
7
8

const schemaCodes = {
"25007": "schema_and_data_statement_mixing_not_supported",
"3F000": "invalid_schema_name",
"42P06": "duplicate_schema",
"42P15": "invalid_schema_definition",
"42000": "syntax_error_or_access_rule_violation",
"42601": "syntax_error"
};

Declare an asynchronous function for the Postgres schema API calls

The next section will make the API calls to access and create Postgres schemas within an asynchronous JavaScript function. The following code declares the function using async:

1	async function schemaFuncs() {

Execute a SQL command to select a schema_name

The first SQL statement executed will use the SQL SELECT keyword to obtain all of the Postgres database’s schema names:

1
2
3
4
5
6
7
8
9
10
11
12

// Declare a string for the Pool's query
let selectSchemasSql = 'SELECT schema_name FROM information_schema.schemata;'
await pool.query(selectSchemasSql, (err, res) => {

// Log the SQL statement to console
console.log('\nselectSchemasSql:', selectSchemasSql)

// Check for Postgres exceptions
if (err) {
console.log("SELECT schema_name:", schemaCodes[err.code])
console.log("ERROR code:", err.code)
}

The above code will log any errors, however, the following code can iterate over the “rows” of schema names and put the names into the pgSchemas array that was declared earlier:

1
2
3
4
5
6
7
8
9
10
11
12
13

else if (res.rows !== undefined) {

// Iterate over the rows of Postgres schema names
res.rows.forEach(row => {
// Push the schema's name to the array
pgSchemas.push( row.schema_name )
})

// Log the number of Postgres schema names to console
console.log("schema names:", pgSchemas)
console.log("SELECT schema_name total schemas:", res.rowCount)
}
})

Create a PostreSQL schema in NodeJs

To complete the asynchronous function, use the Node app to create a new schema with CREATE SCHEMA as the SQL keyword. The following code does this granting authorization only to the specified Postgres role declared previously:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// Create the SCHEMA with user auth if it doesn't exist
let createSql = `CREATE SCHEMA IF NOT EXISTS
${schemaName} AUTHORIZATION ${postgresRole};`

// Log the SQL statement to console
console.log('\ncreateSql:', createSql)
await pool.query(createSql, (createErr, createRes) => {

// check for errors
if (createErr) {
console.log("CREATE SCHEMA ERROR:", createErr.code, "--", schemaCodes[createErr.code])
console.log("ERROR code:", createErr.code)
console.log("ERROR detail:", createErr.detail)
}

Create a table for the PostgreSQL schema

If the result of the CREATE SCHEMA API call returns favorable results, execute another SQL statement, as shown below, to create a table for it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

if (createRes) {
console.log("\nCREATE SCHEMA RESULT:", createRes.command)

let createTableSql = `CREATE TABLE ${schemaName}.test_table(
id INT primary key,
str_col TEXT,
int_col INT
);`

console.log("\ncreateTableSql:", createTableSql)

pool.query(createTableSql, (tableErr, tableRes) => {
if (tableErr) {
console.log("CREATE TABLE ERROR:", tableErr.code, "--", schemaCodes[tableErr.code])
console.log("createTableSql:", tableErr)
}

if (tableRes) {
console.log("\nCREATE TABLE RESULT:", tableRes)
}
})
}
})
}

schemaFuncs()

Note that the schemaFuncs() function call in the above code will call the function at the end of the script.

Drop the PostgreSQL schema in NodeJs

The following SQL statement will DROP, or delete, the table and schema in the PSQL command-line interface:

1	DROP SCHEMA myschema CASCADE;

NOTE: The CASCADE keyword allows for dropping the schema and any of its “child” tables.

The following JavaScript code shows how to execute this in the NodeJs application:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

NOTE: Postgres may return a 3F000 (invalid_schema_name) error if the schema name is not enclosed in double quotation marks.

Execute the following Node script using the node command followed by the JavaScript file name:

1	node my-schema.js

The results should resemble the following screenshot:

Screenshot of a Node script creating a schema and table for PostgreSQL

Going into PSQL, execute the following command to verify the schema and its table were successfully created:

1	SELECT * FROM "myschema".test_table;

The results should resemble the following:

Screenshot of psql returning the PostgreSQL schema table created in Node

Conclusion to the PostgreSQL schema in Node

This tutorial explained how to use Node’s pg client library for PostgreSQL schema node management. The article explained possible Postgres schema errors and syntax error codes. The tutorial also covered how to install the pg module for the NodeJs PostgreSQL client, how to create a Node script for the PostgreSQL schema RESTful API calls, how to setup the constants for the PostgreSQL credentials, create a Pool instance of the pg client library and how to create a JavaScript array for the error codes. Finally, how to execute a SQL command to select a schema name, how to create a PostreSQL schema in NodeJs, how to create a table for the PostgreSQL schema and how to drop the PostgreSQL schema in NodeJs were explained. Remember that Postgres may return a 3F000 error if the schema name is not placed inside double quotation marks.

Just the Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started