MySQL setup

important

This is needed only if you are running the SuperTokens core yourself.

blog

We also have a blog post writeup highlighting all the steps in more detail for different scenarios.

1) Create a database 🛠️#

CREATE DATABASE supertokens;

You can skip this step if you want SuperTokens to write to your own database. In this case, you will need to provide your database's name as shown in the step below.

2) Connect SuperTokens to your database 🔌#

With Docker
Without Docker

caution

Host being localhost / 127.0.0.1 will not work in a docker image. Instead, please provide the database's local / public hostname or IP address.

You also need to make the database listen on all the IP's of the local machine. This can be done by editing the mysqld.cnf config file and setting the value of bind-address to 0.0.0.0.


docker run \
    -p 3567:3567 \
    -e MYSQL_CONNECTION_URI="mysql://username:pass@host/dbName" \
    -d registry.supertokens.io/supertokens/supertokens-mysql

# OR

docker run \
    -p 3567:3567 \
    -e MYSQL_USER="username" \
    -e MYSQL_PASSWORD="password" \
    -e MYSQL_HOST="host" \
    -e MYSQL_PORT="3306" \
    -e MYSQL_DATABASE_NAME="supertokens" \
    -d registry.supertokens.io/supertokens/supertokens-mysql

# You need to add the following to the config.yaml file.
# The file path can be found by running the "supertokens --help" command

mysql_connection_uri: "mysql://username:pass@host/dbName"

# OR

mysql_user: "username"

mysql_password: "password"

mysql_host: "host"

mysql_port: 3306

mysql_database_name: "supertokens"

3) Create tables 👩‍💻👨‍💻#

note

This happens automatically, unless you provide a MySQL user that doesn't have table creation permission.

CREATE TABLE `apps` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `created_at_time` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`)
);

CREATE TABLE `tenants` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `created_at_time` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `tenant_configs` (
  `connection_uri_domain` varchar(256) NOT NULL DEFAULT '',
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `core_config` text,
  `email_password_enabled` tinyint(1) DEFAULT NULL,
  `passwordless_enabled` tinyint(1) DEFAULT NULL,
  `third_party_enabled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`connection_uri_domain`,`app_id`,`tenant_id`)
);

CREATE TABLE `tenant_thirdparty_providers` (
  `connection_uri_domain` varchar(256) NOT NULL DEFAULT '',
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `third_party_id` varchar(28) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `authorization_endpoint` text,
  `authorization_endpoint_query_params` text,
  `token_endpoint` text,
  `token_endpoint_body_params` text,
  `user_info_endpoint` text,
  `user_info_endpoint_query_params` text,
  `user_info_endpoint_headers` text,
  `jwks_uri` text,
  `oidc_discovery_endpoint` text,
  `require_email` tinyint(1) DEFAULT NULL,
  `user_info_map_from_id_token_payload_user_id` varchar(64) DEFAULT NULL,
  `user_info_map_from_id_token_payload_email` varchar(64) DEFAULT NULL,
  `user_info_map_from_id_token_payload_email_verified` varchar(64) DEFAULT NULL,
  `user_info_map_from_user_info_endpoint_user_id` varchar(64) DEFAULT NULL,
  `user_info_map_from_user_info_endpoint_email` varchar(64) DEFAULT NULL,
  `user_info_map_from_user_info_endpoint_email_verified` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`connection_uri_domain`,`app_id`,`tenant_id`,`third_party_id`),
  FOREIGN KEY (`connection_uri_domain`, `app_id`, `tenant_id`) REFERENCES `tenant_configs` (`connection_uri_domain`, `app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE TABLE `tenant_thirdparty_provider_clients` (
  `connection_uri_domain` varchar(256) NOT NULL DEFAULT '',
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `third_party_id` varchar(28) NOT NULL,
  `client_type` varchar(64) NOT NULL DEFAULT '',
  `client_id` varchar(256) NOT NULL,
  `client_secret` text,
  `scope` text,
  `force_pkce` tinyint(1) DEFAULT NULL,
  `additional_config` text,
  PRIMARY KEY (`connection_uri_domain`,`app_id`,`tenant_id`,`third_party_id`,`client_type`),
  FOREIGN KEY (`connection_uri_domain`, `app_id`, `tenant_id`, `third_party_id`) REFERENCES `tenant_thirdparty_providers` (`connection_uri_domain`, `app_id`, `tenant_id`, `third_party_id`) ON DELETE CASCADE
);

CREATE TABLE `key_value` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `name` varchar(128) NOT NULL,
  `value` text,
  `created_at_time` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`name`),
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE TABLE `app_id_to_user_id` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `recipe_id` varchar(128) NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  CONSTRAINT `app_id_to_user_id_ibfk_1` FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `all_auth_recipe_users` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `recipe_id` varchar(128) NOT NULL,
  `time_joined` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`),
  KEY `app_id` (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE,
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `app_id_to_user_id` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE INDEX `all_auth_recipe_users_pagination_index` ON `all_auth_recipe_users` (`time_joined` DESC,`user_id` DESC,`tenant_id` DESC,`app_id` DESC);

CREATE TABLE `userid_mapping` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `supertokens_user_id` char(36) NOT NULL,
  `external_user_id` varchar(128) NOT NULL,
  `external_user_id_info` text,
  PRIMARY KEY (`app_id`,`supertokens_user_id`,`external_user_id`),
  UNIQUE KEY `supertokens_user_id` (`app_id`,`supertokens_user_id`),
  UNIQUE KEY `external_user_id` (`app_id`,`external_user_id`),
  FOREIGN KEY (`app_id`, `supertokens_user_id`) REFERENCES `app_id_to_user_id` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `dashboard_users` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `email` varchar(256) NOT NULL,
  `password_hash` varchar(256) NOT NULL,
  `time_joined` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  UNIQUE KEY `email` (`app_id`,`email`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `dashboard_user_sessions` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `session_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `time_created` bigint unsigned NOT NULL,
  `expiry` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`session_id`),
  KEY `app_id` (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `dashboard_users` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE INDEX `dashboard_user_sessions_expiry_index` ON `dashboard_user_sessions` (`expiry`);

CREATE TABLE `session_access_token_signing_keys` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `created_at_time` bigint unsigned NOT NULL,
  `value` text,
  PRIMARY KEY (`app_id`,`created_at_time`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `session_info` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `session_handle` varchar(255) NOT NULL,
  `user_id` varchar(128) NOT NULL,
  `refresh_token_hash_2` varchar(128) NOT NULL,
  `session_data` text,
  `expires_at` bigint unsigned NOT NULL,
  `created_at_time` bigint unsigned NOT NULL,
  `jwt_user_payload` text,
  `use_static_key` tinyint(1) NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`session_handle`),
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE INDEX `session_expiry_index` ON `session_info` (`expires_at`);

CREATE TABLE `user_last_active` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `last_active_time` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `emailpassword_users` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `email` varchar(256) NOT NULL,
  `password_hash` varchar(256) NOT NULL,
  `time_joined` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `app_id_to_user_id` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `emailpassword_user_to_tenant` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `email` varchar(256) NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`),
  UNIQUE KEY `email` (`app_id`,`tenant_id`,`email`),
  FOREIGN KEY (`app_id`, `tenant_id`, `user_id`) REFERENCES `all_auth_recipe_users` (`app_id`, `tenant_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `emailpassword_pswd_reset_tokens` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `token` varchar(128) NOT NULL,
  `token_expiry` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`,`token`),
  UNIQUE KEY `token` (`token`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `emailpassword_users` (`app_id`, `user_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX `emailpassword_password_reset_token_expiry_index` ON `emailpassword_pswd_reset_tokens` (`token_expiry`);

CREATE TABLE `emailverification_verified_emails` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `email` varchar(256) NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`,`email`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `emailverification_tokens` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `email` varchar(256) NOT NULL,
  `token` varchar(128) NOT NULL,
  `token_expiry` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`,`email`,`token`),
  UNIQUE KEY `token` (`token`),
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE INDEX `emailverification_tokens_index` ON `emailverification_tokens` (`token_expiry`);

CREATE TABLE `thirdparty_users` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `third_party_id` varchar(28) NOT NULL,
  `third_party_user_id` varchar(256) NOT NULL,
  `user_id` char(36) NOT NULL,
  `email` varchar(256) NOT NULL,
  `time_joined` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `app_id_to_user_id` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE INDEX `thirdparty_users_email_index` ON `thirdparty_users` (`app_id`,`email`);

CREATE INDEX `thirdparty_users_thirdparty_user_id_index` ON `thirdparty_users` (`app_id`,`third_party_id`,`third_party_user_id`);

CREATE TABLE `thirdparty_user_to_tenant` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `third_party_id` varchar(28) NOT NULL,
  `third_party_user_id` varchar(256) NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`),
  UNIQUE KEY `third_party_user_id` (`app_id`,`tenant_id`,`third_party_id`,`third_party_user_id`),
  FOREIGN KEY (`app_id`, `tenant_id`, `user_id`) REFERENCES `all_auth_recipe_users` (`app_id`, `tenant_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `passwordless_users` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `email` varchar(256) DEFAULT NULL,
  `phone_number` varchar(256) DEFAULT NULL,
  `time_joined` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `app_id_to_user_id` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `passwordless_user_to_tenant` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` char(36) NOT NULL,
  `email` varchar(256) DEFAULT NULL,
  `phone_number` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`),
  UNIQUE KEY `email` (`app_id`,`tenant_id`,`email`),
  UNIQUE KEY `phone_number` (`app_id`,`tenant_id`,`phone_number`),
  FOREIGN KEY (`app_id`, `tenant_id`, `user_id`) REFERENCES `all_auth_recipe_users` (`app_id`, `tenant_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `passwordless_devices` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `device_id_hash` char(44) NOT NULL,
  `email` varchar(256) DEFAULT NULL,
  `phone_number` varchar(256) DEFAULT NULL,
  `link_code_salt` char(44) NOT NULL,
  `failed_attempts` int unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`device_id_hash`),
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE INDEX `passwordless_devices_email_index` ON `passwordless_devices` (`app_id`,`tenant_id`,`email`);

CREATE INDEX `passwordless_devices_phone_number_index` ON `passwordless_devices` (`app_id`,`tenant_id`,`phone_number`);

CREATE TABLE `passwordless_codes` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `code_id` char(36) NOT NULL,
  `device_id_hash` char(44) NOT NULL,
  `link_code_hash` char(44) NOT NULL,
  `created_at` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`code_id`),
  UNIQUE KEY `link_code_hash` (`app_id`,`tenant_id`,`link_code_hash`),
  KEY `app_id` (`app_id`,`tenant_id`,`device_id_hash`),
  FOREIGN KEY (`app_id`, `tenant_id`, `device_id_hash`) REFERENCES `passwordless_devices` (`app_id`, `tenant_id`, `device_id_hash`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX `passwordless_codes_created_at_index` ON `passwordless_codes` (`app_id`,`tenant_id`,`created_at`);

CREATE TABLE `jwt_signing_keys` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `key_id` varchar(255) NOT NULL,
  `key_string` text NOT NULL,
  `algorithm` varchar(10) NOT NULL,
  `created_at` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`app_id`,`key_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `user_metadata` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `user_metadata` text NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `roles` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `role` varchar(255) NOT NULL,
  PRIMARY KEY (`app_id`,`role`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `role_permissions` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `role` varchar(255) NOT NULL,
  `permission` varchar(255) NOT NULL,
  PRIMARY KEY (`app_id`,`role`,`permission`),
  FOREIGN KEY (`app_id`, `role`) REFERENCES `roles` (`app_id`, `role`) ON DELETE CASCADE
);

CREATE INDEX `role_permissions_permission_index` ON `role_permissions` (`app_id`,`permission`);

CREATE TABLE `user_roles` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `role` varchar(255) NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`,`role`),
  KEY `app_id` (`app_id`,`role`),
  FOREIGN KEY (`app_id`, `role`) REFERENCES `roles` (`app_id`, `role`) ON DELETE CASCADE,
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE INDEX `user_roles_role_index` ON `user_roles` (`app_id`,`tenant_id`,`role`);

CREATE TABLE `totp_users` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE
);

CREATE TABLE `totp_user_devices` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `device_name` varchar(256) NOT NULL,
  `secret_key` varchar(256) NOT NULL,
  `period` int NOT NULL,
  `skew` int NOT NULL,
  `verified` tinyint(1) NOT NULL,
  PRIMARY KEY (`app_id`,`user_id`,`device_name`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `totp_users` (`app_id`, `user_id`) ON DELETE CASCADE
);

CREATE TABLE `totp_used_codes` (
  `app_id` varchar(64) NOT NULL DEFAULT 'public',
  `tenant_id` varchar(64) NOT NULL DEFAULT 'public',
  `user_id` varchar(128) NOT NULL,
  `code` varchar(8) NOT NULL,
  `is_valid` tinyint(1) NOT NULL,
  `expiry_time_ms` bigint unsigned NOT NULL,
  `created_time_ms` bigint unsigned NOT NULL,
  PRIMARY KEY (`app_id`,`tenant_id`,`user_id`,`created_time_ms`),
  KEY `app_id` (`app_id`,`user_id`),
  FOREIGN KEY (`app_id`, `user_id`) REFERENCES `totp_users` (`app_id`, `user_id`) ON DELETE CASCADE,
  FOREIGN KEY (`app_id`, `tenant_id`) REFERENCES `tenants` (`app_id`, `tenant_id`) ON DELETE CASCADE
);

CREATE INDEX `totp_used_codes_expiry_time_ms_index` ON `totp_used_codes` (`app_id`,`tenant_id`,`expiry_time_ms`);

tip

You also have the option to rename these tables.

4) Test the connection 🤞#

To test, start SuperTokens and run the following query in your database

SELECT * FROM key_value;

If you see at least one row, it means that the connection has been successfully completed! 🥳🎉

blog

We also have a blog post writeup highlighting all the steps in more detail for different scenarios.

Auth Methods

Add ons

MySQL setup

important

blog

1) Create a database 🛠️#

2) Connect SuperTokens to your database 🔌#

caution

3) Create tables 👩‍💻👨‍💻#

note

tip

4) Test the connection 🤞#

blog