Let me show you how you can combine PostgreSQL, Docker and DbUp to create pleasant and quick database development environment without installing anything (besides docker of course).
I won't introduce you to docker or Postgres - you should have some basic knowledge in this area although I will follow you with every step. When it comes to DbUp it is a .NET Library that helps you deploy changes to your SQL database. It supports:
The assumption around this library is straightforward. It creates a version table to keep track which scripts were executed and applies new ones incrementally. It embraces transitions instead of "state". Thanks to this approach you can upgrade your databases without db downtime. Check the docs to find out more.
Ready? So let's Go!
Let's start with simple docker-compose.yml:
version: '3.7'
services:
db:
image: postgres
restart: always
environment:
POSTGRES_PASSWORD: Secret!Passw0rd
POSTGRES_USER: postgres
ports:
- 5432:5432
Having that file let's just run command docker-compose up -d
. Now we can verify if docker container with postgres is running on the default port with docker ps
. You should see this:
λ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a7446521a3dg postgres "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 0.0.0.0:5432->5432/tcp postgres_db_1
So once we have postgres up and running we can play a little bit with it by connecting to the container using docker exec -it a74 bash
(make sure to enter yours container id). After we enter interactie mode let's run psql -U postgres
. We can list databases using \l
command or use sql to do whatever we wnat. As an example I will create random database.
postgres=# CREATE DATABASE mytestdb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
mytestdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \q
root@57368050ac99:/# exit
Use \q
command to exit psql and of course ctr+d combination to leave container in detached mode.
Let's create new netcore console app. You can use your IDE but I will stick with CLI for now. First navigate to the directory with your docker-compose.yml and run dotnet new console -o DbMigrator
or any other name. Navigate to new project fodler and add two packages
dotnet add package dbup-core
dotnet add package dbup-postgresql
then modify Program.cs:
using System;
using System.Linq;
using System.Reflection;
using DbUp;
namespace DbMigrator
{
class Program
{
static int Main(string[] args)
{
var connectionString =
args.FirstOrDefault()
?? "Host=localhost;User Id=postgres;Password=Secret!Passw0rd;Database=crazy_database;Port=5432";
EnsureDatabase.For.PostgresqlDatabase(connectionString);
var upgrader = DeployChanges.To
.PostgresqlDatabase(connectionString)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();
if (!result.Successful)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(result.Error);
Console.ResetColor();
return -1;
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(value: "Success!");
Console.ResetColor();
return 0;
}
}
}
or Program.fs if you prefer F#, I do :)
open System
open System.Reflection
open DbUp
let logAndParseEngineResult (result: Engine.DatabaseUpgradeResult) =
match result.Successful with
| true ->
Console.ForegroundColor <- ConsoleColor.Green
Console.WriteLine("Success")
Console.ResetColor()
0
| false ->
Console.ForegroundColor <- ConsoleColor.Red
Console.WriteLine result.Error
Console.ResetColor()
-1
[<EntryPoint>]
let main argv =
let connectionString =
match argv |> Array.tryHead with
| Some connectionString -> connectionString
| None ->
"Server=localhost,1433;Initial Catalog=TravelServicesConsumer;User ID=sa;Password=Strong!Passw0rd;MultipleActiveResultSets=True;Connection Timeout=30;"
EnsureDatabase.For.SqlDatabase(connectionString)
DeployChanges.To.SqlDatabase(connectionString)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole().Build().PerformUpgrade()
|> logAndParseEngineResult
No magic here. Everything is according to dbup documentation. I made some minor changes as the main example on the docs is for sqlserver. I have also used EnsureDatabase...
what is fine for local development but remember that for other environments you should create the database before with all necessary settings (collation, security, connlimit etc...).
Time to run the app! Let dbup create the crazy_database for us. Run dotnet run
we should see this:
λ dotnet run
Master ConnectionString => Host=localhost;Username=postgres;Password=***************;Database=postgres;Port=5432
Beginning database upgrade
Checking whether journal table exists..
Journal table does not exist
No new scripts need to be executed - completing.
Success!
We can connect to docker container again and list databases as described in previous point. Use \c dbname
to connect to specific database. Works for me:
postgres=# \c crazy_database
You are now connected to database "crazy_database" as user "postgres".
Let's add 2 basic sql scripts to create simple tables:
09032020AddTableCustomer.sql
CREATE TABLE Customers (
Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255)
);
002_FillSampleData.sql
INSERT INTO Customers VALUES
(
1,
'Gerard',
'Thomas',
'Nowhere 22/11'
),
(
2,
'Newman',
'Oldman',
'Somwhere 2/12'
)
Set the script files as embedded ressources. You can do it in your IDE or in csproj. Example scripts looks like this:
Having those things let's run the migrator again.
λ dotnet run
Master ConnectionString => Host=localhost;Username=postgres;Password=***************;Database=postgres;Port=5432
Beginning database upgrade
Checking whether journal table exists..
Journal table does not exist
Executing Database Server script 'DbMigrator.SqlScripts.001_AddTable_Customer.sql'
Checking whether journal table exists..
Creating the "schemaversions" table
The "schemaversions" table has been created
Executing Database Server script 'DbMigrator.SqlScripts.002_FillSampleData.sql'
Upgrade successful
Success!
Perfect! So we already have a postgres instance running in docker container and we are able to incrementally apply migrations using DbUp. Let's see what is in the database;
λ docker exec -it 43c bash
root@43c7615a4146:/# psql -U postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.
postgres=# \c crazy_database
You are now connected to database "crazy_database" as user "postgres".
crazy_database=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | customers | table | postgres
public | schemaversions | table | postgres
(2 rows)
crazy_database=# SELECT * FROM schemaversions;
schemaversionsid | scriptname | applied
------------------+-------------------------------------------------+----------------------------
1 | DbMigrator.SqlScripts.001_AddTable_Customer.sql | 2020-03-03 22:39:44.720556
2 | DbMigrator.SqlScripts.002_FillSampleData.sql | 2020-03-03 22:39:44.760178
(2 rows)
crazy_database=#
One may ask why? We already can run DbUp using dotnet CLI against dockerized postgres. This is a good start but when your app is growing and contains different services with different databases this really simplifies your life. Everything you have to do is docker-compose up
and you are ready to go with all your databases + schema up to date! This is a game changer when you want to run integration-tests. It is still possible without DbUp being dockerized but the CI scripts are growing with more and more commands. With good docker-compose you simply don't have to worry about that. Also when I write integration tests I tend to play with database causing some rubbish data. It is really easy to docker-compose down
and docker-compose up
and have everyting fresh. Multiply this act by few times per day and you can save some time for coffee!
We have two options here:
I tend to use the second approach. Docker society (and the docker team) advises to not create monolithic Dockerfiles (so containing multiple tech things). Think for a while... this should make sense! You should be able to use postgres in your docker-compose by other services without waiting to some migrations to apply (for example to run migrations for other database. Or to spin up some 3rd party service which don't need your migrations but needs postgres). Let's get our hands dirty again.
Create Dockerfile in the DbMigrator project like the following:
FROM mcr.microsoft.com/dotnet/core/sdk:3.1
WORKDIR /build
COPY DbMigrator.csproj ./
RUN dotnet restore
COPY . .
RUN dotnet publish -o /publish
WORKDIR /publish
CMD ["sh", "-c", "dotnet DbMigrator.dll \"${DB_CONNECTION}\""]
It's easy one. If your are not sure what is going on you should definetely check the docker docs. In short (number contains script line numbers):
Line 3-4 embrace docker layers caching so we don't need to restore the packages each time we edit a DbMigrator source file.
Now it is time to update our compose-file.
version: '3.6'
services:
db:
image: postgres
restart: always
environment:
POSTGRES_PASSWORD: Secret!Passw0rd
POSTGRES_USER: postgres
POSTGRES_DB: crazy_database
ports:
- 5432:5432
db-migrations:
build:
context: DbMigrator/
dockerfile: ./Dockerfile
depends_on:
- db
environment:
DB_CONNECTION: "Host=db;User Id=postgres;Password=Secret!Passw0rd;Database=crazy_database;Port=5432"
The depends_on
tells docker that even if we decide to run this command:
docker-compose up -d db-migrations
then it should run db container first as this is its upstream dependency.
DONE!
Let's check if this works. Just run docker-compose up -d
. I have this output:
λ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d4546121a9ec postgres "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 0.0.0.0:5432->5432/tcp postgres_db_1
Where is our migrator? Nowhere. There is no long-running process that docker can attach to (some kind of web listener on specific port). The Migrator was deployed and exited so the container lifecycle ended. Let's check the compose logs using docker-compose logs
.
db-migrations_1 | Master ConnectionString => Host=db;Username=postgres;Password=***************;Database=postgres;Port=5432
db-migrations_1 | Unhandled exception. System.Net.Sockets.SocketException (111): Connection refused
db-migrations_1 | at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
db-migrations_1 | at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
db-migrations_1 | at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
db-migrations_1 | at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
db-migrations_1 | at Npgsql.NpgsqlConnection.Open(Boolean async, CancellationToken cancellationToken)
db-migrations_1 | at Npgsql.NpgsqlConnection.Open()
db-migrations_1 | at PostgresqlExtensions.PostgresqlDatabase(SupportedDatabasesForEnsureDatabase supported, String connectionString, IUpgradeLog logger)
db-migrations_1 | at PostgresqlExtensions.PostgresqlDatabase(SupportedDatabasesForEnsureDatabase supported, String connectionString)
db-migrations_1 | at DbMigrator.Program.Main(String[] args) in /build/Program.cs:line 15
db_1 | performing post-bootstrap initialization ... ok
That's bad. What's going on? Postgres didn't make it to be up sooner than migrator so the connection was refused. Let's add restart on-failure policy to compose yml file:
services:
db:
image: postgres
restart: always
environment:
POSTGRES_PASSWORD: Secret!Passw0rd
POSTGRES_USER: postgres
ports:
- 5432:5432
db-migrations:
build:
context: DbMigrator/
dockerfile: ./Dockerfile
depends_on:
- db
environment:
DB_CONNECTION: "Host=db;User Id=postgres;Password=Secret!Passw0rd;Database=crazy_database;Port=5432"
restart: on-failure
Let's run everything again. First let's check for running containers:
λ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
Empty. Let's compose up again:
λ docker-compose up -d --build
Starting postgres_db_1 ... done
Starting postgres_db-migrations_1 ... done
Let's enter containers bash and check the db schema:
λ docker exec -it 30d bash
root@30db9b19add6:/# psql -U postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+----------+----------+------------+------------+-----------------------
crazy_database | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=# \c crazy_database
You are now connected to database "crazy_database" as user "postgres".
crazy_database=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | customers | table | postgres
public | schemaversions | table | postgres
(2 rows)
crazy_database=#
Done!
One thing you may run into when playing with docker:
Traceback (most recent call last):
File "site-packages\docker\utils\build.py", line 96, in create_archive
PermissionError: [Errno 13] Permission denied: '\\\\?\\C:\\postgres\\DbMigrator\\.vs\\DbMigrator\\v16\\Server\\sqlite3\\db.lock'
Just add .dockerignore
file with this content:
.vs
And you are good to go (we've just ignored some visual studio internal things from docker commands ie copy).
Is here: https://github.com/marcingolenia/postgres-dbup
I hope you will enjoy this way of database development. You should be able to use SqlServer in very similar way - just change the db service in compose file, Program.cs of DbMigrator to work with SqlServer and of course connection string. Check my blog again for next post about some tips for working with migrations!