Skip to main content

MySQL

Schema for a MySQL database.

note

When using a MySQL database with the default adapter (TypeORM) all timestamp columns use 6 digits of precision (unless another value for precision is specified in the schema) and the timezone is set to Z (aka Zulu Time / UTC) and all timestamps are stored in UTC.

CREATE TABLE accounts
(
id INT NOT NULL AUTO_INCREMENT,
compound_id VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL,
provider_type VARCHAR(255) NOT NULL,
provider_id VARCHAR(255) NOT NULL,
provider_account_id VARCHAR(255) NOT NULL,
refresh_token TEXT,
access_token TEXT,
access_token_expires TIMESTAMP(6),
created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (id)
);
CREATE TABLE sessions
(
id INT NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
expires TIMESTAMP(6) NOT NULL,
session_token VARCHAR(255) NOT NULL,
access_token VARCHAR(255) NOT NULL,
created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (id)
);
CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255),
email_verified TIMESTAMP(6),
image VARCHAR(255),
created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (id)
);
CREATE TABLE verification_requests
(
id INT NOT NULL AUTO_INCREMENT,
identifier VARCHAR(255) NOT NULL,
token VARCHAR(255) NOT NULL,
expires TIMESTAMP(6) NOT NULL,
created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX compound_id
ON accounts(compound_id);
CREATE INDEX provider_account_id
ON accounts(provider_account_id);
CREATE INDEX provider_id
ON accounts(provider_id);
CREATE INDEX user_id
ON accounts(user_id);
CREATE UNIQUE INDEX session_token
ON sessions(session_token);
CREATE UNIQUE INDEX access_token
ON sessions(access_token);
CREATE UNIQUE INDEX email
ON users(email);
CREATE UNIQUE INDEX token
ON verification_requests(token);