Configuration: malloy-config.json

VSCode

Both the VS Code Extension and the Malloy CLI use a malloy-config.json file to configure database connections. This page documents the shared file format, connection types, and environment variable options.

Config File Format

The file contains a connections object where each key is a connection name and each value specifies the connection type and its parameters. The first connection listed is the default.

{
  "connections": {
    "my_duckdb": {
      "is": "duckdb",
      "databasePath": "./data.db"
    },
    "my_bq": {
      "is": "bigquery",
      "projectId": "my-project"
    }
  }
}

The is field identifies the connection type. All other fields are type-specific parameters documented below.

Default connections

Setting includeDefaultConnections to true automatically creates a connection for every registered database type that you haven't already listed. Each gets the type's name as its connection name and picks up any registered property defaults:

{
  "includeDefaultConnections": true,
  "connections": {
    "warehouse": {
      "is": "duckdb",
      "databasePath": "./data.db"
    }
  }
}

In this example, you get your explicit warehouse connection plus auto-generated connections for bigquery, postgres, snowflake, etc. — each with default settings. A generated connection is skipped if its type or name collides with one you already listed.

Config file discovery

Both VS Code and the CLI discover config files automatically. Starting from the Malloy file you're working with, Malloy walks up through parent directories until it reaches the project root, looking for malloy-config.json at each level. The first one found is used.

You can also place a malloy-config-local.json alongside malloy-config.json for developer-specific overrides (credentials, local paths) that shouldn't be checked into git. When both files exist at the same directory level, they are merged: connections entries are deep-merged by name (local wins on conflicts), while all other sections (manifestPath, includeDefaultConnections, etc.) are replaced wholesale by the local file's value.

Add malloy-config-local.json to your .gitignore:

malloy-config-local.json

Connection Types

duckdb — DuckDB / MotherDuck

Restricted execution

securityPolicy has three levels:

• "none" — default. Ordinary DuckDB behavior.

• "local" — disables network access.

• "sandboxed" — "local" plus a DuckDB directory allowlist (allowedDirectories, defaulting to workingDirectory). POSIX only.

Both "local" and "sandboxed" force enableExternalAccess=false, block httpfs and INSTALL, reject remote databasePath and motherDuckToken, lock configuration, and encrypt temp files. "sandboxed" additionally enforces directory containment and derives a safe tempDirectory inside the sandbox.

Under "sandboxed", DuckDB resolves relative file paths against the host process working directory (getcwd()), not against Malloy's workingDirectory. Relative-path reads only succeed when the process CWD is inside an allowed directory.

{
  "connections": {
    "duckdb": {
      "is": "duckdb",
      "databasePath": "data/app.duckdb",
      "workingDirectory": {"config": "rootDirectory"},
      "securityPolicy": "sandboxed"
    }
  }
}

Policies set a floor, not a ceiling. allowedDirectories and tempDirectory can be set explicitly. Other policy-controlled settings accept matching values but reject weaker ones — connection creation fails closed. setupSQL, additionalExtensions, motherDuckToken, and remote databasePath are incompatible with any restricted policy. Policies do not set resource limits — configure threads, memoryLimit, and timeouts separately.

Concurrency

DuckDB's native file format has limited concurrency support, and the limits are enforced by an OS-level file lock that DuckDB takes when it opens the database. Once VS Code (or any other Malloy host) opens a .duckdb file in the default mode, the lock is held for as long as that connection is alive — and a second tool, like malloy-cli running in your terminal, will fail to open the same file with IO Error: Could not set lock on file.

The DuckDB project documents this directly: writing to a DuckDB file from multiple processes is not supported, and only one writer at a time is allowed. Multiple processes can read the same file concurrently, but only when every connection is opened read-only. There is no built-in read-write coordination across processes.

Malloy exposes two parameters that let you choose how a connection participates in this single-writer model.

readOnly: true opens the file with DuckDB's access_mode = 'READ_ONLY'. Multiple processes can simultaneously open the same file in read-only mode, and they can coexist with any number of other read-only readers — but the file cannot be written to from any process while a read-only connection is open. Use this when the database is a published artifact (a built warehouse, a snapshot file) that the connection only needs to read.

shareable: true is for the case where the file does need to be written to — by malloy-cli build, by a notebook running queries, by the DuckDB CLI, or by another Malloy host — but only one writer at a time. With shareable: true, the connection holds the file open only for the duration of each operation. Between operations the file is released, so another tool can take its turn. Internally, the connection's primary database is bound to :memory: and the real file is bracketed with ATTACH / DETACH around each operation; you write SQL exactly as if the file were the primary database. This adds a small per-operation overhead (a few milliseconds for the ATTACH) but lets you have VS Code open on a project while you run malloy-cli build in a terminal against the same .duckdb file.

{
  "connections": {
    "warehouse": {
      "is": "duckdb",
      "databasePath": "data/warehouse.duckdb",
      "shareable": true
    }
  }
}

readOnly and shareable compose: shareable: true plus readOnly: true means the file is attached read-only and released between operations, so other read-only readers and intermittent writers in other processes can all coexist. Neither flag does anything for :memory: databases (no file lock to release) or for MotherDuck / remote URLs (no local file at all).

shareable and setupSQL interaction. Shareable mode reserves the alias malloy_db and runs ATTACH '<databasePath>' AS malloy_db; USE malloy_db.main internally. Don't use ATTACH against databasePath or the malloy_db alias from setupSQL — those collide. Other ATTACH statements in setupSQL (different file, different alias) are fine, but they run once at connection setup and are not released on idle, so any file you attach that way keeps its lock for the lifetime of the connection. If you need a secondary file to be release-on-idle too, give it its own malloy connection with shareable: true instead of attaching it via setupSQL.

bigquery — Google BigQuery

databricks — Databricks

Authentication: provide either token or the oauthClientId + oauthClientSecret pair.

postgres — PostgreSQL

mysql — MySQL

snowflake — Snowflake

Snowflake also supports TOML configuration at ~/.snowflake/connections.toml. See Snowflake connection configuration for details.

trino — Trino

The ssl, session, extraCredential, and extraHeaders properties accept JSON objects that are passed directly to the underlying trino-client library. For example, to connect to a Trino server with a self-signed certificate:

{
  "connections": {
    "my_trino": {
      "is": "trino",
      "server": "https://trino.example.com",
      "catalog": "hive",
      "user": "analyst",
      "password": {"env": "TRINO_PASSWORD"},
      "ssl": {"rejectUnauthorized": false}
    }
  }
}

Because these properties use the json type, {"env": "..."} inside them is treated as literal data, not as an environment variable reference.

presto — Presto

Setup SQL

All connection types support a setupSQL parameter. This is a multi-line text field containing SQL statements to execute each time the connection is established.

Each statement must end with ; at the end of a line. Statements can span multiple lines. Only one statement-ending ; is allowed per line.

Correct — each statement ends with ; on its own line:

SET search_path TO analytics;
CREATE TEMP TABLE foo
  AS SELECT 1;

Incorrect — two statements on the same line:

SET search_path TO analytics; CREATE TEMP TABLE foo AS SELECT 1;

Manifest Path

The config file can specify where persistence manifests are stored:

{
  "connections": { ... },
  "manifestPath": "MANIFESTS"
}

The manifestPath property is optional and defaults to "MANIFESTS". The manifest file is <manifestPath>/malloy-manifest.json, relative to the config file's location. Both the builder (malloy-cli build) and the VS Code extension read the manifest from this path.

Environment Variables

Any property value can be replaced with an environment variable reference. This is especially useful for sensitive values like passwords and API tokens, so they don't need to appear directly in your config file:

{
  "connections": {
    "my_motherduck": {
      "is": "duckdb",
      "databasePath": "md:my_database",
      "motherDuckToken": {"env": "MOTHERDUCK_TOKEN"}
    },
    "my_postgres": {
      "is": "postgres",
      "host": "db.example.com",
      "password": {"env": "PG_PASSWORD"}
    }
  }
}

The {"env": "VAR_NAME"} syntax looks up the value from the named environment variable at connection time. If the variable is not set, the field is omitted and the connection proceeds without it.

You can also provide a plain string value directly — this is useful for testing but not recommended for shared or committed config files.