SQL from Applications

After this lesson, you will be able to: Use SQL from Node.js (node-postgres), Python (psycopg), and Prisma; avoid SQL injection.

The last mile: SQL from your application code. Get parameterization right and you avoid 90% of security + correctness bugs.

Prerequisites:PostgreSQL Features

Node.js — node-postgres

The standard Postgres driver for Node.

python

// npm i pg
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// PARAMETERIZED query, never string-interpolate!
async function findUser(email: string) {
  const result = await pool.query(
    'SELECT id, name FROM users WHERE email = $1',
    [email]
  );
  return result.rows[0];
}

// Transaction
async function transfer(fromId: number, toId: number, amount: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

Python — psycopg 3

Modern Postgres driver for Python.

python

# pip install psycopg[binary]
import psycopg, os

with psycopg.connect(os.environ['DATABASE_URL']) as conn:
    with conn.cursor() as cur:
        # PARAMETERIZED, %s is a placeholder, NOT printf format
        cur.execute('SELECT id, name FROM users WHERE email = %s', (email,))
        row = cur.fetchone()

        cur.execute(
            'INSERT INTO posts (user_id, title, body) VALUES (%s, %s, %s) RETURNING id',
            (user_id, title, body)
        )
        new_id = cur.fetchone()[0]
    # auto COMMIT on `with conn` exit; ROLLBACK on exception

ORMs — Prisma (TypeScript)

Type-safe query builder; generates types from schema.

python

// schema.prisma
// model User {
//   id    Int    @id @default(autoincrement())
//   email String @unique
//   name  String
//   posts Post[]
// }

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Type-safe queries, auto-completed, validated at compile time
const user = await prisma.user.findUnique({ where: { email: '[email protected]' } });
const usersWithPosts = await prisma.user.findMany({ include: { posts: true } });
const created = await prisma.user.create({ data: { email: '[email protected]', name: 'B' } });

// Raw SQL escape hatch when you need it
const rows = await prisma.$queryRaw<{ id: number; cnt: bigint }[]>`
  SELECT user_id AS id, COUNT(*) AS cnt FROM posts GROUP BY user_id
`;

ORM vs raw SQL — when to pick which

ORM (Prisma, SQLAlchemy, EF Core): type safety, less boilerplate, easier migrations. Default for new app code. Query builder (Knex, Kysely): safer than raw SQL, more SQL-shaped than ORM. Good middle ground. Raw SQL (node-postgres, psycopg directly): max control, max perf, every feature available. Reach for it for complex queries an ORM mangles.

Common mistakes

String interpolation of user input → SQL injection. ORM lazy loading triggering N+1 queries (Prisma uses `include` to fix; EF uses `Include()`). Not using a connection pool (each query opens new connection = slow + can exhaust DB). Leaving transactions open in error paths (always wrap in try/finally + release).

←PostgreSQL-Specific Features

Back to SQL

Database Performance→