Connecting from Python and Node.js

~12 min · apps, drivers

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Drivers, not magic

Every language has a Postgres driver. The best ones (psycopg for Python, pg for Node, sqlx for Rust, pgx for Go, libpq for C) speak the wire protocol directly, support binary parameter encoding, and integrate with connection pools. ORMs and query builders all sit on top of these.

Connection strings

The standard URL form: postgresql://user:pass@host:5432/dbname?sslmode=require. Memorise the parts. The same string works in psql, in any driver, in pgAdmin, anywhere.

Always parameterise

Driver-level parameter binding is your primary defence against SQL injection. Never concatenate user input into a SQL string. Pass it as a parameter; the driver and the database handle escaping correctly.

Code

psycopg (Python)·python

import psycopg
with psycopg.connect("postgresql://app:secret@localhost:5432/mydb") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
            ("Alice", "alice@example.com"),
        )
        new_id = cur.fetchone()[0]

pg (Node.js)·javascript

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

const { rows } = await pool.query(
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
  ['Alice', 'alice@example.com']
);
const newId = rows[0].id;

Always parameterise — never concatenate·python

# DANGEROUS — SQL injection waiting to happen
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")

# SAFE — driver escapes correctly
cur.execute("SELECT * FROM users WHERE email = %s", (email,))

External links

Exercise

Write a 20-line script in your favourite language that connects to Postgres, runs a parameterised SELECT, and prints the rows. Then write the same script with string concatenation; consciously feel how easy it would be to introduce a bug.

Progress

Progress is local-only — sign in to sync across devices.

← 🧩 Extensions and Semi-Structured DataQuiz · 5 questions Next →Raw SQL vs ORMs

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.