Scaffolding Project GFT & Postgres CLI Commands

The time has come! Today I started coding for Project GFT. Basically it was scaffolding the app. There were some things I ran into that I hadn’t done in a while, so here’s the step-by-step for scaffolding, for future reference.

Step 1 - Initiate Sequence

Create a new directory
Run git init to begin version control with an initial commit
Run npm init to set up the app for building with Node packages

Step 2 - Install Basic Node Packages

I’m building a Node app which will use Express to run a server and Sequelize to manage a PostgreSQL database. I also want to keep sensitive information out of the repo so will use an environment variable manager.

npm i express sequelize pg pg-hstore dotenv

That said, I ignored most of these packages initially. Instead I set up a basic Hello, world application (app.js file in the root folder) with a single get route to make sure Express was working correctly:

const express = require("express");
const app = express();

app.get("/", (req, res) => res.send("Hello, world!"));

const port = 3000;
app.listen(port, () =>
  console.log(`App is running on port ${port} in ${nodeEnv} mode.`)
);

Step 3 - Add Initial Routes

Create a routes directory in the root folder

Add an index.js file and move the get route to this file. Preface it with a router and export it as a module at the end of the file:

const express = require("express");
const router = express.Router();

router.get("/", (req, res) => res.send("Hello, world!"));

module.exports = router;

Require this router module in the main app.js file

Step 4 - Set Up Views

Create a views directory in the root folder
Create a public directory in the root folder, as well as public/css and public/js directories. This is where Express will look for any static files to serve
Install the EJS templating engine with npm i ejs

Set up the templating engine and public folder in the main app.js file:

app.set("view engine", "ejs");
app.use(express.static(__dirname + "/public"));

Add some basic content to views/index.ejs and update the get route to serve this file.
Now’s also a good time to break up the permanent elements of the template (like head, footer, etc.) into partials, which would go into a new views/partials directory.
Add some test code to CSS and JS files in their respective public folders. Link these in the index.ejs file (or relevant partials) to confirm it’s all linked and working.
Don’t forget to add a 404.ejs file to serve non-existing routes.

Step 5 - Connect Database

Update postgres locally on your machine (or install it if it’s not installed). I use homebrew.
Open the postgres command line with psql
Create a new user who will be interacting with the app: CREATE ROLE username WITH LOGIN PASSWORD 'password' CREATEDB;. This gives them permission to create databases…this user will be employed by Sequelize shortly.
Create a database for the app if it doesn’t already exist: CREATE DATABASE dbname;

Back in the app, set up a new database connection, and add the relevant fields to the .env file:

const db = new Sequelize(
  process.env.PG_DATABASE, // dbname above
  process.env.PG_USER, // username above
  process.env.PG_PASSWORD,
  {
    // password above
    host: process.env.PG_HOST, // locally, localhost
    dialect: "postgres",
    port: process.env.PG_PORT, // different from Node's app port
    operatorsAliases: false,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
  }
);

db.authenticate()
  .then(() => {
    console.log("Database connection established.");
  })
  .catch(err => {
    console.error("Unable to connect to the database:", err);
  });

Note: if you don’t create the user and database from the postgres command line first, it won’t be possible to establish a connection from the Express app. There are also npm packages that can do this.

Create a test model and seed it with some data:

const TestAnimal = db.define("animal", {
  name: {
    type: Sequelize.STRING,
    unique: true
  },
  habitat: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

TestAnimal.sync({ force: true }).then(() => {
  return TestAnimal.create({
    name: "alligator",
    habitat: "swamp"
  });
});

Go back to the postgres command line and verify that the data has been saved.

Postgres CLI Commands

Speaking of which, here are the basic Postgres CLI commands I’ve been using to date:

From Main Command Line

brew info postgresql gives some information about the postgres installation
psql opens the postgres command line as the super user

User Management

\du lists all users
CREATE ROLE username WITH LOGIN PASSWORD 'password' CREATEDB; creates a new user, requires a password, and gives permission to create databases.
ALTER ROLE username CREATEDB; gives/removes permissions for the user
DROP USER username; deletes the user and their permissions

Database Management

\l lists all databases
CREATE DATABASE dbname; creates a new database
\c dbname username moves you into using the database to access its tables as the specified user (username is optional)

Table Management

\dt lists all tables
\d tablename lists columns in a table
TABLE tablename; displays table data contents
DROP TABLE tablename; permanently deletes the table and its contents

Other

\? lists all the available postgres command line commands
\q quits the postgres command line

Up Next

So that’s the basics done! Next up is setting up some db models for my actual app, along with some accompanying views & forms to be able to start storing actual data. I’ll be working with a few different data sources, so need to plan the models to be as compatible as possible.