Coding Project - Setting up the Backend (Node, Sequelize, Express, PostgresQL): Part 1

07.07.2021 — postgresql, express, react, node, sequelize, coding project — 1 min read

I wanted to try setting up my own app from start to finish, because while I'm good at maintaining and developing features, I've not had the chance to set something up for myself.

Backend Setup: Sequelize

Reference

Note: I won't rewrite the referenced tutorials above, just pick out some parts that I use frequently and put them here.

Set up models and migration files

Generate a model via sequelize-cli, which then automatically creates a migration file for the model.

1npx sequelize-cli model:generate --name Ingredient --attributes 'name:string, variant:string, price:jsonb, nutritional_value:jsonb, serving:jsonb, created_at:date, updated_at:date'

Generate a migration file to add, change columns manually. Use dashes.

1npx sequelize-cli migration:generate --name add-ingredient-list-to-recipe

Associations

Had to define many-to-many association, and it was quite confusing.

Reference

model.js

1include: [
2    {
3      model: Ingredient,
4      through: 'RecipeIngredient'
5    }, // load the recipe ingredients
6  ]

Future Directions: User Auth

React + Node.js Express: User Authentication with JWT example

PostgreSQL

I'm using PostgreSQL as my RDBMS of choice, because I want to be very familiar with it. I was exposed to it through work, and I want to take this opportunity to explore more.

Ordering tables by size

Using a PostgreSQL client, e.g. Postico, I wanted to see the size of my relations.

query.sql

1select table_name, pg_relation_size(quote_ident(table_name))
2from information_schema.tables
3where table_schema = 'public'
4order by 2

Most Voted Answer

query.sql

1select table_name, pg_size_pretty( pg_relation_size(quote_ident(table_name)) )
2  from information_schema.tables
3  where table_schema = 'public'
4  order by pg_relation_size(quote_ident(table_name)) desc

Alternative

This is for all schemas, not just public. Ref

query.sql

1SELECT
2  schema_name,
3  relname,
4  pg_size_pretty(table_size) AS size,
5  table_size
6
7FROM (
8       SELECT
9         pg_catalog.pg_namespace.nspname AS schema_name,
10         relname,
11         pg_relation_size(pg_catalog.pg_class.oid) AS table_size
12
13       FROM pg_catalog.pg_class
14         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
15     ) t
16WHERE schema_name NOT LIKE 'pg_%'
17ORDER BY table_size DESC;

Coding Project - Setting up the Backend (Node, Sequelize, Express, PostgresQL): Part 1

Backend Setup: Sequelize

Reference

Set up models and migration files

Associations

Reference

Future Directions: User Auth

PostgreSQL

Ordering tables by size

Read more: