I always have a heavy feeling in my stomach when I am working on production. There is this voice yelling at me: “Be careful! Don’t screw this up!” This keeps me on my toes and I remember to take a little more care than on staging.
Sometimes, I do want to connect to the production database and investigate: How many records in this table? How long does this query take? …
When taking such adventures, I only want to read data and not screw anything up. So I want a way to keep myself from destroying something accidentally.
Luckily, there is this (works PostgreSQL and MySQL):
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
After running this command, you only have read access (only in the current database session). Now, I can do my production research without the voice screaming at me. If I then want to make changes, I can either start a new session or run the following command:
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
This is so useful, that I have the read-only statement as a start-up script in my database GUI (I use TablePlus). This way I can not forget to execute it.
Happy hacking!