Support separate configuration of user, password and host name

[stefaneg]

In certain setups, like when using temporary generated database credentials to run migrations, it is a bit of a hassle to put together a connection string for dbmate. It would make dbmate much more convenient in this scenario if it supported reading the password from environment or .env file and insert into the connection string. To illustrate the idea:

DATABASE_USER=dbuser
DATABASE_PASSWORD=asadfqwerty
DATABASE_URL=db://{{DATABASE_USER}}:{{DATABASE_PASSWORD}}@{{DATABASE_HOSTNAME}}

[dossy]

@stefaneg dbmate supports reading .env files using the Go gotdotenv package, and you can do exactly what you describe:

# cat <<-'EOT' >.env
DATABASE_USER=root
DATABASE_PASSWORD=root
DATABASE_HOST=mysql
DATABASE_NAME=dbmate_test
DATABASE_URL=mysql://${DATABASE_USER}:${DATABASE_PASSWORD}@${DATABASE_HOST}/${DATABASE_NAME}
EOT

# cat .env
DATABASE_USER=root
DATABASE_PASSWORD=root
DATABASE_HOST=mysql
DATABASE_NAME=dbmate_test
DATABASE_URL=mysql://${DATABASE_USER}:${DATABASE_PASSWORD}@${DATABASE_HOST}/${DATABASE_NAME}

# dist/dbmate -d testdata/db/migrations up  
Creating: dbmate_test
Applying: 20151129054053_test_migration.sql
Applied: 20151129054053_test_migration.sql in 19.427218ms
Applying: 20200227231541_test_posts.sql
Applied: 20200227231541_test_posts.sql in 19.542ms
Writing: ./db/schema.sql

dbmate will load the file .env by default. If you want to load a different file, use the --env-file command line argument and specify the path to the file you want loaded.

[stefaneg]

Excellent.

I did look through the documentation - and now twice - was not able to spot this support, the only mention around .env file is support for DATABASE_URL. Did I miss something ?

[stefaneg]

Unfortunately this method does not work with passwords requiring URL encoding, which is the main hassle around this, as it requires adding a url encoding tool to the dbmate container.

[dossy]

I did look through the documentation - and now twice - was not able to spot this support, the only mention around .env file is support for DATABASE_URL. Did I miss something ?

In the Command Line Options section of the README, there's this:

--env-file ".env" - specify an alternate environment variables file(s) to load.

And, in the Alternatives section of the README, it specifically lists "Automatically load .env file" as a feature.

And, if you search the README for the string ".env" it appears 9 times.

Unfortunately this method does not work with passwords requiring URL encoding, which is the main hassle around this, as it requires adding a url encoding tool to the dbmate container.

If you don't want to add a dedicated URL encoding tool to your dbmate container, if you at least have a reasonably standard awk available, you can use that:

$ docker run --rm -it debian:latest /bin/bash
root@9bc901abbdfd:/# PS1='\n\$ '

# grep PRETTY_NAME /etc/os-release
PRETTY_NAME="Debian GNU/Linux 13 (trixie)"

# awk --version
mawk 1.3.4 20250131
Copyright 2008-2024,2025, Thomas E. Dickey
Copyright 1991-1996,2014, Michael D. Brennan

random-funcs:       srandom/random
regex-funcs:        internal

compiled limits:
sprintf buffer      8192
maximum-integer     9223372036854775808

## Let's generate ourselves 20 random bytes.

# SOME_RANDOM_BYTES="$(dd if=/dev/urandom bs=20 count=1 2>/dev/null)"

## Inspect the bytes, see that they span the range of 0x00 to 0xFF.

# echo -n $SOME_RANDOM_BYTES | od -tx1c
0000000  7a  96  cd  bf  5f  a0  5f  82  8a  19  16  d5  4e  67  d2  38
          z 226 315 277   _ 240   _ 202 212 031 026 325   N   g 322   8
0000020  ac  31  1c  bb
        254   1 034 273
0000024

## Use awk to percent-encode the bytes, effectively URL-encoding them.
## Even printable characters can, technically, be percent-encoded.

# echo -n $SOME_RANDOM_BYTES | LC_ALL=C awk 'BEGIN { FS=""; for (i = 0; i < 256; i++) { ord[sprintf("%c", i)] = i } } { for (i = 1; i <= NF; i++) { printf("%%%x", ord[$i]); } } END { print "" }'
%7a%96%cd%bf%5f%a0%5f%82%8a%19%16%d5%4e%67%d2%38%ac%31%1c%bb

## We're going to install Node.js to double-check our work above.

# (apt-get update && apt-get install -y nodejs) > /dev/null 2>&1

## Let's encode those bytes using Node.js's encodeURIComponent().

# echo -n $SOME_RANDOM_BYTES | node -e 'buf = require("fs").readFileSync("/dev/stdin"); console.log(buf); console.log(encodeURIComponent(buf.toString("binary")));'
<Buffer 7a 96 cd bf 5f a0 5f 82 8a 19 16 d5 4e 67 d2 38 ac 31 1c bb>
z%C2%96%C3%8D%C2%BF_%C2%A0_%C2%82%C2%8A%19%16%C3%95Ng%C3%928%C2%AC1%1C%C2%BB

## First, let's verify that we can decode Node.js's version back to their original bytes.

# echo -n "z%C2%96%C3%8D%C2%BF_%C2%A0_%C2%82%C2%8A%19%16%C3%95Ng%C3%928%C2%AC1%1C%C2%BB" | node -e 'buf = require("fs").readFileSync("/dev/stdin"); console.log(buf.toString()); console.log(Buffer.from(decodeURIComponent(buf.toString()), "binary"));'
z%C2%96%C3%8D%C2%BF_%C2%A0_%C2%82%C2%8A%19%16%C3%95Ng%C3%928%C2%AC1%1C%C2%BB
<Buffer 7a 96 cd bf 5f a0 5f 82 8a 19 16 d5 4e 67 d2 38 ac 31 1c bb>

## Okay, we get the same Buffer contents back.  Now, let's decode the awk output from before.

# echo -n "%7a%c2%96%c3%8d%c2%bf%5f%c2%a0%5f%c2%82%c2%8a%19%16%c3%95%4e%67%c3%92%38%c2%ac%31%1c%c2%bb" | node -e 'buf = require("fs").readFileSync("/dev/stdin"); console.log(buf.toString()); console.log(Buffer.from(decodeURIComponent(buf.toString()), "binary"));'
%7a%c2%96%c3%8d%c2%bf%5f%c2%a0%5f%c2%82%c2%8a%19%16%c3%95%4e%67%c3%92%38%c2%ac%31%1c%c2%bb
<Buffer 7a 96 cd bf 5f a0 5f 82 8a 19 16 d5 4e 67 d2 38 ac 31 1c bb>

## Exacty same Buffer, so both encoded strings decode back to the same bytes, confirming that the awk output is correct.

Hope this helps!

[stefaneg]

Good to know that you can do this with awk in the image. I had already put together an image using jq do to password encoding.

Regarding the docs, assuming that readers know godotenv capabilities is bit of a stretch IMHO. I would think its worth a mention that you can compose environment variables like this with it.

[dossy]

Regarding the docs, assuming that readers know godotenv capabilities is bit of a stretch IMHO. I would think its worth a mention that you can compose environment variables like this with it.

Mmm, yeah. It's unfortunate that the godotenv package's documentation is really lacking, so we can't just link off to it and hope users understand all that they can do in .env files with dbmate. And, I think trying to document all the capabilities of the current version of godotenv ourselves in dbmate's documentation and trying to keep that up-to-date isn't something we should really be doing, either.

No easy solution here, for sure.