Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Setup scripts for test databases for Oracle, MySQL, Postgres, SQL Server, and Db2

Test Database Setup

Docker run

Test database and user creation

Connect as test user to test database

Deletion

Docker run

docker volume create mysql
docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=LetsDocker1 -p 3306:3306 -v mysql:/var/lib/mysql mysql

docker volume create postgres
docker run -d --name postgres -e POSTGRES_PASSWORD=LetsDocker1 -p 5432:5432 -v postgres:/var/lib/postgresql/data postgres

docker volume create oracle-xe
docker run -d --name oracle-xe -e ORACLE_PWD=LetsDocker1 -p 1521:1521 -v oracle-xe:/opt/oracle/oradata oracle/database:18.4.0-xe

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

docker volume create db2
docker run -d --name db2 -e LICENSE=accept --privileged=true -e DB2INST1_PASSWORD=LetsDocker1 -p 50000:50000 -v db2:/database ibmcom/db2

Test database and user creation

MySQL

Shell

mysql -u root -pLetsDocker1

Docker

docker exec -ti mysql mysql -u root -pLetsDocker1

Create test database and test user

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

Postgres

Shell

psql -U postgres

Docker

docker exec -it postgres psql -U postgres

Create test database and test user

CREATE DATABASE test;
CREATE USER test WITH ENCRYPTED PASSWORD 'LetsDocker1';
GRANT ALL PRIVILEGES ON DATABASE test TO test;
exit;

Oracle

Shell

sqlplus sys/LetsDocker1@localhost/XE as sysdba

Docker

docker exec -ti oracle-xe su oracle -c "sqlplus sys/LetsDocker1@localhost/XE as sysdba"

Create test database and test user

CREATE PLUGGABLE DATABASE test ADMIN USER test IDENTIFIED BY LetsDocker1 FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed','/opt/oracle/oradata/XE/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 -S localhost -U sa -P LetsDocker1

Docker

docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P LetsDocker1

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 = 'LetsDocker1';
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 -pLetsDocker1 -D test

Docker

docker exec -ti mysql mysql -u test -pLetsDocker1 -D test

Postgres

Shell

psql -U test -d test

Docker

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

Oracle

Shell

sqlplus test/LetsDocker1@localhost/XE

Docker

docker exec -ti oracle-xe su oracle -c "sqlplus test/LetsDocker1@localhost/XE"

SQL Server

Shell

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

Docker

docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U test -P LetsDocker1 -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 -pLetsDocker1

Docker

docker exec -ti mysql mysql -u root -pLetsDocker1

Drop test database and test user

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

Postgres

Shell

psql -U postgres

Docker

docker exec -it postgres psql -U postgres

Drop test database and test user

DROP DATABASE test;
DROP USER test;
exit;

Oracle

Shell

sqlplus sys/LetsDocker1@localhost/XE as sysdba

Docker

docker exec -ti oracle-xe su oracle -c "sqlplus sys/LetsDocker1@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 LetsDocker1

Docker

docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P LetsDocker1

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.