Design DB schema and generate SQL code with dbdiagram.io
Tags: database, tutorial, webdev, beginners
Published: 2020-06-08 08:56:45

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

{% youtube rx6CPDK_5mU %}

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.

![simple-bank](https://dev-to-uploads.s3.amazonaws.com/i/w165mfip9n28u98m8j0d.png)

## 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](https://dbdiagram.io/home)
- 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.

![db-design](https://dev-to-uploads.s3.amazonaws.com/i/4rgb5s990ulmcznt5zaq.png)

### Design DB schema

OK, let’s start by going to [dbdiagram.io](https://dbdiagram.io/home) and click Go to app.

![db-diagram](https://dev-to-uploads.s3.amazonaws.com/i/1k0v1jekv3xsg6v9qh7n.png)

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.

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

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

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

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

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

![other-example](https://dev-to-uploads.s3.amazonaws.com/i/15akznq7o98jmp87d4dg.png)

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.

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

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.

```sql
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:

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

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

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.

![db-diagram](https://dev-to-uploads.s3.amazonaws.com/i/n1rsep611e4qgz6nblxq.png)

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.

![export-sql](https://dev-to-uploads.s3.amazonaws.com/i/jw9hgjeg8iif9q20hoju.png)

The file is downloaded. Let’s open it!

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

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

![export-pdf](https://dev-to-uploads.s3.amazonaws.com/i/4py7u3vismmjuqemvff7.png)

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.

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

![share-diagram](https://dev-to-uploads.s3.amazonaws.com/i/ee2y0h1tai46qp9699ok.png)

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](http://bit.ly/techschoolguru) and [follow us on Twitter](https://twitter.com/techschoolguru) 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.