Add users table with unique & foreign key constraints in PostgreSQL

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.

Here's:

• Link to the full series playlist on Youtube
• And its Github repository

Add table users

Alright, so this is the current schema of our simple bank database written using dbdiagram.io

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.

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.

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.

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.

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:

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:

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

And a composite unique index for the owner and currency.

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:

migrate -help

To create a new migration, we run this command:

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:

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.

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:

-- 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.

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.

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.

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.

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.

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.

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:

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 and click on the Info button at the bottom of the window.

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:

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:

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.

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:

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:

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

And go back to the terminal and run:

make migratedown1

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

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.

So the migration down script worked!

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

make migrateup1 

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

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

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 and follow us on Twitter or Facebook 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. Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.