Database Connections
A database run never carries a password. It carries the name of a connection, and Avatar stores the rest. This page explains what you can connect to, who can see a connection, and how to add, test, rename and delete one.
You can manage connections in the web client, in Account > Database connections, or from the Python client. They are the same connections.
Supported Engines
| Engine | Kind | Status |
|---|---|---|
| PostgreSQL | Tables (SQL) | supported |
| MySQL | Tables (SQL) | supported |
| MariaDB | Tables (SQL) | supported |
| Oracle | Tables (SQL) | supported |
| SQL Server | Tables (SQL) | supported |
| ClickHouse | Tables (SQL) | supported |
| MongoDB | Documents (NoSQL) | supported |
| Snowflake | Tables (SQL) | preview |
| Google BigQuery | Tables (SQL) | preview, source only |
| IBM Db2 | Tables (SQL) | preview, only on some installations |
| SQLite | Tables (SQL) | demo databases only |
Your installation can offer fewer engines. The web client shows only the engines that your installation can reach. Preview means the engine is offered but not yet proven against a real server: try it first on a database where a mistake has no consequence.
Every engine, the demo databases included, is opened by the database connector. Without it, an installation reaches no database, PostgreSQL included.
In MongoDB, each collection is read as a table. Its variables are the top-level fields found in a
sample of 500 documents, _id is the key, and nested values arrive as JSON text. The Avatar
method cannot model a JSON value: drop the nested fields that the users of the copy do not need. A
collection has no foreign keys, so each collection is anonymized on its own. To give one individual the same
identity in several collections, declare a
shared entity.
Each engine has its own form. You never write a connection string yourself.
| Engine | Fields | Behind More options |
|---|---|---|
| PostgreSQL | host, port, database, user, password | SSL mode: prefer (default), disable, require, verify-ca, verify-full |
| ClickHouse | host, port, database, user, password | TLS (off by default) |
| MongoDB | host, port, database, user, password | auth source, replica set, TLS (on by default) |
| Snowflake | account, user, password, database, schema (optional), warehouse | role |
| Google BigQuery | project, dataset, service-account key |
The other engines ask for a host, a port, a database, a user and a password. A port is a number between 1 and 65535. Test before saving stays disabled until every required field is valid.
Connection Scopes
A connection has a scope, which says who can see it:
| Scope | Who can use it | Who can change it |
|---|---|---|
| Just me | you only. Administrators do not see it. | you |
| Whole organization | every member of your organization | the administrators of the organization |
| Deployment | every user of the installation | your operator, in the configuration of the installation. An administrator can take it out of use for the organization. |
In the web client, a new connection is Just me by default. Only an administrator can create a
Whole organization connection. The Python client and the API default to the organization
scope: a user who is not an administrator must pass scope="user", or the connection is refused.
A deployment connection has no Edit button. Only an administrator sees Delete next to it, and deleting it only takes it out of use for the organization.
Write-Only Credentials
The password and the other credentials of a connection are sent once, stored encrypted, and never returned: not by the web client, not by the API, and not in a job. A listing shows the host and the database name, enough to tell two connections apart, and nothing you can connect with.
When a job runs, its worker does not get the password. It gets a short-lived token for one connection. A separate service, the database connector, presents the token, receives the connection string from the API, and opens the database. The rows go from your database into the storage of the job.
Add a Connection
- Go to Account > Database connections and click New connection. You can also click New connection in the source and target pickers when you create a database run.
- Choose the Type of database and fill in its form.
- Choose a Name, for example
crm_prod. The web client suggests one from the database field. A name has at most 63 characters: letters, digits, underscores, hyphens and dots, and it starts with a letter or a digit. Jobs and saved configurations refer to a connection by its name. - Choose a Display name (optional), for example CRM (production). It is only what users see in a picker.
- Choose Who can use this connection: Just me or Whole organization.
- Click Test before saving, then Save.
Test a Connection
Click Test next to a connection. Avatar opens it from the server and reports three facts:
- Is the database reachable?
- Does it have a schema (tables)?
- Is it empty?
These are the three facts that a database run checks on a target, so test a target before you start a run. When the test fails, the message says which kind of failure it is: the database is unreachable, the credentials are wrong, the database does not exist, or a privilege is missing. The message never repeats the connection string.
Rename, Relabel, Replace or Delete a Connection
Click Edit next to a connection.
- Relabel: change the display name at any time. Nothing else changes.
- Rename: jobs already submitted are not affected. A saved configuration that uses the old name will not find the connection, and neither will a re-run of an earlier run (Re-run this group, Re-run every group, Adjust and rerun): jobs carry the name of a connection, not the connection itself.
- Replace: to change the host or the password, click Replace details… and fill in every field again. The name stays. Avatar never sends the stored credentials back, so there is nothing to edit in place.
- Delete: Avatar forgets the connection and its credentials. Jobs already submitted keep working, but a saved configuration or a re-run that names the connection will no longer find it. You can delete your own connections and, as an administrator, the connections of your organization. For a deployment connection, an administrator can only take it out of use for the organization.
Demo Databases
To try a database run before you connect your own databases, click Add demo connections in Account > Database connections. The wizard also offers it in the Read from picker, as long as you have no connection yet. The button only appears when your installation offers the demo databases. They are added as Just me connections. Two demo databases are available, each with a source and an empty target:
| Database | Content |
|---|---|
| Northwind | a trading company: 13 tables, 600,000 order lines |
| Chinook | a music store: 11 tables |
The demo sources are read only and shared by every user. Each run gets its own empty copy of the demo target, so two users can run at the same time without interfering. A demo target is deleted 24 hours after its last write, and when the database connector restarts. A demo target already has the schema of its demo source, so it only accepts a copy of that source.
Good Practice
- Give the source connection a database role that can only read.
- Give the target connection a role that can write and delete rows. A run does not create tables, so the target role does not need to.
- Never point a target at a production database. A run refuses a target that holds rows unless you ask for it to be emptied first. It refuses a target that is the same connection as the source, but it cannot tell that two different connections point at the same database: check that yourself.
For Administrators
Database connections need the database connector. On a self-hosted installation, your operator enables it: see Database connector. On the Octopize SaaS, the connector can only reach databases on a public address.