Relational Database (Postgres) Notes

Introduction to Postgres

• PostgreSQL (Postgres) is a powerful, open-source object-relational database system
• Key features:
  • Reliable architecture
  • Runs on major operating systems
  • ACID compliant
  • Supports foreign keys, joins, views, triggers, and stored procedures
  • Includes most SQL:2008 data types
  • Supports binary large object storage
  • Native programming interfaces for multiple languages

Setting up Postgres

1. Sign up on neon.tech
2. Create a new project and database
3. Copy connection details (PGHOST, PGDATABASE, PGUSER, PGPASSWORD)

Connecting to Postgres

• Use pgAdmin as a GUI tool for Postgres
• Configure pgAdmin with your Neon.tech database credentials
• Explore database structure and tables using pgAdmin

Sequelize ORM

• Object-Relational Mapper (ORM) for interacting with relational databases
• Benefits:
  • Database-agnostic code
  • Built-in data validation
• Installation: npm install sequelize pg pg-hstore

Connecting to the database using Sequelize

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'user', 'password', {
  host: 'host',
  dialect: 'postgres',
  port: 5432,
  dialectOptions: {
    ssl: { rejectUnauthorized: false },
  },
});

Defining Models (Tables)

• Models represent tables in the database
• Example of defining a model:

const Project = sequelize.define('Project', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT,
});

• Common data types: STRING, TEXT, INTEGER, FLOAT, DOUBLE, DATE, TIME, BOOLEAN

Model Relationships / Associations

• Define relationships between tables using methods like:
  • hasMany()
  • belongsTo()
  • hasOne()
  • belongsToMany()
• Example of a one-to-many relationship:

User.hasMany(Task);
Task.belongsTo(User);

CRUD Operations

Create

Model.create({
  field1: 'value1',
  field2: 'value2',
});

Read

Model.findAll({
  attributes: ['field1', 'field2'],
  where: { condition: value },
  order: ['field'],
});

Update

Model.update(
  { field: newValue },
  { where: { id: recordId } }
);

Delete

Model.destroy({
  where: { id: recordId },
});

Best Practices

• Use Sequelize's built-in validation when defining models

const User = sequelize.define('User', {
  username: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isAlphanumeric: true,
      len: [3, 20]
    }
  },
  email: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  age: {
    type: Sequelize.INTEGER,
    validate: {
      min: 18,
      max: 99
    }
  }
});

• The username must be alphanumeric and between 3 and 20 characters long.
• The email must be a valid email address.
• The age must be between 18 and 99.

• Sync database with models using sequelize.sync()
• Handle promises properly using .then() and .catch()
• Use meaningful names for models and fields
• Implement proper error handling in database operations

Remember to always refer to the official documentation for the most up-to-date information and advanced features.