Database Configuration

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=supertokens \
  -p 5432:5432 \
  postgres:15

-- Connect to PostgreSQL
psql -U postgres

-- Create database
CREATE DATABASE supertokens;

-- Create user
CREATE USER supertokens_user WITH PASSWORD 'your_secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE supertokens TO supertokens_user;

-- For PostgreSQL 15+, also grant schema privileges
\c supertokens
GRANT ALL ON SCHEMA public TO supertokens_user;

postgresql_connection_uri: "postgresql://supertokens_user:your_secure_password@localhost:5432/supertokens"

POSTGRESQL_CONNECTION_URI="postgresql://supertokens_user:your_secure_password@localhost:5432/supertokens"

postgresql://[user[:password]@][host][:port][/database][?param1=value1&...]

# Basic connection
postgresql_connection_uri: "postgresql://user:pass@localhost:5432/supertokens"

# With SSL
postgresql_connection_uri: "postgresql://user:pass@host:5432/supertokens?sslmode=require"

# Cloud providers (AWS RDS)
postgresql_connection_uri: "postgresql://user:[email protected]:5432/supertokens?sslmode=require"

# Multiple hosts (failover)
postgresql_connection_uri: "postgresql://user:pass@host1:5432,host2:5432/supertokens"

-- Tune PostgreSQL configuration
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET effective_cache_size = '1GB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET default_statistics_target = 100;
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET work_mem = '4MB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET max_wal_size = '4GB';

-- Restart PostgreSQL to apply changes

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql
sudo mysql_secure_installation

docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e MYSQL_DATABASE=supertokens \
  -e MYSQL_USER=supertokens_user \
  -e MYSQL_PASSWORD=your_secure_password \
  -p 3306:3306 \
  mysql:8.0

-- Connect to MySQL
mysql -u root -p

-- Create database
CREATE DATABASE supertokens CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user
CREATE USER 'supertokens_user'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON supertokens.* TO 'supertokens_user'@'%';
FLUSH PRIVILEGES;

mysql_connection_uri: "mysql://supertokens_user:your_secure_password@localhost:3306/supertokens"

MYSQL_CONNECTION_URI="mysql://supertokens_user:your_secure_password@localhost:3306/supertokens"

mysql://[user[:password]@][host][:port][/database][?param1=value1&...]

# Basic connection
mysql_connection_uri: "mysql://user:pass@localhost:3306/supertokens"

# With SSL
mysql_connection_uri: "mysql://user:pass@host:3306/supertokens?useSSL=true"

# Cloud providers (AWS RDS)
mysql_connection_uri: "mysql://user:[email protected]:3306/supertokens?useSSL=true"

# Connection pooling settings
mysql_connection_uri: "mysql://user:pass@host:3306/supertokens?maxPoolSize=10"

-- Optimize for SuperTokens workload
SET GLOBAL innodb_buffer_pool_size = 268435456; -- 256MB
SET GLOBAL max_connections = 200;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_log_file_size = 67108864; -- 64MB

# Install MongoDB
sudo apt install mongodb-org
sudo systemctl start mongod
sudo systemctl enable mongod

mongodb_connection_uri: "mongodb://username:password@localhost:27017/supertokens"

# SQLite will be used by default
# No configuration needed

# Increase server thread pool
max_server_pool_size: 50

# Located in the installation directory
ls migration_scripts/

-- PostgreSQL
\i migration_scripts/postgresql/001_initial_schema.sql

-- MySQL
source migration_scripts/mysql/001_initial_schema.sql

SELECT * FROM st_schema_version;

# Primary connection with replica failover
postgresql_connection_uri: "postgresql://user:pass@primary:5432,replica1:5432,replica2:5432/supertokens?target_session_attrs=read-write"

postgresql_connection_uri: "postgresql://user:pass@pgbouncer:6432/supertokens"

mysql_connection_uri: "mysql://user:pass@node1:3306,node2:3306,node3:3306/supertokens"

mysql_connection_uri: "mysql://user:pass@proxysql:6033/supertokens"

# Backup
pg_dump -U supertokens_user -h localhost supertokens > backup.sql

# Restore
psql -U supertokens_user -h localhost supertokens < backup.sql

# Configure WAL archiving in postgresql.conf
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

# Base backup
pg_basebackup -D /backup/base -F tar -z -P

# Backup
mysqldump -u supertokens_user -p supertokens > backup.sql

# Restore
mysql -u supertokens_user -p supertokens < backup.sql

# Enable binary logging in my.cnf
log_bin = mysql-bin
server_id = 1

# PostgreSQL
postgresql_connection_uri: "postgresql://admin:[email protected]:5432/supertokens?sslmode=require"

# MySQL
mysql_connection_uri: "mysql://admin:[email protected]:3306/supertokens?useSSL=true"

# Using Cloud SQL Proxy
postgresql_connection_uri: "postgresql://user:pass@localhost:5432/supertokens"

# Or direct connection
postgresql_connection_uri: "postgresql://user:pass@IP_ADDRESS:5432/supertokens"

# PostgreSQL
postgresql_connection_uri: "postgresql://user@servername:[email protected]:5432/supertokens?sslmode=require"

# MySQL
mysql_connection_uri: "mysql://user@servername:[email protected]:3306/supertokens?useSSL=true"

postgresql_connection_uri: "postgresql://user:[email protected]:25060/supertokens?sslmode=require"

# PostgreSQL
psql -U supertokens_user -h localhost -d supertokens

# MySQL
mysql -u supertokens_user -h localhost -p supertokens

-- PostgreSQL
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- MySQL
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- PostgreSQL
SELECT count(*) FROM pg_stat_activity;

-- MySQL
SHOW PROCESSLIST;