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.
|
1 2 3 |
CREATE TABLE posts ( id integer PRIMARY KEY ) |
When creating primary keys with ids, we can take advantage of the SERIAL type.
|
1 2 3 |
CREATE TABLE posts ( id serial PRIMARY KEY ) |
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.
|
1 2 3 4 5 |
CREATE TABLE users ( first_name text, last_name text, PRIMARY KEY (first_name, last_name) ) |
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.
|
1 2 3 4 5 6 7 8 9 10 |
import { Entity, PrimaryColumn, } from 'typeorm'; @Entity() export class Post { @PrimaryColumn() public id: number; } |
To create a column that is auto-incremented, we need the @PrimaryGeneratedColumn decorator.
|
1 2 3 4 5 6 7 8 9 10 |
import { Entity, PrimaryGeneratedColumn, } from 'typeorm'; @Entity() export class Post { @PrimaryGeneratedColumn() public id: number; } |
We can use the @PrimaryColumn decorator more than once to create a primary key that consists of multiple columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import { Entity, PrimaryColumn, } from 'typeorm'; @Entity() export class User { @PrimaryColumn() public firstName: number; @PrimaryColumn() public lastName: number; } |
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.
|
1 2 3 4 |
CREATE TABLE posts ( id serial PRIMARY KEY, title text NOT NULL ) |
If we at some point attempt to insert a row into the above table without the title, we get an error:
|
1 |
INSERT INTO posts DEFAULT VALUES; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'; @Entity() export class Post { @PrimaryGeneratedColumn() public id: number; @Column({ nullable: true }) public title?: string; } |
Unique
With the unique constraint, we can ensure that the values in the column are unique across all of the rows in the table.
|
1 2 3 4 |
CREATE TABLE posts ( id serial PRIMARY KEY, title text UNIQUE ) |
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.
|
1 2 3 4 5 6 |
CREATE TABLE users ( id serial PRIMARY KEY, first_name text, last_name text, UNIQUE (first_name, last_name) ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'; @Entity() export class Post { @PrimaryGeneratedColumn() public id: number; @Column({ unique: true }) public title: string; } |
If we want to use multiple columns for our unique constraint, we need to use the @Unique decorator.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import { Entity, PrimaryGeneratedColumn, PrimaryColumn, Unique } from 'typeorm'; @Entity() @Unique(['firstName', 'lastName']) export class User { @PrimaryGeneratedColumn() public id: number; @PrimaryColumn({ name: 'first_name' }) public firstName: number; @PrimaryColumn({ name: 'last_name' }) public lastName: number; } |
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.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts ( id serial PRIMARY KEY, author_id integer REFERENCES users(id) ); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
import { Entity, OneToMany, PrimaryGeneratedColumn } from 'typeorm'; import { Post } from '../posts/post.entity'; @Entity() export class User { @PrimaryGeneratedColumn() public id: number; @OneToMany(() => Post, (post: Post) => post.author) public posts: Post[]; } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
import { Entity, ManyToOne, PrimaryGeneratedColumn, } from 'typeorm'; import { User } from '../users/user.entity'; @Entity() export class Post { @PrimaryGeneratedColumn() public id: number; @ManyToOne(() => User, (author: User) => author.posts) public author: User } |
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.
|
1 2 3 4 |
CREATE TABLE products ( id serial PRIMARY KEY, price numeric CHECK (price > 0) ) |
We can also use multiple columns in our check constraint.
|
1 2 3 4 5 6 |
CREATE TABLE events ( id serial PRIMARY KEY, start_date timestamptz, end_date timestamptz, CHECK (start_date < end_date) ) |
We can use different comparison operators and logical operators. Also, we can take advantage of pattern matching with regular expressions.
|
1 2 3 4 5 |
CREATE TABLE users ( id serial PRIMARY KEY, email text, CONSTRAINT email CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$') ); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
import { Entity, Column, PrimaryGeneratedColumn, Check } from 'typeorm'; @Entity() @Check('"price" > 0') class Product { @PrimaryGeneratedColumn() public id: number; @Column() public price: number; } |
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.
|
1 2 3 4 5 6 |
enum PostgresErrorCode { UniqueViolation = '23505', CheckViolation = '23514', NotNullViolation = '23502', ForeignKeyViolation = '23503' } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
public async register(registrationData: RegisterDto) { const hashedPassword = await bcrypt.hash(registrationData.password, 10); try { return await this.usersService.create({ ...registrationData, password: hashedPassword }); } catch (error) { if (error?.code === PostgresErrorCode.UniqueViolation) { throw new Error('User with that email already exists'); } throw new Error('Something went wrong'); } } |
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.
|
1 2 3 4 |
CREATE TABLE users ( id serial PRIMARY KEY, email text CONSTRAINT unique_user_email_constraint UNIQUE ); |
We can also do that through TypeORM.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import { Column, Entity, PrimaryGeneratedColumn, Unique } from 'typeorm'; export const UNIQUE_USER_EMAIL_CONSTRAINT = 'unique_user_email_constraint'; @Entity() @Unique(UNIQUE_USER_EMAIL_CONSTRAINT, ['email']) export class User { @PrimaryGeneratedColumn() public id: number; @Column() public email: string; } |
Thanks to naming our constraint, we can now refer to it in our catch block.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
public async register(registrationData: RegisterDto) { const hashedPassword = await bcrypt.hash(registrationData.password, 10); try { return await this.usersService.create({ ...registrationData, password: hashedPassword }); } catch (error) { if (error?.constraint === UNIQUE_USER_EMAIL_CONSTRAINT) { throw new Error('User with that email already exists'); } throw new Error('Something went wrong'); } } |
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!
