Skip to main content

PostgreSQL setup

important

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 ๐Ÿ”Œ#

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 postgresql.conf config file and setting the value of listen_addresses to 0.0.0.0.


docker run \    -p 3567:3567 \    -e POSTGRESQL_CONNECTION_URI="postgresql://username:[email protected]/dbName" \     -d supertokens/supertokens-postgresql
# OR
docker run \    -p 3567:3567 \    -e POSTGRESQL_USER="username" \    -e POSTGRESQL_PASSWORD="password" \    -e POSTGRESQL_HOST="host" \    -e POSTGRESQL_PORT="5432" \    -e POSTGRESQL_DATABASE_NAME="supertokens" \     -d supertokens/supertokens-postgresql
tip

You can also provide the table schema by providing the POSTGRESQL_TABLE_SCHEMA option.

3) Create tables ๐Ÿ‘ฉโ€๐Ÿ’ป๐Ÿ‘จโ€๐Ÿ’ป#

note

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

CREATE TABLE IF NOT EXISTS key_value (    name VARCHAR(128),    value TEXT,    created_at_time BIGINT,    CONSTRAINT key_value_pkey PRIMARY KEY(name));
CREATE TABLE IF NOT EXISTS all_auth_recipe_users(    user_id CHAR(36) NOT NULL,    recipe_id VARCHAR(128) NOT NULL,    time_joined BIGINT NOT NULL,    CONSTRAINT all_auth_recipe_users_pkey PRIMARY KEY (user_id));CREATE INDEX all_auth_recipe_users_pagination_index ON all_auth_recipe_users (time_joined DESC, user_id DESC);
CREATE TABLE session_access_token_signing_keys (    created_at_time BIGINT NOT NULL,    value TEXT,    CONSTRAINT session_access_token_signing_keys_pkey PRIMARY KEY (created_at_time));
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 NOT NULL,    created_at_time BIGINT NOT NULL,    jwt_user_payload TEXT,    CONSTRAINT session_info_pkey PRIMARY KEY(session_handle));
CREATE TABLE IF NOT EXISTS emailpassword_users (    user_id CHAR(36) NOT NULL,    email VARCHAR(256) NOT NULL CONSTRAINT emailpassword_users_email_key UNIQUE,    password_hash VARCHAR(128) NOT NULL,    time_joined BIGINT NOT NULL,    CONSTRAINT emailpassword_users_pkey PRIMARY KEY (user_id));
CREATE TABLE IF NOT EXISTS emailpassword_pswd_reset_tokens (    user_id CHAR(36) NOT NULL,    token VARCHAR(128) NOT NULL CONSTRAINT emailpassword_pswd_reset_tokens_token_key UNIQUE,    token_expiry BIGINT NOT NULL,    CONSTRAINT emailpassword_pswd_reset_tokens_pkey PRIMARY KEY (user_id, token),    CONSTRAINT emailpassword_pswd_reset_tokens_user_id_fkey 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 VARCHAR(128) NOT NULL,    email VARCHAR(256),    CONSTRAINT emailverification_verified_emails_pkey PRIMARY KEY (user_id, email));
CREATE TABLE IF NOT EXISTS emailverification_tokens (    user_id VARCHAR(128) NOT NULL,    email VARCHAR(256),    token VARCHAR(128) NOT NULL CONSTRAINT emailverification_tokens_token_key UNIQUE,    token_expiry BIGINT NOT NULL,    CONSTRAINT emailverification_tokens_pkey 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 CONSTRAINT thirdparty_users_user_id_key UNIQUE,    email VARCHAR(256) NOT NULL,    time_joined BIGINT NOT NULL,    CONSTRAINT thirdparty_users_pkey PRIMARY KEY (third_party_id, third_party_user_id));
CREATE TABLE IF NOT EXISTS jwt_signing_keys (    key_id VARCHAR(255) NOT NULL,    key_string TEXT NOT NULL,    algorithm VARCHAR(10) NOT NULL,    created_at BIGINT,    CONSTRAINT jwt_signing_keys_pkey PRIMARY KEY(key_id));
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! ๐Ÿฅณ๐ŸŽ‰