📝 How To: Manage Database Schema

AI Query generates SQL queries but we can also use it to manage database schema. Let's take a look at how to manage database schema.

First, setup an empty database. Once the database is setup, we are ready to manage the database schema.

Create Database Schema

A database schema is the blueprint of a database that outlines its tables, columns, data types, constraints, and relationships. The CREATE TABLE statement is used to create a new table in a database, specifying the table name, column names, and data types for each column.

💡

For example purposes we will use the empty MySQL database that we have setup.

Let's take a look at some Query Prompt's and generated SQL Queries to see how we can create database schema! 😁

Go to the AI Query Dashboard, navigate to Query Playground and select the Database you have setup and set the Query Type as Create Query. Now you are ready to go!

Example 1

Query Prompt

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

SQL Query

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

Example 2

Query Prompt

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

SQL Query

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

Example 3

Query Prompt

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

SQL Query

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

Example 4

Query Prompt

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

SQL Query

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

Example 5

Query Prompt

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

SQL Query

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

Manage Database Schema

Managing a database schema through statements means making modifications to the existing schema or creating new elements in the schema using SQL statements. SQL statements such as ALTER TABLE, DROP TABLE, and CREATE INDEX and many more can be used to modify and manage a database schema.

💡

For example purposes we will use the empty MySQL database that we have setup.

Let's take a look at some Query Prompt's and generated SQL Queries to see how we can manage database schema! 😁

Go to the AI Query Dashboard, navigate to Query Playground and select the Database you have setup and set the Query Type as Create Query. Now you are ready to go!

Example

Query Prompt

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

SQL Query

ALTER TABLE user ADD COLUMN password VARCHAR(255);
CREATE INDEX user_id_index ON account (user_id);
CREATE INDEX email_index ON user (email);

Final Thoughts

As you can see it is fairly easy to get AI Query to generate SQL statements to create and/or manage database schema. You bring the database, AI Query does the rest! 😉

Generate Accurate SQL Queries Overview