Skip to content

Database

Jump to bottom
Kevin Camellini edited this page Oct 1, 2018 · 13 revisions

Heroku?

What is Heroku?
The website is hosted on Heroku using a hobby dyno. A hobby dyno is a free server that goes to sleep after 30 minutes of inactivity, after which the system takes a few seconds to boot back up once accessed again. More info on Heroku's dyno system can be found here. Any commits to the master branch are automatically pushed to the server. CI integration is also possible and coming soon.

Postgres?

What is PostgreSQL?
We are using is an object relational DBMS called Postgres.
Node.js, Express, and Heroku offer dead simple support for implementing a postgres database. Instructions can be found here. Postgres' query language is very similar to MySQL and SQL if your familiar with those using postgres is hardly discernible.

Access the production DB remotely

By now you should have the node project running locally. See this link for instructions on the initial setup. You will need the following command line tools to get started.

Then run the following commands:

$ heroku login
$ heroku git:remote -a soen343t5

-- for Mac and Linux
$ export DATABASE_URL=postgres://$(whoami)
-- for Windows
$ set DATABASE_URL=postgres://$(whoami)
$ cd into/postgres/directory --should be here c:/Program Files/postgreSQL/10/bin 

$ heroku "pg:promote DATABASE_URL -a soen343t5
$ heroku pg:psql
=> ­soen343t5::DATABASE 
=> COMMANDS FROM HERE ON ARE NOW IN SQL

Accessing the server remotely

$ heroku login
$ heroku git:remote -a soen343t5
$ heroku run node
> now in node console 
# will run any node commands on the server

# OR

$ heroku run bash
> now in bash console
# will run any bash commands on the server

else

  • Navigate to the soen343t5 app on heroku.com.
  • In the top right click on more to bring up a dropdown menu
  • Click on run console
  • Type in node and click on run
  • > now in node console

Runing Postgres locally

You will need the following installed:

then run:

$ createdb test
$ psql -h localhost

For further reading on running postgres locally

Running the producuction DB locally

Create a script to run the SQL commands to create and populate the db? which an be used locally/remotely developement/production ?

For further reading on running postgres locally

Seeing the result

the routes/index.js file contains access to the database, a route, and a query:

//  DB connection
var connString = process.env.DATABASE_URL
const { Pool } = require('pg');
const pool = new Pool({
    connectionString: connString,
    ssl: true
});
...
/* GET db page */
router.get('/db', async (req, res) => {
    try {
        const client = await pool.connect()
        const result = await client.query('SELECT * FROM test_table');
        const results = { 'results': (result) ? result.rows : null};
        res.render('db', results );
        client.release();
    } catch (err) {
        console.error(err);
        res.send("Error " + err);
    }
})

the views/db.pug file contains a few lines of pug/html interpreting the data being passed into the view:

p= results[0].id
p=results[0].name
p= results[1].id
p=results[1].name

the results can be seen at soen343t5.herokuapp.com/db.

Clone this wiki locally