How to: Connecting to the Database
Dbmate locates your database using the `DATABASE_URL` environment variable by default. If you are writing a [twelve-factor app](http://12factor.net/), you should be storing all connection strings in environment variables.

To make this easy in development, dbmate looks for a `.env` file in the current directory, and treats any variables listed there as if they were specified in the current environment (existing environment variables take preference, however).

If you do not already have a `.env` file, create one and add your database connection URL:

```sh
$ cat .env
DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_development?sslmode=disable"
```

`DATABASE_URL` should be specified in the following format:

```
protocol://username:password@host:port/database_name?options
```

- `protocol` must be one of `mysql`, `postgres`, `postgresql`, `sqlite`, `sqlite3`, `clickhouse`
- `username` and `password` must be URL encoded (you will get an error if you use special charactors)
- `host` can be either a hostname or IP address
- `options` are driver-specific (refer to the underlying Go SQL drivers if you wish to use these)

Dbmate can also load the connection URL from a different environment variable. For example, before running your test suite, you may wish to drop and recreate the test database. One easy way to do this is to store your test database connection URL in the `TEST_DATABASE_URL` environment variable:

```sh
$ cat .env
DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_dev?sslmode=disable"
TEST_DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable"
```

You can then specify this environment variable in your test script (Makefile or similar):

```sh
$ dbmate -e TEST_DATABASE_URL drop
Dropping: myapp_test
$ dbmate -e TEST_DATABASE_URL --no-dump-schema up
Creating: myapp_test
Applying: 20151127184807_create_users_table.sql
Applied: 20151127184807_create_users_table.sql in 123µs
```

Alternatively, you can specify the url directly on the command line:

```sh
$ dbmate -u "postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable" up
```

The only advantage of using `dbmate -e TEST_DATABASE_URL` over `dbmate -u $TEST_DATABASE_URL` is that the former takes advantage of dbmate's automatic `.env` file loading.

#### PostgreSQL

When connecting to Postgres, you may need to add the `sslmode=disable` option to your connection string, as dbmate by default requires a TLS connection (some other frameworks/languages allow unencrypted connections by default).

```sh
DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?sslmode=disable"
```

A `socket` or `host` parameter can be specified to connect through a unix socket (note: specify the directory only):

```sh
DATABASE_URL="postgres://username:password@/database_name?socket=/var/run/postgresql"
```

A `search_path` parameter can be used to specify the [current schema](https://www.postgresql.org/docs/13/ddl-schemas.html#DDL-SCHEMAS-PATH) while applying migrations, as well as for dbmate's `schema_migrations` table.
If the schema does not exist, it will be created automatically. If multiple comma-separated schemas are passed, the first will be used for the `schema_migrations` table.

```sh
DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema"
```

```sh
DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema,public"
```

#### MySQL

```sh
DATABASE_URL="mysql://username:password@127.0.0.1:3306/database_name"
```

A `socket` parameter can be specified to connect through a unix socket:

```sh
DATABASE_URL="mysql://username:password@/database_name?socket=/var/run/mysqld/mysqld.sock"
```

#### SQLite

SQLite databases are stored on the filesystem, so you do not need to specify a host. By default, files are relative to the current directory. For example, the following will create a database at `./db/database.sqlite3`:

```sh
DATABASE_URL="sqlite:db/database.sqlite3"
```

To specify an absolute path, add a forward slash to the path. The following will create a database at `/tmp/database.sqlite3`:

```sh
DATABASE_URL="sqlite:/tmp/database.sqlite3"
```

Note that for some common [settings](https://sqlite.org/pragma.html) like `journal_mode` to improve performance, transactions need to be disabled for that migration file, e.g.

```sql
-- migrate:up transaction:false
PRAGMA journal_mode = WAL;
```

Otherwise the migration will fail with "Error: cannot change into wal mode from within a transaction".

#### ClickHouse

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name"
```

To work with ClickHouse cluster, there are 4 connection query parameters that can be supplied:

- `on_cluster` - Indicataion to use cluster statements and replicated migration table. (default: `false`) If this parameter is not supplied, other cluster related query parameters are ignored.

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/d