Malloy Documentation
search
previousPublish Your Models
REST APInext

Publisher Connections

VSCode document

Configure database connections for Malloy Publisher deployments. For an overview of supported databases, see Database Support.

Configuration File

Publisher uses publisher.config.json for database connections. By default it is read from your server root, and --config <path> points at one anywhere else. Package locations inside it resolve against the directory holding the config, so a config kept next to its packages travels with them.

Keeping credentials out of the file: any ${VAR} inside a string value is replaced with that environment variable's value when the config loads, so passwords and keys can stay out of the file and out of version control:

"postgresConnection": {
  "host": "${PG_HOST}",
  "password": "${PG_PASSWORD}"
}

Substitution applies to every string in the config, including package location values. Only uppercase, braced names are matched: $PG_PASSWORD (no braces) and ${lowercase} are left as literal text.

This keeps credentials out of the file, not out of the server. Publisher resolves ${VAR} at load time and then serves the resolved connection configuration, password included, from its API: /api/v0/environments/{environment}/connections returns it, and so do /api/v0/environments and /api/v0/status, the endpoint most people wire into a health check.

Publisher ships with no authentication and binds all interfaces by default, so anyone who can reach the port can read those credentials, run SQL through any connection you have configured, and (unless you set frozenConfig: true) add connections of their own. This is not only a concern for servers that hold credentials: the DuckDB sandbox every package gets automatically will also read files off the server's filesystem, so even a Publisher with no connections configured at all exposes its host. Keep Publisher on a trusted network or behind your own authentication.

Set every variable your config references before starting. If one is unset, the whole config fails to load and Publisher comes up serving nothing, which is easy to miss because /api/v0/status still reports "operationalState": "serving". Check /api/v0/environments to confirm your environments actually loaded. A variable that is set but empty is not an error: it substitutes an empty string, so a blank ${PG_PASSWORD} becomes a blank password rather than a failure.

Basic Structure 

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "connection_name",
          "type": "backend_type",
          "<type>Connection": {
            ...connection options...
          }
        }
      ],
      "packages": [
        {
          "name": "my-package",
          "location": "./my-package"
        }
      ]
    }
  ]
}

Each connection object requires:

  • name: The connection name used in your Malloy code

  • type: The backend type (duckdb, postgres, bigquery, etc.)

  • Type-specific options nested under <type>Connection (e.g., postgresConnection, bigqueryConnection)

Note: For packages with local parquet/CSV files, you don't need a connections config at all — Publisher automatically uses DuckDB.

DuckDB (Local Parquet Files)

For packages with embedded parquet/CSV files, no connection configuration is needed. Publisher automatically uses DuckDB for local data files:

{
  "environments": [
    {
      "name": "default",
      "packages": [
        {
          "name": "my-analytics",
          "location": "./my-analytics"
        }
      ]
    }
  ]
}

In your Malloy model, reference files relative to the package root:

source: flights is duckdb.table('data/flights.parquet')

Folder structure:

my-analytics/
├── publisher.json
├── model.malloy
└── data/
    └── flights.parquet

DuckDB with Attached Databases

DuckDB can federate queries to external databases (BigQuery, Snowflake, PostgreSQL) using attached databases. This lets you query cloud data warehouses through DuckDB.

Note: the connection name duckdb is reserved for the per-package sandbox described above, so an environment-level DuckDB connection needs a different name. Using duckdb here does not stop the server: it logs the error, skips that whole environment, and carries on reporting "operationalState": "serving" with nothing loaded. These examples use shared_duckdb; reference that same name in your Malloy code.

Attach BigQuery:

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "shared_duckdb",
          "type": "duckdb",
          "duckdbConnection": {
            "attachedDatabases": [
              {
                "name": "my_bq",
                "type": "bigquery",
                "bigqueryConnection": {
                  "defaultProjectId": "my-gcp-project",
                  "serviceAccountKeyJson": "{ \"type\": \"service_account\", ... }"
                }
              }
            ]
          }
        }
      ],
      "packages": [...]
    }
  ]
}

In your Malloy model:

source: events is shared_duckdb.table('my_bq.my_dataset.events')

Attach Snowflake:

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "shared_duckdb",
          "type": "duckdb",
          "duckdbConnection": {
            "attachedDatabases": [
              {
                "name": "my_sf",
                "type": "snowflake",
                "snowflakeConnection": {
                  "account": "myorg-myaccount",
                  "username": "my_user",
                  "password": "my_password",
                  "database": "analytics",
                  "warehouse": "compute_wh"
                }
              }
            ]
          }
        }
      ],
      "packages": [...]
    }
  ]
}

Attach PostgreSQL:

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "shared_duckdb",
          "type": "duckdb",
          "duckdbConnection": {
            "attachedDatabases": [
              {
                "name": "my_pg",
                "type": "postgres",
                "postgresConnection": {
                  "host": "db.example.com",
                  "port": 5432,
                  "databaseName": "analytics",
                  "userName": "readonly_user",
                  "password": "my_password"
                }
              }
            ]
          }
        }
      ],
      "packages": [...]
    }
  ]
}

Multiple attached databases:

{
  "duckdbConnection": {
    "attachedDatabases": [
      {
        "name": "warehouse",
        "type": "bigquery",
        "bigqueryConnection": { ... }
      },
      {
        "name": "app_db",
        "type": "postgres",
        "postgresConnection": { ... }
      }
    ]
  }
}

MotherDuck 

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "md",
          "type": "motherduck",
          "motherduckConnection": {
            "accessToken": "your_motherduck_token_here",
            "database": "my_database"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

BigQuery

With service account (recommended for production):

The service account JSON goes in serviceAccountKeyJson as a string. To keep the key out of the file, put it in an environment variable and reference it as ${BIGQUERY_SA_JSON}:

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "bigquery",
          "type": "bigquery",
          "bigqueryConnection": {
            "defaultProjectId": "my-gcp-project",
            "location": "US",
            "serviceAccountKeyJson": "${BIGQUERY_SA_JSON}"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

Then start Publisher with the key in the environment:

export BIGQUERY_SA_JSON="$(cat service-account.json)"
npx @malloy-publisher/server --server_root .

The whole key can also be pasted inline as a JSON string ("{\n \"type\": \"service_account\", ...}") if you would rather not use an environment variable.

With gcloud auth (development only):

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "bigquery",
          "type": "bigquery",
          "bigqueryConnection": {
            "defaultProjectId": "my-gcp-project",
            "location": "US"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

Snowflake

Password authentication:

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "snowflake",
          "type": "snowflake",
          "snowflakeConnection": {
            "account": "myorg-myaccount",
            "username": "publisher_user",
            "password": "your_password_here",
            "warehouse": "compute_wh",
            "database": "analytics",
            "schema": "public"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

RSA private key authentication:

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "snowflake",
          "type": "snowflake",
          "snowflakeConnection": {
            "account": "myorg-myaccount",
            "username": "publisher_user",
            "privateKey": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBAD...your-key-here...\n-----END PRIVATE KEY-----",
            "privateKeyPass": "your_passphrase_if_encrypted",
            "warehouse": "compute_wh",
            "database": "analytics"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

PostgreSQL 

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "postgres",
          "type": "postgres",
          "postgresConnection": {
            "host": "db.example.com",
            "port": 5432,
            "databaseName": "analytics",
            "userName": "publisher_readonly",
            "password": "your_password_here"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

MySQL 

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "mysql",
          "type": "mysql",
          "mysqlConnection": {
            "host": "db.example.com",
            "port": 3306,
            "database": "analytics",
            "user": "publisher_readonly",
            "password": "your_password_here"
          }
        }
      ],
      "packages": [...]
    }
  ]
}

Trino 

{
  "environments": [
    {
      "name": "default",
      "connections": [
        {
          "name": "trino",
          "type": "trino",
          "trinoConnection": {
            "server": "https://trino.example.com",
            "port": 8443
          }
        }
      ],
      "packages": [...]
    }
  ]
}

Multi-Environment Configuration

You can configure multiple environments, for example one per deployment stage:

{
  "environments": [
    {
      "name": "staging",
      "connections": [
        {
          "name": "postgres",
          "type": "postgres",
          "postgresConnection": {
            "host": "staging-db.example.com",
            "port": 5432,
            "databaseName": "analytics",
            "userName": "staging_user",
            "password": "staging_password"
          }
        }
      ],
      "packages": [
        {
          "name": "analytics",
          "location": "./packages/analytics"
        }
      ]
    },
    {
      "name": "production",
      "connections": [
        {
          "name": "postgres",
          "type": "postgres",
          "postgresConnection": {
            "host": "prod-db.example.com",
            "port": 5432,
            "databaseName": "analytics",
            "userName": "prod_readonly",
            "password": "prod_password"
          }
        }
      ],
      "packages": [
        {
          "name": "analytics",
          "location": "./packages/analytics"
        }
      ]
    }
  ]
}

Next Steps