Getting error trying to create Postgres DB in Node.js

Question

Im learning Node and trying create a server using Express and connecting it to a postgres db and keep getting the following when I run node server.js:

events.js:72
        throw er; // Unhandled 'error' event
              ^
error: role "username" does not exist
    at Connection.parseE (/Users/rs/Desktop/Jobletics/node_modules/pg/lib/connection.js:526:11)
    at Connection.parseMessage (/Users/rs/Desktop/Jobletics/node_modules/pg/lib/connection.js:356:17)
    at Socket.<anonymous> (/Users/rs/Desktop/Jobletics/node_modules/pg/lib/connection.js:105:22)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:764:14)
    at Socket.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:426:10)
    at emitReadable (_stream_readable.js:422:5)
    at readableAddChunk (_stream_readable.js:165:9)
    at Socket.Readable.push (_stream_readable.js:127:10)

my server.js file looks like this:

// app dependencies
var express = require("express");
var Sequelize = require("sequelize");
var bodyParser = require('body-parser');
var morgan = require('morgan');
var app = express();


//middleware
app.use(bodyParser());
app.use(morgan('dev'));

//sequalize initialization
var sequelize = new Sequelize("postgres://username:password@localhost:5432/jobletics");
var employerRoute = require("./routes/employer")(sequelize);

//sync the model with the database
sequelize.sync().success(function (err) {
    app.get("/employer", employerRoute.get);
    app.post("/employer", employerRoute.create);
    app.listen(5000);
});

I have postgres running. Do I need to create a new db in the command-line then run $psql to create username/password? Shouldn't the db get created automatically?

Answer 1

You can use

var sequelize = new Sequelize("postgres://username:password@localhost:5432/jobletics");

for connecting to postgresql database, but sequelize does not create nor user nor database for you. You must do it yourself. Use createuser and createdb postgres utilities to create them, or user -c flag of psql command. You also must have privilege to do it, so in the next example commands run using postgres user:

su postgres -c "psql -U postgres -c \"CREATE USER username WITH ENCRYPTED PASSWORD 'password'\""
su postgres -c "psql -U postgres -c \"CREATE DATABASE jobletics WITH OWNER=username ENCODING='UTF8'\""

Answer 2

Although Sequelize does not have a method to create databases, you can use the create query method to execute a custom method.

But the catch is the following,

You need to "connect to a database" to execute any command

Here you can't do that because it's that database creation command that you would want to create in the first place.

There's one simple way to overcome this. Postgress has two inbuilt template DBs that cannot be dropped called template0 and template1 respectively. More on template databases here

So all you have to do is connect to template1 (not template0) in your connection string

const sequelize = new Sequelize('postgres://username:password@localhost:5432/template1')

and execute the sequelize query method with the create query command

const createQuery = "CREATE DATABASE YOUR_DB_NAME WITH  OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' TABLESPACE = pg_default CONNECTION LIMIT = -1;"
sequelize.query(createQuery)
    .then(() => console.log("DB created"))
    .catch(err => console.log("error creating DB", err))

Answer 3

I have not seen the database getting created automatically. When I use sequelize.js, i normally run it within a vm which has a puppet manifest to assert that the database already exists.

Also, when it comes to connecting to the db, I would normally do it like so as I think it is easier to read:

var Sequelize = require("sequelize");

var sequelize = new Sequelize(
    "dbName",
    "username",
    "password",
    {
         "dialect": "postgres",
         "port": 5423
    }
);

Finally, make sure that postgres existing in your package.json, if is doesn't you will have to run the following: npm install --save pg pg-hstore