Raw Queries

SELECT id, name, email
FROM users
WHERE created_at > '2024-01-01';

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute(
        "SELECT id, name, email FROM users "
        "WHERE created_at > '2024-01-01'"
    )
    rows = cursor.fetchall()

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text(
            "SELECT id, name, email FROM users "
            "WHERE created_at > '2024-01-01'"
        )
    )
    rows = result.fetchall()

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text(
            "SELECT id, name, email FROM users "
            "WHERE created_at > '2024-01-01'"
        )
    )
    rows = result.fetchall()

from tortoise import connections

conn = connections.get("default")
rows = await conn.execute_query_dict(
    "SELECT id, name, email FROM users "
    "WHERE created_at > '2024-01-01'"
)

cursor = db.execute_sql(
    "SELECT id, name, email FROM users "
    "WHERE created_at > '2024-01-01'"
)
rows = cursor.fetchall()

const users = await prisma.$queryRaw`
  SELECT id, name, email FROM users
  WHERE created_at > '2024-01-01'
`;

import { sql } from "drizzle-orm";

const users = await db.execute(
  sql`SELECT id, name, email FROM users
      WHERE created_at > '2024-01-01'`
);

import { sql } from 'kysely'

const rows = await sql<{
  id: number
  name: string
  email: string
}[]>`
  SELECT id, name, email
  FROM users
  WHERE created_at > '2024-01-01'
`.execute(db)

const users = await dataSource.query(
  `SELECT id, name, email FROM users
   WHERE created_at > '2024-01-01'`
);

const rows = await orm.em.getConnection().execute(
  `SELECT id, name, email FROM users
   WHERE created_at > '2024-01-01'`,
);

const [rows] = await sequelize.query(`
  SELECT id, name, email
  FROM users
  WHERE created_at > '2024-01-01'
`);

-- PostgreSQL positional parameters
SELECT id, name, email
FROM users
WHERE age > $1 AND city = $2;
-- e.g. [25, 'NYC']

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute(
        "SELECT id, name, email FROM users "
        "WHERE age > %s AND city = %s",
        [25, "NYC"],
    )
    rows = cursor.fetchall()

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text(
            "SELECT id, name, email FROM users "
            "WHERE age > :age AND city = :city"
        ),
        {"age": 25, "city": "NYC"},
    )
    rows = result.fetchall()

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text(
            "SELECT id, name, email FROM users "
            "WHERE age > :age AND city = :city"
        ),
        {"age": 25, "city": "NYC"},
    )
    rows = result.fetchall()

from tortoise import connections

conn = connections.get("default")
_, rows = await conn.execute_query(
    "SELECT id, name, email FROM users "
    "WHERE age > $1 AND city = $2",
    [25, "NYC"],
)

cursor = db.execute_sql(
    "SELECT id, name, email FROM users "
    "WHERE age > %s AND city = %s",
    (25, "NYC"),
)
rows = cursor.fetchall()

const age = 25;
const city = "NYC";

const users = await prisma.$queryRaw`
  SELECT id, name, email FROM users
  WHERE age > ${age} AND city = ${city}
`;

import { sql } from "drizzle-orm";

const age = 25;
const city = "NYC";

const users = await db.execute(
  sql`SELECT id, name, email FROM users
      WHERE age > ${age} AND city = ${city}`
);

import { sql } from 'kysely'

const rows = await sql<{
  id: number
  name: string
  email: string
}[]>`
  SELECT id, name, email
  FROM users
  WHERE age > ${25} AND city = ${'NYC'}
`.execute(db)

const users = await dataSource.query(
  `SELECT id, name, email FROM users
   WHERE age > $1 AND city = $2`,
  [25, "NYC"]
);

const rows = await orm.em.getConnection().execute(
  `SELECT id, name, email FROM users
   WHERE age > ? AND city = ?`,
  [25, "NYC"],
);

const [rows] = await sequelize.query(
  `SELECT id, name, email FROM users
   WHERE age > :age AND city = :city`,
  {
    replacements: { age: 25, city: "NYC" },
  },
);

-- No model layer in raw SQL;
-- application code maps rows to objects
SELECT * FROM users WHERE age > 25;

# .raw() returns User model instances
users = User.objects.raw(
    "SELECT * FROM users WHERE age > %s",
    [25],
)
for user in users:
    print(user.name)  # User instance

from sqlalchemy import select, text

with Session(engine) as session:
    users = session.execute(
        select(User).from_statement(
            text("SELECT * FROM users "
                 "WHERE age > :age")
        ),
        {"age": 25},
    ).scalars().all()
    # Returns User model instances

from sqlalchemy import text
from sqlmodel import select

with Session(engine) as session:
    users = session.exec(
        select(User).from_statement(
            text("SELECT * FROM users WHERE age > :age")
        ).params(age=25)
    ).all()
    # Returns User model instances

# .raw() returns model instances
users = await User.raw(
    "SELECT * FROM users WHERE age > 25"
)
for user in users:
    print(user.name)  # User instance

# .raw() returns User model instances
users = User.raw(
    "SELECT * FROM users WHERE age > %s",
    25,
)
for user in users:
    print(user.name)  # User instance

// $queryRaw returns typed objects (no classes)
type UserRow = {
  id: number;
  name: string;
  email: string;
};

const users = await prisma.$queryRaw<UserRow[]>`
  SELECT * FROM users WHERE age > ${25}
`;

// Alternative: no built-in raw-to-schema mapping;
// Drizzle returns typed rows from query builder
import { gt } from "drizzle-orm";

const result = await db
  .select()
  .from(users)
  .where(gt(users.age, 25));
// Returns typed rows matching schema

import { sql } from 'kysely'

// Kysely has no model instances; returns typed rows.
type UserRow = {
  id: number
  name: string
  email: string
}

const users = await sql<UserRow[]>`
  SELECT * FROM users WHERE age > ${25}
`.execute(db)

// query() returns plain objects;
// map to entities manually
const raw = await dataSource.query(
  "SELECT * FROM users WHERE age > $1",
  [25]
);
const users = raw.map((row) =>
  Object.assign(new User(), row)
);

const rows = await orm.em.getConnection().execute(
  "SELECT * FROM users WHERE age > ?",
  [25],
);

const users = rows.map((row) => orm.em.map(User, row));
// Returns User entity instances

// query() can map rows back to model instances
const users = await sequelize.query(
  "SELECT * FROM users WHERE age > :age",
  {
    replacements: { age: 25 },
    model: User,
    mapToModel: true,
  },
);
for (const user of users) {
  console.log(user.name); // User instance
}