EctoBigquery

Ecto adapter for Google BigQuery focused on schema management and data seeding: creating and evolving BigQuery datasets and tables through Ecto migrations, and loading initial data via the Seeds helper.

Why this library exists

I work as a data engineer and part of my day-to-day is maintaining BigQuery schemas across multiple environments. For a long time our team used Terraform for that, and while it works well for provisioning infrastructure, it falls short when it comes to schema evolution — ALTER TABLE operations like adding or dropping columns aren't natively supported, which forced us to write workarounds or apply changes manually.

Having spent years working with tools like Active Record and Ecto Migrations, I believe the migration pattern is simply the best way to manage a database schema: changes are versioned, ordered, reproducible, and reversible. That mental model transfers directly to BigQuery, so I built this library to bring it there.

Scope: This library is intentionally limited to DDL operations (mix ecto.create/drop/migrate/rollback) and seeding (INSERT/DELETE via EctoBigquery.Seeds). It does not implement a general-purpose query interface — Repo.all, Repo.insert, Repo.update, and Repo.delete are not supported for application tables. This is a deliberate design decision: the goal of this library is schema and seed management, not runtime data access.

Features

• mix ecto.create — creates the BigQuery dataset
• mix ecto.drop — drops the BigQuery dataset
• mix ecto.migrate — runs DDL migrations (create/drop tables, add/drop/rename columns)
• mix ecto.rollback — reverts migrations
• Seeds helper — INSERT and DELETE via BigQuery DML

Installation

Add ecto_bigquery to your dependencies in mix.exs:

def deps do
  [
    {:ecto_bigquery, "~> 0.1"}
  ]
end

Setup

1. Define your Repo

defmodule MyApp.Repo do
  use EctoBigquery.Repo, otp_app: :my_app
end

2. Configure your application

In config/config.exs:

config :my_app, MyApp.Repo,
  project_id: "my-gcp-project",
  dataset_id: "my_dataset",
  goth: MyApp.Goth

3. Add to your supervision tree

# lib/my_app/application.ex
children = [
  {Goth, name: MyApp.Goth, source: {:default, []}},
  MyApp.Repo
]

4. Authentication

EctoBigquery uses Goth for Google Cloud authentication.

Application Default Credentials (recommended for local development):

gcloud auth application-default login

Service Account (recommended for production):

{Goth,
  name: MyApp.Goth,
  source: {:service_account, Jason.decode!(File.read!("credentials.json"))}}

Migrations

Migrations use standard Ecto syntax. Supported operations:

Create table

defmodule MyApp.Repo.Migrations.CreateEvents do
  use Ecto.Migration

  def change do
    create table(:events) do
      add :user_id,    :string,       null: false
      add :event_type, :string
      add :amount,     :decimal
      add :active,     :boolean
      add :metadata,   :map
      timestamps()
    end
  end
end

Alter table

defmodule MyApp.Repo.Migrations.AlterEvents do
  use Ecto.Migration

  def change do
    alter table(:events) do
      add    :contact_email, :string
      remove :legacy_field
    end

    rename table(:events), :amount, to: :total_amount
  end
end

Drop table

defmodule MyApp.Repo.Migrations.DropEvents do
  use Ecto.Migration

  def change do
    drop table(:events)
  end
end

Type Mapping

Standard types

Column modes

BigQuery columns support three modes as an alternative to null: false:

create table(:analytics) do
  add :user_id,    :string,  mode: :required   # STRING NOT NULL
  add :tags,       :string,  mode: :repeated   # ARRAY<STRING>
  add :notes,      :string,  mode: :nullable   # STRING (explicit)
end

mode: :repeated is a shorthand for {:array, type}. Both are equivalent:

add :tags, :string,          mode: :repeated   # ARRAY<STRING>
add :tags, {:array, :string}                   # ARRAY<STRING>

The generator supports :repeated and :nullable as modifiers:

mix ecto_bigquery.gen.migration create_analytics user_id:string:required tags:string:repeated notes:string:nullable

Special BigQuery types

create table(:locations) do
  add :point,        :geography
  add :exact_amount, :bignumeric
  add :tags,         {:array, :string}
  add :scores,       {:array, :integer}
  add :address,      {:struct, [street: :string, city: :string, zip: :string]}
  add :contacts,     {:array, {:struct, [name: :string, email: :string]}}
end

CREATE TABLE `locations` (
  point GEOGRAPHY,
  exact_amount BIGNUMERIC,
  tags ARRAY<STRING>,
  scores ARRAY<INTEGER>,
  address STRUCT<street STRING, city STRING, zip STRING>,
  contacts ARRAY<STRUCT<name STRING, email STRING>>
)

Seeds

Use EctoBigquery.Seeds in priv/repo/seeds.exs:

# Option A: via Repo (Goth must be running in the supervision tree)
EctoBigquery.Seeds.insert(MyApp.Repo, "users", [
  %{id: "1", name: "Alice", active: true},
  %{id: "2", name: "Bob",   active: false}
])

# Atomic multi-table insert — rolls back all if any insert fails
EctoBigquery.Seeds.insert_all(MyApp.Repo, [
  {"users",    [%{id: "1", name: "Alice"}]},
  {"products", [%{sku: "P1", price: 9.99}]}
])

EctoBigquery.Seeds.delete(MyApp.Repo, "users", "active = FALSE")
EctoBigquery.Seeds.truncate(MyApp.Repo, "users")

# Option B: explicit connection (useful in standalone scripts)
config     = MyApp.Repo.config()
project_id = Keyword.get(config, :project_id)
dataset_id = Keyword.get(config, :dataset_id)
{token, 0} = System.cmd("gcloud", ["auth", "print-access-token"])
conn = EctoBigquery.Connection.new(String.trim(token))

EctoBigquery.Seeds.insert_with_conn(conn, project_id, dataset_id, "users", [
  %{id: "1", name: "Alice", active: true},
  %{id: "2", name: "Bob",   active: false}
])

EctoBigquery.Seeds.delete_with_conn(conn, project_id, dataset_id, "users", "active = FALSE")
EctoBigquery.Seeds.truncate_with_conn(conn, project_id, dataset_id, "users")

Migration generator

mix ecto_bigquery.gen.migration generates migration files with pre-filled column definitions based on the migration name pattern.

# Create table
mix ecto_bigquery.gen.migration create_users name:string age:integer active:boolean

# NOT NULL columns
mix ecto_bigquery.gen.migration create_products sku:string:required price:decimal:required

# Add column
mix ecto_bigquery.gen.migration add_email_to_users email:string:required

# Remove column
mix ecto_bigquery.gen.migration remove_phone_from_users phone

# Drop table
mix ecto_bigquery.gen.migration drop_legacy_data

# Special types
mix ecto_bigquery.gen.migration create_places location:geography amount:bignumeric "tags:array[string]"

Generator type aliases

Note: STRUCT types must be written manually in the migration file since their nested fields cannot be expressed in a single CLI argument.

Mix tasks

# Create the BigQuery dataset
mix ecto.create

# Drop the dataset (deletes all tables)
mix ecto.drop

# Run pending migrations
mix ecto.migrate

# Revert the last migration
mix ecto.rollback

# Run seeds
mix run priv/repo/seeds.exs

Out of scope

The following are intentionally not supported:

• Generic query interface — Repo.all, Repo.insert, Repo.update, Repo.delete for application tables. Use google_api_big_query directly for runtime data access.
• DDL transactions — BigQuery does not support transactional DDL. If a migration with multiple changes fails midway, previously applied changes remain permanent.