Defining constraints with PostgreSQL and TypeORM

JavaScript SQL

An important part of designing a database is controlling the data we store inside. The most fundamental way of doing that is defining the types for our columns. With that, we can make sure that a particular column holds only text, for example. In this article, we use constraints to have even more control of our data. With constraints, we can reject the data that doesn’t match our guidelines. By doing so, we can ensure the integrity of our data to a greater extent than by doing that through the application logic.

Primary key

When we mark a column as a primary key, we indicate that it can be used as a unique identifier for rows in the table. Because of that, all values in this column need to be unique and not equal to null.

When creating primary keys with ids, we can take advantage of the SERIAL type.

Under the hood, PostgreSQL creates an integer column with a sequence that auto increments every time we add a new row to the table.

A table can’t have more than one primary key, but the primary key can consist of multiple columns.

Above, we create a primary key that contains both the first_name and last_name. Because of that, we can’t have two users that have the same combination of the first name and the last name. They can share the same first name, for example, though.

Primary keys with TypeORM

With TypeORM, we can create primary keys easily. To do that, we need the @PrimaryColumn decorator.

To create a column that is auto-incremented, we need the @PrimaryGeneratedColumn decorator.

We can use the @PrimaryColumn decorator more than once to create a primary key that consists of multiple columns.

Please keep in mind that even though we use @PrimaryColumn twice, this is still a single primary key that has two columns.

Not null

The not null constraint enforces a column to have a value different than null.

If we at some point attempt to insert a row into the above table without the title, we get an error:

ERROR: null value in column “title” of relation “posts” violates not-null constraint
DETAIL: Failing row contains (2, null).

Handling non-nullable columns in TypeORM

The PostgreSQL documentation states that in most database designs, the majority of columns should be marked as not null. This is a tip important enough for TypeORM to make it a default behavior.

If we want to make the column nullable, we need to use the nullable property.

Unique

With the unique constraint, we can ensure that the values in the column are unique across all of the rows in the table.

We now expect every post to have a unique title. With PostgreSQL, we can also expect a group of columns to have a unique value. To do that, we need a different syntax.

We expect users to have a unique combination of first name and last name with the above table definition. However, they can still share the same last name, for example, if their first name differs.

The unique constraint with TypeORM

To create a unique constraint with TypeORM, we can use the unique parameter.

If we want to use multiple columns for our unique constraint, we need to use the @Unique decorator.

An important thing about the @Unique decorator is that we supply it with the field names, not the names of the columns. To make it more apparent, in the above example, we explicitly change the name of the columns.

Foreign key

With the foreign key constraint, we can indicate that the values in the column match values from another table. This is commonly used when defining relations.

We could shorten the above command with author_id integer REFERENCES users because the default column used for referencing is the primary key.

When we use the REFERENCES keyword, we define a foreign key. Thus, in the above example, each post now needs to refer to an existing user.

Foreign keys with TypeORM

To create foreign keys with TypeORM, we need to define relations. A good example is a one-to-many relation:

Doing the above creates the authorId column with a foreign constraint for the posts table.

Relations are a broad topic. For a dedicated article, check out API with NestJS #7. Creating relationships with Postgres and TypeORM.

Check

The check constraint is the most generic type of constraint. With it, we can specify the requirements a value in a certain column needs to meet.

We can also use multiple columns in our check constraint.

We can use different comparison operators and logical operators. Also, we can take advantage of pattern matching with regular expressions.

With ~* we perform case insensitive pattern matching. For more information check out the official documentation.

Check constraints with TypeORM

To create a check constraint with TypeORM, we need the @Check decorator.

Error handling with TypeORM

A big part of implementing constraints is handling errors that they might cause. PostgreSQL assigned error codes for every error message it produces. Let’s create an enum with the error codes we need.

For a full list of error codes check out the documentation.

Thanks to defining the error codes in an enum, we can now refer to them.

The above snippet is a simplified version of a code from API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookie

The important thing is that we can assign names to our constraints. This will come in handy when implementing error handling.

We can also do that through TypeORM.

Thanks to naming our constraint, we can now refer to it in our catch block.

Summary

In this article, we’ve gone through different constraints and used them both through SQL and TypeORM. We’ve also learned how to implement error handling in our application to react to our constraints being violated. We’ve learned how to have more control over the data saved into our database by doing so. PostgreSQL also has the exclusion constraint, but that might be a topic for a separate article. Stay tuned!