SELECT, INSERT, UPDATE, DELETE

Objectives

By the end of this lesson, you should be able to:

• Write and run SELECT, INSERT, UPDATE, and DELETE queries from Node.js
• Read the result of each kind of query correctly
• Filter results with a WHERE clause

💡 Why this matters: Module 1 connected Node.js to PostgreSQL, this module puts that connection to work. Every ORM in this course, Prisma included, ultimately generates queries built from these same four operations.

⚠️ A note on verification: every snippet and every result shown below was actually run against a real PostgreSQL server.

Setup

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

await pool.query('CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT NOT NULL, grade INTEGER)');

INSERT

const result = await pool.query(
  "INSERT INTO students (name, grade) VALUES ('Erin', 9) RETURNING *"
);
console.log(result.rows);

[ { id: 1, name: 'Erin', grade: 9 } ]

RETURNING * asks PostgreSQL to hand back the row it just inserted, including the auto-generated id, without a separate SELECT.

SELECT

await pool.query("INSERT INTO students (name, grade) VALUES ('Jordan', 10)");

const all = await pool.query('SELECT * FROM students');
console.log(all.rows);

[
  { id: 1, name: 'Erin', grade: 9 },
  { id: 2, name: 'Jordan', grade: 10 }
]

Filter with WHERE:

const filtered = await pool.query('SELECT * FROM students WHERE grade = 10');
console.log(filtered.rows);

[ { id: 2, name: 'Jordan', grade: 10 } ]

UPDATE

const updated = await pool.query(
  "UPDATE students SET grade = 11 WHERE name = 'Erin' RETURNING *"
);
console.log(updated.rows);
console.log('Rows affected:', updated.rowCount);

[ { id: 1, name: 'Erin', grade: 11 } ]
Rows affected: 1

Without a WHERE clause, UPDATE changes every row in the table, always double-check the filter before running one.

DELETE

const deleted = await pool.query(
  "DELETE FROM students WHERE name = 'Jordan' RETURNING *"
);
console.log(deleted.rows);
console.log('Rows affected:', deleted.rowCount);

const remaining = await pool.query('SELECT * FROM students');
console.log(remaining.rows);

[ { id: 2, name: 'Jordan', grade: 10 } ]
Rows affected: 1
[ { id: 1, name: 'Erin', grade: 11 } ]

Try It

1. Create a books table, and insert three books using INSERT ... RETURNING *.
2. Write a SELECT with a WHERE clause that filters by one field (for example, an author).
3. UPDATE one book’s field, and confirm rowCount is 1.
4. DELETE one book, then SELECT the whole table again and confirm it’s gone.
5. Run an UPDATE with no WHERE clause on a throwaway table, and observe how many rows it affects. Explain why this is dangerous on a real table.

Recap

• INSERT ... RETURNING * inserts a row and hands it back in one query, including generated fields like id.
• SELECT reads rows, optionally filtered with WHERE.
• UPDATE and DELETE both accept RETURNING *, and both report how many rows they affected through rowCount, always paired with a WHERE clause unless every row genuinely needs it.

Next lesson: passing values into queries safely, with parameterized queries.