Skip to content
Tutorials Center
My learning

Topics

Lesson 81 of 90 ยท Using PostgreSQL from Applications

ORMs, Migrations and PostgreSQL Features

Use JSONB, arrays and enums from SQLAlchemy, Prisma, Eloquent and Django, drop to raw SQL when needed, and manage schema changes with Flyway, Alembic or Liquibase.

An ORM (object-relational mapper) lets you work with tables as classes and rows as objects. It saves a great deal of typing for ordinary create, read, update and delete work, and it keeps parameters safe by default. But PostgreSQL has features that most ORMs don't model well or at all: jsonb operators, array columns, native enums, partial indexes, exclusion constraints, ON CONFLICT, full-text search. Using PostgreSQL well through an ORM means knowing what the tool supports directly and where to reach for raw SQL.

This lesson tours four popular ORMs (SQLAlchemy, Prisma, Eloquent and Django) with the PostgreSQL features you are most likely to use, then covers schema migrations and the tools that run them. The ORMs and migration tools need real servers and packages, so their code is read-only. Live examples use a Python migration runner built on SQLite and SQL that mimics what the ORM generates for JSON lookups.

What an ORM gives you

Models Classes mapped to tables and columns.
Safe queries Values are always sent as parameters.
Migrations Schema changes tracked as versioned files.
1 / 2

JSONB, arrays and enums in each ORM

All four ORMs can store a jsonb document in a column and query inside it; arrays and enums vary more.

SQLAlchemy has the PostgreSQL types built in, in sqlalchemy.dialects.postgresql:

Python
from sqlalchemy import Enum, Text
from sqlalchemy.dialects.postgresql import ARRAY, JSONB
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
import enum

class Status(enum.Enum):
    draft = "draft"
    live = "live"

class Base(DeclarativeBase): ...

class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(primary_key=True)
    attrs: Mapped[dict] = mapped_column(JSONB, default=dict)
    tags: Mapped[list[str]] = mapped_column(ARRAY(Text), default=list)
    status: Mapped[Status] = mapped_column(Enum(Status, name="product_status"))

# attrs @> '{"color": "red"}' and tags @> ARRAY['sale']
stmt = select(Product).where(Product.attrs.contains({"color": "red"}), Product.tags.contains(["sale"]))

Two gotchas: a plain JSONB column does not notice in-place changes such as product.attrs["color"] = "blue"; reassign the whole value, or use MutableDict.as_mutable(JSONB). And Enum(..., name=...) creates a real PostgreSQL enum type, which is awkward to change later.

Prisma maps Json to jsonb, scalar lists such as String[] to arrays, and enum blocks to native enums:

TEXT
model Product {
  id     Int      @id @default(autoincrement())
  attrs  Json
  tags   String[]
  status Status   @default(draft)

  @@index([attrs], type: Gin)
}

enum Status { draft live }

Filtering inside JSON uses path filters (where: { attrs: { path: ['color'], equals: 'red' } }) and arrays have has, hasSome and hasEvery. Prisma's schema can describe a GIN index but not partial indexes or exclusion constraints; for those you edit the SQL migration by hand (create it with prisma migrate dev --create-only).

Eloquent (Laravel) keeps things simple. A migration column $table->jsonb('attrs') plus a cast 'attrs' => 'array' gives you PHP arrays, and Laravel's JSON path syntax compiles to -> and ->>:

PHP
Product::where('attrs->color', 'red')->get();
Product::whereJsonContains('attrs->sizes', 'M')->get();

Laravel's schema builder has no array column type and ->enum() on PostgreSQL creates a varchar with a CHECK constraint rather than a native enum, so arrays and native enums need a raw DB::statement() in a migration.

Django has the broadest PostgreSQL support in django.contrib.postgres:

Python
from django.contrib.postgres.fields import ArrayField
from django.contrib.postgres.indexes import GinIndex
from django.db import models

class Product(models.Model):
    attrs = models.JSONField(default=dict)               # jsonb on PostgreSQL
    tags = ArrayField(models.CharField(max_length=30), default=list)
    status = models.CharField(max_length=10, choices=[("draft", "Draft"), ("live", "Live")])

    class Meta:
        indexes = [GinIndex(fields=["attrs"])]

Product.objects.filter(attrs__color="red", tags__contains=["sale"])

Django's choices store text, not a native enum, which keeps migrations easy. The same package provides ExclusionConstraint, range fields, full-text search helpers and ON CONFLICT through bulk_create(update_conflicts=True, ...).

The queries above all end up as SQL operators like attrs @> '{"color": "red"}'. SQLite spells JSON lookups with functions instead, but the logic is the same, and you can run it live. The ORM-style filters "colour is red" and "tags contain sale" look like this:

SQL What an ORM's JSON and array filters turn into

Press Run (or Ctrl + Enter) to see the result here.

The raw SQL escape hatch

Every ORM lets you drop to SQL, and you should, without guilt, for reports, bulk operations and anything that needs a PostgreSQL-only feature. What matters is doing it with parameters:

Python
# SQLAlchemy
session.execute(
    text("SELECT id FROM products WHERE attrs @> CAST(:doc AS jsonb)"),
    {"doc": '{"color": "red"}'},
)
JavaScript
// Prisma: the tagged template sends ${...} as parameters
const rows = await prisma.$queryRaw`SELECT id FROM products WHERE attrs @> ${doc}::jsonb`;
// $queryRawUnsafe(string) pastes text and is open to injection: avoid it with user input
PHP
// Eloquent / Laravel
DB::select('SELECT id FROM products WHERE attrs @> ?::jsonb', [$doc]);
Product::whereRaw("attrs @> ?::jsonb", [$doc])->get();   // note the bound value
Python
# Django
Product.objects.raw("SELECT * FROM products WHERE attrs @> %s::jsonb", [doc])

Never f-string or concatenate user input into these strings; see SQL injection and safe queries. Watch two ORM habits that hurt on PostgreSQL: the N+1 query problem (one query per row when loading related objects; fix it with eager loading such as selectinload, include, with() or select_related) and loading entire rows when you need two columns.

Migrations: schema changes as code

A migration is a versioned, ordered change to the schema, stored in your repository and applied in the same order on every environment. The database remembers which migrations have run (usually in a table such as flyway_schema_history, alembic_version or django_migrations), so deploying means "apply what is missing". The popular tools:

  • Flyway runs plain SQL files named V1__create_products.sql, V2__add_tags.sql, in version order, and checks that applied files were not edited afterwards (checksums). Simple and SQL-first.
  • Alembic (SQLAlchemy) writes Python migration scripts, and alembic revision --autogenerate compares your models with the database and drafts the change. Apply with alembic upgrade head.
  • Liquibase describes changes as changesets in XML, YAML, JSON or SQL, with rollback support and conditions.
  • Django migrations, Prisma Migrate and Laravel migrations are built into those frameworks.

Auto-generated migrations are a starting point, not a final answer. Always read them: a rename can be generated as drop-and-add, which destroys data. And mind PostgreSQL's locking: CREATE INDEX CONCURRENTLY cannot run inside a transaction, but most tools wrap each migration in one by default, so you have to switch that off for that migration (Alembic: with op.get_context().autocommit_block():; Django: atomic = False and AddIndexConcurrently; Liquibase: runInTransaction="false"). The full list of risky operations is in schema changes and migrations and the general migrations lesson.

Every tool is a variation of the same loop: find which versions have run, apply the missing ones in order, record each. This runnable Python version uses SQLite to show the whole idea, including what happens when a migration fails halfway:

Python A tiny migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Migration 003 fails on its second statement, and the rollback also removes the row its first statement inserted, so the table stays clean and the history lists only 001 and 002. A second run tries 003 again and fails the same way, which is exactly how a real tool behaves until you fix the file. PostgreSQL helps here because most DDL is transactional too; MySQL cannot roll back ALTER TABLE, which makes failed migrations messier there.

Practical advice

  • Keep migrations small and never edit one that has run in a shared environment: add a new migration instead.
  • Check generated SQL (sqlmigrate in Django, --sql in Alembic, prisma migrate diff) before it reaches production.
  • Put PostgreSQL-specific objects (partial indexes, exclusion constraints, triggers, extensions) in migrations as raw SQL, so they live next to the rest of your schema history.
  • Run application code with a role that cannot alter tables; use a separate migration role.
  • Test migrations against a copy of production-sized data, not only an empty database.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In SQLAlchemy, you run product.attrs["color"] = "blue" on a plain JSONB column and commit, but nothing is saved. Why?

    A plain JSON column is tracked by identity, so mutating the dict in place goes unnoticed. Assign a new dict, or use the mutable extension.
  2. 2. Which Prisma call sends interpolated values as bound parameters?

    The tagged template form turns each ${...} into a parameter. $queryRawUnsafe pastes text, so untrusted input can inject SQL.
  3. 3. Why must CREATE INDEX CONCURRENTLY be handled specially in a migration tool?

    PostgreSQL refuses CREATE INDEX CONCURRENTLY inside a transaction, so you disable the wrapping transaction for that migration.
  4. 4. What do migration tools use to know which migrations have already run?

    Each tool records applied versions in a table, so a deploy applies only the migrations that are missing.

Summary

  • SQLAlchemy and Django model jsonb, arrays and exclusion constraints well; Prisma covers JSON, arrays, enums and GIN indexes; Eloquent covers JSON but needs raw SQL for arrays and native enums.
  • Reach for raw SQL for special indexes, reports and PostgreSQL-only features, always with bound parameters.
  • Avoid N+1 queries with eager loading.
  • Migrations are versioned schema changes tracked in a history table: Flyway (SQL files), Alembic (Python), Liquibase (changesets), or the framework's own.
  • Review generated migrations, disable the wrapping transaction for CREATE INDEX CONCURRENTLY, and never edit applied migrations.

Next: Bulk Loading and COPY.