Database Support

VSCode

Malloy connects to a variety of databases. This page provides an overview of supported databases and links to platform-specific setup guides.

Supported Databases

Database	Backend Type	Notes
DuckDB	`duckdb`	Built-in, no setup required. Reads Parquet, CSV, JSON files
MotherDuck	`duckdb`	Cloud-hosted DuckDB
BigQuery	`bigquery`	OAuth or service account authentication
Snowflake	`snowflake`	Password or RSA key authentication
PostgreSQL	`postgres`	Standard credentials
MySQL	`mysql`	Environment variables only (VS Code)
Trino / Presto	`trino` / `presto`	Server URL + optional auth

Platform-Specific Setup

Configure database connections based on how you're using Malloy:

Platform	Guide	Notes
VS Code Extension	VS Code Extension	Interactive development with UI configuration and environment variables
Malloy CLI	Malloy CLI	Command-line interface with config file
Publisher	Publisher Connections	Deployment with `publisher.config.json`

Connection Basics

In Malloy, you reference data using connection_name.table('path'):

source: flights is duckdb.table('data/flights.parquet')
source: orders is bigquery.table('my-project.analytics.orders')
source: users is postgres.table('public.users')

The connection name (e.g., duckdb, bigquery, postgres) maps to a configured connection. You can name connections whatever you want—they don't need to match the database type:

// Using a custom connection name
source: orders is my_warehouse.table('analytics.orders')

Connection Methods

.table() - Reference a table or view:

source: flights is duckdb.table('flights.parquet')
source: users is bigquery.table('my-project.analytics.users')

.sql() - Define a source from a SQL query:

source: recent_orders is postgres.sql("""
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
""")

See SQL Sources for more details.

Database Functions Reference

Each database supports Malloy Standard Functions plus database-specific functions. Consult the database documentation for available functions:

Database	Function Reference
DuckDB	DuckDB Functions
BigQuery	BigQuery Functions
Snowflake	Snowflake Functions
PostgreSQL	PostgreSQL Functions
MySQL	MySQL Functions
Trino	Trino Functions
Presto	Presto Functions

HyperLogLog Support: BigQuery, Snowflake, and Trino/Presto support HyperLogLog for efficient cardinality estimation.

Dialect-Specific Features

Each database has unique capabilities and limitations. See the dialect documentation for details:

DuckDB - File-based queries, approximate counts
BigQuery - HyperLogLog, approximate counts
Snowflake - HyperLogLog, TOML credential file support
PostgreSQL - String aggregation extensions
MySQL - Boolean type workarounds
Trino / Presto - HyperLogLog, array operations