Design DB schema and generate SQL code with dbdiagram.io

Backend master class

Hello and welcome to Tech School!

In this backend master class, we’re going to learn everything about how to design, develop, and deploy a complete backend system from scratch using PostgreSQL, Golang and Docker.

The service that we’re going to build is a simple bank. It will provide APIs for the frontend to do following things:

• First, create and manage bank accounts, which are composed of owner’s name, balance, and currency.
• Second, record all balance changes to each of the account. So every time some money is added to or subtracted from the account, an account entry record will be created.
• And third, perform a money transfer between 2 accounts. This should happen within a transaction, so that either both accounts’ balance are updated successfully or none of them are.

Lecture 1: database design

In the first lecture, we will learn about database design. I will show you how to:

• Design a SQL database schema using dbdiagram.io
• Save the schema as a PDF or PNG diagram to share it with your team
• And finally generate SQL codes to create the schema in a target database engine of your choice, such as PostgreSQL, MySQL, or SQL server.

Design DB schema

OK, let’s start by going to dbdiagram.io and click Go to app.

This is a sample DB schema. On the left, we define the table structure with some simple syntax. Then its corresponding diagram will show up on the right.

We can use the Export tool at the top to save this diagram as a PDF or PNG file, or generate SQL codes for Postgres, MySQL or SQL server.

Alright, now let’s modify this schema for our database. I’m gonna change the name of this diagram to "Simple bank".

Table accounts

First we will have an accounts table. We use the Table keyword to declare a table, and use the as keyword to set a short alias name for it.

Let’s say we want each account to have a unique ID, so I’m gonna use an auto-increment id field for it.

Table accounts as A {
  id bigserial [pk]
}

In the sample script, they use the increment keyword for that purpose. But in Postgres, we can also use the type bigserial. It basically means a big auto-incrementing integers (8-byte/64-bit).

We use pk keyword to say that this field is the primary key of this table.

The next field is owner, which stores the name of the account owner. So its type can be text or varchar.

Table accounts as A {
  id bigserial [pk]
  owner varchar
  balance bigint
}

Then a balance field to store the amount of available money of the account. To be simple, I just use bigint type here. In reality, some currencies are not always integers, so you should consider using something like decimal type instead.

Now let’s add one more field to store the name of the currency. And finally a standard created_at field to know when the account is created.

We should use timestamptz type instead of just timestamp because it includes the timezone information as well. And we want it to be automatically set by the database, so let’s set a default value for it with the default keyword.

Postgres has a now() function to get the current time. We can simply put it inside a backtick pair to use as the default value.

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

And that’s it! The accounts table is done.

Table entries

The next table is entries. This table will record all changes to the account balance. It also has an auto-increment id column, which is the primary key.

And it has a foreign key account_id of type bigint that references the id column of the account table. We use the ref keyword to declare this reference.

As you might know, this represent 1-to-many relationship between accounts and entries table, because 1 account can have many entries to change its balance.

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id]
  amount bigint
  created_at timestamptz [default: `now()`]
}

Another field we will need is the amount of money that is added to the account balance in this entry. It can be positive or negative depending on whether the money is going in to or out of the account.

And finally a created_at field to record when the entry is created.

Table transfers

The last table is transfers. It records all the money transfers between 2 accounts. Let’s say in this course, we just care about internal transfer within simple bank.

So this table will have:

• An auto-increment id primary key
• A foreign key from_account_id, and another foreign key to_account_id, both reference the id column of the accounts table.
• Then the amount of money that moves from one account to the other. But unlike amount in the entries table, this amount value must be positive.
• And finally the created_at field just like in the other tables.

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id]
  to_account_id bigint [ref: > A.id]
  amount bigint
  created_at timestamptz [default: `now()`]
}

Add constraints

OK, our DB schema is almost done. Now let’s look at the rest of the example to see if there’s anything we can add.

This is another way to declare the foreign key reference.

Ah, we can also add some not null or unique constraint to some fields in our tables.

I don’t think we have any fields that should be unique, except for the primary keys, which already are.

So I’m gonna add not null constraint to:

• The owner, balance, currency, and created_at column of accounts table.
• The amount and created_at column of entries table.
• And similar for the transfers table.

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()`]
}

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()`]
}

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()`]
}

Add notes

We can also add some notes to the columns. So let’s add a note to the amount column of entries table, saying it can be negative or positive. And another note to the amount column of transfers table, saying it must be positive.

Table entries {
  ...
  amount bigint [not null, note: 'can be negative or positive']
}

Table transfers {
  ...
  amount bigint [not null, note: 'must be positive']
}

Define enum type

We can even define a custom enum type for our column. For example, we can define a Currency enum with 2 possible values: USD and EUR, and use it as the type of the accounts.currency column, like this:

enum Currency {
    USD
    EUR
}

Table accounts as A {
  ...
  currency Currency [not null]
}

However, I prefer to use built-in type varchar here, and let the application code handle the value validation.

Add indexes

OK, the last thing we need to do is to add indexes to our tables. For that, we will use the Indexes keyword.

In the accounts table, we might want to search for accounts by owner name, So let’s add owner to the list of the indexes.

In the entries table, we might want to list all entries of a specific account, so let’s add account_id to the index.

The transfers table is the most complex:

• We might want to search for all transfers that going out of an account. So from_account_id should be 1 index.
• Similarly, we might want to look for all transfers that going in to an account. So to_account_id should be another index.
• And finally, if we want to search for all transfers between 2 specific accounts, then we need a composite index of both from_account_id and to_account_id.

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)
  }
}

And that’s it! Our schema definition is completed.

View the DB diagram

Now on the right, we can click auto-arrange to reorganize the DB diagram that has been automatically generated for us.

As you can see, there’s a 1-to-many links from accounts to entries table. And there are two 1-to-many links from accounts to transfers table.

Generate SQL code

Now let’s try to generate PostgreSQL code by using the export tool at the top of the page.

The file is downloaded. Let’s open it!

Yee, we have beautiful codes ready to be ran in PostgreSQL to create the database schema.

CREATE TABLE "accounts" (
  "id" bigserial PRIMARY KEY,
  "owner" varchar NOT NULL,
  "balance" bigint NOT NULL,
  "currency" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "entries" (
  "id" bigserial PRIMARY KEY,
  "account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "transfers" (
  "id" bigserial PRIMARY KEY,
  "from_account_id" bigint NOT NULL,
  "to_account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");

CREATE INDEX ON "accounts" ("owner");

CREATE INDEX ON "entries" ("account_id");

CREATE INDEX ON "transfers" ("from_account_id");

CREATE INDEX ON "transfers" ("to_account_id");

CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");

COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive';

COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';

• There are 3 CREATE TABLE queries to create the accounts, entries and transfers table.
• Then 3 ALTER TABLE queries to add foreign keys to the tables.
• Then 5 CREATE INDEX queries to create all the indexes.
• And finally 2 COMMENT queries to add the comments to the amount columns.

Export as other format

Now let’s try to export the diagram to PDF file.

Here we go, the diagram is successfully saved as a PDF file, ready to be shared with the team.

You can also export to other formats as well. For example, this is the generated MySQL code.

CREATE TABLE `accounts` (
  `id` bigserial PRIMARY KEY,
  `owner` varchar(255) NOT NULL,
  `balance` bigint NOT NULL,
  `currency` varchar(255) NOT NULL,
  `created_at` timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE `entries` (
  `id` bigserial PRIMARY KEY,
  `account_id` bigint NOT NULL,
  `amount` bigint NOT NULL COMMENT 'can be negative or positive',
  `created_at` timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE `transfers` (
  `id` bigserial PRIMARY KEY,
  `from_account_id` bigint NOT NULL,
  `to_account_id` bigint NOT NULL,
  `amount` bigint NOT NULL COMMENT 'must be positive',
  `created_at` timestamptz NOT NULL DEFAULT (now())
);

ALTER TABLE `entries` ADD FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`);

ALTER TABLE `transfers` ADD FOREIGN KEY (`from_account_id`) REFERENCES `accounts` (`id`);

ALTER TABLE `transfers` ADD FOREIGN KEY (`to_account_id`) REFERENCES `accounts` (`id`);

CREATE INDEX `accounts_index_0` ON `accounts` (`owner`);

CREATE INDEX `entries_index_1` ON `entries` (`account_id`);

CREATE INDEX `transfers_index_2` ON `transfers` (`from_account_id`);

CREATE INDEX `transfers_index_3` ON `transfers` (`to_account_id`);

CREATE INDEX `transfers_index_4` ON `transfers` (`from_account_id`, `to_account_id`);

Alright, the last thing we need to do is to Save this design, and click Share to get the link to share it with others. Or use the embedded link to embed it into other pages.

And that’s everything I want to share with you in this first lecture of the course. I hope it’s useful for you.

Thanks a lot for reading, and I’ll catch you guys in the next one.

If you like the article, please subscribe to our Youtube channel and follow us on Twitter 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.