Database Related

Install Postgres in Github Codespace

sudo apt install postgresql
sudo service postgresql start

Init Database

Create Database

create database and user using cmd below and update the values into .env file

USER_NAME=fastapi
USER_PASSWORD=fastapi
DB_NAME=app
sudo su - postgres -c "psql -c \"CREATE USER $USER_NAME WITH PASSWORD '$USER_PASSWORD';\""
sudo su - postgres -c "psql -c 'ALTER ROLE $USER_NAME SUPERUSER;'"
sudo su - postgres -c "psql -c 'CREATE DATABASE $DB_NAME OWNER $USER_NAME;'"
sudo su - postgres -c "psql -c 'GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $USER_NAME;'"
sudo su - postgres -c "psql -c 'CREATE DATABASE ${DB_NAME}_test OWNER $USER_NAME;'"
sudo su - postgres -c "psql -c 'GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME}_test TO $USER_NAME;'"

SQLALCHEMY_URI=postgresql+asyncpg://fastapi:fastapi@localhost:5432/app

Create Tables

uv run alembic upgrade head

View Schema Change History

To show all revisions

uv run alembic history

Uprade Table Schema

To generate a new revision

uv run alembic revision --autogenerate -m "Added New table"
uv run alembic upgrade head

Downgrade Table Schema

To rollback to brand new

uv run alembic downgrade base