Lasted edited: February 25, 2025

A work in progress. Maybe.

PostgreSQL

To Reach PSQL CLI Inside Devcontainer

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

You May Have To Install PSQL Client

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

When changes are made to the models

Generate a new migration

alembic revision --autogenerate -m "Description of your changes"

Apply the migration

alembic upgrade head

To get into database with password

psql -U bread_user -d bread_db -h localhost

psql -U user_name -d databasename_db -h localhost

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.

React

Rebuild frontend changes

cd ~/projects/bread/frontend

npm run build

Remove old files (optional, but recommended)

sudo rm -rf /var/www/recipes/html/*

Copy new files

sudo cp -r dist/* /var/www/recipes/html/

Set permissions

sudo chown -R www-data:www-data /var/www/recipes/html/

sudo chmod -R 755 /var/www/recipes/html/

Restarting Server

Kill any existing uvicorn processes

pkill -f uvicorn

Start uvicorn with explicit host binding

cd ~/projects/bread/backend

source venv/bin/activate

nohup uvicorn app.main:app --host 127.0.0.1 --port 8000 --forwarded-allow-ips='*' &

Visit Emlekezik.com