DEV Community

Cover image for Uniqueness Pre-Checks vs. Database Constraints: Why You Need Both
Janarthanan Soundararajan (Jana)
Janarthanan Soundararajan (Jana)

Posted on AI-assisted

Uniqueness Pre-Checks vs. Database Constraints: Why You Need Both

#backend #database #webdev #javascript

Many applications need to make sure that a value is unique.

Examples include:

  • Email addresses
  • Usernames
  • Phone numbers
  • Product slugs
  • Invitation codes

A common approach is to check whether the value already exists before saving it.

That check is useful, but it is not enough.

A uniqueness pre-check improves UX.

A unique database constraint protects data integrity.

You usually want both.

The uniqueness pre-check

The uniqueness pre-check
Suppose a user is registering with an email address.

Before creating the account, the application can check whether the email already exists:

const existingUser = await db.user.findUnique({
  where: {
    email: input.email,
  },
});

if (existingUser) {
  return {
    error: "This email is already registered.",
  };
}
Enter fullscreen mode Exit fullscreen mode

This allows the application to show a clear message:

This email cannot be used.
Enter fullscreen mode Exit fullscreen mode

That is more helpful than showing:

Something went wrong.
Enter fullscreen mode Exit fullscreen mode

The pre-check improves the user experience because it explains the problem early.

Security note: Be careful with email availability messages. Saying “This email is already registered” can reveal whether someone has an account. In sensitive flows, use a more general message, such as:

  • “This email cannot be used.”
  • “If an account exists for this email, you will receive further instructions.”

But a pre-check is not a guarantee

A pre-check asks the database a question first:

Does this email already exist?
Enter fullscreen mode Exit fullscreen mode

Then, in a separate operation, the application creates the record:

Create the user.
Enter fullscreen mode Exit fullscreen mode

There is a small gap between these two operations.

During that gap, another request can create the same value.

A simple race-condition example

Imagine two people submit the same username at almost the same time:

Request A: Is "alex" available? → Yes
Request B: Is "alex" available? → Yes

Request A: Create "alex" → Success
Request B: Create "alex" → Success
Enter fullscreen mode Exit fullscreen mode

If the database does not enforce uniqueness, both requests may succeed.

This is called a race condition.

The username was available when both requests checked it. But both requests checked it before either request saved it.

This is also known as a TOCTOU problem:

Time Of Check
        ↓
Time Of Use
Enter fullscreen mode Exit fullscreen mode

The result of the check can become outdated before the actual write happens.

The database constraint

The database constraint

A unique database constraint makes the database responsible for enforcing the rule.

For example, in Prisma:

model User {
  id       String @id @default(cuid())
  username String @unique
  email    String @unique
}
Enter fullscreen mode Exit fullscreen mode

Now the database guarantees that two users cannot have the same username or email address.

Even if two requests arrive at the same time, only one can successfully create the record.

The other request will fail.

This protects the data even when the write comes from:

  • A web form
  • An API client
  • A background job
  • An admin tool
  • A migration
  • A webhook
  • A script

The database enforces the rule for every write path.

Handling the database error

The database constraint protects the data, but users should not see a raw database error.

The server should catch the unique-constraint error and return a friendly response.

With Prisma:

try {
  await db.user.create({
    data: {
      username: input.username,
      email: input.email,
    },
  });

  return {
    success: true,
  };
} catch (error) {
  if (
    error instanceof Prisma.PrismaClientKnownRequestError &&
    error.code === "P2002"
  ) {
    return {
      error: "That username or email is already in use.",
    };
  }

  throw error;
}
Enter fullscreen mode Exit fullscreen mode

The pre-check gives an early explanation in normal situations.

The database constraint handles the situations that the pre-check cannot guarantee.

Why not use only the database constraint?

You can rely only on the database constraint for data integrity.

Your data will still be safe.

However, the user experience may be less helpful.

The user might submit a form and receive an error only after the request finishes.

A pre-check can provide earlier feedback:

This username is already taken.
Enter fullscreen mode Exit fullscreen mode

That is better than:

Unique constraint violation.
Enter fullscreen mode Exit fullscreen mode

Why not use only the pre-check?

A pre-check alone cannot protect your data.

It can fail because:

  • Two requests can arrive at the same time.
  • A different API may skip the check.
  • A background job may write directly to the database.
  • A bug may bypass the application validation.
  • A malicious client may send requests directly.

Application-level checks are useful, but they are not the final authority.

The recommended flow

A robust implementation usually follows this flow:

User submits the form
        ↓
Application performs a pre-check
        ↓
If the value exists, show a friendly message
        ↓
If it appears available, attempt the insert
        ↓
Database constraint enforces uniqueness
        ↓
If a race occurs, catch the error
        ↓
Show a friendly message
Enter fullscreen mode Exit fullscreen mode

The pre-check and the database constraint are not competing solutions.

They solve different problems:

Layer Purpose
Application pre-check Gives users early and helpful feedback
Database constraint Guarantees that duplicate data cannot be stored
Error handling Converts conflicts into a friendly response

One detail about checking while typing

Some applications check username availability while the user is typing.

For example:

alex
alex_
alex_d
alex_de
alex_dev
Enter fullscreen mode Exit fullscreen mode

This can create many database requests.

If you use this approach, consider:

  • Debouncing the request
  • Checking only after a minimum number of characters
  • Rate-limiting the endpoint
  • Checking again when the form is submitted

Availability shown in the UI can become outdated.

The final database write must still rely on the unique constraint.

Final takeaway

A uniqueness pre-check is helpful, but it is only a preview of the current state.

A unique database constraint is the actual rule.

The pre-check improves the user experience.

The constraint protects the truth of your data.

Pre-checks communicate.

Constraints enforce.

For reliable applications, use the pre-check to guide users, use the database constraint to protect your data, and handle the final conflict gracefully.

Top comments (2)

Collapse
 
marcusykim profile image
Marcus Kim •

The two-request alex sequence makes the TOCTOU gap concrete: both pre-checks can honestly return available, while only the unique index can decide the winner. Catching Prisma's P2002 after the insert is just as important as debouncing availability checks, because every write path still needs the same friendly conflict handling. One product wrinkle is account enumeration: live username checks may be fine, but confirming that an email is registered can expose user membership, so the UX message sometimes needs to be less specific even while the database rule stays strict.

Collapse
 
janarthanan_soundararajan profile image
Janarthanan Soundararajan (Jana) •

Thanks for the thoughtful comment! I missed the account-enumeration concern with email availability checks. That’s an important security detail.

I’ve updated the article to mention this and explain why email-related messages sometimes need to be less specific.