cli.malloynb

>>>markdown
# Installing the CLI

The [Malloy CLI](https://github.com/malloydata/malloy-cli) is a command-line interface for running `.malloysql` and `.malloy` files. Use it to automate transformations, build pipelines, or compile Malloy queries to SQL.

---

## Installation Options

### Option 1: NPX (No Installation)

Run directly without installing:

```bash
npx malloy-cli --help
```

### Option 2: Global Install via npm

```bash
npm install -g malloy-cli
malloy-cli --help
```

---

## Configure Connections

The CLI stores its own configuration separately from VS Code in `~/.config/malloy/config.json`.

**Note:** The CLI does not support environment variables for connection configuration (except for MotherDuck, which can use `MOTHERDUCK_TOKEN`). Use the CLI commands below to manage connections.

### Connection Commands

```bash
# List all connections
malloy connections list

# Create a new connection
malloy connections create-<type> <name>

# Test a connection
malloy connections test <name>

# Show connection details
malloy connections show <name>

# Delete a connection
malloy connections delete <name>
```

### Create a Connection

```bash
# For BigQuery
malloy-cli connections create-bigquery <connection-name>

# For Postgres
malloy-cli connections create-postgres <connection-name>

# For DuckDB (or MotherDuck)
malloy-cli connections create-duckdb <connection-name>
```

View options for each database type:

```bash
malloy-cli connections create-bigquery --help
```

**BigQuery options:**

```
-p, --project <id>                    GCP project ID
-l, --location <region>               Query location (default: "US")
-k, --service-account-key-path <path> Path to service account JSON key
-t, --timeout <milliseconds>          Query timeout
-m, --maximum-bytes-billed <bytes>    Limit bytes scanned
```

**DuckDB options:**

```
    --database-path <database>        Path to DuckDB database file or MotherDuck database (e.g., "md:my_database")
    --mother-duck-token <token>       MotherDuck API token (can also use MOTHERDUCK_TOKEN env var)
```

### Default Connections

Two connections are created automatically:

- **`bigquery`**: Uses existing gCloud authentication
- **`duckdb`**: Uses a built-in DuckDB instance

If your Malloy files reference these connection names, they work without explicit setup.

### BigQuery with gCloud

If you already use gCloud:

```bash
gcloud auth login --update-adc
malloy-cli connections create-bigquery my-bq
```

### MotherDuck

MotherDuck connections use the `create-duckdb` command with special options. To connect to MotherDuck, you'll need an access token:

1. Log in to MotherDuck and go to **Settings**
2. Create and copy your access token
3. Create the connection using `create-duckdb` with `--database-path` set to `md:` followed by your database name:

**Using environment variable (recommended):**

```bash
export MOTHERDUCK_TOKEN="your_token_here"
malloy-cli connections create-duckdb my-md --database-path "md:my_database"
```

**Using command-line flag:**

```bash
malloy-cli connections create-duckdb my-md \
  --database-path "md:my_database" \
  --mother-duck-token "your_token_here"
```

If you don't specify a database name, use `md:`:

```bash
malloy-cli connections create-duckdb my-md \
  --database-path "md:" \
  --mother-duck-token "your_token_here"
```

---

## Basic Commands

### Run Queries

Execute a query and return results:

```bash
# Run a named query from a Malloy file
malloy-cli run path/to/model.malloy --query query_name

# Run a MalloySQL file
malloy-cli run transforms.malloysql
```

### Compile to SQL

Get the generated SQL without executing:

```bash
malloy-cli compile path/to/model.malloy --query query_name
```

Useful for:
- Debugging query logic
- Copying SQL to other tools
- Understanding what Malloy generates

### Get Help

```bash
malloy-cli --help
malloy-cli run --help
malloy-cli compile --help
```

---

## Next Steps

- **[Database Support](database_support.malloynb)** — Overview of all supported databases
- **[Transform & Materialize](../user_guides/transform.malloynb)** — Use the CLI with MalloySQL for data transformations
- [malloy-cli on GitHub](https://github.com/malloydata/malloy-cli)