Node Sequelize Postgres Tutorial

Why?

Sometimes you want your team to use an ORM.

Setup

Step 1: Create your npm project

$ npm init

Step 2: Install the packages

$ npm install --save sequelize sequelize-cli pg pg-hstore

Step 3: Create a new database

Lets create a local database (this depends on your target). If you have postgres installed use:

$ createdb user-demo-db

Step 4 : Create a .sequelizerc file

This will be used by sequelize for determining which paths to create directories

var path = require('path')

module.exports = {
  'config': path.resolve('config', 'config.js'),
  'migrations-path': path.resolve('db', 'migrations'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
}

* we are using config.js because javascript will allow us to access environment variables. We will typically not want to store password sensitive strings in our production app.

Step 5: Initialize your sequelize directories

$ node_modules/.bin/sequelize init

Step 6: Fix config.js

Since we used .js instead of .json we have to:

  1. edit our config file to export our object
  2. Your generated sequelize index.js file will default to using the development database so change development database field to the name of your database user-demo-db 
  3. Change your username to your system username (probably not root)
  4. Also make sure to change the dialect to postgres

* later, we will want to use environment variables to access passwords

Now what? Using Sequelize

We can either create our database tables with the psql shell using raw queries or we can use sequelize to manage our database.

In this article we will use sequelize and if we do, we now have two options: 1. Using sync or 2. Using migrations.

In most tutorials you will see code using sync() or even sync({force: true}). This is completely fine in a test database as it will completely overwrite changes you make in your “model” files.

We will use migrations for one big reason. Migrations will create “migration files” for all database changes where we can get valuable source controlled information.

Step 7 : Create your first model by generating your first migration files

Here we are creating a new model by generating both the initial model file AND the migration file for a sample model User with some attributes firstName;lastName;email

* Note: we are using the singular “User” and not the plural “Users”. This will be an ORM convention that you will need to get used to.

$ node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string

Look inside your new model file: 

It should export a new “model” using sequelize.define("User", ...) with the attributes that you specified in the command line arguments.

* Note that the default id field is INTEGER and not something cool like UUID, its your code now, you can change this.

Look inside your new migration file:

It should use the  queryInterface function  createTable('User', ...) with attributes that you specified in the command line arguments plus three fields that sequelize will add to every table id, createdAt and updatedAt

* One thing that is key to note is that by default you table name is pluralized

Step 8: Run the migration

Before you run this migration, you may change the migration/model file to edit your attributes.

$ node_modules/.bin/sequelize db:migrate

Sequelize will create a SequelizeMeta table in your database to keep track of which migrations have been run. That also means you can create many other models before running this command and it will run all not-yet-run migrations

Check your migration

After you run your migration you can check your table status with psql shell or pgadmin

$ psql user-demo-db

psql> \d "Users" <- note the “s”; note the double quotes 

Get used to this pattern, you will use this a lot.

(Optional) Create an instance of your model – User

Note that we use the singular User instead of the Users from the actual table

node> var models = require('./db/models')

node> models.create.User({

  firstName: "John", lastName: "Doe", email: "jd@gmail.com"})

Step 9: Create a 1:Many relationship

Lets create a model called Task that can only have one User. 1(User):Many(Tasks).

Create your migration files and migrate your database to add the new table

$ node_modules/.bin/sequelize model:generate --name task --attributes body:string,done:boolean

$ node_modules/.bin/sequelize db:migrate

Now we will need to associate the Task to the User in some way.

According to the docs we need to run Task.belongsTo(User)

However if we add this to the db/models/Task.js file in the associate function, our file now fails to load correctly when accessing db/models within the node shell. We can open up our node shell and do this manually.

node> const models = require('./db/models')

node> models.Task.belongsTo(models.User)

What happened?

BelongsTo associations are associations where the foreign key for the one-to-one relation exists on the source model.

– docs

We ran the code <source>.belongsTo(<target>) with having the appropriate “foreign key” on our source model (Our Task model is missing the column UserId). Instead of undoing our migration, this is a perfect time to learn how to do a database migration.

Step 11: Fix our 1:M relationship

This time we only need the migration file, and not the model generated file.

$ node_modules/.bin/sequelize migration:generate --name add_userid_to_task

Adding a foreign key field to a model – Task

We want to create a new column UserId on our Task model/table that represents a foreign key relationship. The docs explain that all we need is a references key with the correct values

Your migration file will look something like this: code

return queryInterface.addColumn(
  SOURCE_TABLE_NAME,  // "Tasks"
  TARGET_FIELD_NAME, // "UserId"
  {
    type: Sequelize.INTEGER,
    reference: {
      model: TARGET_TABLE_NAME, // "Users"
      key: "id"
    }
    onDelete: 'cascade
  }
)

Important Notes:

  • we have to match the type of the target field (“integer” in our case above, “UUID” if you wanted to create an advanced User model)
  • Your source “table” name is likely the plural version of your “model” name. You can get the model name with node> require('../models')[model_name].getTableName()
  • Your target field name should be “title-case” based on sequelize convention, so if you’re targeting id on User, your target field name is UserId

Step 12: Remember to migrate your database!

$ node_modules/.bin/sequelize db:migrate

 

Resources:

Code Sample

Docs

How to define Sequelize associations using migrations

Postgres timestamp with timezone – “timestamptz”

Postgres can store your timestamp WITH or WITHOUT timezone.
If you don’t specify either, it will default to “timestamp without timezone”

Which one should you use? TLDR: “timestamps with time zones”

Whats the problem living life without timezones?

Say you have two users in two different timezones – one in California and one in France. Imagine you may need to know which one acted “first”, maybe in an auction or draft scenario where the whoever acts first wins.

Without specifying timezone, your database will save the plain timestamp of each user, say “midnight” and “midnight”. How will you know which one acted first?

You could save the original timezone from the user and perform the necessary algebra when needed.

Solution – Use “timestamp with time zone” otherwise known as “timestamptz” 

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

Postgres docs

This bears repeating: 

“For timestamp with time zone, the internally stored value is always in UTC.”

A proper name for this might be timestamp saved as UTC by default. By saving everything in the “same” timezone, we know the absolute time of the timestamps.

This brings up the main UX concern: What do users see?

Your users want to read data from the database using their preferred timezone.

(If they added a blog post at 3:00pm PST, they want to see the timestamp as 3:00pm not, )

Dates are saved as UTC so you will eventually need to convert the time

Converting on the server:

Best practice is to save the users’ preferred timezone as a name (e.g. America/Los_Angeles). This way your query or ORM can convert the date according to user preference.

Remember, timezones offsets are affected by political actions. A timezones actual hour offset can change depending on whether the timezone recognizes daylight savings time or just simply when the political “owners” of the timezone decide to CHANGE the offset. Read The Long, Painful History of Time for more info.

Converting on the browser:

Many browsers have default timezones that can be used by passing in your date to new Date. You can also, simply use moment.js to parse the dates on the client to show the correctly formatted date string for your purpose.

What about user input?

User input should always be converted to UTC. Many functions already return UTC by default such as  new Date() in Browser JavaScript and node.

 

Resources:

PostgreSQL Data Types: Date, Timestamp, and Time Zones

Detecting the time zone from the browser: 

Postgres tutorial – installing with brew + basics

Step 1: Install postgres

brew install postgres

Step 2: Start postgres server

brew services start postgresql

Step 3: Create a database

Lets call our database “book”

$ createdb book

Step 4: open the psql shell

List all databases in terminal with: $ psql --list

Adding the name of the db after the command psql will open the shell to that db

$ psql book

Your psql shell has different command. For the rest of the article, we will be using the psql shell.

List your databases in psql shell with: \l

* When checking the docs make sure you check know your postgres version with: select version();

Step 5: Create a new table

list your current database tables with: \dt

Create format:

CREATE TABLE <table_name> (<field_name>, <field_type>);

Lets create fields for “body”, “date” and “code”:

CREATE TABLE dids (body text, date timestamp, code text);

Inspect your table schema with: \d <table_name>, e.g. \d dids

Step 6: Add some table rows

Remember: SINGLE QUOTES ONLY and remember the ending semi colon

INSERT INTO dids (body, date)

  VALUES ('paired with Corey', NOW());

INSERT INTO dids (body, date)

  VALUES ('remembered that SQL is SINGLE QUOTES ONLY', NOW());

 

OOPS… – we realize here that our table does not have a primary key

Step 7: Alter your table columns (Schema migration)

Your tables will likely evolve throughout time – you may want to add fields to you table, or like above, we forget to add a primary key.

Add a column for “primary key” that auto increments

ALTER TABLE dids ADD COLUMN id serial primary key;

look at the table schema with: \d <table_name>

Step 8: Query your new database!

select * from dids

 

Installed with:

Homebrew 1.7.1

postgres (PostgreSQL) 10.4