Advanced

Select ORMs to compare

Pick one or more ORMs from the bar above

Window Functions

SQL

SELECT
  name, amount,
  ROW_NUMBER() OVER (
    ORDER BY amount DESC
  ) AS rank
FROM orders;

Django ORM

from django.db.models import F, Window
from django.db.models.functions import RowNumber

orders = Order.objects.annotate(
    rank=Window(
        expression=RowNumber(),
        order_by=F("amount").desc(),
    )
).values("name", "amount", "rank")

SQLAlchemy

from sqlalchemy import func, select

stmt = select(
    Order.name,
    Order.amount,
    func.row_number()
    .over(order_by=Order.amount.desc())
    .label("rank"),
)
rows = session.execute(stmt).all()

SQLModel

from sqlalchemy import func
from sqlmodel import select

stmt = select(
    Order.name,
    Order.amount,
    func.row_number()
    .over(order_by=Order.amount.desc())
    .label("rank"),
)
rows = session.exec(stmt).all()

Tortoise ORM

# No window function API; use raw SQL
from tortoise import connections

conn = connections.get("default")
rows = await conn.execute_query_dict(
    "SELECT name, amount, "
    "ROW_NUMBER() OVER ("
    "  ORDER BY amount DESC"
    ") AS rank "
    "FROM orders"
)

Peewee

from peewee import fn

orders = (
    Order.select(
        Order.name,
        Order.amount,
        fn.ROW_NUMBER().over(
            order_by=[Order.amount.desc()]
        ).alias("rank"),
    )
)

Prisma

// No window function API; use raw SQL
const orders = await prisma.$queryRaw`
  SELECT name, amount,
    ROW_NUMBER() OVER (
      ORDER BY amount DESC
    ) AS rank
  FROM orders
`;

Drizzle

import { sql } from "drizzle-orm";

const result = await db
  .select({
    name: orders.name,
    amount: orders.amount,
    rank: sql<number>`ROW_NUMBER() OVER (
      ORDER BY ${orders.amount} DESC
    )`,
  })
  .from(orders);

Kysely

import { sql } from 'kysely'

const orders = await db
  .selectFrom('orders')
  .select([
    'name',
    'amount',
    sql<number>`row_number() over (order by amount desc)`.as('rank'),
  ])
  .execute()

TypeORM

const orders = await dataSource
  .getRepository(Order)
  .createQueryBuilder("order")
  .select("order.name", "name")
  .addSelect("order.amount", "amount")
  .addSelect(
    "ROW_NUMBER() OVER (ORDER BY order.amount DESC)",
    "rank"
  )
  .getRawMany();

MikroORM

const rows = await orm.em
  .createQueryBuilder(Order, "o")
  .select([
    "o.name",
    "o.amount",
    "row_number() over (order by o.amount desc) as rank",
  ])
  .execute();

Sequelize

import { literal } from "sequelize";

const orders = await Order.findAll({
  attributes: [
    "name",
    "amount",
    [
      literal('ROW_NUMBER() OVER (ORDER BY "amount" DESC)'),
      "rank",
    ],
  ],
  raw: true,
});

Common Table Expressions (CTE)

SQL

WITH high_value AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 1000
)
SELECT u.name, h.total
FROM users u
JOIN high_value h ON u.id = h.user_id;

Django ORM

# Django has no built-in CTE support;
# use raw SQL
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        WITH high_value AS (
            SELECT user_id,
                   SUM(amount) AS total
            FROM orders
            GROUP BY user_id
            HAVING SUM(amount) > 1000
        )
        SELECT u.name, h.total
        FROM users u
        JOIN high_value h
          ON u.id = h.user_id
    """)
    rows = cursor.fetchall()

SQLAlchemy

from sqlalchemy import func, select

high_value = (
    select(
        Order.user_id,
        func.sum(Order.amount).label("total"),
    )
    .group_by(Order.user_id)
    .having(func.sum(Order.amount) > 1000)
    .cte("high_value")
)

stmt = select(
    User.name, high_value.c.total
).join(
    high_value,
    User.id == high_value.c.user_id,
)
rows = session.execute(stmt).all()

SQLModel

from sqlalchemy import func
from sqlmodel import select

high_value = (
    select(
        Order.user_id,
        func.sum(Order.amount).label("total"),
    )
    .group_by(Order.user_id)
    .having(func.sum(Order.amount) > 1000)
    .cte("high_value")
)

stmt = (
    select(User.name, high_value.c.total)
    .join(high_value, User.id == high_value.c.user_id)
)
rows = session.exec(stmt).all()

Tortoise ORM

# No CTE API; use raw SQL
from tortoise import connections

conn = connections.get("default")
rows = await conn.execute_query_dict("""
    WITH high_value AS (
        SELECT user_id,
               SUM(amount) AS total
        FROM orders
        GROUP BY user_id
        HAVING SUM(amount) > 1000
    )
    SELECT u.name, h.total
    FROM users u
    JOIN high_value h
      ON u.id = h.user_id
""")

Peewee

# Use raw SQL for CTEs
cursor = db.execute_sql("""
    WITH high_value AS (
        SELECT user_id,
               SUM(amount) AS total
        FROM orders
        GROUP BY user_id
        HAVING SUM(amount) > 1000
    )
    SELECT u.name, h.total
    FROM users u
    JOIN high_value h
      ON u.id = h.user_id
""")
rows = cursor.fetchall()

Prisma

// No CTE API; use raw SQL
const results = await prisma.$queryRaw`
  WITH high_value AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
  )
  SELECT u.name, h.total
  FROM users u
  JOIN high_value h ON u.id = h.user_id
`;

Drizzle

import { sql, eq } from "drizzle-orm";

const highValue = db.$with("high_value").as(
  db
    .select({
      userId: orders.userId,
      total: sql<number>`SUM(${orders.amount})`
        .as("total"),
    })
    .from(orders)
    .groupBy(orders.userId)
    .having(sql`SUM(${orders.amount}) > 1000`)
);

const result = await db
  .with(highValue)
  .select({
    name: users.name,
    total: highValue.total,
  })
  .from(users)
  .innerJoin(
    highValue, eq(users.id, highValue.userId)
  );

Kysely

import { sql } from 'kysely'

const rows = await db
  .with('high_value', (db) =>
    db
      .selectFrom('orders')
      .select([
        'user_id',
        sql<number>`sum(amount)`.as('total'),
      ])
      .groupBy('user_id')
      .having(sql`sum(amount)`, '>', 1000)
  )
  .selectFrom('users as u')
  .innerJoin('high_value as h', 'h.user_id', 'u.id')
  .select(['u.name', 'h.total'])
  .execute()

TypeORM

// No CTE API; use raw SQL
const results = await dataSource.query(`
  WITH high_value AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
  )
  SELECT u.name, h.total
  FROM users u
  JOIN high_value h ON u.id = h.user_id
`);

MikroORM

// No high-level CTE API; use raw SQL
const rows = await orm.em.getConnection().execute(`
  WITH high_value AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
  )
  SELECT u.name, h.total
  FROM users u
  JOIN high_value h ON u.id = h.user_id
`);

Sequelize

// No high-level CTE API; use raw SQL
const [rows] = await sequelize.query(`
  WITH high_value AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
  )
  SELECT u.name, h.total
  FROM users u
  JOIN high_value h ON u.id = h.user_id
`);

JSON Field Queries

SQL

-- Store JSON
UPDATE users
SET metadata = '{"theme": "dark"}'
WHERE id = 1;

-- Query JSON field
SELECT * FROM users
WHERE metadata->>'theme' = 'dark';

-- Nested JSON
SELECT metadata->'address'->>'city'
FROM users WHERE id = 1;

Django ORM

# model: metadata = JSONField(default=dict)

# Store JSON
User.objects.filter(id=1).update(
    metadata={"theme": "dark", "lang": "en"}
)

# Query JSON field
users = User.objects.filter(
    metadata__theme="dark"
)

# Nested JSON
users = User.objects.filter(
    metadata__address__city="NYC"
)

SQLAlchemy

# model: metadata = mapped_column(JSONB)

# Store JSON
user.metadata = {"theme": "dark", "lang": "en"}
session.commit()

# Query JSON field
users = session.scalars(
    select(User).where(
        User.metadata["theme"].astext == "dark"
    )
).all()

# Nested JSON
users = session.scalars(
    select(User).where(
        User.metadata["address"]["city"].astext
        == "NYC"
    )
).all()

SQLModel

from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import JSONB
from sqlmodel import Field, SQLModel, select


class User(SQLModel, table=True):
    __tablename__ = "users"

    id: int | None = Field(default=None, primary_key=True)
    profile: dict = Field(
        default_factory=dict,
        sa_column=Column(JSONB),
    )


# Store JSON
user = session.get(User, 1)
user.profile = {"theme": "dark", "lang": "en"}
session.add(user)
session.commit()

# Query JSON field
users = session.exec(
    select(User).where(
        User.profile["theme"].astext == "dark"
    )
).all()

# Nested JSON
users = session.exec(
    select(User).where(
        User.profile["address"]["city"].astext == "NYC"
    )
).all()

Tortoise ORM

# model: metadata = fields.JSONField(default=dict)

# Store JSON
await User.filter(id=1).update(
    metadata={"theme": "dark", "lang": "en"}
)

# Query JSON field
users = await User.filter(
    metadata__theme="dark"
)

# Nested JSON
users = await User.filter(
    metadata__address__city="NYC"
)

Peewee

from playhouse.postgres_ext import BinaryJSONField

# model: metadata = BinaryJSONField(default=dict)

# Store JSON
User.update(
    metadata={"theme": "dark", "lang": "en"}
).where(User.id == 1).execute()

# Query JSON field
users = User.raw(
    "SELECT * FROM users "
    "WHERE metadata->>'theme' = %s",
    "dark",
)

# Nested JSON
users = User.raw(
    "SELECT * FROM users "
    "WHERE metadata->'address'->>'city' = %s",
    "NYC",
)

Prisma

// schema: metadata Json

// Store JSON
await prisma.user.update({
  where: { id: 1 },
  data: {
    metadata: { theme: "dark", lang: "en" },
  },
});

// Query JSON field
const users = await prisma.user.findMany({
  where: {
    metadata: {
      path: ["theme"],
      equals: "dark",
    },
  },
});

Drizzle

import { eq, sql } from "drizzle-orm";

// schema: metadata: jsonb("metadata")

// Store JSON
await db
  .update(users)
  .set({ metadata: { theme: "dark", lang: "en" } })
  .where(eq(users.id, 1));

// Query JSON field
const result = await db
  .select()
  .from(users)
  .where(
    sql`${users.metadata}->>'theme' = 'dark'`
  );

// Nested JSON
const result2 = await db
  .select()
  .from(users)
  .where(
    sql`${users.metadata}->'address'->>'city' = 'NYC'`
  );

Kysely

import { sql } from 'kysely'

// Store JSON
await db
  .updateTable('users')
  .set({
    metadata: sql`'{"theme":"dark","lang":"en"}'::jsonb`,
  })
  .where('id', '=', 1)
  .executeTakeFirst()

// Query JSON field
const users = await db
  .selectFrom('users')
  .selectAll()
  .where(sql`metadata->>'theme'`, '=', 'dark')
  .execute()

// Nested JSON
const users2 = await db
  .selectFrom('users')
  .selectAll()
  .where(sql`metadata->'address'->>'city'`, '=', 'NYC')
  .execute()

TypeORM

// entity: @Column({ type: "jsonb" }) metadata: object;

// Store JSON
await dataSource.getRepository(User).update(
  { id: 1 },
  { metadata: { theme: "dark", lang: "en" } }
);

// Query JSON field
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .where(
    "user.metadata->>'theme' = :theme",
    { theme: "dark" }
  )
  .getMany();

// Nested JSON
const users2 = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .where(
    "user.metadata->'address'->>'city' = :city",
    { city: "NYC" }
  )
  .getMany();

MikroORM

import { Entity, PrimaryKey, Property } from "@mikro-orm/core";

@Entity({ tableName: "users" })
class User {
  @PrimaryKey()
  id!: number;

  @Property({ type: "json" })
  metadata!: Record<string, unknown>;
}

// Store JSON
await orm.em.nativeUpdate(User, { id: 1 }, {
  metadata: { theme: "dark", lang: "en" },
});

// Query JSON field
const users = await orm.em
  .createQueryBuilder(User, "u")
  .where(`u.metadata->>'theme' = ?`, ["dark"])
  .getResultList();

// Nested JSON
const users2 = await orm.em
  .createQueryBuilder(User, "u")
  .where(`u.metadata->'address'->>'city' = ?`, ["NYC"])
  .getResultList();

Sequelize

import { literal } from "sequelize";

// model: metadata: { type: DataTypes.JSONB, defaultValue: {} }

// Store JSON
await User.update(
  { metadata: { theme: "dark", lang: "en" } },
  { where: { id: 1 } },
);

// Query JSON field
const users = await User.findAll({
  where: literal(`metadata->>'theme' = 'dark'`),
});

// Nested JSON
const users2 = await User.findAll({
  where: literal(`metadata->'address'->>'city' = 'NYC'`),
});

Full-Text Search

SQL

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body)
  @@ plainto_tsquery('english', 'database optimization');

Django ORM

from django.contrib.postgres.search import (
    SearchVector,
    SearchQuery,
)

results = Article.objects.annotate(
    search=SearchVector("title", "body"),
).filter(
    search=SearchQuery("database optimization")
)

SQLAlchemy

from sqlalchemy import func

tsvector = func.to_tsvector(
    "english",
    Article.title + " " + Article.body,
)
tsquery = func.plainto_tsquery(
    "english", "database optimization"
)

stmt = select(Article).where(
    tsvector.op("@@")(tsquery)
)
results = session.execute(stmt).scalars().all()

SQLModel

from sqlalchemy import func
from sqlmodel import select

tsvector = func.to_tsvector(
    "english",
    Article.title + " " + Article.body,
)
tsquery = func.plainto_tsquery(
    "english", "database optimization"
)

stmt = select(Article).where(
    tsvector.op("@@")(tsquery)
)
results = session.exec(stmt).all()

Tortoise ORM

from tortoise.contrib.postgres.search import (
    SearchVector,
    SearchQuery,
)

results = await Article.annotate(
    search=SearchVector("title", "body")
).filter(
    search=SearchQuery("database optimization")
)

Peewee

# Use raw SQL for PostgreSQL full-text search
results = Article.raw(
    "SELECT * FROM articles "
    "WHERE to_tsvector('english', title || ' ' || body) "
    "@@ plainto_tsquery('english', %s)",
    "database optimization",
)

Prisma

// preview: fullTextSearch
const articles = await prisma.article.findMany({
  where: {
    title: { search: "database & optimization" },
  },
});

Drizzle

import { sql } from "drizzle-orm";

const results = await db
  .select()
  .from(articles)
  .where(
    sql`to_tsvector('english',
      ${articles.title} || ' ' || ${articles.body})
    @@ plainto_tsquery('english',
      ${"database optimization"})`
  );

Kysely

import { sql } from 'kysely'

const results = await db
  .selectFrom('articles')
  .selectAll()
  .where(
    sql<boolean>`to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', ${'database optimization'})`
  )
  .execute()

TypeORM

const articles = await dataSource
  .getRepository(Article)
  .createQueryBuilder("a")
  .where(
    `to_tsvector('english',
      a.title || ' ' || a.body)
    @@ plainto_tsquery('english', :query)`,
    { query: "database optimization" }
  )
  .getMany();

MikroORM

const results = await orm.em
  .createQueryBuilder(Article, "a")
  .where(
    `to_tsvector('english', a.title || ' ' || a.body)
     @@ plainto_tsquery('english', ?)`,
    ["database optimization"],
  )
  .getResultList();

Sequelize

import { literal } from "sequelize";

const results = await Article.findAll({
  where: literal(`
    to_tsvector('english', title || ' ' || body)
    @@ plainto_tsquery('english', 'database optimization')
  `),
});

Database Views

SQL

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE is_active = true;

SELECT * FROM active_users
WHERE name LIKE 'A%';

Django ORM

# Create view via migration RunSQL
# Map to an unmanaged model
class ActiveUser(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField()

    class Meta:
        managed = False
        db_table = "active_users"

# Query like any model
users = ActiveUser.objects.filter(
    name__startswith="A"
)

SQLAlchemy

from sqlalchemy import select

# Map view to a model
class ActiveUser(Base):
    __tablename__ = "active_users"

    id: Mapped[int] = mapped_column(
        primary_key=True
    )
    name: Mapped[str]
    email: Mapped[str]

# Query like any model
users = session.scalars(
    select(ActiveUser).where(
        ActiveUser.name.like("A%")
    )
).all()

SQLModel

from sqlmodel import SQLModel, Field, select


# Create view via migration SQL
class ActiveUser(SQLModel, table=True):
    __tablename__ = "active_users"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str


# Query like any model
users = session.exec(
    select(ActiveUser).where(
        ActiveUser.name.startswith("A")
    )
).all()

Tortoise ORM

# Create view via migration raw SQL
# Map to an unmanaged model
from tortoise.models import Model
from tortoise import fields

class ActiveUser(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=100)
    email = fields.CharField(max_length=255)

    class Meta:
        table = "active_users"
        managed = False

# Query like any model
users = await ActiveUser.filter(
    name__startswith="A"
)

Peewee

from peewee import CharField, IntegerField, Model

# Create view via SQL migration
# Map to read-only model
class ActiveUser(Model):
    id = IntegerField(primary_key=True)
    name = CharField(max_length=100)
    email = CharField(max_length=255)

    class Meta:
        table_name = "active_users"


# Query like any model
users = ActiveUser.select().where(
    ActiveUser.name.startswith("A")
)

Prisma

// schema.prisma:
// view ActiveUser {
//   id    Int    @id
//   name  String
//   email String
//   @@map("active_users")
// }

const users = await prisma.activeUser.findMany({
  where: { name: { startsWith: "A" } },
});

Drizzle

import { pgView } from "drizzle-orm/pg-core";
import { eq, like } from "drizzle-orm";

const activeUsers = pgView("active_users").as(
  (qb) =>
    qb
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
      })
      .from(users)
      .where(eq(users.isActive, true))
);

const result = await db
  .select()
  .from(activeUsers)
  .where(like(activeUsers.name, "A%"));

Kysely

// Map view in DB types
interface DB {
  active_users: {
    id: number
    name: string
    email: string
  }
}

// Query like any table
const users = await db
  .selectFrom('active_users')
  .selectAll()
  .where('name', 'like', 'A%')
  .execute()

TypeORM

@ViewEntity({
  name: "active_users",
  expression: `
    SELECT id, name, email
    FROM users WHERE is_active = true
  `,
})
class ActiveUser {
  @ViewColumn()
  id: number;

  @ViewColumn()
  name: string;

  @ViewColumn()
  email: string;
}

const users = await dataSource
  .getRepository(ActiveUser)
  .createQueryBuilder("u")
  .where("u.name LIKE :name", { name: "A%" })
  .getMany();

MikroORM

import { Entity, PrimaryKey, Property } from "@mikro-orm/core";

// Create view via migration SQL and map as read-only entity
@Entity({ tableName: "active_users", readonly: true })
class ActiveUser {
  @PrimaryKey()
  id!: number;

  @Property({ length: 100 })
  name!: string;

  @Property({ length: 255 })
  email!: string;
}

const users = await orm.em.find(ActiveUser, {
  name: { $like: "A%" },
});

Sequelize

import { DataTypes, Model, Op } from "sequelize";

// Create view via migration SQL and map it as read-only model
class ActiveUser extends Model {
  declare id: number;
  declare name: string;
  declare email: string;
}

ActiveUser.init(
  {
    id: { type: DataTypes.INTEGER, primaryKey: true },
    name: { type: DataTypes.STRING },
    email: { type: DataTypes.STRING },
  },
  {
    sequelize,
    tableName: "active_users",
    timestamps: false,
  },
);

const users = await ActiveUser.findAll({
  where: { name: { [Op.like]: "A%" } },
});

Introspect (DB → Models)

SQL

$ pg_dump --schema-only --table=users mydb

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Generated by: pg_dump --schema-only

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Django ORM

$ python manage.py inspectdb

# This inspects the database tables and outputs
# Django model classes to stdout
# Redirect to a file:
# python manage.py inspectdb > models.py

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

# Generated by: python manage.py inspectdb

from django.db import models


class Users(models.Model):
    name = models.CharField(max_length=100)
    email = models.CharField(unique=True, max_length=254)
    created_at = models.DateTimeField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = "users"

SQLAlchemy

# No built-in introspection — sqlacodegen is the most popular tool
$ pip install sqlacodegen
$ sqlacodegen postgresql://user:pass@localhost/mydb

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

# Generated by: sqlacodegen

from sqlalchemy import Column, DateTime, Integer, String
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(254), nullable=False, unique=True)
    created_at = Column(DateTime(timezone=True))

SQLModel

# No built-in introspection — sqlacodegen is the most popular tool
$ pip install sqlacodegen
$ sqlacodegen --generator sqlmodels postgresql://user:pass@localhost/mydb

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

# Generated by: sqlacodegen --generator sqlmodels

from datetime import datetime
from typing import Optional

from sqlmodel import Field, SQLModel


class Users(SQLModel, table=True):
    __tablename__ = "users"

    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=100)
    email: str = Field(max_length=254, unique=True)
    created_at: Optional[datetime] = None

Tortoise ORM

# No built-in introspection — aerich is the migration/introspection tool
$ pip install aerich
$ aerich --app models inspectdb -t user

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

# Generated by: aerich inspectdb

from tortoise import fields
from tortoise.models import Model


class Users(Model):
    id = fields.IntField(primary_key=True)
    name = fields.CharField(max_length=100)
    email = fields.CharField(max_length=254, unique=True)
    created_at = fields.DatetimeField(null=True)

    class Meta:
        table = "users"

Peewee

$ python -m pwiz -e postgresql -H localhost mydb

# Built-in — no extra install needed
# Supports: postgresql, mysql, sqlite, cockroachdb

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

# Generated by: python -m pwiz

from peewee import *

database = PostgresqlDatabase("mydb")


class BaseModel(Model):
    class Meta:
        database = database


class Users(BaseModel):
    name = CharField(max_length=100)
    email = CharField(max_length=254, unique=True)
    created_at = DateTimeField(null=True)

    class Meta:
        table_name = "users"

Prisma

$ npx prisma db pull

# Introspects the database defined in schema.prisma
# and updates the data model in schema.prisma

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

// Generated by: npx prisma db pull

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model users {
  id         Int       @id @default(autoincrement())
  name       String    @db.VarChar(100)
  email      String    @unique @db.VarChar(254)
  created_at DateTime? @default(now()) @db.Timestamptz
}

Drizzle

$ npx drizzle-kit pull

# Introspects the database and generates schema.ts

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

// Generated by: npx drizzle-kit pull

import { pgTable, serial, timestamp, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  email: varchar("email", { length: 254 }).notNull().unique(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
});

Kysely

# No built-in introspection — kysely-codegen is the most popular tool
$ npm install kysely-codegen
$ npx kysely-codegen --dialect postgres

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

// Generated by: npx kysely-codegen

import type { ColumnType } from "kysely";

export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
  ? ColumnType<S, I | undefined, U>
  : ColumnType<T, T | undefined, T>;

export type Timestamp = ColumnType<Date, Date | string, Date | string>;

export interface Users {
  id: Generated<number>;
  name: string;
  email: string;
  created_at: Timestamp | null;
}

export interface DB {
  users: Users;
}

TypeORM

# No built-in introspection — typeorm-model-generator is the most popular tool
$ npx typeorm-model-generator \
    -e postgres -h localhost \
    -d mydb -u user -x pass

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

// Generated by: typeorm-model-generator

import { Column, Entity, Index, PrimaryGeneratedColumn } from "typeorm";

@Entity("users")
export class Users {
  @PrimaryGeneratedColumn()
  id: number;

  @Column("varchar", { length: 100 })
  name: string;

  @Index({ unique: true })
  @Column("varchar", { length: 254 })
  email: string;

  @Column("timestamptz", { nullable: true })
  created_at: Date | null;
}

MikroORM

# Separate package required
$ npm install @mikro-orm/entity-generator
$ npx mikro-orm generate-entities --save --path=./entity

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

// Generated by: mikro-orm generate-entities

import { Entity, PrimaryKey, Property } from "@mikro-orm/core";

@Entity({ tableName: "users" })
export class Users {
  @PrimaryKey()
  id!: number;

  @Property({ length: 100 })
  name!: string;

  @Property({ length: 254, unique: true })
  email!: string;

  @Property({ type: "timestamptz", nullable: true })
  createdAt?: Date;
}

Sequelize

# No built-in introspection — sequelize-auto is the most popular tool
$ npm install sequelize-auto
$ npx sequelize-auto \
    -h localhost -d mydb \
    -u user -x pass \
    --dialect postgres -l ts

-- Source table to introspect
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

// Generated by: sequelize-auto

import { DataTypes, Model } from "sequelize";
import type { Sequelize } from "sequelize";

export class Users extends Model {
  declare id: number;
  declare name: string;
  declare email: string;
  declare created_at: Date | null;
}

export function initModels(sequelize: Sequelize) {
  Users.init(
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      name: {
        type: DataTypes.STRING(100),
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING(254),
        allowNull: false,
        unique: true,
      },
      created_at: {
        type: DataTypes.DATE,
        allowNull: true,
      },
    },
    { sequelize, tableName: "users" }
  );
}