After using OpenAPI scheme to design my API, I wonder if I could also use similar notation to design the database. This is an attempt to do such that and to my surprise it works pretty well. Since there is no CLI that can generate the actual SQL file, we can use any of the available LLM chatbot to generate one for us.
This part shows the name, description, author, etc.
openapi: 3.0.1
info:
title: My Database
description: Design of tables for the my database.
version: 1.0.0
contact:
name: Author
email: [email protected]
metadata:
dateCreated: '2024-11-05'
dateModified: '2024-11-15'
database:
engine: InnoDB
default_charset: utf8mb4
collate: utf8mb4_general_ci
tables:tables:
users:
description: Users table
tags:
- User
columns:
id:
type: char
length: 8
is_null: false
description: User id
name:
type: char
length: 64
is_null: false
description: User name
password:
type: char
length: 255
is_null: false
description: Hashed password
role:
type: int
length: 11
is_null: false
default: 0
description: User role (0=user, 1=admin)
constraints:
- check: [0, 1]
indexes:
primary:
unique: true
column: [id]
idx_name:
unique: false
column: [name]
tasks:
description: Tasks table
tags:
- Task
columns:
id:
type: int
length: 11
is_null: false
auto_increment: true
description: Task id
description:
type: varchar
length: 255
is_null: false
description: Task description
datetime:
type: datetime
is_null: false
description: Task datetime
user_id:
type: char
length: 8
is_null: false
description: User id. Foreign key.
created_at:
type: timestamp
is_null: false
default: current_timestamp
description: Task datetime created
updated_at:
type: timestamp
is_null: false
default: current_timestamp
description: Task datetime updated
status:
type: int
length: 11
is_null: false
default: 0
description: Task status (0=pending, 1=cancelled, 2=completed)
constraints:
- check: [0, 1, 2]
indexes:
primary:
unique: true
column: [id]
idx_user_id:
unique: false
column: [user_id]
foreign_keys:
- column: user_id
references:
table: users
column: idUse any of the popular LLM chatbots available.
Sample Prompt:
I adapted the OpenAPI scheme to design my mysql database tables.
Please generate the corresponding SQL file.
openapi: 3.0.1
info:
title: My Database
description: Design of tables for the my database.
version: 1.0.0
contact:
name: Author
email: [email protected]
metadata:
dateCreated: '2024-11-05'
dateModified: '2024-11-15'
database:
engine: InnoDB
default_charset: utf8mb4
collate: utf8mb4_general_ci
tables:
users:
description: Users table
tags:
- User
columns:
id:
type: char
length: 8
is_null: false
description: User id
name:
type: char
length: 64
is_null: false
description: User name
password:
type: char
length: 255
is_null: false
description: Hashed password
role:
type: int
length: 11
is_null: false
default: 0
description: User role (0=user, 1=admin)
constraints:
- check: [0, 1]
indexes:
primary:
unique: true
column: [id]
idx_name:
unique: false
column: [name]
tasks:
description: Tasks table
tags:
- Task
columns:
id:
type: int
length: 11
is_null: false
auto_increment: true
description: Task id
description:
type: varchar
length: 255
is_null: false
description: Task description
datetime:
type: datetime
is_null: false
description: Task datetime
user_id:
type: char
length: 8
is_null: false
description: User id. Foreign key.
created_at:
type: timestamp
is_null: false
default: current_timestamp
description: Task datetime created
updated_at:
type: timestamp
is_null: false
default: current_timestamp
description: Task datetime updated
status:
type: int
length: 11
is_null: false
default: 0
description: Task status (0=pending, 1=cancelled, 2=completed)
constraints:
- check: [0, 1, 2]
indexes:
primary:
unique: true
column: [id]
idx_user_id:
unique: false
column: [user_id]
foreign_keys:
- column: user_id
references:
table: users
column: idOutput:
CREATE DATABASE `MyDatabase` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `MyDatabase`;
CREATE TABLE `users` (
`id` CHAR(8) NOT NULL COMMENT 'User id',
`name` CHAR(64) NOT NULL COMMENT 'User name',
`password` CHAR(255) NOT NULL COMMENT 'Hashed password',
`role` INT(11) NOT NULL DEFAULT 0 COMMENT 'User role (0=user, 1=admin)',
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `tasks` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Task id',
`description` VARCHAR(255) NOT NULL COMMENT 'Task description',
`datetime` DATETIME NOT NULL COMMENT 'Task datetime',
`user_id` CHAR(8) NOT NULL COMMENT 'User id. Foreign key.',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Task datetime created',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Task datetime updated',
`status` INT(11) NOT NULL DEFAULT 0 COMMENT 'Task status (0=pending, 1=cancelled, 2=completed)',
PRIMARY KEY (`id`),
INDEX `idx_user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;Neat, huh? :)
2024-11-15