Creating a database automatically upon startup

[LuisBosquez]

A couple of comments (4) in the DockerHub page suggest adding parameters to create a database with username and password automatically.

[rburgst]

+1

[rburgst]

Is there a timeline for this? Also the missing tools in this container/image (see #8 ) make this hard to achieve for me on my own.

[potzkovge]

+1

[twright-msft]

FYI - We released CTP 1.4 today. This release of the mssql-server-linux image now includes the mssql-tools package (sqlcmd and bcp) in it.

Executing sqlcmd as part of the entrypoint.sh script can be used for this kind of scenario for now. Since this is such a commonplace requirement we want to make it easier in the future, but sqlcmd will provide a reasonable option until then.

[kuncevic]

Was wondering how would you create default db and user just by using docker-compose.yaml file That is what i've got now:

  sqlserver:
    container_name: 'sqlserver'
    image: microsoft/mssql-server-linux:2017-CU1
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
      - SA_PASSWORD=<pwd>
      - MSSQL_DB=mydb (was expected db to be created)
      - MSSQL_USER=me
      - MSSQL_PASSWORD=<pwd>
    ports:
      - "1433:1433"
    networks:
      - app-network

Using this image for now https://github.com/mcmoe/mssqldocker to create the container and default db

[ghost]

hi @kuncevic did this docker compose work for you? did you get a db and user on the container?

[twright-msft]

Creating databases and users at container run time is not implemented yet.

[kuncevic]

@pjpradeepjha using this image https://github.com/mcmoe/mssqldocker - yes But the mssql docker image is so huge in size also it needs at least 3.5 gb of ram to run (2gb with CU2). So I ended up using PostgresSQL alpine image which is like 14mb size. I wish mssql has a small alpine image just to handle nothing else but basic select, update and delete

[ghost]

@kuncevic @twright-msft thanks for the comments. appreciate the help. :) I was constantly trying to create user and db from docker compose on the mssql docker image to no effect.

[twright-msft]

@kuncevic - We will continue to make the SQL image container smaller. Just takes some time to minify something like SQL Server as you can imagine. :)

[brunooliveiramac]

:) Thanks a lot @kuncevic

[felixhayashi]

Bump. Both postgres and mysql images already support using environment variable to create an initial database when the image is run and the container is created (POSTGRES_DB: "mydb" or MYSQL_DATABASE: "mydb"). Would be great if this were also supported in the official mssql image, otherwise we need to rely on executing sqlcmd to create the db on container startup.

[celamb4]

Any updates? Its been in the backlog for over 2 years. Doesnt seem like that big of a request. If we cant create one based on ENV is there a default DB created when the container boots up?

[0x-2a]

The naming convention in these env vars is inconsistent with industry standard database connectors.

MYSQL_DATABASE here actually refers to the database Instance in connector drivers, not the Database.

For example, MYSQL_DATABASE treated as an Instance variable would have users connect with the following (which is currently what it does): sqlserver://localhost\Foo:1433

Where as MYSQL_DATABASE as a Database variable would have users connect with: sqlserver://localhost:1433;database=Foo, which is currently does not.

I think this is why some folks have connection issues and others don't. It would probably be best to support both.

For others wondering, it looks like you can do this after your docker starts up:

docker exec -i YOUR_MSSQL_DOCKER_CONTAINER /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'YOUR_MSSQL_PASS_HERE' -Q 'CREATE DATABASE YOUR_DATABASE_NAME'

[lkurzyniec]

It is possible, here you are examples: https://github.com/microsoft/sql-server-samples/tree/master/samples/containers

And also my example, IMHO much more straight forward: https://github.com/lkurzyniec/netcore-boilerplate

docker-compose

version: "3.6"

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: mssql
    command: /bin/bash ./entrypoint.sh
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
      - SA_PASSWORD=SomeStrongPwd123
    volumes:
      - dbdata:/var/opt/mssql/data
      - ./db/mssql/docker-entrypoint.sh:/entrypoint.sh
      - ./db/mssql/docker-db-init.sh:/db-init.sh
      - ./db/mssql/mssql-cars.sql:/db-init.sql

  netcore-boilerplate:
    image: netcore-boilerplate:local
    container_name: netcore-boilerplate
    build:
      context: .
    ports:
      - 5000:80
    depends_on:
      - mssql

volumes:
  dbdata:

docker-entrypoint.sh

#start SQL Server, start the script to create/setup the DB
 /db-init.sh & /opt/mssql/bin/sqlservr

!!! There is a space in front of.

db-init.sh

#wait for the SQL Server to come up
sleep 30s

echo "running set up script"
#run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P SomeStrongPwd123 -d master -i db-init.sql

db-init.sql

USE [master]
GO

IF DB_ID('cars') IS NOT NULL
  set noexec on               -- prevent creation when already exists

/****** Object:  Database [cars]    Script Date: 18.10.2019 18:33:09 ******/
CREATE DATABASE [cars];
GO

USE [cars]
GO

/****** Object:  Table [dbo].[Cars]    Script Date: 18.10.2019 18:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cars](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Plate] [varchar](50) NOT NULL,
    [Model] [varchar](50) NULL,
    [OwnerId] [int] NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Owners]    Script Date: 18.10.2019 18:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Owners](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [FullName]  AS (([FirstName]+' ')+[LastName]),
 CONSTRAINT [PK_Owners] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Cars] ON
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (1, N'JHV 770', N'Mercedes-Benz GLE Coupe', 1)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (2, N'TAD-3173', N'Datsun GO+', 1)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (3, N'43-L348', N'Maruti Suzuki Swift', 2)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (4, N'XPB-2935', N'Land Rover Discovery Sport', 3)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (5, N'805-UXC', N'Nissan GT-R', NULL)
GO
SET IDENTITY_INSERT [dbo].[Cars] OFF
GO

SET IDENTITY_INSERT [dbo].[Owners] ON
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (1, N'Peter', N'Diaz')
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (2, N'Leon', N'Leonard')
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (3, N'Shirley', N'Baker')
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (4, N'Nancy', N'Davis')
GO
SET IDENTITY_INSERT [dbo].[Owners] OFF
GO

ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Owners] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[Owners] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_Owners]
GO

CREATE LOGIN [user]
    WITH PASSWORD = 'simplePWD123!';

CREATE USER [user] FOR LOGIN [user] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT ALL ON Cars TO [user];
GRANT ALL ON Owners TO [user];
GO

[damien-git]

How do we know sleep 30s will be the right delay ? I have been trying hacks like this, but nothing works... Support for database creation is still needed.

[vin-yu]

@damien-git check this sample out: https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-customize

this will ping the server, and if it is ready, it will run the startup script, setup.sql. This is where you want to put your create DB script

[filiphr]

Sorry @vin-yu, but suggesting to create our own Docker image just so we can create a DB upon startup is not satisfiabile solution.

For me the idea to create a DB upon startup is because I want to use your own Docker image in our CI. I don't want to build a new image everytime you release a new image.

Would really be interested to know what the problem is to add such support to the official docker image? Why not move that example here and parameterise it?

[rburgst]

I totally agree with @filiphr . Also the fact that basically EVERY other db container offers such a feature should be an indication that this is a VERY useful thing to have.

[damien-git]

@vin-yu : thanks for the example, but it does not work for me. In configure-db.sh, ERRCODE is 1 before the server is done starting up, so it fails unless I add a sleep statement before (which makes the whole code useless). Also, there are several syntax errors on line 19.

[kkanellis]

@damien-git I am aware that the previous "solution" is neither complete nor appropriate, but after tweaking the configure-db.sh, I could (partly) replicate the desired behavior:

#!/bin/bash

TIMEOUT=60
DBSTATUS=1
ERRCODE=1
i=0

while [[ $i -lt $TIMEOUT ]] ; do
    i=$i+1
    DBSTATUS=$(/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P $SA_PASSWORD -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases")
    ERRCODE=$?
    sleep 1

    if [[ $DBSTATUS -eq 0 ]] && [[ $ERRCODE -eq 0 ]]; then
        break
    fi
done

if [[ $DBSTATUS -ne 0 ]] || [[ $ERRCODE -ne 0 ]]; then
    echo "SETUP: SQL Server took more than $TIMEOUT seconds to start up or one or more databases are not in an ONLINE state"
    exit 1
fi

sleep 2

# Run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S db -U sa -P $SA_PASSWORD -i /usr/config/setup.sql

[damien-git]

I ended up making my own hack. It works in my case, for now, but that is still not satisfying...

#!/bin/bash
# (see https://github.com/microsoft/mssql-docker/issues/2 )
echo "Container initialization: waiting for the server to come up"
while [ ! -f /var/opt/mssql/log/errorlog ]
do
  sleep 1
done
FOUND=0
i=0
while [[ $FOUND -ne 1 ]] && [[ $i -lt 60 ]]; do
  i=$i+1
  FOUND=$(grep -cim1 "Service Broker manager has started" /var/opt/mssql/log/errorlog)
  if [[ $FOUND -ne 1 ]]; then
    sleep 1
  fi
done
if [[ $FOUND -ne 1 ]]; then
  echo "Container initialization: Error: waited for more than 60 seconds for the server to start. Trying to create the database now..."
fi

echo "Container initialization: creating the database if needed"
/opt/mssql-tools/bin/sqlcmd etc...
echo "Container initialization: done"

[agates4]

this shouldn't be a closed issue

are there no plans to implement this extremely useful feature?

if the benefits are not immediately apparent then I would be happy to help explain them

[agates4]

@twright-msft

[martimors]

I recently came into a situation where I had no choice but to use mssql server, and I am honestly completely baffled at how cumbersome it is to use this docker image. Must have feature imo!

[patricklucas]

I'm a bit baffled to come across this three-and-a-half-year-old closed issue for such an extremely obvious and expected feature that is supported by every other major RDBMS Docker image.

@vin-yu do you think you could explain again why you closed this issue? I realize it may have been accidental, since the "Comment" and "Close and comment" buttons are right next to each other.

I would join @agates4 in helping justify the feature, if it's still unclear.

[vin-yu]

@agates4 @patricklucas - Thank you for the feedback. We don't have plans to implement this in the near future, and might have closed this by accident. Reopening but please refer to this work around for now: https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-customize

@damien-git - I'll look into why this doesn't work but got it working before we posted this.

There are multiple workarounds to create a database or run custom .sql scripts post start-up so we are focused on other container improvements/products at the moment.

Thank you.

[patricklucas]

Thanks for the reply @vin-yu. I think the main concern was that the issue was closed without a clear resolution, so I appreciate your quick response and reopening it.

[NGPixel]

@vin-yu I find it extremely baffling that you don't consider this a priority and that you have no plan to implement it in the near future.

All other database vendors (MySQL, Postgres, MariaDB, etc.) offer this feature out-of-the-box. Why? Because it significantly reduce both the time it takes to use it and the learning curve. I shouldn't have to search on google for 30 minutes on how to automatically create a database.

Frankly, I doubt any other container improvements/products you could come up with would be as beneficial as this one, and by a very huge margin. The amount of upvotes on this ticket should be a clear sign.

The very first comment I get from other devs trying to use the mssql docker image the first time is always the same: Why can't I quickly create a database, like the other DB vendors? It doesn't make any sense to ask your users to go customize a startup script and build a new image.

Unless your goal is not to promote the usage of SQL Server, this very basic feature should be added. There's a reason SQL Server isn't the go to database for most users, adding unnecessary barriers to its use is probably the biggest reason.

[davidathompson]

If anybody else had problems with the approaches here, this worked for me:

echo "Starting Sql Server"

( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" \ && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -d master -i setup.sql \ && sleep infinity

[NO1225]

For those using entity framework with asp.net core I'm doing like this right now and I think this may help someone

Calling this before (EnsureCreatedAsync or MigrateAsync) will make sure that you have a database to work on that also accept your password

private static async Task InitDatabase(IConfiguration config)
        {
            string hostServer = config["HOST_SERVER"] ?? "(localdb)\\MSSQLLocalDB";
            string serverPort = config["HOST_PORT"] ?? "1433";
            string databaseName = config["DATABASE_NAME"] ?? "testdb";
            string userName = config["USERNAME"];
            string passward = config["SA_PASSWORD"];
            string connectionString;
            if (!string.IsNullOrEmpty(userName) && !string.IsNullOrEmpty(passward))
            {
                connectionString = $"Server={hostServer};User Id={userName};Password={passward};";
                var optionsBuilder = new DbContextOptionsBuilder<DataAccess>();
                optionsBuilder.UseSqlServer(connectionString);
                using (var dbContext = new DataAccess(optionsBuilder.Options))
                {
                    var query = @$"IF DB_ID('{databaseName}') IS NOT NULL
set noexec on
CREATE DATABASE [{databaseName}];";
                    await dbContext.Database.ExecuteSqlRawAsync(query);
                }
            }
        }

[MaxHorstmann]

Using docker-compose, this works for me:

version: "3"
services:
        sql:
                image: "mcr.microsoft.com/mssql/server"
                container_name: sql
                ports:
                        - "1433:1433"
                environment:
                        - ACCEPT_EULA=Y
                        - MSSQL_PID=Enterprise
                        - SA_PASSWORD=Password1
                command: /bin/sh -c "(/opt/mssql/bin/sqlservr &) && sleep 10s && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Password1 -d master -Q 'CREATE DATABASE [MyDatabaseName]' && sleep infinity"

[NickCraver]

Chiming in here from our conversation on Twitter. I think the main use case for Docker here, at least where this matters, is people want to spin this up as a service. For me and my teams, we are generally doing this in Actions or locally. For things like integration tests it's a primary use case - you're using Docker in the first place because you want to quickly spin it up and run. One of the critical components in doing this is to have a database.

We have developers, who may or may not have experience creating a database, but that shouldn't be a barrier to entry here. With an environmental-variable-based approach, we can lower that barrier. Please consider that even though this issue has workarounds with scripting to workaround this issue, some amount of users will give up, never having made it to this issue, or far enough through it for a working solution. Given the volume of input we have here, I'd suggest this be given some priority, since adoption is greatly hindered for any technology if users hit large barriers early.

If it helps, our main use cases are integration tests (e.g. for libraries like MiniProfiler, StackExchange.Exceptional, and Dapper), and also less public ones like internal software (also integration tests), or per-PR build environments we spin up services in for some time period.

Here's an example GitHub Actions config from a project spinning up services for some integration tests:

    services:
      mongo:
        image: mongo
        ports:
        - 27017/tcp
        env:
          MONGO_INITDB_DATABASE: test
      postgres:
        image: postgres
        ports:
        - 5432/tcp
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test
      sqlserver:
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
        - 1433/tcp
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: Password1!
      mysql:
        image: mysql
        ports:
        - 3306/tcp
        env:
          MYSQL_ROOT_PASSWORD: root
          MYSQL_DATABASE: test

Note that MySQL (and MariaDB) use MYSQL_DATABASE and PostgreSQL uses POSTGRES_DB. When we're consuming SQL Server, we're either scripting this out manually, or resorting to abusing tempdb overall.

[ah1508]

In addition to @NickCraver comment, postgresql also consider files present in /docker-entrypoint-initdb.d/ directory (in the container). It helps to create tables and fill them when container is created.

Exemple :

services:
  pg:
    image: postgres
    ports:
    - 5432/tcp
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: test
    volumes:
    - ./pg.sql:/docker-entrypoint-initdb.d/init.sql

init.sql is not a keyword, multiple files can exist in this directory (postgres execute them in alphabetical order). Without this feature, an external script would be required to create tables and insert data.

[coryechobind]

Any plans to change this? Auto DB creation on startup is the standard for every other database container.

[NGPixel]

Come on @vin-yu, @twright-msft , how much longer are you guys going to ignore this very important issue? It's literally the single most requested feature and should be very quick to implement on your side.

DB Engine	Has init DB variables?
DB2	:white_check_mark:
InfluxDB	:white_check_mark:
MariaDB	:white_check_mark:
MongoDB	:white_check_mark:
MS SQL Server	:x:
MySQL	:white_check_mark:
Neo4j	:white_check_mark:
PostgreSQL	:white_check_mark:

[mpern]

If this feature is ever tackled, please add support for init.d directory to the entrypoint script. i.e. run all shell / sql scripts in the init.d directory in order once the DB is up and ready to receive commands.

[amvin87-zz]

Thank you all for the suggestions and feedback. I am working on triaging this internally and will update this thread on the next action we take. Until then the options mentioned by @vin-yu is something that can be used as a workaround. While we work on this feedback internally.

[Schlurcher]

I'm chipping in with a slightly modified entrypoint.sh which plays nicely with wait-for-it, dockerize, etc. The idea is to only make the server available to other containers once the init/setup scripts ran.

#!/bin/bash

_setup() {
  for _ in {1..60}; do
    if /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P "$SA_PASSWORD" -d master -i setup.sql; then
      break
    fi
    echo "Server is not ready..."
    sleep 1
  done
}

echo "Starting SQL-Server on 127.0.0.1"
/opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1
/opt/mssql/bin/sqlservr &
_setup

echo "Setup finished. Stopping SQL-Server..."
pid=$(pgrep -o sqlservr)
kill "$pid"
tail --pid="$pid" -f /dev/null

echo "Starting SQL-Server on 0.0.0.0"
/opt/mssql/bin/mssql-conf set network.ipaddress 0.0.0.0
/opt/mssql/bin/sqlservr

Other containers then can simply dockerize -wait tcp://db:1433 ... for example and be sure that the database is only available once it's fully initialised.

[allanFunrock]

@amvin87 its been a couple more months now and still no update, what is the status? i'm likewise amazed that this is still an open issue after almost four years... what could possibly have a greater cost/benefit than this?

You can put "microsoft heart linux/containers/docker" on slides all day long but not supporting basic features like this what really tells the story :/ Dont ruin the good will dotnet is creating and make it that much harder for us to convince people Microsoft is not just full of hot air when it comes to this stuff

[crzysan]

@amvin87 I don't understand why you going silent. A small feedback will be appreciated and a basic feature implementation will be honoured. Thnx in advance!

[potatoqualitee]

Hello @amvin87, do you have any updates or a timeline that you can share?

Thank you 🙇🏼

[fardarter]

Honestly find it quite shocking that this isn't considered an obvious feature.

[NGPixel]

[bartelink]

I have only scanned the above for 5/10 mins and realize it duplicates things (as does most of what precedes this). Trust me I will edit and/or delete this post if a better story or workaround appears.

edited to add: I now use the hack from below: https://github.com/microsoft/mssql-docker/issues/2#issuecomment-1683956282

---

@twright-msft Is the TL;DR on this still that your images do not have a good story for spinning up a DB with docker compose without needing to dig into a pile of version-specific scripting? Literally every other DB has a story for this -- it's 2022 after all

I'm doing pretty unexotic testing; the typical prod database this runs against is an Azure SQL instance.

Can someone be assigned this please, or do you have a good one liner that explains why there's a need to differentiate here? Right now it seems like this is the moral equivalent of https://github.com/JamesNK/Newtonsoft.Json/issues/862, except that's an actual OSS project (and I see lots of annoying drive by commenters on that but feel their pain, as I'm sure some will when they see this (and sorry to those that don't get what drives a human to and-me-three post on a thread like this))

Or is this something the Azure SQL team should be tasked with?

It makes it very hard to include MSSQL in a test matrix alongside sensible children like PG and Mysql as-is, and has thoroughly wasted my Saturday afternoon updating a test rig for an OSS project (with a lot of curse words along the way)

---

Expected reasonable docker-compose fragment for a db called test with a well known SA password

    container_name: equinox-mssql
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_DB=test
      - SA_PASSWORD=mssql1Ipw

---

Ugly hack while the world waits

1. docker-compose.yml fragment:

   services:
   equinox-mssql:
   container_name: equinox-mssql
   image: mcr.microsoft.com/mssql/server:2019-latest
   ports:
     - 1433:1433
   volumes:
     - ~/apps/mssql/data:/var/lib/mssqlql/data
   environment:
     - ACCEPT_EULA=Y
     - SA_PASSWORD=mssql1Ipw

2. Scripted command to run after docker compose up

   docker exec -it equinox-mssql /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U sa -P mssql1Ipw \
   -Q "CREATE database test"

3. Connection string:

   Server=localhost,1433;User=sa;Password=mssql1Ipw;Database=test

[kasvith]

It's been 6 years and still not done? come on Microsoft

[kolendomichal]

Any sort of update on this? @amvin87

[potatoqualitee]

I am eager for an update as well 🙏🏼

[abdennour]

6 years and no fix ! wy wy wy

[tracker1]

Give this a look... just dumped it from another project, and minor tweaks... completely untested in current state.

https://github.com/tracker1/mssql-docker-enhanced