I’m having one database disaster after another. Contrary to rumor, Alembic and Postgres will oftentimes run amok. I got it, “That’s by design.” Tables get created during an Alembic upgrade that aren’t explicitly mentioned in any version files but are defined as relationships in your models. Like in life, watch your relationships.

There ain’t nothing like doing a migration and waking up next to a hundred other tables.

Anyway, this is my shot at creating a process to control the Alembic Beast.

The most important command is:

alembic upgrade <specific alembic revision id> --sql > migration_preview.sql

And never use

alembic upgrade head

By adding –sql > migration_preview.sql, you’ll get a preview of the what alembic upgrade is going to run so that there are no surprises.

To Reach PSQL CLI Inside Devcontainer

psql -h db -p 5432 -U postgres -d postgres

*You May Have To Install PSQL Client First*

apt-get install -y --no-install-recommends \   postgresql-client

To Start Over In A Clean Postgres Database

DROP SCHEMA public CASCADE;

CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO public;

To Get A Glimpse At the Alembic Version That Will Cause your Eventual Downfall

alembic revision --autogenerate -m "some meaningless message"

For A Sneak Preview Of A Specific Alembic Upgrade

alembic upgrade <specific alembic revision id> --sql > migration_preview.sql

To Only Upgrade A Specific Migration File

alembic upgrade <revision id>

For A Sneak Preview if you’re going to be foolish enough to let alembic do it all

alembic upgrade head --sql > migration_preview.sql

The above will create an sql file that will show you all table commands that are gonna be fun.

To Finalize The Table Creation After You Miss Something Obvious That Will Waste Hours of Your Life Down The Road - Never Use Head

alembic upgrade <specific revision id>

To get into database with password

psql -U bread_user -d bread_db -h localhost

psql -U user_name -d databasename_db -h localhost

Find Your Password Postgres After You Swore You’d Never Forget It

check the docker-compose.yml file

If you’re lucky, you hard-coded the password into the docker-compose file. Oh yeah, probably not a good idea when you moved into production. Instead, use environment variables and, ideally, a secrets management system like Docker Secrets or Vault. This prevents the password from being exposed in plain text within your container configuration files or image.

Common PostgreSQL Commands (Terminal/psql)

To switch to superuser from linux command line

sudo -u postgres psql

Basic Connection and Navigation:

  • psql: Opens the psql interactive terminal (default connection).
  • psql -U <username> -d <database_name>: Connect to a specific database as a user.
  • psql -h <hostname> -p <port> -U <username> -d <database_name>: Connect to a remote server.
  • \q or \quit: Exit psql.
  • \l or \list: List databases.
  • \c <database_name> or \connect <database_name>: Connect to a database.
  • \dt: List tables.
  • \dv: List views.
  • \df: List functions.
  • \du: List users (roles).
  • \h: Help for SQL commands.
  • \?: Help for psql meta-commands.
  • \e: Open query in editor.
  • \s: Display query history.
  • \timing: Toggle query timing.

Data Manipulation and Queries:

  • Don’t forget a semi-colon (;) at the end of the line/command

  • SELECT * FROM <table_name>;: Retrieve all data.
  • SELECT column1, column2 FROM <table_name> WHERE condition;: Retrieve specific columns.
  • INSERT INTO <table_name> (column1, column2) VALUES (value1, value2);: Insert data.
  • UPDATE <table_name> SET column1 = value1 WHERE condition;: Update data.
  • DELETE FROM <table_name> WHERE condition;: Delete data.
  • CREATE TABLE <table_name> (column1 type, column2 type, ...);: Create a table.
  • DROP TABLE <table_name>;: Delete a table.
  • ALTER TABLE <table_name> ADD COLUMN column3 type;: Add a column.
  • ALTER TABLE <table_name> DROP COLUMN column3;: Delete a column.
  • CREATE INDEX index_name ON table_name (column_name);: Create an index.
  • EXPLAIN SELECT * FROM table_name WHERE condition;: Show query plan.
  • BEGIN; ... COMMIT;: Start and commit a transaction.
  • BEGIN; ... ROLLBACK;: Start and rollback a transaction.

User and Role Management:

  • CREATE USER <username> WITH PASSWORD '<password>';: Create a user.
  • ALTER USER <username> WITH PASSWORD '<new_password>';: Change user password.
  • DROP USER <username>;: Delete a user.
  • GRANT <privilege> ON <object> TO <user/role>;: Grant privileges.
  • REVOKE <privilege> ON <object> FROM <user/role>;: Revoke privileges.
  • CREATE ROLE <role_name> WITH LOGIN;: create a role that can login.
  • GRANT <role_name> to <user_name>;: Grants a role to a user.

Database Management:

  • CREATE DATABASE <database_name>;: Create a database.
  • DROP DATABASE <database_name>;: Delete a database.
  • \set AUTOCOMMIT off: Disable autocommit.
  • \set AUTOCOMMIT on: Enable autocommit.

Visit Emlekezik.com