Setup scripts for test databases for Oracle, MySQL, Postgres, SQL Server, and Db2

TestDatabaseSetup.md

Test Database Setup

Container creation

Podman run

Test database and user creation

• MySQL
• Postgres
• Oracle
• SQL Server
• Db2

Connect as test user to test database

• MySQL
• Postgres
• Oracle
• SQL Server
• Db2

Test database and user deletion

• MySQL
• Postgres
• Oracle
• SQL Server
• Db2

Podman run

podman run -d --name mysql -e MYSQL_ROOT_PASSWORD=LetsTest1 -p 3306:3306 -v mysql:/var/lib/mysql mysql

podman run -d --name postgres -e POSTGRES_PASSWORD=LetsTest1 -p 5432:5432 -v postgres:/var/lib/postgresql/data postgres

podman run -d --name oracle-free -e ORACLE_PASSWORD=LetsTest1 -p 1521:1521 -v oracle-free:/opt/oracle/oradata gvenzl/oracle-free:23-slim

# Volumes aren't supported for SQL Server on Mac
podman run -d --name sqlserver -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=LetsTest1' -p 1433:1433 -v sqlserver:/var/opt/mssql mcr.microsoft.com/mssql/server:2022-latest

podman run -d --name db2 -e LICENSE=accept --privileged=true -e DBNAME=test -e DB2INST1_PASSWORD=LetsTest1 -p 50000:50000 -v db2:/database icr.io/db2_community/db2

Test database and user creation

MySQL

Shell

mysql -u root -pLetsTest1

Podman

podman exec -ti mysql mysql -u root -pLetsTest1

Create test database and test user

CREATE DATABASE test;
CREATE USER 'test' IDENTIFIED BY 'LetsTest1';
GRANT ALL PRIVILEGES ON test.* TO 'test';
FLUSH PRIVILEGES;
exit;

Postgres

Shell

psql -U postgres

Podman

podman exec -it postgres psql -U postgres

Create test database and test user

CREATE DATABASE test;
CREATE USER test WITH ENCRYPTED PASSWORD 'LetsTest1';
GRANT ALL PRIVILEGES ON DATABASE test TO test;
\c test test
CREATE SCHEMA test AUTHORIZATION test;
exit;

Oracle

Shell

sqlplus sys/LetsTest1@localhost/XE as sysdba

Podman

podman exec -ti oracle-xe sqlplus sys/LetsTest1@localhost/XE as sysdba

Create test database and test user

CREATE PLUGGABLE DATABASE test ADMIN USER test IDENTIFIED BY LetsTest1 FILE_NAME_CONVERT=('pdbseed','test');
ALTER PLUGGABLE DATABASE test OPEN;
ALTER PLUGGABLE DATABASE test SAVE STATE;
ALTER SESSION SET CONTAINER=test;
GRANT CONNECT, RESOURCE, CREATE VIEW, UNLIMITED TABLESPACE TO test;
exit;

SQL Server

Shell

sqlcmd -C -S localhost -U sa -P LetsTest1

Podman

podman exec -it sqlserver /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P LetsTest1

Create test database and test user

sp_configure 'contained database authentication', 1;
RECONFIGURE;
go
CREATE DATABASE test CONTAINMENT=PARTIAL;
go
USE test;
CREATE USER test WITH PASSWORD = 'LetsTest1';
go
ALTER ROLE db_owner ADD MEMBER test;
go
exit

Db2

Shell

db2 -t

Docker

docker exec -ti db2 su - db2inst1 -c "db2 -t"

Create test database

CREATE DATABASE test;
quit;

Connect as test user to test database

MySQL

Shell

mysql -u test -pLetsTest1 -D test

Podman

podman exec -ti mysql mysql -u test -pLetsTest1 -D test

Postgres

Shell

psql -U test -d test

Podman

podman exec -it postgres psql -U test -d test

Oracle

Shell

sqlplus test/LetsTest1@localhost/XE

Podman

sudo podman exec -ti oracle-xe su oracle -c "sqlplus test/LetsTest1@localhost/XE"

SQL Server

Shell

sqlcmd -S localhost -U test -P LetsTest1 -d test

Podman

podman exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U test -P LetsTest1 -d test

Db2

Shell

db2 -t
CONNECT TO TEST;

Docker

docker exec -ti db2 su - db2inst1 -c "db2 -t"
CONNECT TO TEST;

Test database and user deletion

MySQL

Shell

mysql -u root -pLetsTest1

Podman

podman exec -ti mysql mysql -u root -pLetsTest1

Drop test database and test user

DROP DATABASE test;
DROP USER 'test';
FLUSH PRIVILEGES;
exit;

Postgres

Shell

psql -U postgres

Podman

podman exec -it postgres psql -U postgres

Drop test database and test user

DROP DATABASE test;
DROP USER test;
exit;

Oracle

Shell

sqlplus sys/LetsTest1@localhost/XE as sysdba

Podman

sudo podman exec -ti oracle-xe su oracle -c "sqlplus sys/LetsTest1@localhost/XE as sysdba"

Drop test database including test user

ALTER PLUGGABLE DATABASE test CLOSE;
DROP PLUGGABLE DATABASE test INCLUDING DATAFILES;
exit;

SQL Server

Shell

sqlcmd -S localhost -U sa -P LetsTest1

Podman

podman exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P LetsTest1

Drop test database including test user

DROP DATABASE test;
go
exit

Db2

Shell

db2

Docker

docker exec -ti db2 su - db2inst1 -c db2

Drop test database

CONNECT TO TEST
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
UNQUIESCE DATABASE
CONNECT RESET
DEACTIVATE DATABASE test
DROP DATABASE test
quit