API Types vs PostgreSQL Schema Reliability

Your API types are only as reliable as your PostgreSQL schema — and often less so, because type definitions can drift from the actual database structure. This article explains why that drift happens, how to detect it, and how to keep your types and schema in sync.

Formula

The calculator above computes average pace and speed from a finish time and distance. The formulas are:

• Pace (min/km) = Finish time (minutes) ÷ Distance (km)
• Speed (km/h) = Distance (km) ÷ (Finish time (minutes) ÷ 60)

Variables: t = finish time in minutes, d = distance in kilometers. Pace is expressed as minutes per kilometer, and speed as kilometers per hour.

Worked Example

Using the default values: t = 50 minutes, d = 10 km.

• Pace = 50 ÷ 10 = 5.00 min/km
• Speed = 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333 = 12.00 km/h

The calculator returns a pace of 5.00 min/km and a speed of 12.00 km/h.

Why API Types Drift from the Schema

API types (like TypeScript interfaces, GraphQL schemas, or OpenAPI models) are often written by hand or generated from a different source than the database. Even when you generate types from the schema, the schema itself changes over time — columns are added, renamed, or dropped; types change from integer to bigint; nullability flips. If the API layer isn’t updated in lockstep, the types become a lie: they claim a field is a string when the database now returns a number, or they omit a new required column.

Common causes of drift:

• Manual type definitions that are not regenerated after migrations.
• Multiple teams owning different parts of the stack.
• Using a separate ORM model that doesn’t exactly mirror the schema.
• Database views or functions that return a different shape than the underlying tables.

How to Keep Types in Sync with the Schema

The most reliable approach is to generate types directly from the database schema. Tools like pg-to-ts for PostgreSQL can introspect the live schema and emit TypeScript types. For GraphQL, you can use a schema-first approach where the database schema is reflected into GraphQL types via tools like PostGraphile. For REST, OpenAPI generators can read a database schema if you maintain a mapping, but it’s less direct.

If you can’t generate automatically, enforce a manual check: run a diff between your type definitions and the schema in CI. For example, you can write a script that queries information_schema.columns and compares it to your TypeScript types. That won’t catch every mismatch (e.g., a type that is a union of two column types), but it catches the most common ones: missing columns, extra columns, and type changes.

Another practical step is to use the database as the single source of truth for column names and types. If you use an ORM like Prisma or Sequelize, generate the client from the schema, not the other way around. Avoid hand-writing model classes that duplicate the schema.

Common Problems and How to Detect Them

Drift often shows up as runtime errors: a query returns a value that doesn’t match the type, causing a crash or undefined behavior. For example, if a column changes from integer to numeric, the API might return a string in JSON (because PostgreSQL numeric is a decimal, and some drivers serialize it as a string). Your TypeScript type says number, but the actual value is a string — a classic mismatch.

Another common problem is nullability. A column that was NOT NULL might become nullable, but the API type still says string instead of string | null. This can cause type errors in strict TypeScript, or worse, silent undefined values.

To detect these issues proactively, add a runtime validation layer at the API boundary. Libraries like Zod or io-ts can validate the response against a schema and throw if it doesn’t match. That doesn’t fix the drift, but it surfaces it immediately instead of letting bad data propagate.

Choosing the Right Strategy for Your Project

For small projects with a single developer, manual types might be fine if you’re disciplined. But as soon as you have more than one developer or a database that changes frequently, invest in automation. The choice depends on your stack:

• TypeScript + PostgreSQL: Use a generator like pg-to-ts or an ORM that generates types from migrations.
• GraphQL: Use a schema-first tool that reflects the database, such as PostGraphile or Hasura, so the GraphQL types are always derived from the actual schema.
• REST + OpenAPI: Generate the OpenAPI spec from your database schema using a tool like sql-to-openapi, or at least generate the TypeScript types from the OpenAPI spec and keep that spec in sync with the schema.

No matter the approach, ensure that every migration triggers a regeneration of API types. Add a CI step that fails if the generated types are not up to date.

Costs and Trade-offs of Auto-Generation

Auto-generating types from the schema saves time and prevents drift, but it has costs. Generated types can be verbose and include internal columns you don’t want to expose in the API. You may need to map or filter columns before returning them. Also, if the schema has complex constraints or computed columns, the generated types might not capture the full semantics — for example, a column that is always a UUID but the type is just string.

Another trade-off is that generated types are only as good as the database introspection. If you use views, functions, or materialized views, the generator might not see them or might infer the wrong type. You’ll need to manually define types for those, which reintroduces drift risk.

Finally, auto-generation can lock you into a specific tool. If you switch from one ORM to another, you might need to change your type-generation pipeline. That’s a migration cost, but it’s usually less than the cost of fixing runtime type errors.

Alternatives to Generated Types

If you don’t want to generate types from the schema, you can use a runtime validation library and keep the types as a manual but validated layer. For example, define a Zod schema that mirrors your API response, and use z.infer to get the TypeScript type. Then validate every response against that schema. This doesn’t guarantee the schema matches the database, but it guarantees the API contract is enforced at runtime.

Another alternative is to use a database migration tool that also generates type definitions. Tools like Prisma Migrate and Atlas can generate TypeScript types from the migration files, so the types are always in sync with the intended schema. The caveat is that the migration files might not reflect the actual database if someone applies manual changes outside the migration system.

Frequently Asked Questions

Can I rely on TypeScript types alone to catch schema changes?

No. TypeScript types are compile-time only; they don’t check the actual data returned by PostgreSQL. If the schema changes without updating the types, the types will be wrong and you won’t know until runtime.

What is the best tool to generate TypeScript types from PostgreSQL?

There are several, but the most common are pg-to-ts for a simple one-off generation, and ORMs like Prisma that generate types from your schema file. The best choice depends on your stack and whether you want the ORM’s query builder.

How often should I regenerate types?

Every time the database schema changes. Ideally, you run the generation as part of your migration process, so the types are updated in the same commit as the migration.

Does a GraphQL schema automatically stay in sync with PostgreSQL?

Only if you use a tool that reflects the database schema directly, like PostGraphile. If you write the GraphQL schema by hand, it can drift just like any other API type.

Is it worth using runtime validation even if I generate types?

Usually yes. Generated types catch compile-time mismatches, but runtime validation catches unexpected data from the database, such as a value that violates a constraint or a type that changed without a migration. It adds a small overhead but increases reliability.

Related

• Elden Ring Lore Before the Film: A Practical Catch-Up Guide
• Skype Coding Interviews: Easier or Not?
• Physical SIM vs eSIM: How to Check Your Phone

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2