DevSoc — Developer Society

Coming from a database course that only covered MySQL? Here's everything you need to be productive with PostgreSQL in your first DevSoc project team.

The Key Differences That Will Bite You

Most MySQL habits transfer fine to PostgreSQL, but a few will cause confusing bugs. The biggest: PostgreSQL is case-sensitive for identifiers by default. If you CREATE TABLE User, you must always quote it as `"User"` in queries. The convention in Postgres is snake_case for everything — `user`, `created_at`, `team_member`.

String quoting is different too. PostgreSQL uses single quotes for string literals and double quotes for identifiers. MySQL lets you use either interchangeably. Writing `WHERE name = "alex"` in Postgres will fail with a confusing error about a missing column named `alex`.

Postgres Features You'll Actually Use

The JSONB column type is genuinely useful for semi-structured data that doesn't warrant a separate table. We use it in UniTrack to store flexible metadata on student profiles without schema migrations every time the product team has a new idea.

Common Table Expressions (CTEs) with `WITH` make complex queries readable. Instead of nesting five subqueries, you write a CTE that's almost readable prose. Once you learn them you'll wonder how you survived without them.

sql

-- CTE to find team members with more than 2 submissions
WITH active_teams AS (
  SELECT team_id, COUNT(*) as submission_count
  FROM submissions
  GROUP BY team_id
  HAVING COUNT(*) > 2
)
SELECT u.email, t.name
FROM users u
JOIN team_members tm ON u.id = tm.user_id
JOIN active_teams at ON tm.team_id = at.team_id
JOIN teams t ON t.id = at.team_id;

Managing Migrations

Never alter a production database by hand. Use a migration tool — we use Supabase's built-in migrations, but Flyway and Liquibase are solid choices for self-hosted setups. Every schema change goes through a migration file that's committed to Git alongside the application code.

Write your migrations to be reversible where possible. A `down` migration that can undo a `up` migration has saved us twice in production. The five minutes it takes to write the reverse migration is worth it every single time.

Chris WongBackend Lead · DevSoc

3rd year CS student, backend developer, and database nerd. Maintains UniTrack's database architecture.

PostgreSQL for Students Who Only Know MySQL

The Key Differences That Will Bite You

Postgres Features You'll Actually Use

Managing Migrations

JOIN US