UXDL Docs
Backend

PostgreSQL + Sequelize

Node — Sequelize models, migrations, associations, and seeding.

Use Sequelize for PostgreSQL when you need mature migration tooling, associations, and an established ORM pattern.

Install

bash
pnpm add sequelize pg pg-hstore
pnpm add -D @types/pg sequelize-cli

Connection setup

typescript
// src/db/sequelize.ts
import { Sequelize } from "sequelize";
 
export const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: "postgres",
  logging: process.env.NODE_ENV === "development" ? console.log : false,
  pool: { max: 10, min: 0, acquire: 30_000, idle: 10_000 },
});

Model definition

typescript
// src/models/user.model.ts
import { DataTypes, Model, Optional } from "sequelize";
import { sequelize } from "../db/sequelize";
 
interface UserAttributes {
  id: string;
  email: string;
  cognitoSub: string;
  createdAt: Date;
}
 
type UserCreation = Optional<UserAttributes, "id" | "createdAt">;
 
export class User extends Model<UserAttributes, UserCreation> implements UserAttributes {
  declare id: string;
  declare email: string;
  declare cognitoSub: string;
  declare createdAt: Date;
}
 
User.init(
  {
    id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true },
    email: { type: DataTypes.STRING, allowNull: false, unique: true },
    cognitoSub: { type: DataTypes.STRING, allowNull: false, unique: true },
    createdAt: { type: DataTypes.DATE, allowNull: false },
  },
  { sequelize, tableName: "users", timestamps: true, updatedAt: false },
);

Associations

typescript
// src/models/index.ts
import { User } from "./user.model";
import { Project } from "./project.model";
 
User.hasMany(Project, { foreignKey: "ownerId", as: "projects" });
Project.belongsTo(User, { foreignKey: "ownerId", as: "owner" });
 
export { User, Project };

Migrations

bash
npx sequelize-cli init
npx sequelize-cli migration:generate --name create-users-table
npx sequelize-cli db:migrate
npx sequelize-cli db:migrate:undo   # Rollback last
javascript
// migrations/20260524-create-users-table.js
"use strict";
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable("users", {
      id: { type: Sequelize.UUID, primaryKey: true, defaultValue: Sequelize.UUIDV4 },
      email: { type: Sequelize.STRING, allowNull: false, unique: true },
      cognito_sub: { type: Sequelize.STRING, allowNull: false, unique: true },
      created_at: { type: Sequelize.DATE, allowNull: false },
    });
  },
  async down(queryInterface) {
    await queryInterface.dropTable("users");
  },
};

Usage in services

typescript
// src/services/users.service.ts
import { User } from "../models/user.model";
 
export async function findByCognitoSub(sub: string) {
  return User.findOne({ where: { cognitoSub: sub } });
}

Scripts

json
{
  "scripts": {
    "db:migrate": "sequelize-cli db:migrate",
    "db:migrate:undo": "sequelize-cli db:migrate:undo",
    "db:seed": "sequelize-cli db:seed:all"
  }
}

Official documentation