Question:

JavaScript Database Error Solved: Syntax error at or near ";"

Problem

I am working on a project In which a user database is created when he successfully signs Up. On the frontend form user inputs the size of the database. Now on the backend, I have a createDatabase function which creates a database for the user and sets the size of the database as per the size given by the user. Here is the code of my function;

exports.createAdminDatabase = async (user , spaceneeded) => {

try {

const { id, name } = user;

const uniqueDbName = `${name}_${id}`;

const megabytes = spaceneeded;

const bytes = megabytes * 1024 * 1024; // Convert MB to bytes

// Creating the new database

await db.none(`CREATE DATABASE $1:name;`, [uniqueDbName]);

// Alter the new database to set the size

await db.none(`ALTER DATABASE $1:name SIZE $2:raw;`, [uniqueDbName, `${bytes}B`]);

// Switching to the newly created database

const newDb = pgp({ ...connection, database: uniqueDbName });

// Creating the 'cars' table within the new database

await newDb.none(`

CREATE TABLE cars (

carName TEXT,

carModel TEXT,

carCc INT

);

`);

// Updating the db_created value for the admin to true

user.db_created = true;

await user.save();

return uniqueDbName;

}

catch (error) {

// Handle errors, e.g., database or table creation failure

console.error('Database creation failed:', error);

throw new Error('Database creation failed');

} finally {

// Release the database connection

pgp.end();

}

};

Now I am getting this error on backend terminal when I submit the form;

Database creation failed: error: syntax error at or near ";"

at Parser.parseErrorMessage (E:\Job Tasks\LoginSignupApp\Backend\node_modules\pg-protocol\dist\parser.js:287:98)

at Parser.handlePacket (E:\Job Tasks\LoginSignupApp\Backend\node_modules\pg-protocol\dist\parser.js:126:29)

at Parser.parse (E:\Job Tasks\LoginSignupApp\Backend\node_modules\pg-protocol\dist\parser.js:39:38)

at Socket.<anonymous> (E:\Job Tasks\LoginSignupApp\Backend\node_modules\pg-protocol\dist\index.js:11:42)

at Socket.emit (node:events:514:28)

at addChunk (node:internal/streams/readable:324:12)

at readableAddChunk (node:internal/streams/readable:297:9)

at Readable.push (node:internal/streams/readable:234:10)

at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {

length: 90,

severity: 'ERROR',

code: '42601',

detail: undefined,

hint: undefined,

position: '47',

internalPosition: undefined,

internalQuery: undefined,

where: undefined,

schema: undefined,

table: undefined,

column: undefined,

dataType: undefined,

constraint: undefined,

file: 'scan.l',

line: '1180',

routine: 'scanner_yyerror',

query: 'ALTER DATABASE "bytesSpace_75" SIZE 104857600B;',

params: undefined

}

Error: Database creation failed

Solution

There's no size clause in >alter database and 104857600B is not a valid literal of any kind, in any version. Different versions parse this differently, which is why they complain at different stages:

>15+ at the literal:

ERROR: trailing junk after numeric literal at or near "104857600B"

LINE 1: alter database "template1" size 104857600B;

>9.5-14 at the semicolon - in these versions >104857600B works as 104857600::int AS "b" which confuses the parser in a different way:

ERROR: syntax error at or near ";"

LINE 1: alter database "template1" size 104857600B;

Even if you fix the literal, this still makes no sense to PostgreSQL:

alter database "template1" size 104857600;

ERROR: option "size" not recognized

LINE 1: alter database "template1" size 104857600;

>9.4 complains about it regardless of whether the literal is valid, even though it would potentially accept it as 104857600::int AS "b" elsewhere:

alter database "template1" size 104857600B;