使用 dbdiagram.io 设计数据库模式并生成 SQL 代码

后端大师班

您好，欢迎来到技术学校！

在本次后端大师班中，我们将学习如何使用 PostgreSQL、Golang 和 Docker 从头开始设计、开发和部署完整的后端系统。

我们要构建的服务是一个简单的银行。它将为前端提供 API 来执行以下操作：

首先，创建和管理银行账户，账户由所有者姓名、余额和货币组成。
其次，记录每个账户的所有余额变化。因此，每次账户中有资金增加或减少时，都会创建一条账户入账记录。
第三，在两个账户之间进行转账。这应该在一次交易中完成，因此两个账户的余额要么都更新成功，要么都不更新。

第一讲：数据库设计

在第一节课中，我们将学习数据库设计。我将向你展示如何：

使用dbdiagram.io设计 SQL 数据库模式
将架构保存为 PDF 或 PNG 图表，以便与您的团队共享
最后生成 SQL 代码以在您选择的目标数据库引擎（例如 PostgreSQL、MySQL 或 SQL 服务器）中创建模式。

设计数据库模式

好的，让我们首先访问dbdiagram.io并单击“转到应用程序”。

这是一个示例数据库架构。左侧我们用一些简单的语法定义了表结构。然后，其对应的图表将显示在右侧。

我们可以使用顶部的导出工具将此图表保存为 PDF 或 PNG 文件，或者为 Postgres、MySQL 或 SQL 服务器生成 SQL 代码。

好了，现在让我们修改一下数据库的架构。我要把这个图的名称改成“简单银行”。

表帐户

首先我们需要一个accounts表。我们使用Table关键字来声明一个表，并使用as关键字为其设置一个简短的别名。

假设我们希望每个帐户都有一个唯一的 ID，因此我将id为其使用一个自动增量字段。



Table accounts as A {
  id bigserial [pk]
}

在示例脚本中，他们使用了increment关键字来实现此目的。但在 Postgres 中，我们也可以使用类型bigserial。它的基本含义是一个大的自增整数（8 字节/64 位）。

我们用pk关键字来表示这个字段是这个表的主键。

下一个字段是owner，用于存储账户所有者的姓名。因此它的类型可以是text或varchar。



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

然后是一个balance用于存储账户可用金额的字段。为了简单起见，我bigint在这里只使用了 type 类型。实际上，有些货币并不总是整数，所以你应该考虑使用type 之decimal类的类型。

现在让我们再添加一个字段来存储的名称currency。最后添加一个标准created_at字段来了解帐户的创建时间。

我们应该使用timestamptztype 而不是 just，timestamp因为它也包含时区信息。我们希望它由数据库自动设置，所以让我们用default关键字为其设置一个默认值。

Postgres 有一个now()获取当前时间的函数。我们可以简单地将其放在反引号对中，以用作默认值。



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

就这样！accounts表格完成了。

表条目

下一个表是entries。该表将记录账户余额的所有变化。它还有一个自动递增id列，即主键。

它有一个account_id类型的外键bigint，引用了表id的列account。我们使用ref关键字来声明这个引用。

accounts您可能知道，这代表表之间的一对多关系entries，因为 1 个帐户可以有多个条目来更改其余额。



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

我们需要的另一个字段是amount此条目中添加到账户余额的金额。它可以是正数或负数，具体取决于资金是存入账户还是流出账户。

最后还有一个created_at字段用于记录条目的创建时间。

餐桌转移

最后一张表是transfers。它记录了两个账户之间的所有资金转账。假设在本课程中，我们只关注简单银行内部的转账。

因此该表将包含：

自动增量id主键
一个外键from_account_id和另一个外键to_account_id都引用表id的列accounts。
然后amount是从一个账户转移到另一个账户的资金。但与表格amount中不同entries，该amount值必须为正。
最后，该created_at字段就像其他表中的一样。



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

添加约束

好的，我们的数据库模式几乎完成了。现在让我们看看示例的其余部分，看看是否还有什么可以添加的。

这是声明外键引用的另一种方法。

啊，我们还可以在表中的某些字段上添加一些not null或约束。unique

我认为除了主键之外，我们没有任何字段应该是唯一的，主键已经是唯一的了。

所以我要添加非空约束：

表的owner、balance、currency和created_at列accounts。
表的amount和列。created_atentries
表格也类似transfers。



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

添加注释

我们还可以给列添加一些注释。比如，在表格amount的列中添加一条注释，说明它可以是正数或负数。在表格的列entries中添加另一条注释，说明它必须是正数。amounttransfers



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

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

定义枚举类型

我们甚至可以为列定义自定义枚举类型。例如，我们可以定义一个Currency包含和两个可能值的枚举USD，EUR并将其用作列的类型accounts.currency，如下所示：



enum Currency {
    USD
    EUR
}

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

但是，我更喜欢在这里使用内置类型varchar，并让应用程序代码处理值验证。

添加索引

好的，我们需要做的最后一件事是为表添加索引。为此，我们将使用Indexes关键字。

在accounts表中，我们可能想要按所有者姓名搜索帐户，因此让我们将其添加owner到索引列表中。

在entries表中，我们可能想要列出特定帐户的所有条目，因此让我们添加account_id到索引中。

该transfers表是最复杂的：

我们可能想搜索某个账户的所有转账记录。所以from_account_id应该使用 1 个索引。
类似地，我们可能想查找所有转入某个账户的资金。所以to_account_id应该添加另一个索引。
最后，如果我们想要搜索两个特定账户之间的所有转账，那么我们需要和的复合from_account_id索引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)
  }
}

就这样！我们的模式定义完成了。

查看数据库图

现在在右侧，我们可以单击自动排列来重新组织已为我们自动生成的 DB 图。

如你所见，从条目表到条目表有一个一对多链接。从条目表到条目表accounts有两个一对多链接。accountstransfers

生成 SQL 代码

PostgreSQL现在让我们尝试使用页面顶部的导出工具来生成代码。

文件已下载。让我们打开它！

是的，我们已经准备好了漂亮的代码，可以在 PostgreSQL 中运行以创建数据库模式。



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';

有 3 个CREATE TABLE查询用于创建accounts、entries和transfers表。
然后通过 3 个ALTER TABLE查询向表中添加外键。
然后进行 5 次CREATE INDEX查询来创建所有索引。
最后，使用 2 个 COMMENT 查询将评论添加到数量列。

导出为其他格式

现在让我们尝试将图表导出为 PDF 文件。

好了，图表已成功保存为 PDF 文件，可以与团队共享了。

您还可以导出为其他格式。例如，这是生成的 MySQL 代码。



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

好了，最后一步就是完成Save这个设计，点击Share获取链接分享给其他人。或者使用嵌入链接将其嵌入到其他页面。

这就是我在本课程第一讲中想与大家分享的全部内容。希望对大家有所帮助。

非常感谢您的阅读，下期再见。

如果您喜欢这篇文章，请订阅我们的 Youtube 频道并在 Twitter 上关注我们，以便将来获取更多教程。

如果你想加入我目前在 Voodoo 的优秀团队，请查看我们的职位空缺。你可以远程办公，也可以在巴黎/阿姆斯特丹/伦敦/柏林/巴塞罗那现场办公，但需获得签证担保。

文章来源：https://dev.to/techschoolguru/design-db-schema-and-generate-sql-code-with-dbdiagram-io-4ko5