From our Discord channel, seemed like something worth noting here in the forum:
RustyAutopsyYesterday at 11:18 AM
OK, cool. I was wondering if anyone has advice on how to create a MSSQL database via Liquibase? One our dev’s has indicated they are having issues doing so and to work around this they have created a bunch of PowerShell to handle the initial DB creation.
[ 11:19 AM ]
jdbc:mysql://localhost:3306/database_name?createDatabaseIfNotExist=true <-- would that work with MS SQL?(edited)
@RustyAutopsy An alternative path that would keep the experience in Liquibase would be to have a change set that executes plain old sql that creates the database. Downside of this is that the default database on your connection would be a different database than the one you’re trying to target so your change sets would have to specifically target the new database instead of inferring which database to target from the connection. I did the following: 1. Created a SQL File to create a db named TESTTEST if it doesn’t already exist. I set the changeset that uses this script to runAlways=true so it would run with every update 2. Set my liquibase jdbc URL to a different database: url=jdbc:sqlserver://localhost:1433/database=master 3. Ran update Again, it worked but there are trade offs. Here’s my sql:
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE name = 'TESTTEST'))
CREATE DATABASE [TESTTEST];
@Matias added some color to why the createDatabaseIfNotExist may not work on all db platform implementation of JDBC:
One issjö is the command structure. It is very different in different Databases, no ansi there… but you can have a dedicated script for it while having liquibase log in to a management DB. The next reason is for how liquibase works, it livs on an verifiera which scriptas has not been run and records running them. It also locks execution so no two users runs the same scripts at the same time against the same target.
For dev, why not the same model? Just have a local DB serve as the management DB? H2?
A database in MySQL is not conceptually the same as a database in oracle/MS SQL. In Oracle we create lots of physical objects, control where it is stored, set up log shipping, archival and so on.
MySQL database is what others call schema. It is a one line command where your only options are collation, character sets and encryption. None of the options are mandatory, only the database name is.
For Oracle you create a server process that controls the database and the basics of how it works. Without even considering clustering there are lots of options. Here is a standard syntax for it.
CREATE DATABASE newcdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 (’/u01/logs/my/redo01a.log’,’/u02/logs/my/redo01b.log’)
SIZE 100M BLOCKSIZE 512,
GROUP 2 (’/u01/logs/my/redo02a.log’,’/u02/logs/my/redo02b.log’)
SIZE 100M BLOCKSIZE 512,
GROUP 3 (’/u01/logs/my/redo03a.log’,’/u02/logs/my/redo03b.log’)
SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE ‘/u01/app/oracle/oradata/newcdb/system01.dbf’
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/newcdb/sysaux01.dbf’
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
DATAFILE ‘/u01/app/oracle/oradata/newcdb/deftbs01.dbf’
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/u01/app/oracle/oradata/newcdb/temp01.dbf’
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE ‘/u01/app/oracle/oradata/newcdb/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = (’/u01/app/oracle/oradata/newcdb/’,
‘/u01/app/oracle/oradata/pdbseed/’)
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE ‘/u01/app/oracle/oradata/pdbseed/usertbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
LOCAL UNDO ON;
This is why it today is all but mandatory to use DBCA to configure and create a database. If it should be done by hand, one would probably want to run it in silent not with a parameter file.
So for Liquibase to bridge that gap from logging into an instance and just saying “I’d like a user (schema) with the name xxxxxxxxx, to a command structure creating a massive instance and database with all kinds of critical options set it would make it be something it was never meant to be.
Sorry for the long thread. I figured it required a bit of text to explain/show why create database is not even close to being comparable between databases. The thing that different databases has common with it is that the name as far as I know always start with “create database”, what follows is completely different.
