The PostgreSQL Schema in NodeJs
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 | let dropSql = `DROP SCHEMA "${schemaName}" CASCADE;`; pool.query(dropSql, (err, res) => { // Log the SQL statement to console console.log("\ndropSql:", dropSql); if (err) { console.log("DROP SCHEMA ERROR:", schemaCodes[err.code]); console.log("ERROR code:", err.code); console.log("ERROR detail:", err.detail); } if (res) { console.log("DROP SCHEMA RESULT:", res.command); } }); |
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:
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:
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 | const Pool = require("pg").Pool; // Declare a constant for the schema name const schemaName = "myschema"; // Declare a constant for the Postgres ROLE const postgresRole = "objectrocket"; // Declare global array for the Postgres schema names var pgSchemas = []; // Declare a new client instance from Pool() const pool = new Pool({ user: postgresRole, host: "localhost", database: "some_database", password: "1234", port: "5432" }); 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" }; async function schemaFuncs() { // 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); } 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 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) => { if (createErr) { console.log( "CREATE SCHEMA ERROR:", createErr.code, "--", schemaCodes[createErr.code] ); console.log("ERROR code:", createErr.code); console.log("ERROR detail:", createErr.detail); } 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(); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started