packageTracker/server.js at master · boltboxapp/packageTracker · GitHub

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
103
104
105
106
107
108
109
'use strict';

const pg = require('pg');
const express = require('express');
const cors = require('cors')
const bodyParser = require('body-parser');
const PORT = process.env.PORT || 3000;
const app = express();
// const conString = 'postgres://USERNAME:PASSWORD@HOST:PORT/databaseName';
const conString = 'postgres://localhost:5432/packagemanager';
const client = new pg.Client(conString);
client.connect();
client.on('error', err => console.error(err));

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));
app.use(express.static('./public'));


app.get('/getUsers', (request, response) => {
  client.query(`SELECT * FROM users;`)
  .then(result => response.send(result.rows))
  .catch(console.error);
});

app.get('/getUserByID', (request, response) => {
  client.query(`SELECT * FROM users WHERE user_id=$1;`, [request.body.user_id])
  .then(result => response.send(result.rows))
  .catch(console.error);
});

app.post('/addUser', function(request, response) {
  client.query(
    'INSERT INTO users (user_name) VALUES ($1) ON CONFLICT DO NOTHING RETURNING *;', [request.body.user_name])
  .then(result => response.send(result.rows))
  .catch(console.error);
});

app.get('/getPackagesByUser', (request, response) => {
  client.query(`SELECT * FROM packages WHERE user_id=$1;`, [request.body.user_id])
  .then(result => response.send(result.rows))
  .catch(console.error);
})

app.post('/addPackageByUser', function(request, response) {
  client.query(
    'INSERT INTO packages (tracking_num, user_id, carrier_id) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING RETURNING *;', [request.body.tracking_num, request.body.user_id, request.body.carrier])
  .then(result => response.send(result.rows))
  .catch(console.error);
})

createTables();
// setCarriers();

app.listen(PORT, () => console.log(`Server started on port ${PORT}!`));

function createTables(){

	client.query(`
    CREATE TABLE IF NOT EXISTS
    users (
      user_id SERIAL PRIMARY KEY,
      user_name VARCHAR (255) NOT NULL
    );`
  )
  .then(console.log('User table created'))
  .catch(console.error);


	client.query(`
    CREATE TABLE IF NOT EXISTS
    carriers (
      carrier_id SERIAL PRIMARY KEY,
      carrier VARCHAR (255) NOT NULL
    );`
  )
  .then(console.log('Carrier table created'))
  .catch(console.error);


  	client.query(`
    CREATE TABLE IF NOT EXISTS
    packages (
       package_id SERIAL PRIMARY KEY,
       user_id INTEGER REFERENCES users(user_id) NOT NULL,
       tracking_num VARCHAR (255) NOT NULL,
       carrier_id INTEGER REFERENCES carriers(carrier_id) NOT NULL
    );`
  )
  .then(console.log('Packages table created'))
  .catch(console.error);

};


// Only to be used when reseting tables
function setCarriers(){
	client.query("INSERT INTO carriers (carrier) VALUES ('UPS') ON CONFLICT DO NOTHING;")
  .then(console.log)
  .catch(console.error);

  client.query("INSERT INTO carriers (carrier) VALUES ('Fedex') ON CONFLICT DO NOTHING;")
  .then(console.log)
  .catch(console.error);

  client.query("INSERT INTO carriers (carrier) VALUES ('USPS') ON CONFLICT DO NOTHING;")
  .then(console.log)
  .catch(console.error);
}