mZubWxTB

1const pg = require('pg');
2const dotenv = require('dotenv').config()
3const connectionString = process.env.DATABASE_URL || 'admintechfugees://admintechfugees:CeP$787@localhost:5432/dbbasefugees';
4 
5const client = new pg.Client(connectionString);
6client.connect();
7 
8const queryUser = client.query(
9  'CREATE TABLE IF NOT EXISTS Users('
10  + 'user_id SERIAL,'
11  + 'username VARCHAR(40) NOT NULL UNIQUE,'
12  + 'name VARCHAR(40) NOT NULL,'
13  + 'firstname VARCHAR(40) NOT NULL,'
14  + 'email VARCHAR(60) UNIQUE,'
15  + 'location Varchar(60) NOT NULL,'
16  + 'CONSTRAINT pk_user PRIMARY KEY (user_id))');
17 
18const queryProject = client.query(
19  'CREATE TABLE IF NOT EXISTS Projects('
20  + 'project_id SERIAL,'
21  + 'name VARCHAR(40) NOT NULL,'
22  + 'CONSTRAINT pk_project PRIMARY KEY (project_id))');
23 
24const queryOrganization = client.query(
25  'CREATE TABLE IF NOT EXISTS Organizations('
26  + 'organization_id SERIAL,'
27  + 'name VARCHAR(40) NOT NULL UNIQUE,'
28  + 'website VARCHAR(100),'
29  + 'user_id INTEGER,'
30  + 'project_id INTEGER,'
31  + 'CONSTRAINT pk_organization PRIMARY KEY (organization_id),'
32  + 'CONSTRAINT fk_organization_user FOREIGN KEY (user_id) REFERENCES Users(user_id),'
33  + 'CONSTRAINT fk_organization_project FOREIGN KEY (project_id) REFERENCES Projects(project_id))');
34 
35const queryChallenge = client.query(
36  'CREATE TABLE IF NOT EXISTS Challenges('
37  + 'challenge_id SERIAL,'
38  + 'name VARCHAR(40) NOT NULL,'
39  + 'description VARCHAR(600) NOT NULL,'
40  + 'organization_id INTEGER,'
41  + 'CONSTRAINT pk_challenge PRIMARY KEY (challenge_id),'
42  + 'CONSTRAINT fk_challenge_organization FOREIGN KEY (organization_id) REFERENCES Organizations(organization_id))');
43 
44const queryResponse = client.query(
45  'CREATE TABLE IF NOT EXISTS Responses('
46  + 'response_id SERIAL,'
47  + 'name VARCHAR(40) NOT NULL,'
48  + 'project_id INTEGER,'
49  + 'CONSTRAINT fk_response_project FOREIGN KEY (project_id) REFERENCES Projects(project_id))');
50 
51Promise.all([queryUser, queryProject, queryOrganization, queryChallenge, queryResponse]).then(() => client.end()).catch(err => console.log(err));