Source adapter differences

Why this page exists

Looky supports three source adapters: BigQuery, Postgres, and MySQL. Most of the platform behaves identically across all three. A handful of areas behave differently — those are the ones model and dashboard authors trip on. This page lists each one in observable terms (what you see, not how it is implemented).

Source declaration

BigQuery

Required: type: bigquery, project_id (the GCP project paying for queries), credentials_file (plain filename of a service-account JSON inside secrets/ — no path).
Optional: location for multi-region datasets, datasets (required at the time of schema introspection).

Postgres

Required: type: postgres, dsn (libpq URI without user:password@; carries host, port, database, and any libpq query-string flag like sslmode=require, application_name, connect_timeout, …), credentials_file (filename of a JSON inside secrets/ declaring {"user", "password"}).
Optional: name (cosmetic label), schemas (limits introspection scope; default is all non-system schemas).

MySQL

Required: type: mysql, dsn (mysql://host:port/database URI without user:password@ and without any ?query parameters — the port defaults to 3306), credentials_file (filename of a JSON inside secrets/ declaring {"user", "password"}).
Optional: name (cosmetic label), schemas (databases to introspect; default is the database named in the dsn).
Note: MySQL connections are not encrypted — there is no TLS option yet, so use MySQL over a trusted network.

See Sources for working examples per adapter.

Schema introspection

BigQuery — requires an explicit dataset list. Without it, introspection cannot find tables.
Postgres — discovers tables across the schemas in your schemas list (or the smart default of every non-system schema). No table-list declaration is needed.
MySQL — discovers tables in the database named in the dsn (or the databases in your schemas list). System databases (mysql, performance_schema, sys, information_schema) are always excluded. No table-list declaration is needed.

The error messages on introspection failures look completely different per adapter — when in doubt, run a small test query first to confirm Looky can reach the source at all.

Parameter binding

String, numeric, and array parameters

Behave identically on all three adapters. No special handling needed.

Date and timestamp parameters

BigQuery — bind natively, no extra work.
Postgres and MySQL — native binding fails with an explicit error in some query shapes. Add an @param placeholder in the underlying SQL of that source so Looky substitutes the value into the SQL string. See Malloy support for a worked example.

Filter types that produce date / timestamp parameters: cutoff_date, date_range, date_range_preset. Plan for the @param pattern when those filters drive a Postgres- or MySQL-backed model.

NULL parameter values

Each adapter writes a typed null differently: BigQuery and MySQL require an explicit cast, Postgres uses a bare typed null. Looky handles this for you — there is nothing to configure — but it is the reason a malformed nullable parameter can fail on one adapter and silently work on another.

Boolean values (MySQL only)

MySQL has no real boolean type — boolean-looking columns come back as numbers (0/1). To filter on a true/false condition, add an explicit cast(… as boolean) in the model. BigQuery and Postgres have native booleans and need no such cast.

Query execution

BigQuery — exposes a free pre-flight cost estimate (in scanned bytes) before the actual run. Useful for planning around scan-heavy queries.
Postgres and MySQL — pre-flight validates the SQL plan (via EXPLAIN) but does not estimate cost.

Filters and cross-filtering

The filter resolver and the cross-filter pill mechanism are adapter-agnostic. Differences only show up at the parameter-binding step described above.

A select filter behaves identically on all three.
The date filters land on the temporal-parameter caveat for Postgres and MySQL — fix on the model side with @param.
Pill values from Cross-filtering are typically strings or short identifiers, so they are unaffected.

Pagination (grid & report_matrix)

Server-side pagination uses the same protocol on all three adapters. Cost characteristics differ:

BigQuery — every uncached page is a billed scan. Larger page size + caching is cheaper overall. Avoid unbounded grids on un-cached queries.
Postgres and MySQL — connection roundtrip latency dominates; cost depends mostly on indexed-scan performance of the underlying tables. Make sure the columns referenced in order by have appropriate indexes.

Cache TTL recommendations

Cache logic itself is the same on all three adapters. The TTL choice is editorial:

BigQuery — favor longer TTLs for queries that scan large tables. A 30-minute TTL on a daily-batch dataset is overkill; a 24-hour TTL is usually fine.
Postgres and MySQL — TTL is mostly about freshness rather than cost. Caching helps less if the underlying table is small and well-indexed; consider whether the cost saved outweighs the staleness introduced.

Aggregations and dialect features

Looky does not branch on adapter for aggregate or windowing functions. Anything dialect-specific (BigQuery-only ARRAY functions, Postgres-only date_trunc on certain types, MySQL-only date functions, etc.) surfaces as a Malloy compilation error. The fix is on the Malloy side — adjust the model to use a feature every target adapter supports, or guard the model against the wrong adapter.

Worked migration patterns

A date filter that works on BigQuery but breaks on Postgres or MySQL

The native-binding model:

# works on BigQuery, fragile on Postgres / MySQL in some shapes
##! experimental.parameters

source: orders(
  p_date_from::date is @2024-01-01,
  p_date_to::date   is @2024-12-31
) is bigquery.table('...') extend {

  view: revenue is {
    where:
      created_at::date >= p_date_from
      and created_at::date <= p_date_to
    aggregate:
      revenue is sum(sale_price)
  }
}

Switch the source to sql() with placeholders for Postgres / MySQL compatibility (this example uses Postgres syntax; on MySQL use the equivalent mysql.sql("""…""") with MySQL-dialect casts):

# works on Postgres (and BigQuery)
##! experimental.parameters

source: orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is postgres.sql("""
  select *
  from orders
  where (@date_from::date is null or created_at::date >= @date_from::date)
    and (@date_to::date   is null or created_at::date <= @date_to::date)
""") extend {
  view: revenue is {
    aggregate:
      revenue is sum(sale_price)
  }
}

Each @param placeholder must have a matching declaration on the source signature; Looky substitutes the value (or typed NULL when unset) before compile.

A grid that paginates well on Postgres / MySQL but blows BigQuery scan budget

Increase pagination.page_size, add a cache sidecar with a long TTL, and pre-aggregate when possible. On BigQuery, paging through millions of rows uncached is expensive — pre-summarise.

Quick reference

Source auth — BigQuery: service-account file. Postgres / MySQL: connection string + a {"user","password"} secret.
Transport — BigQuery / Postgres: TLS available. MySQL: not encrypted yet.
Introspection — BigQuery: explicit datasets required. Postgres: smart-default schemas. MySQL: the DSN's database (or a schemas list).
Numeric / string parameters — identical.
Date / timestamp parameters — BigQuery: native. Postgres / MySQL: need @param placeholder in SQL.
Booleans — native on BigQuery / Postgres. MySQL: numeric — cast explicitly.
NULL parameters — handled automatically; no configuration.
Pre-flight — BigQuery: free cost estimate. Postgres / MySQL: SQL plan check.
Filters & cross-filter routing — identical.
Pagination protocol — identical; cost characteristics differ.
Aggregations / dialect features — delegated entirely to Malloy; no Looky-side branches.