Skip to content

Instantly share code, notes, and snippets.

@julianjupiter
Forked from gvenzl/TestDatabaseSetup.md
Created March 2, 2020 08:48
Show Gist options
  • Save julianjupiter/d2a88ab626dd6a9f0f9af8aca8099ced to your computer and use it in GitHub Desktop.
Save julianjupiter/d2a88ab626dd6a9f0f9af8aca8099ced to your computer and use it in GitHub Desktop.
A script that sets up test databases for Oracle, MySQL, Postgres, SQL Server, and Db2

Test Database Setup scripts

MySQL
Postgres
Oracle
SQL Server
Db2

Docker run scripts

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

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

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

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

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

Test 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;

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;

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, UNLIMITED TABLESPACE TO test;

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

Db2

Shell

db2

Docker

docker exec -ti db2 su - db2inst1 -c db2

Create test database

CREATE DATABASE test
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment