Add the Postgres foreign key indexes your queries need
A foreign key limits which values a row may reference. It doesn't automatically create an index on the columns that hold the reference.
npm install --save-dev @sitecmd/cli
npx sitecmd audit .
The CLI runs on your computer or in your CI, and your code stays there.
What it finds on a demo project
The audit reads your Prisma and Drizzle schemas and reports relation fields with no index covering them, and join tables with no composite unique constraint across their relations.
SiteCMD CLI 2.0.0
Demo project: Prisma schema with foreign keys
npx sitecmd audit .
SiteCMD Code Scan - .
Checked: November 9, 2026
Framework: Express
Issues: 2 total | critical 0 | high 0 | medium 1 | low 1
Medium (1)
- [Database Analysis · architecture] Schema join-style model is missing composite uniqueness - prisma/schema.prisma:31
Low (1)
- [Database Analysis · data] Schema relation fields have no clear index coverage - prisma/schema.prisma:17
Your code passes? Now watch your site.
Does Postgres index foreign key columns automatically?
Postgres requires a suitable unique key on the referenced side, which has index coverage. On the referencing side, add an index only where lookups, joins or changes to parent rows justify it. Postgres documents this distinction.
Review a missing index on a foreign key against real queries
Check the applied schema and plans for typical queries before adding an index. A finding based only on schema files may miss a declaration in a migration. An unused index adds storage and work on writes.
Add a composite unique constraint to a Postgres join table
If you want one membership per pair, add a unique constraint across both relation columns. Repeated pairs may still be intentional.
Frequently asked questions
Does a unique constraint create an index in Postgres?
Yes. Postgres creates a unique B-tree index for every unique constraint and primary key. A composite one works best for lookups that filter on its first column, so a lookup on the second column alone may need its own index.
Check migration history before changing database indexes
Website speed
Find what makes your pages slow enough that visitors leave.
Database migration drift in Prisma and Drizzle
Keep your local database in step with the migrations in your repository.
SQL injection with an ORM
Keep request values out of the raw SQL your ORM runs.