Generate SQL CREATE TABLE Statements with AI Bot

AI Bot generates SQL queries for you in seconds but it can also easily be used to build your database schema, and also migrations. The AI Bot is a effective tool to use when you want to create a database schema for a new project, or when you want to create a migration for an existing project.

Create database schema

To demonstrate this I will use AI Bot to create a database schema for next-auth. The database schema comprises of 4 tables and stores authentication and user data. I have take the liberty to straighten out the notation so that all uses snake case (as well as adding created_at and updated_at columns).

Let's start with the user table. It has 7 columns, id, name, email, email_verified, image, created_at and updated_at. To create the table and columns we ask AI Bot to create the user table (since other tables has a foreign key to user.id we will create the user table first):

create table user with id, name, email, email_verified, image, created_at, updated_at

AI Bot returns (snippet link):

CREATE TABLE
user (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  email_verified TINYINT (1) DEFAULT 0,
  image VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

And the account table and columns:

create table account with id, user_id (foreign key to user.id), type, provider, provider_account_id, refresh_token, access_token, expires_at (int), token_type, scope, id_token, session_state, created_at, updated_at

AI Bot returns (snippet link):

CREATE TABLE
account (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (id),
  type VARCHAR(255) NOT NULL,
  provider VARCHAR(255) NOT NULL,
  provider_account_id VARCHAR(255) NOT NULL,
  refresh_token TEXT,
  access_token TEXT,
  expires_at INT,
  token_type VARCHAR(255),
  scope VARCHAR(255),
  id_token TEXT,
  session_state TEXT
);

And the session table and columns:

create table session id, session_token, user_id (foreign key to user.id), expires, created_at, updated_at

AI Bot returns (snippet link):

CREATE TABLE
session (
  id INT NOT NULL AUTO_INCREMENT,
  session_token VARCHAR(255),
  user_id INT,
  expires DATETIME,
  created_at DATETIME,
  updated_at DATETIME,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user (id)
);

And the verification_token table and columns:

create table verification_token with identifier, token (unique), expires, created_at, updated_at

AI returns (snippet link):

CREATE TABLE
verification_token (
  identifier VARCHAR(255),
  token VARCHAR(255) UNIQUE,
  expires DATETIME,
  created_at DATETIME,
  updated_at DATETIME
);

AI Bot will generate the create table SQL statement and deduct any information it can from the column names. You can feed it with more information to make it fit your requirements. The easiest way to do this is too add column specifications in parenthesis after the column name such as:

create table account with id, user_id (foreign key to user.id, unique), type, provider, provider_account_id, refresh_token, access_token, expires_at (int, not null), token_type, scope, id_token, session_state, created_at, updated_at

CREATE TABLE
account (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNIQUE NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (id),
  type VARCHAR(255),
  provider VARCHAR(255),
  provider_account_id VARCHAR(255),
  refresh_token VARCHAR(255),
  access_token VARCHAR(255),
  expires_at INT NOT NULL,
  token_type VARCHAR(255),
  scope VARCHAR(255),
  id_token VARCHAR(255),
  session_state VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Notice after re-running the command the created_at and updated_at columns are now timestamp columns with default values. Sometimes adding more information and re-running the command will give you a better result. AI Bot makes it not only a breeze to create a database schema but also to create migrations.

Create database migration

Let's say we wanted to add a password column to the user table and we wanted account.user_id to be index along with user.email. We can do this by adding the column to the command:

add password column to user table and add index to account.user_id and user.email

AI Bot returns (snippet link):

ALTER TABLE `user`
ADD COLUMN `password` VARCHAR(255) NOT NULL;

CREATE INDEX `idx_user_id` ON `account` (`user_id`);

CREATE INDEX `idx_user_email` ON `user` (`email`);

Conclusion

Using AI Bot I have build a database schema for a project and created a migration in minutes. Using AI Bot you can use SQL directly without having to rely on 3rd party tools or libraries (no more ORM abstractions). It keeps your tech stack simple and you can use it in any language. You can use it to generate SQL for any database (Oracle, MSSQL, Postgres, SQLite etc.). It's not limited to MySQL. Whether seasoned or new to SQL, AI Bot can help you build better and faster database schemas and migrations.