It starts like any other day in the development environment. A developer in your team is debugging a tricky issue, SQL client open. They write a quick DELETE FROM orders WHERE id = 123 to clean up some test data.
They highlight the query. They hit Execute.
But their hands were too fast, and they missed the WHERE clause. :]]]
Suddenly, DELETE 15,000 rows flashes on the screen.
In production, strict permissions and backups often save us. But in shared environments like Dev, Staging, or UAT , access is often more open to facilitate speed. A simple "muscle memory" slip by one person can inadvertently wipe out a colleague's test data or corrupt the state of a feature being demoed to a client.
We needed a way to protect the team from these moments of haste without slowing down our workflow.
1. The Solution: A Database Safety Lock
I built a lightweight, pure-SQL solution for PostgreSQL that introduces a Double-Confirmation step for destructive operations.
It works like a safety switch on a power tool. If you try to run an UPDATE or DELETE:
First Attempt: 🛑 BLOCKED. The database triggers an exception:
Second Attempt: ✅ SUCCESS. If you immediately re-run the exact same query (same SQL, same session), the lock unlocks and the query executes.
This simple friction point forces you to pause and think: "Wait, did I mean to run that?"
2. How It Works (Under the Hood)
This isn't a client-side plugin. It's a robust Server-Side Trigger (BEFORE UPDATE OR DELETE) that runs right inside PostgreSQL.
Key features:
- Zero Dependencies: Just plain PL/pgSQL.
-
Session-Aware: It hashes your query (
text) combined with your Session ID (pid). You can only confirm your own queries. -
Smart Bypass: Backend services (like your API server or background workers) are automatically whitelisted via
application_name, so automated tests and app logic aren't blocked. - Concurrency Safe: Multiple developers can use the system continuously without blocking each other.
3. Get the Code
The full source code, installation guide, and technical details are available on my GitHub. It's a single SQL script you can drop into any non-production database.
👉 Get PostgreSQL Safety Lock on GitHub
Give it a try and let me know if it saves your dev data (or your sanity)!
Top comments (0)