Add users table with unique & foreign key constraints in PostgreSQL
Tags: database, tutorial, webdev, beginners
Published: 2020-12-27 16:40:45

Hi guys, welcome back to the backend master class!

So far we’ve implemented several features for our simple bank system such as create, update, retrieve, or transfer money between bank accounts.

However, there’s one very important feature that’s still missing: user authentication and authorization. Our banking system could not be completed until this feature is implemented.

So today, we’re gonna take the first step to implement it, which is adding a new users table to the database and link it with the existing accounts table via some db constraints.

{% youtube D4VtNC3vQUs %}

Here's:

- Link to the [full series playlist on Youtube](https://bit.ly/backendmaster)
- And its [Github repository](https://github.com/techschool/simplebank)

## Add table users

Alright, so this is the current schema of our simple bank database written using [dbdiagram.io](https://dbdiagram.io/home)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/56masonkwxnfukk48qoe.jpg)

```sql
Table accounts as A {
  id bigserial [pk]
  owner varchar [not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
  }
}

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'can be negative or positive']
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    account_id
  }
}

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id, not null]
  to_account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'must be positive']
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    from_account_id
    to_account_id
    (from_account_id, to_account_id)
  }
}
```

At the moment, the `accounts` table has an `owner` field to tell us whom this account belong to. So we can use this field as a foreign key to link to the new `users` table that we’re going to create.

Let’s define `Table users` with an alias `U`.

The first field of this table is a `username` of type `varchar`. Every user should have a unique username, so this field can serve as the primary key of this table.

Next, we have the `hashed_password` field to store the hash value of the user’s password. Its type is also `varchar`, and it should be not `null`.

Why is it `hashed_password` and not just `password`? Well, basically we should never store a naked password in the database, because if we do then everyone who has access to the database will see the passwords of all users, which is a very critical security issue.

We will learn more about how to store and verify password in another lecture. For now, let’s just focus on adding this new `users` table.

```sql
Table users as U {
  username varchar [pk]
  hashed_password varchar [not null]
  full_name varchar [not null]
  email varchar [unique, not null]
  password_changed_at timestamptz [not null, default: '0001-01-01 00:00:00Z']
  created_at timestamptz [not null, default: `now()`]
}
```

OK, the next field will store the `full_name` of the user, so it is also of `varchar` type, and should not be `null`.

One more important field the users table should have is `email`. We will use it later to communicate with the users, for example, when they forget their password and want to reset it. So the type of this field should also be `varchar`. And note that it must be `unique` and not `null`, since we don’t want to have 2 users with the same email.

Then just like other tables, we will have a `created_at` field to store the time this user is created. This field is not `null`, and it has a default value of `now()`, so Postgres will automatically fill in the current timestamp when the new user record is inserted.

Moreover, for security reason, it’s often a good idea to ask users to change their password frequently, like once every month for instance. So we will need a field: `password_changed_at` to know when was the last time user changed their password. Its type should be `timestamp with timezone`, and should also be not `null`.

The reason I want every field to be not null is because it makes our developer’s life much easier since we don’t have to deal with null pointers.

If the password has never been changed, we will just use a default value which is a long time in the past. As we’re using golang, I’m gonna use a zero value timestamp of Go here. So it should be year 1, month 1, date 1, at 0 hour, 0 minute, 0 second, and the timezone is UTC: `'0001-01-01 00:00:00Z'`. The letter `Z` here means zero timezone.

OK so that’s the definition of the new `users` table.

## Add foreign key constraint

Now keep in mind that we want to allow 1 user to have multiple accounts with different currencies, so what I’m gonna do is to link the `owner` field of the `accounts` table to the `username` field of `users` table. That would make the `owner` field become a foreign key.

Here in the definition of the `owner`, I’m gonna add a reference tag that points to `U.username`. To remind you, `U` is just an alias of the `users` table.

```sql
Table accounts as A {
  id bigserial [pk]
  owner varchar [ref: > U.username, not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
  }
}
```

Now in the diagram, we can see that there’s a new link between the `username` field of `users` table and the `owner` field of the `accounts` table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8k60ofpfxszbsm45p7i8.jpg)

The number `1` and character `*` at the end of this link tell us that this is a 1-to-many relationship, which basically means, 1 user can have multiple accounts, but 1 account can only belong to exactly 1 single user.

## Add unique constraint

There’s one more thing we should pay attention to here. We allow 1 user to have multiple accounts, but those accounts should have different currencies. For example, you can have 1 `USD` account and 1 `EUR` account, but clearly should not have 2 different `USD` accounts.

One way to set this constraint at the database level is to add a `composite unique index` to the `accounts` table. This index is composed of 2 fields: `owner` and `currency`. That’s why it’s called composite index - an index that involves more than 1 field.

```sql
Table accounts as A {
  id bigserial [pk]
  owner varchar [ref: > U.username, not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
    (owner, currency) [unique]
  }
}
```

## Export to PostgreSQL

OK so now our new schema is ready. Let’s export it to PostgreSQL.

Here we can see the code to create `users` table:

```sql
CREATE TABLE "users" (
  "username" varchar PRIMARY KEY,
  "hashed_password" varchar NOT NULL,
  "full_name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password_changed_at" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00Z',
  "created_at" timestamptz NOT NULL DEFAULT (now())
);
```

Then the `alter table` command to add foreign key constraint to the `owner` field:

```sql
ALTER TABLE "accounts" ADD FOREIGN KEY ("owner") REFERENCES "users" ("username");
```

And a composite unique index for the `owner` and `currency`.

```sql
CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");
```

## Add new schema change to our project

Next, we have to add these new changes to our simple bank project.

One way to do that is to replace the whole content of the `init_schema migration` file with the new one, then reset the database and rerun the `migrate up` command.

However, in a real-world project, it’s not the right way go. Why?

Because requirements change all the time, and it might come after the first version of our system is deployed to the production. And once we have data in the production DB, we cannot reset it to rerun the old migration.

So the right way to apply new schema change is to create a new migration version. Let’s open the terminal to generate a new migration.

We have learned how to do this in lecture 3 of this course. But if you don’t remember the command, just run:

```bash
migrate -help
```

To create a new migration, we run this command:

```bash
migrate create -ext sql -dir db/migration -seq add_users
```

We use some parameters to tell migrate to set the the output file extension to `sql`, the output directory to `db/migration`, use a sequential number as the file name prefix, and the migration name is `add_users`.

As you can see here, 2 migration files has been generated inside the `db/migration` folder:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/krhnzg3s7v9johalf1hw.jpg)

Let’s implement them in visual studio code.

## Implement the up migration

I’m gonna start with the up migration.

First, we need to create table users. So let’s copy the SQL query that dbdiagram has generated for us and paste it to this migration file `000002_add_users.up.sql`

Next, I’m gonna copy the query that adds a new foreign key constraint to the `owner` field of accounts table. And finally, the query to create a unique composite index for the `owner` and `currency` as well.

```sql
CREATE TABLE "users" (
  "username" varchar PRIMARY KEY,
  "hashed_password" varchar NOT NULL,
  "full_name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password_changed_at" timestamptz NOT NULL DEFAULT('0001-01-01 00:00:00Z'),  
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

ALTER TABLE "accounts" ADD FOREIGN KEY ("owner") REFERENCES "users" ("username");

CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");
```

This would be good enough for our new migration up script. However, I’m gonna show you another way to ensure that each owner has at most 1 account for a specific currency.

Instead of using a direct unique index like this, we can add a unique constraint for the pair of `owner` and `currency` on the accounts table. It will be very similar to the command to add foreign key constraint above:

```sql
-- CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");
ALTER TABLE "accounts" ADD CONSTRAINT "owner_currency_key" UNIQUE ("owner", "currency");
```

Basically, under the hood, adding this unique constraint will automatically create the same unique composite index for `owner` and `currency` as the command we wrote above.

Postgres will need that index to check and enforce the unique constraint faster. So you can choose either of these 2 commands, whatever you like.

## Run the migration up

OK, now let’s open the terminal and run `make migrateup` to apply this new migration.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xkbgvjmahv1qx2zu951o.jpg)

Oops, we’ve got an error. And the reason is that the foreign key constraint is violated. Why?

Well, that’s because we already have some existing rows in the accounts table, but their `owner` field is completely random and doesn’t link to any existed user. Of course, since the users table doesn’t exist until now.

So in this case, we have to clean up all the existing data before running `migrate up`. This is possible because our existing system is not ready to deploy to production yet.

But note that, as the previous migrate up run was failed, it will change the current schema migration to `version 2` but in a `dirty` state.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7qtuuslwz52r3yppo9gj.jpg)

So now if we run `make migratedown` with the purpose of cleaning up the data, we will get an error because of this dirty version.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/qoqe7v7fka9swuzejird.jpg)

To fix this, I’m gonna manually update the value of this `dirty` field to `false`, save it, and go back to the terminal to run `make migratedown`.

This time the migrations run successfully, and all tables in our database are gone. We can now run `make migrateup` again to get them back.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/bzuvogzsx7ijxz4fh191.jpg)

OK, the migrations are successful. And back to TablePlus, we can see the new `users` table. Let’s click on the `Structure` button to see its schema.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/r5pol1xc4btdhki4gwfp.jpg)

It has all the fields that we declared in the code: `username`, `hashed_password`, `full_name`, `email`.

The `password_changed_at` field has the correct default value, and similar for the `created_at` field.

Look at the bottom, there are 2 BTREE indexes:

- One is for the primary key `username`, which should be `unique`.
- And the other is for the `email`, which is a `unique` index as well.

Let’s check the `accounts` table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/cgz3l31ojdg6vdb3lqib.jpg)

Here in the `owner` field, we can see that it now has a foreign key constraint that links to the `username` field of the `users` table.

And at the bottom, there’s a new `unique` index for the `owner` and `currency` pair.

So our migration up script works perfectly! Now let’s go back to the code and complete the migration down.

## Implement the migration down

When writing the migration down, we should reverse what was done in the migration up.

So first we have to drop the `unique` constraint for the `owner` and `currency` pair of the accounts table. The command is:

```sql
ALTER TABLE IF EXISTS "accounts" DROP CONSTRAINT IF EXISTS "owner_currency_key";
```

Next, we have to drop the `foreign key` constraint for the `owner` field in a similar way. But now how can we know the name of this foreign key constraint?

Well, it’s very easy! Let’s go back to [Table Plus](https://tableplus.com/) and click on the `Info` button at the bottom of the window.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/r67bvip50fkgce74o4wh.jpg)

Here in the table definition, we can see the foreign key constraint name: `accounts_owner_fkey`

Let’s copy it, and paste it to this command:

```sql
ALTER TABLE IF EXISTS "accounts" DROP CONSTRAINT IF EXISTS "accounts_owner_fkey";
```

The last step we should do is to drop the `users` table. So let’s add this command to the file:

```sql
DROP TABLE IF EXISTS "users";
```

And that’s it! The `000002_add_users.down.sql` file is done. How can we test if it works or not?

## Test the up and down migrations

At the moment, in the Makefile, we only have one `make migratedown` command to run all the migration down versions. But in this case, we only want to run 1 last migration down version.

So let’s add a new make command for this purpose. I’m gonna called it `migratedown1`. The migrate command would be the same as before, except that we need to add one more argument at the end.

```Makefile
migratedown1:
  migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose down 1
```

The number `1` here means that we only want to rollback 1 last migration, or more precisely, just run the last down migration version that was applied before.

Similarly, I will duplicate the `migrateup` command, and add a new `migrateup1` command that will only applies 1 next migration version from the current one:

```Makefile
migrateup1:
  migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up 1
```

Alright, now let’s add these 2 new commands to the `PHONY` list:

```Makefile
.PHONY: postgres createdb dropdb migrateup migratedown migrateup1 migratedown1 sqlc test server mock
```

And go back to the terminal and run:

```bash
make migratedown1
```

The migration is successful. Now in TablePlus, we can see the current version has been changed to 1.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/x1mlic1vemtcjkq55y5l.jpg)

The `users` table is gone, and in the accounts table, there’s no more foreign key constraint for the `owner` column, as well as the `unique` constraint for the `owner` and `currency` pair.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/mav7hpbpbxn9s62rtwpd.jpg)

So the migration down script worked!

OK, now let’s run this command to update the schema to the latest version.

```bash
make migrateup1 
```

As you can see, the new constraints in the `accounts` table are back.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/c3q3hz7sgykoyo87plt4.jpg)

The current migration version is 2, and the new `users` table is here as expected.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/khw4nc2j9lomg83h8hoy.jpg)

So today we have successfully added a new `users` table to the simple bank database.

And while doing so, we also learned how to add some `foreign key` and `unique` constraints to build up the relationship between tables and to ensure the consistency of the data.

In the next lectures, we’re gonna update our golang code to work with this new table, and then add more feature to authenticate and authorize users.

Thank you for reading, and I’ll see you guys in the next one very soon!

---

If you like the article, please subscribe to our [Youtube channel](http://bit.ly/techschoolguru) and follow us on [Twitter](https://twitter.com/techschoolguru) or [Facebook](https://www.facebook.com/techschool.guru) for more tutorials in the future.

---
If you want to join me on my current amazing team at Voodoo, check out [our job openings here](https://bit.ly/voodoojobs). Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.