SuperTokens

SuperTokens

  • Docs
  • Discord
  • Blog

โ€บDatabase Setup

SIDEBAR_REPLACE_DOC_About this recipe

  • About this recipe

Quick setup

  • Frontend
  • Backend
  • Core

    • Self Hosted setup with Docker
    • Self Hosted setup without Docker
    • Managed Service

    Database Setup

    • MySQL
    • PostgreSQL
    • MongoDB
    • Rename database tables

Common customizations

  • Redirect To Auth Screen
  • Sign Out
  • SignUp Form

    • Prerequisite
    • Adding Extra Fields
    • Adding / Modifying field validators
    • Built in providers
    • Custom providers
    • Embed in a page
    • Terms of service & Privacy policy links
    • Show Sign Up by default

    SignIn Form

    • Prerequisite
    • Adding / Modifying field validators
    • Built in providers
    • Custom providers
    • Password managers
    • Embed in a page
  • Post sign up callbacks
  • Post sign in callbacks
  • User Roles

    • Assigning roles to users
    • Assigning roles to a session
    • Reading roles in an API
    • Reading roles in the frontend
    • Updating roles in a session

    Reset Password

    • About
    • Reset Password Email
    • Embed in a page

    Email Verification

    • About
    • Customising the email sent
    • Embed in a page

    Sessions

    • About
    • Storing session data
    • Session Verification in API
    • Revoking a session manually
    • Change session timeout
    • Checking if a session exists on the frontend
    • Get user information on the frontend
    • Fetching sessions for a user
    • Update JWT Payload
    • Update Session Data
    • Cookies and Https
    • Cookie Consent
    • Share sessions across sub domains
    • Anti CSRF
    • Same site cookies
    • JWT Signing key rotation
    • Access token blacklisting
    • Customizing Error Handling

    Styling

    • Changing Colours
    • Changing Style via CSS
    • Themes

    Changing base path

    • Website Base Path
    • API Base Path
  • User Pagination
  • Core

    • Adding API Keys
    • Tuning Performance
    • Logging

    Core CLI

    • Overview
    • Start
    • List
    • Stop
    • Uninstall

NextJS

  • About
  • 1. Configuration
  • 2. Showing Login UI
  • 3. Adding auth APIs
  • 4. Protecting a website route
  • 5. Session verification

    • 5a. Session verification in an API call
    • 5b. Session verification in getServerSideProps
  • 6. Next steps

SIDEBAR_REPLACE_DOC_Serverless Optimisation

  • Running on serverless env

SIDEBAR_REPLACE_DOC_SDKs API Reference

  • SDKs API Reference

MySQL setup

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

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 name as shown in the step below.

2๏ธโƒฃ Connect SuperTokens to your database ๐Ÿ”Œ

With Docker
Without Docker
# NOTE: host being localhost / 127.0.0.1 will not work in a docker image.
# Please provide the database's local / public hostname or IP address

docker run \
-p 3567:3567 \
-e MYSQL_CONNECTION_URI="mysql://username:[email protected]/dbName" \
-d 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 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:[email protected]/dbName"

# OR

mysql_user: "username"

mysql_password: "password"

mysql_host: "host"

mysql_port: 3306

mysql_database_name: "supertokens"

3๏ธโƒฃ Create tables ๐Ÿ‘ฉโ€๐Ÿ’ป๐Ÿ‘จโ€๐Ÿ’ป

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

CREATE TABLE IF NOT EXISTS key_value (
    name VARCHAR(128),
    value TEXT,
    created_at_time BIGINT UNSIGNED,
    PRIMARY KEY(name)
);

CREATE TABLE IF NOT EXISTS session_info (
    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,
    PRIMARY KEY(session_handle)
);

CREATE TABLE IF NOT EXISTS emailpassword_users (
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256) NOT NULL UNIQUE,
    password_hash VARCHAR(128) NOT NULL,
    time_joined BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE INDEX emailpassword_user_pagination_index ON emailpassword_users(time_joined DESC, user_id DESC);

CREATE TABLE IF NOT EXISTS emailpassword_pswd_reset_tokens (
    user_id CHAR(36) NOT NULL,
    token VARCHAR(128) NOT NULL UNIQUE,
    token_expiry BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, token),
    FOREIGN KEY (user_id) REFERENCES emailpassword_users (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 IF NOT EXISTS emailverification_verified_emails (
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256),
    PRIMARY KEY (user_id, email)
);

CREATE TABLE IF NOT EXISTS emailverification_tokens (
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256),
    token VARCHAR(128) NOT NULL UNIQUE,
    token_expiry BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, email, token)
);

CREATE INDEX emailverification_tokens_index ON emailverification_tokens(token_expiry);

CREATE TABLE IF NOT EXISTS thirdparty_users (
    third_party_id VARCHAR(28) NOT NULL,
    third_party_user_id VARCHAR(128) NOT NULL,
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256) NOT NULL UNIQUE,
    time_joined BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (third_party_id, third_party_user_id)
);

CREATE INDEX thirdparty_users_pagination_index ON thirdparty_users(time_joined DESC, user_id DESC);

You also have the option to rename these tables if you want. You can do so by passing relevant configs to the core via the docker env variables or the config.yaml file.

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! ๐Ÿฅณ๐ŸŽ‰

โ† Managed ServicePostgreSQL โ†’