3.4SQL with Node: PostgreSQL, MySQL, Prisma
The essentials in 30 seconds
- Relational structures: tables, rows, foreign keys (PostgreSQL/MySQL), strict schema and ACID transactions for integrity.
- SQL is written or generated: SELECT/INSERT/UPDATE/DELETE + JOIN as parameterised raw SQL, or ORM (Prisma/Sequelize) methods generating safe SQL.
- Security is non-negotiable: never concatenate user data (SQL injection) — parameterised queries or ORM plus understanding of the underlying SQL.
Running case — Salim invoices in SQL flawlessly
Salim, junior developer in Algiers, must invoice: linked clients, orders and line items, totals to guarantee. Flexible NoSQL is no longer enough. He models three tables linked by foreign keys (PostgreSQL), writes his first parameterised SELECT/JOIN queries, then moves to Prisma: declarative schema.prisma schema, versioning migration, typed prisma.commande.create() and findMany() client. When a search field contains a trapped quote, parameterisation neutralises it: no injection.
Last episode of the Salim thread — from Express to both database families: complete persistence.
1. The relational model & SQL
The SQL / relational family (mainly PostgreSQL, powerful and open source, and MySQL/MariaDB) organises data into tables (typed columns), rows (records) and formal relations via foreign keys (an order references a client). The SQL language queries: SELECT (read), INSERT (create), UPDATE, DELETE, JOIN (combine linked tables). Strong guarantees: strict schema and ACID transactions for consistency — ideal for structured, critical data.
From Node, two levels: the raw driver (e.g. pg for PostgreSQL) where SQL is written in strings — with the imperative parameterised queries ($1, $2…) since concatenating user input opens SQL injection, a critical flaw. Or the ORM generating the SQL for you.
2. Prisma: schema, migrations, typed client
An ORM (Object-Relational Mapper) maps tables ↔ JS objects: you handle prisma.client.create({ data }) and findMany({ where }) instead of SQL strings — more readable, more productive, and protected from injections by automatic parameterisation. The common ones: Prisma (modern, highly regarded) and Sequelize (older, proven).
Prisma stands out with its declarative schema (schema.prisma file describing models and relations), its migrations (versioning schema evolution) and its generated, typed client (autocompletion, type safety — excellent with TypeScript, section 5-1). Recommended for most apps; keep raw SQL for very specialised queries. Salim’s habit: understand the generated SQL (load relations efficiently, avoid N+1) instead of treating the ORM as a black box.
🔎 Concrete example — Prisma: a typed, safe CRUD
Salim creates and lists clients without writing SQL.
// schema.prisma declares linked Client and Commande (1-N)
const nouveau = await prisma.client.create({
data: { nom: 'Salim', email: 'salim@fictitious-example.test' }
});
const actifs = await prisma.client.findMany({
where: { actif: true }
});
// Equivalent SAFE raw (pg) — never any concatenation:
// await pool.query('SELECT * FROM clients WHERE email = $1', [emailSaisi]);Reading: objects and methods on the Prisma side, parameterised $1 on the raw side.
Lesson: both paths neutralise injection; concatenation, never.
Section vocabulary
- SQL / relational
- Databases in tables, rows and foreign keys (PostgreSQL, MySQL); strict schema and ACID transactions.
Simple example: Linked clients, orders and line items at Salim’s.
What it is not: flexible documents with no schema.
Reflex: choose it for linked, critical data.
- SQL (language)
- SELECT (read), INSERT (create), UPDATE, DELETE, JOIN (combine linked tables).
Simple example: Salim’s SELECT with commands-clients JOIN.
What it is not: ORM method calls.
Reflex: know how to read a SELECT/JOIN even with an ORM.
- SQL injection
- Critical flaw: concatenated input becomes executed code; prevented by parameterisation.
Simple example: A trapped quote neutralised by $1.
What it is not: a mere harmless display error.
Reflex: never concatenate, always parameterise.
- ORM (Prisma, Sequelize)
- Maps tables ↔ JS objects and generates SQL: readable, safe, productive methods.
Simple example: prisma.client.findMany({ where }) at Salim’s.
What it is not: hand-concatenated raw SQL.
Reflex: prefer the ORM for everyday CRUD.
- Prisma (modern)
- ORM with declarative schema (schema.prisma), versioned migrations and generated typed client.
Simple example: The migration adding the line-items table.
What it is not: a raw driver with no model or type.
Reflex: version each evolution with a migration.
In practice — Persist with a SQL database and an ORM
- Understand the relational model: tables, typed columns, rows, foreign-key relations; and basic SQL operations (SELECT, INSERT, UPDATE, DELETE, JOIN).
- Remember the security imperative: NEVER concatenate user data into SQL (injection) — parameterised queries or ORM.
- Discover a modern ORM: define a schema (Prisma: schema.prisma) and run a CRUD through methods (prisma.user.create/findMany/update/delete).
- Explore Prisma migrations (versioning schema evolution) and the value of the typed client (autocompletion, type safety with TypeScript).
Ready-to-say script — presenting your safe SQL (30 s)
“Invoicing runs on PostgreSQL: clients, orders and line-item tables linked by foreign keys. CRUD goes through Prisma, declarative schema plus migrations, typed client. Remaining raw SQL is parameterised with $1, never concatenated: tested with a trapped input, the injection is neutralised.”
Say in security review; show the schema and one parameterised query.
Copy-paste template — Prisma + parameterised query
// schema.prisma (fictitious excerpt):
// model Client { id Int @id @default(autoincrement())
// nom String
// email String @unique
// commandes Commande[] }
async function main() {
const nouveau = await prisma.client.create({
data: { nom: 'Salim', email: 'salim@fictitious-example.test' }
});
const liste = await prisma.client.findMany({ where: { actif: true } });
await prisma.client.update({
where: { email: 'salim@fictitious-example.test' },
data: { actif: false }
});
// Equivalent SAFE raw (pg) — never any concatenation:
// await pool.query('SELECT * FROM clients WHERE email = $1', [emailSaisi]);
}
main().catch(console.error);Freelancer
Invoice in PostgreSQL + Prisma: three linked tables and one typed CRUD are enough.
SME in Algiers
Salim versions each column addition with a migration reviewed before deployment.
Product team
Enforce the shared typed client for consistent queries across teams.
Beginner
This week: one Prisma model, one create and one findMany with where.
✓ Best practices
- Use a modern ORM (Prisma) for productivity and security.
- Always parameterised queries (never concatenation) against SQL injection.
- Understand the underlying SQL and load relations efficiently (avoid N+1).
✗ Common pitfalls
- Concatenating user data into a SQL query (critical injection).
- Treating the ORM as a black box (inefficient queries, N+1).
- Ignoring SQL basics while relying fully on the ORM.
Key takeaways
- SQL / RELATIONAL (PostgreSQL, MySQL): data in TABLES/ROWS with FOREIGN-KEY relations; strong guarantees (ACID).
- SQL language: SELECT/INSERT/UPDATE/DELETE + JOIN. CRITICAL security: never concatenate user data (SQL INJECTION).
- An ORM (PRISMA, Sequelize) maps tables ↔ objects and generates SQL: readable, injection-safe, productive.
- PRISMA: declarative schema + migrations + TYPED client. Mastering SQL + NoSQL = complete persistence toolbox.
48h challenge — apply tomorrow
Declare 1 Prisma model and run a create + findMany, then rewrite one SELECT as parameterised $1.
- I name tables, foreign keys and ACID.
- My raw SQL is parameterised, never concatenated.
- My Prisma CRUD goes through schema + migration.
Frequently asked questions
Raw SQL driver or ORM (Prisma/Sequelize): which to choose?
For most apps, an ORM: productivity (concise prisma.user.create()), security (automatic anti-injection parameterisation), readability and simple relations. The raw driver (pg) keeps its place for fine control and specialised queries. Salim combines: ORM daily, parameterised SQL for complex JOIN reports.
What is SQL injection and how to protect against it?
A concatenated user input in SQL can become executed code (auth bypass, data reading or destruction). Danger example: ' OR '1'='1 slipped into an email. Protection: parameterised queries ($1 placeholders separating code and data) and ORMs parameterising by default. Never concatenate, even in tests — fundamental security knowledge.
Which practice protects against SQL injection in Node?