Connect to your database
In this section we perform read-only operations and make no changes to your database.
With the Atlas CLI installed and logged-in to your account, let's now make sure we can connect to your database with the CLI.
Step 1: Determine the database's URL
Atlas uses a standard URL format to connect to databases and load schemas and migrations from various sources. The format below covers the supported parts of a URL, with subsequent sections providing more detailed examples.
driver://[username[:password]@]address/[schema|database][?param1=value1&...¶mN=valueN]
Here are some popular examples you can use:
| Engine | |
|---|---|
| PostgreSQL | postgres://root:pass@localhost:5432/database?search_path=public |
| MySQL | mysql://root:pass@localhost:3306/app_schema |
| SQL Server | sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master&mode=schema |
| ClickHouse | clickhouse://user:pass@localhost:9000/schema?secure=true |
| More examples | In the URL Docs |
Step 2: Choose connection scope
Schema vs Database-scope
Most databases provide a way for users to namespace resources. This feature is commonly named schemas.
For instance, PostgreSQL and MySQL users may create additional schemas in a target database using a command such as:
CREATE SCHEMA auth;
Going forward, resources may be created under the auth schema, in a few ways:
- By qualifying the resource name with the schema name, so the database knows which specific resource we are talking about, for example:
create table `auth`.`session_log` (
-- ...
)
- By using the
USE <schema>statement before executing the relevant statement. For example:
USE `auth`;
create table `session_log` (
-- ...
)
Which kind of scope should you use?
Depending on your use-case, you may require a schema or database scope connection. The following table summarizes some of these cases.
| Use-case | Scope to use |
|---|---|
| All of my tables are in one schema | Schema-scope |
I want to use qualified identifiers (e.g auth.users) | Database-scope |
I want to use database EXTENSIONS (Postgres) Why? | Database-scope |
| I want to see the entire database | Database-scope |
Scoped connections
By default, Atlas will create a database-scoped connection. However, it is possible to limit the scope of the connection by providing it within the connection URL. For example:
| Engine | Schema-scope | Database-scope |
|---|---|---|
| PostgreSQL | postgres://localhost:5432/database?search_path=public | postgres://:5432/database |
| MySQL | mysql://localhost:3306/app_schema | mysql://localhost:3306/ |
| ClickHouse | clickhouse://localhost:9000/schema | clickhouse://localhost:9000 |
| SQL Server | sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master&mode=schema | sqlserver://sa:P@ssw0rd0995@localhost:1433?database=master&mode=database |
| More examples | In the URL Docs |
Test your connection
Once you have determined your full connection URL including its scope, you can test that Atlas is able to connect to the database using the following command:
atlas schema inspect --url "<url>" --format "OK"
Be sure to replace <url> with your database's URL.
If Atlas is able to connect to the given URL you should see the following output:
OK
Troubleshooting
My password contains non-alphanumeric characters
Atlas uses standard RFC 3986 URLs, which means that "userinfo" section (which contains user and password) needs to be "pct-encoded" - commonly referred to as URI encoding.
For example, Pa$$w@rd becomes Pa%24%24w%40rd and a%b%c becomes a%25b%25c.
In case your password contains non-alphanumeric characters (as is often required by system administrators who want to increase minimal password complexity), you must encode your password before using it in your Atlas URL.
Suppose your database password is BnB+PjA, attempting to use this password without encoding it first will result in
an error message similar to:
Error: mysql: query system variables: dial tcp: lookup root:BnB+PjA:3309: no such host
To solve this, first encode your password using URL encoding. For example:
import urllib.parse
pw = "BnB+PjA"
print(urllib.parse.quote(pw))
This program prints:
BnB%2BPjA
My connection times out
When you try to connect to your database using Atlas, you might encounter an error message similar to:
Error: postgres: scanning system variables: dial tcp 10.0.5.243:5432: connect: operation timed out
Or
Error: mysql: query system variables: dial tcp 10.0.5.243:5432: connect: operation timed out
These errors suggest that Atlas is attempting to connect to the database but is unable to establish a network connection to the remote database at all.
To resolve this error, ensure that you can establish a network connection to the database from your workstation. This might involve:
- Connecting to your company's VPN which has access to the database
- Connecting through a bastion server
- Changing firewall rules for the database to allow a connection from your machine.
If you encounter any issues during this step, don't hesitate to reach out to us.
Commercial PoC
Please reach out to us via our shared Slack Connect channel.
Independent PoC
Please use our Community Support channels to contact our team. If possible, please supply us with a minimal example schema to reproduce the issue.