Stop 06 · SQL DatabasesTopics · 12/14

Stored Procedures

In short

Some logic can live inside the database itself. Stored procedures run right next to the data, which saves round trips for multi-step jobs.

Precompiled SQL logic stored and executed directly inside the database — can improve performance for complex operations but couples logic to a specific database.

The blob pours tea at a roadside chai stall for a regular customer.

Think of it like this

The chaiwala's 'usual'

A regular at the tea stall just says 'the usual'. The chaiwala already knows: less sugar, extra adrak, glass not cup. One word instead of five instructions. A stored procedure is your 'usual': a named recipe saved in the database, run with one call.

Watch it happen

One call, three steps inside

API

Postgres

CALL place_order(7, 3)

1/5The API sends one call: user 7 wants dish 3.

The big ideas

What you need to know

Code saved in the database

Written in PL/pgSQL for Postgres. Procedures run with CALL; functions return values and can be used inside queries.

Fewer round trips

Several steps run inside the database, which helps when your server and database are far apart.

Shared rules

Every app using the database gets the same logic, whatever language it's written in.

Harder to test and review

Logic in the database is easy to miss in code review. Version it in migrations like any other code.

See it in code

Walk through the code

sql

1CREATE PROCEDURE place_order(p_user INT, p_dish INT)2LANGUAGE plpgsql AS $$3BEGIN4  UPDATE stock SET qty = qty - 15  WHERE dish_id = p_dish AND qty > 0;6 7  IF NOT FOUND THEN8    RAISE EXCEPTION 'Dish % is out of stock', p_dish;9  END IF;10 11  INSERT INTO orders (user_id, dish_id) VALUES (p_user, p_dish);12END;13$$;14 15CALL place_order(7, 3);

1/4Define a procedure with two inputs. $$ marks where its body starts and ends.

PL/pgSQL is Postgres's built-in procedure language. MySQL has stored procedures too, with different syntax.

Save yourself the pain

Common mistakes

Oops

Moving all business logic into stored procedures.

Do this instead

Keep most rules in your app, where tests and logs are easy. Use procedures for tight, data-heavy steps.

Oops

Editing a procedure directly on the production database.

Do this instead

Put CREATE OR REPLACE PROCEDURE in a migration file so every environment gets the same version.

Your mission

Your first procedure

~25 min

Quick check

Why might a stored procedure beat running the same three queries from Node?

Go deeper

1Write a simple stored procedure that performs a multi-step operation.
2Learn the trade-off: stored procedures are fast but harder to version-control and test than application code.
3Understand why most modern teams keep business logic in the application layer instead.