A Database Toolkit. A CLI designed to help companies better work with their data

# Atlas - Manage Your Database Schema as Code

[![Twitter](https://img.shields.io/twitter/url.svg?label=Follow%20%40atlasgo_io&style=social&url=https%3A%2F%2Ftwitter.com%2Fatlasgo_io)](https://twitter.com/atlasgo_io)
[![Discord](https://img.shields.io/discord/930720389120794674?label=discord&logo=discord&style=flat-square&logoColor=white)](https://discord.com/invite/zZ6sWVg6NT)


 <a href="https://atlasgo.io" target="_blank">
 <img alt="Atlas banner" src="https://github.com/ariga/atlas/assets/7413593/2e27cb81-bad6-491a-8d9c-20920995a186">
 </a>


Atlas is a language-agnostic tool for managing and migrating database schemas using modern DevOps principles.
It offers two workflows:

- **Declarative**: Similar to Terraform, Atlas compares the current state of the database to the desired state, as
 defined in an [HCL], [SQL], or [ORM] schema. Based on this comparison, it generates and executes a migration plan to
 transition the database to its desired state.

- **Versioned**: Unlike other tools, Atlas automatically plans schema migrations for you. Users can describe their desired
 database schema in [HCL], [SQL], or their chosen [ORM], and by utilizing Atlas, they can plan, lint, and apply the
 necessary migrations to the database.


## Supported Databases

[PostgreSQL](https://atlasgo.io/guides/postgres) ·
[MySQL](https://atlasgo.io/guides/mysql) ·
[MariaDB](https://atlasgo.io/guides/mysql) ·
[SQL Server](https://atlasgo.io/guides/mssql) ·
[SQLite](https://atlasgo.io/guides/sqlite) ·
[ClickHouse](https://atlasgo.io/guides/clickhouse) ·
[Redshift](https://atlasgo.io/guides/redshift) ·
[Oracle](https://atlasgo.io/guides/oracle) ·
[Snowflake](https://atlasgo.io/guides/snowflake) ·
[CockroachDB](https://atlasgo.io/guides/cockroachdb) ·
[TiDB](https://atlasgo.io/guides/mysql) ·
[Databricks](https://atlasgo.io/guides/databricks) ·
[Spanner](https://atlasgo.io/guides/spanner) ·
[Aurora DSQL](https://atlasgo.io/guides/dsql) ·
[Azure Fabric](https://atlasgo.io/guides/azure-fabric)

## Installation

**macOS + Linux:**

```bash
curl -sSf https://atlasgo.sh | sh
```

**Homebrew:**

```bash
brew install ariga/tap/atlas
```

**Docker:**

```bash
docker pull arigaio/atlas
```

**NPM:**

```bash
npx @ariga/atlas
```

See [installation docs](https://atlasgo.io/getting-started#installation) for all platforms.

## Key Features

- **[Declarative schema migrations](https://atlasgo.io/declarative/apply)**: The `atlas schema` command offers various options for [inspecting](https://atlasgo.io/inspect), diffing, comparing, [planning](https://atlasgo.io/declarative/plan) and applying migrations using standard Terraform-like workflows.
- **[Versioned migrations](https://atlasgo.io/versioned/intro)**: The `atlas migrate` command provides a state-of-the-art experience for [planning](https://atlasgo.io/versioned/diff), [linting](https://atlasgo.io/lint/analyzers), and [applying](https://atlasgo.io/versioned/apply) migrations.
- **[Schema as Code](https://atlasgo.io/atlas-schema)**: Define your desired database schema using [SQL], [HCL], or your chosen [ORM]. Atlas supports [16 ORM loaders](https://atlasgo.io/orms) across 6 languages.
- **[Security-as-Code](https://atlasgo.io/guides/postgres/security-declarative)**: Manage roles, permissions, and [row-level security](https://atlasgo.io/guides/postgres/row-level-security) policies as version-controlled code.
- **[Data management](https://atlasgo.io/atlas-schema/sql)**: Manage seed and lookup data declaratively alongside your schema.
- **[Cloud-native CI/CD](https://atlasgo.io/integrations)**: [Kubernetes operator](https://atlasgo.io/integrations/kubernetes), [Terraform provider](https://atlasgo.io/integrations/terraform), [GitHub Actions](https://atlasgo.io/integrations/github-actions), [GitLab CI](https://atlasgo.io/integrations/gitlab), [ArgoCD](https://atlasgo.io/integrations/kubernetes/argocd), and more.
- **[Testing framework](https://atlasgo.io/testing/schema)**: Unit test schema logic (functions, views, triggers, procedures) and [migration behavior](https://atlasgo.io/testing/migrate).
- **[50+ safety analyzers](https://atlasgo.io/lint/analyzers)**: Database-aware migration linting that detects destructive changes, data-dependent modifications, table locks, backward-incompatible changes, and more.
- **[Multi-tenancy](https://atlasgo.io/guides/multi-tenancy)**: Built-in support for multi-tenant database migrations.
- **[Drift detection](https://atlasgo.io/monitoring)**: Monitoring as Code with automatic schema drift detection and remediation.
- **[Cloud integration](https://atlasgo.io/guides/deploying/secrets)**: IAM-based authentication for [AWS RDS](https://atlasgo.io/guides/deploying/secrets#aws-rds-iam-authentication) and [GCP Cloud SQL](https://atlasgo.io/guides/deploying/secrets#gcp-cloudsql-iam-authentication), secrets management via [AWS Secrets Manager](https://atlasgo.io/guides/deploying/secrets#aws-secrets-manager), [GCP Secret Manager](https://atlasgo.io/guides/deploying/secrets#gcp-secret-manager), [HashiCorp Vault](https://atlasgo.io/guides/deploying/secrets#hashicorp-vault), and more.

## Getting Started

Get started with Atlas by following the [Getting Started](https://atlasgo.io/getting-started/) docs.

Inspect an existing database schema:
```shell
atlas schema inspect -u "postgres://localhost:5432/mydb"
```

Apply your desired schema to the database:
```shell
atlas schema apply \
 --url "postgres://localhost:5432/mydb" \
 --to file://schema.hcl \
 --dev-url "docker://postgres/16/dev"
```

📖 [Getting Started docs](https://atlasgo.io/getting-started/)

## Migration Linting

Atlas ships with 50+ built-in [analyzers](https://atlasgo.io/lint/analyzers) that review your migration files
and catch issues before they reach production. Analyzers detect [destructive changes](https://atlasgo.io/lint/analyzers#destructive-changes)
like dropped tables or columns, [data-dependent modifications](https://atlasgo.io/lint/analyzers#data-dependent-changes)
such as adding non-nullable columns without defaults, and database-specific risks like table locks
and table rewrites that can cause downtime on busy tables. You can also define
your own [custom policy rules](https://atlasgo.io/lint/rules).

```bash
atlas migrate lint --dev-url "docker://postgres/16/dev"
```

## Schema Testing

[Test](https://atlasgo.io/testing/schema) database logic (functions, views, triggers, procedures) and
[data migrations](https://atlasgo.io/testing/migrate) with `.test.hcl` files:

```hcl
test "schema" "postal" {
 # Valid postal codes pass
 exec {
 sql = "SELECT '12345'::us_postal_code"
 }
 # Invalid postal codes fail
 catch {
 sql = "SELECT 'hello'::us_postal_code"
 }
}

test "schema" "seed" {
 for_each = [
 {input: "hello", expected: "HELLO"},
 {input: "world", expected: "WORLD"},
 ]
 exec {
 sql = "SELECT upper('${each.value.input}')"
 output = each.value.expected
 }
}
```

```bash
atlas schema test --dev-url "docker://postgres/16/dev"
```

📖 [Testing docs](https://atlasgo.io/testing/schema)

## Security-as-Code

Manage database [roles, permissions](https://atlasgo.io/guides/postgres/security-declarative), and
[row-level security](https://atlasgo.io/guides/postgres/row-level-security) as version-controlled code:

```hcl
role "app_readonly" {
 comment = "Read-only access for reporting"
}

role "app_writer" {
 comment = "Read-write access for the application"
 member_of = [role.app_readonly]
}

user "api_user" {
 password = var.api_password
 conn_limit = 20
 comment = "Application API service account"
 member_of = [role.app_writer]
}

permission {
 for_each = [table.orders, table.products, table.users]
 for = each.value
 to = role.app_readonly
 privileges = [SELECT]
}

policy "tenant_isolation" {
 on = table.orders
 for = ALL
 to = ["app_writer"]
 using = "(tenant_id = current_setting('app.current_tenant')::integer)"
 check = "(tenant_id = current_setting('app.current_tenant')::integer)"
}
```

📖 [Security-as-Code docs](https://atlasgo.io/guides/postgres/security-declarative)

## Data Management

Manage seed and lookup data declaratively alongside your schema:

```sql
CREATE TABLE countries (
 id INT PRIMARY KEY,
 code VARCHAR(2) NOT NULL,
 name VARCHAR(100) NOT NULL
);

INSERT INTO countries (id, code, name) VALUES
 (1, 'US', 'United States'),
 (2, 'IL', 'Israel'),
 (3, 'DE', 'Germany');
```

📖 [Data management docs](https://atlasgo.io/atlas-schema/sql)

## ORM Support

Define your schema in any of the 16 supported ORMs. Atlas reads your models and generates migrations:

| Language | ORMs |
|----------|------|
| Go | [GORM](https://atlasgo.io/guides/orms/gorm), [Ent](https://atlasgo.io/guides/orms/ent), [Bun](https://atlasgo.io/guides/orms/bun), [Beego](https://atlasgo.io/guides/orms/beego), [sqlc](https://atlasgo.io/guides/frameworks/sqlc-versioned) |
| TypeScript | [Prisma](https://atlasgo.io/guides/orms/prisma), [Drizzle](https://atlasgo.io/guides/orms/drizzle), [TypeORM](https://atlasgo.io/guides/orms/typeorm), [Sequelize](https://atlasgo.io/guides/orms/sequelize) |
| Python | [Django](https://atlasgo.io/guides/orms/django), [SQLAlchemy](https://atlasgo.io/guides/orms/sqlalchemy) |
| Java | [Hibernate](https://atlasgo.io/guides/orms/hibernate) |
| .NET | [EF Core](https://atlasgo.io/guides/orms/efcore) |
| PHP | [Doctrine](https://atlasgo.io/guides/orms/doctrine) |

📖 [ORM integration docs](https://atlasgo.io/orms)

## Integrations

Lint, test, and apply migrations automatically in your CI/CD pipeline or infrastructure-as-code workflow:

| Integration | Docs |
|-------------|------|
| GitHub Actions | [Versioned guide](https://atlasgo.io/guides/ci-platforms/github-versioned) · [Declarative guide](https://atlasgo.io/guides/ci-platforms/github-declarative) |
| GitLab CI | [Versioned guide](https://atlasgo.io/guides/ci-platforms/gitlab-versioned) · [Declarative guide](https://atlasgo.io/guides/ci-platforms/gitlab-declarative) |
| CircleCI | [Versioned guide](https://atlasgo.io/guides/ci-platforms/circleci-versioned) · [Declarative guide](https://atlasgo.io/guides/ci-platforms/circleci-declarative) |
| Bitbucket Pipes | [Versioned guide](https://atlasgo.io/guides/ci-platforms/bitbucket-versioned) · [Declarative guide](https://atlasgo.io/guides/ci-platforms/bitbucket-declarative) |
| Azure DevOps | [GitHub repos](https://atlasgo.io/guides/ci-platforms/azure-devops-github) · [Azure repos](https://atlasgo.io/guides/ci-platforms/azure-devops-repos) |
| Terraform Provider | [atlasgo.io/integrations/terraform-provider](https://atlasgo.io/integrations/terraform-provider) |
| Kubernetes Operator | [atlasgo.io/integrations/kubernetes](https://atlasgo.io/integrations/kubernetes) |
| ArgoCD | [atlasgo.io/guides/deploying/k8s-argo](https://atlasgo.io/guides/deploying/k8s-argo) |
| Flux | [atlasgo.io/guides/deploying/k8s-flux](https://atlasgo.io/guides/deploying/k8s-flux) |
| Crossplane | [atlasgo.io/guides/deploying/crossplane](https://atlasgo.io/guides/deploying/crossplane) |
| Go SDK | [pkg.go.dev/ariga.io/atlas-go-sdk/atlasexec](https://pkg.go.dev/ariga.io/atlas-go-sdk/atlasexec) |

### AI Agent Integration

Atlas provides [Agent Skills](https://atlasgo.io/guides/ai-tools/agent-skills), an open standard for packaging
migration expertise for AI coding assistants:
[Claude Code](https://atlasgo.io/guides/ai-tools/claude-code-instructions),
[GitHub Copilot](https://atlasgo.io/guides/ai-tools/github-copilot-instructions),
[Cursor](https://atlasgo.io/guides/ai-tools/cursor-rules),
[OpenAI Codex](https://atlasgo.io/guides/ai-tools/codex-instructions). Learn more at [AI tools docs](https://atlasgo.io/guides/ai-tools).

## CLI Usage

### `schema inspect`

_**Easily inspect your database schema by providing a database URL and convert it to HCL, JSON, SQL, ERD, or other formats.**_

Inspect a specific MySQL schema and get its representation in Atlas DDL syntax:
```shell
atlas schema inspect -u "mysql://root:pass@localhost:3306/example" > schema.hcl
```

<details><summary>Result</summary>

```hcl
table "users" {
 schema = schema.example
 column "id" {
 null = false
 type = int
 }
 ...
}
```
</details>

Inspect the entire MySQL database and get its JSON representation:
```shell
atlas schema inspect \
 --url "mysql://root:pass@localhost:3306/" \
 --format '{{ json . }}' | jq
```

<details><summary>Result</summary>

```json
{
 "schemas": [
 {
 "name": "example",
 "tables": [
 {
 "name": "users",
 "columns": [
 ...
 ]
 }
 ]
 }
 ]
}
```
</details>

Inspect a specific PostgreSQL schema and get its ERD representation in Mermaid syntax:
```shell
atlas schema inspect \
 --url "postgres://root:pass@:5432/test?search_path=public&sslmode=disable" \
 --format '{{ mermaid . }}'
```

```mermaid
erDiagram
 users {
 int id PK
 varchar name
 }
 blog_posts {
 int id PK
 varchar title
 text body
 int author_id FK
 }
 blog_posts }o--o| users : author_fk
```

Use the [split format](https://atlasgo.io/inspect/database-to-code) for one-file-per-object output:

```bash
atlas schema inspect -u '<url>' --format '{{ sql . | split | write }}'
```

```
├── schemas
│ └── public
│ ├── public.sql
│ ├── tables
│ │ ├── profiles.sql
│ │ └── users.sql
│ ├── functions
│ └── types
└── main.sql
```

📖 [Schema inspection docs](https://atlasgo.io/inspect)

### `schema diff`

_**Compare two schema states and get a migration plan to transform one into the other. A state can be specified using a
database URL, HCL, SQL, or ORM schema, or a migration directory.**_

```shell
atlas schema diff \
 --from "postgres://postgres:pass@:5432/test?search_path=public&sslmode=disable" \
 --to file://schema.hcl \
 --dev-url "docker://postgres/15/test"
```

📖 [Declarative workflow docs](https://atlasgo.io/declarative/apply)

### `schema apply`

_**Generate a migration plan and apply it to the database to bring it to the desired state. The desired state can be
specified using a database URL, HCL, SQL, or ORM schema, or a migration directory.**_

```shell
atlas schema apply \
 --url mysql://root:pass@:3306/db1 \
 --to file://schema.hcl \
 --dev-url docker://mysql/8/db1
```

<details><summary>Result</summary>

```shell
-- Planned Changes:
-- Modify "users" table
ALTER TABLE `db1`.`users` DROP COLUMN `d`, ADD COLUMN `c` int NOT NULL;
Use the arrow keys to navigate: ↓ ↑ → ←
? Are you sure?:
 ▸ Apply
 Abort
```
</details>

📖 [Declarative workflow docs](https://atlasgo.io/declarative/apply)

### `migrate diff`

_**Write a new migration file to the migration directory that brings it to the desired state. The desired state can be
specified using a database URL, HCL, SQL, or ORM schema, or a migration directory.**_

```shell
atlas migrate diff add_blog_posts \
 --dir file://migrations \
 --to file://schema.hcl \
 --dev-url docker://mysql/8/test
```

📖 [Versioned workflow docs](https://atlasgo.io/versioned/diff)

### `migrate apply`

_**Apply all or part of pending migration files in the migration directory on the database.**_

```shell
atlas migrate apply \
 --url mysql://root:pass@:3306/db1 \
 --dir file://migrations
```

📖 [Versioned workflow docs](https://atlasgo.io/versioned/apply)

## Supported Version Policy

To ensure the best performance, security and compatibility with the Atlas Cloud service, the Atlas team
will only support the two most recent minor versions of the CLI. For example, if the latest version is
`v0.25`, the supported versions will be `v0.24` and `v0.25` (in addition to any patch releases and the
"canary" release which is built twice a day).


## Community

[Documentation](https://atlasgo.io/getting-started) ·
[Discord](https://discord.com/invite/zZ6sWVg6NT) ·
[Twitter](https://twitter.com/atlasgo_io)

[HCL]: https://atlasgo.io/atlas-schema/hcl
[SQL]: https://atlasgo.io/atlas-schema/sql
[ORM]: https://atlasgo.io/orms

atlas

Discover SQL schemas and convert them to AVRO schemas. Query SQL records into AVRO bytes

# *avro*

[![Build Status](https://github.com/khezen/avro/workflows/build/badge.svg?branch=master)](https://github.com/khezen/avro/actions?query=workflow%3Abuild) [![codecov](https://img.shields.io/codecov/c/github/khezen/avro/master.svg)](https://codecov.io/gh/khezen/avro)
[![Go Report Card](https://goreportcard.com/badge/github.com/khezen/avro)](https://goreportcard.com/report/github.com/khezen/avro)

The purpose of this package is to facilitate use of AVRO with `go` strong typing.

## Features

### `github.com/khezen/avro`

[![GoDoc](https://img.shields.io/badge/go-documentation-blue.svg)](https://godoc.org/github.com/khezen/avro)

* [Marshal/Unmarshal AVRO schema](#schema-marshalunmarshal)

### `github.com/khezen/avro/sqlavro`

[![GoDoc](https://img.shields.io/badge/go-documentation-blue.svg)](https://godoc.org/github.com/khezen/avro/sqlavro)

* [Discover SQL tables](#convert-sql-table-to-avro-schema)
* [Convert SQL tables to AVRO schemas](#convert-sql-table-to-avro-schema)
* [Query records from SQL into AVRO or CSV binary](#query-records-from-sql-into-avro-or-csv-binary)

### `github.com/khezen/avro/redshiftavro`

[![GoDoc](https://img.shields.io/badge/go-documentation-blue.svg)](https://godoc.org/github.com/khezen/avro/redshiftavro)

* [Produce Redshit create statement from AVRO schema](#produce-redshift-create-statement-from-avro-schema)

## What is AVRO

[Apache AVRO](http://avro.apache.org/docs/current/spec.html) is a data serialization system which relies on JSON schemas.

It provides:

* Rich data structures
* A compact, fast, binary data format
* A container file, to store persistent data
* Remote procedure call (RPC)

AVRO binary encoded data comes together with its schema and therefore is fully self-describing.

When AVRO data is read, the schema used when writing it is always present. This permits each datum to be written with no per-value overheads, making serialization both fast and small.

When AVRO data is stored in a file, its schema is stored with it, so that files may be processed later by any program. If the program reading the data expects a different schema this can be easily resolved, since both schemas are present.

## Examples

### Schema Marshal/Unmarshal

```golang
package main

import (
  "encoding/json"
  "fmt"

  "github.com/khezen/avro"
)

func main() {
  schemaBytes := []byte(
    `{
      "type": "record",
      "namespace": "test",
      "name": "LongList",
      "aliases": [
        "LinkedLongs"
      ],
      "doc": "linked list of 64 bits integers",
      "fields": [
        {
          "name": "value",
          "type": "long"
        },
        {
          "name": "next",
          "type": [
            "null",
            "LongList"
          ]
        }
      ]
    }`,
  )

  // Unmarshal JSON  bytes to Schema interface
  var anySchema avro.AnySchema
  err := json.Unmarshal(schemaBytes, &anySchema)
  if err != nil {
    panic(err)
  }
  schema := anySchema.Schema()  
  // Marshal Schema interface to JSON bytes
  schemaBytes, err = json.Marshal(schema)
  if err != nil {
    panic(err)
  }
  fmt.Println(string(schemaBytes))
}
```

```json
{
    "type": "record",
    "namespace": "test",
    "name": "LongList",
    "aliases": [
        "LinkedLongs"
    ],
    "doc": "linked list of 64 bits integers",
    "fields": [
        {
            "name": "value",
            "type": "long"
        },
        {
            "name": "next",
            "type": [
                "null",
                "LongList"
            ]
        }
    ]
}
```

### Convert SQL Table to AVRO Schema

```golang
package main
import (
  "database/sql"
  "encoding/json"
  "fmt"

  "github.com/khezen/avro/sqlavro"
)

func main() {
  db, err := sql.Open("mysql", "root@/blog")
  if err != nil {
    panic(err)
  }
  defer db.Close()
  _, err = db.Exec(
    `CREATE TABLE posts(
      ID INT NOT NULL,
      title VARCHAR(128) NOT NULL,
      body LONGBLOB NOT NULL,
      content_type VARCHAR(128) DEFAULT 'text/markdown; charset=UTF-8',
      post_date DATETIME NOT NULL,
      update_date DATETIME,
      reading_time_minutes DECIMAL(3,1),
      PRIMARY KEY(ID)
    )`,
  )
  if err != nil {
    panic(err)
  }
  schemas, err := sqlavro.SQLDatabase2AVRO(db, "blog")
  if err != nil {
    panic(err)
  }
  schemasBytes, err := json.Marshal(schemas)
  if err != nil {
    panic(err)
  }
  fmt.Println(string(schemasBytes))
}
```

```json
[
    {
        "type": "record",
        "namespace": "blog",
        "name": "posts",
        "fields": [
            {
                "name": "ID",
                "type": "int"
            },
            {
                "name": "title",
                "type": "string"
            },
            {
                "name": "body",
                "type": "bytes"
            },
            {
                "name": "content_type",
                "type": [
                    "string",
                    "null"
                ],
                "default": "text/markdown; charset=UTF-8"
            },
            {
                "name": "post_date",
                "type": {
                    "type": "int",
                    "doc":"datetime",
                    "logicalType": "timestamp"
                }
            },
            {
                "name": "update_date",
                "type": [
                    "null",
                    {
                        "type": "int",
                        "doc":"datetime",
                        "logicalType": "timestamp"
                    }
                ]
            },
            {
                "name": "reading_time_minutes",
                "type": [
                    "null",
                    {
                        "type": "bytes",
                        "logicalType": "decimal",
                        "precision": 3,
                        "scale": 1
                    }
                ]
            }
        ]
    }
]
```

### Query records from SQL into AVRO or CSV binary

```golang
package main

import (
	"database/sql"
	"fmt"
	"io/ioutil"
	"time"

	"github.com/khezen/avro"
	"github.com/khezen/avro/sqlavro"
)

func main() {
	db, err := sql.Open("mysql", "root@/blog")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	_, err = db.Exec(
		`CREATE TABLE posts(
			ID INT NOT NULL,
			title VARCHAR(128) NOT NULL,
			body LONGBLOB NOT NULL,
			content_type VARCHAR(128) DEFAULT 'text/markdown; charset=UTF-8',
			post_date DATETIME NOT NULL,
			update_date DATETIME,
			reading_time_minutes DECIMAL(3,1),
			PRIMARY KEY(ID)
		)`,
	)
	if err != nil {
		panic(err)
	}
	_, err = db.Exec(
		// statement
		`INSERT INTO posts(ID,title,body,content_type,post_date,update_date,reading_time_minutes)
		 VALUES (?,?,?,?,?,?,?)`,
		// values
		42,
		"lorem ispum",
		[]byte(`Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.`),
		"text/markdown; charset=UTF-8",
		"2009-04-10 00:00:00",
		"2009-04-10 00:00:00",
		"4.2",
	)
	if err != nil {
		panic(err)
	}
	schema, err := sqlavro.SQLTable2AVRO(db, "blog", "posts")
	if err != nil {
		panic(err)
	}
	limit := 1000
	order := avro.Ascending
	from, err := time.Parse("2006-02-01 15:04:05", "2009-04-10 00:00:00")
	if err != nil {
		panic(err)
	}
	avroBytes, updatedCriteria, err := sqlavro.Query(sqlavro.QueryConfig{
		DB:     db,
		DBName: "blog",
		Schema: schema,
		Limit:  limit,
		Criteria: []sqlavro.Criterion{
			*sqlavro.NewCriterionDateTime("post_date", &from, order),
		},
		Output: "avro",
	})
	if err != nil {
		panic(err)
	}
	err = ioutil.WriteFile("/tmp/blog_posts.avro", avroBytes, 0644)
	if err != nil {
		panic(err)
	}
	fmt.Println(updatedCriteria)
}
```

#### Notes

* When record fields contains aliases, the first alias is used in the query instead of the field name.

## Types

| Avro               | Go                       | SQL
| ------------------ | ------------------------ | ---
| `null`             | `nil`                    | `NULL`
| `bytes`            | `[]byte`                 | `BLOB`,`MEDIUMBLOB`,`LONGBLOB`
| `fixed`            | `[]byte`                 | `CHAR`,`NCHAR`
| `string`,`enum`    | `string`                 | `VARCHAR`, `NVARCHAR`,`TEXT`,`TINYTEXT`,`MEDIUMTEXT`,`LONGTEXT`,`ENUM`,`SET`
| `float`            | `float32`                | `FLOAT`
| `double`           | `float64`                | `DOUBLE`
| `long`             | `int64`                  | `BIGINT`
| `int`              | `int32`                  | `TINYINT`,`SMALLINT`,`INT`,`YEAR`
| `decimal`          | `*big.Rat`               | `DECIMAL`
| `time`             | `int32`                  | `TIME`
| `timestamp`        | `int32`                  | `TIMESTAMP`,`DATETIME`
| `date`             | `time.Time`              | `DATE`
| `array`            | `[]interface{}`          | **N/A**
| `map`,`record`     | `map[string]interface{}` | **N/A**
| `union`            | *see below*              | **any type nullable**

Because of encoding rules for Avro unions, when an union's value is
`null`, a simple Go `nil` is returned. However when an union's value
is non-`nil`, a Go `map[string]interface{}` with a single key is
returned for the union. The map's single key is the Avro type name and
its value is the datum's value.

### Produce Redshift create statement from AVRO schema

```golang
package main

import (
	"encoding/json"
	"fmt"

	"github.com/khezen/avro"
	"github.com/khezen/avro/redshiftavro"
)

func main() {
	schemaBytes := []byte(`
	{
        "type": "record",
        "namespace": "blog",
        "name": "posts",
        "fields": [
            {
                "name": "ID",
                "type": "int"
            },
            {
                "name": "title",
                "type": "string"
            },
            {
                "name": "body",
                "type": "bytes"
            },
            {
                "name": "content_type",
                "type": [
                    "string",
                    "null"
                ],
                "default": "text/markdown; charset=UTF-8"
            },
            {
                "name": "post_date",
                "type": {
                    "type": "int",
                    "doc":"datetime",
                    "logicalType": "timestamp"
                }
            },
            {
                "name": "update_date",
                "type": [
                    "null",
                    {
                        "type": "int",
                        "doc":"datetime",
                        "logicalType": "timestamp"
                    }
                ]
            },
            {
                "name": "reading_time_minutes",
                "type": [
                    "null",
                    {
                        "type": "bytes",
                        "logicalType": "decimal",
                        "precision": 3,
                        "scale": 1
                    }
                ]
            }
        ]
	}`)
	var anySchema avro.AnySchema
	err := json.Unmarshal(schemaBytes, &anySchema)
	if err != nil {
		panic(err)
	}
	schema := anySchema.Schema().(*avro.RecordSchema)
	cfg := redshiftavro.CreateConfig{
		Schema:      *schema,
		SortKeys:    []string{"post_date", "title"},
		IfNotExists: true,
	}
	statement, err := redshiftavro.CreateTableStatement(cfg)
	if err != nil {
		panic(err)
	}
	fmt.Println(statement)
}
```

```sql
CREATE TABLE IF NOT EXISTS posts(
	ID INTEGER ENCODE LZO NOT NULL,
	title VARCHAR(65535) ENCODE RAW NOT NULL,
	body VARCHAR(65535) ENCODE ZSTD NOT NULL,
	content_type VARCHAR(65535) ENCODE ZSTD NULL,
	post_date TIMESTAMP WITHOUT TIME ZONE ENCODE RAW NOT NULL,
	update_date TIMESTAMP WITHOUT TIME ZONE ENCODE LZO NULL,
	reading_time_minutes DECIMAL(3,1) ENCODE RAW NULL
)
SORTKEY(
	post_date,
	title
)
```

## Issues

If you have any problems or questions, please ask for help through a [GitHub issue](https://github.com/khezen/avro/issues).

## Contributions

Help is always welcome! For example, documentation (like the text you are reading now) can always use improvement. There's always code that can be improved. If you ever see something you think should be fixed, you should own it. If you have no idea what to start on, you can browse the issues labeled with [help wanted](https://github.com/khezen/avro/labels/help%20wanted).

As a potential contributor, your changes and ideas are welcome at any hour of the day or night, weekdays, weekends, and holidays. Please do not ever hesitate to ask a question or send a pull request.

[Code of conduct](https://github.com/khezen/avro/blob/master/CODE_OF_CONDUCT.md).


avro

Safe database schema change and version control for DevOps teams

<h1 align="center">
 <a href="https://www.bytebase.com?source=github" target="_blank">
 <img alt="Bytebase" src="https://raw.githubusercontent.com/bytebase/bytebase/main/docs/assets/banner.webp" />
 </a>
</h1>


 Database CI/CD for DevOps teams 
 Manage database schema changes with confidence



 <a href="https://docs.bytebase.com/get-started/self-host-vs-cloud" target="_blank">⚙️ Install</a> •
 <a href="https://docs.bytebase.com">📚 Docs</a> •
 <a href="https://demo.bytebase.com">🎮 Demo</a> •
 <a href="https://discord.gg/huyw7gRsyA">💬 Discord</a> •
 <a href="https://www.bytebase.com/request-demo/">🙋‍♀️ Book Demo</a>



 <a href="https://goreportcard.com/report/github.com/bytebase/bytebase">
 <img alt="go report" src="https://goreportcard.com/badge/github.com/bytebase/bytebase" />
 </a>
 <a href="https://artifacthub.io/packages/search?repo=bytebase">
 <img alt="Artifact Hub" src="https://img.shields.io/endpoint?url=https://artifacthub.io/badge/repository/bytebase" />
 </a>
 <a href="https://github.com/bytebase/bytebase">
 <img alt="Github Stars" src="https://img.shields.io/github/stars/bytebase/bytebase?logo=github">
 </a>


---

## What is Bytebase?

Bytebase is an open-source database DevOps tool, it's the **only database CI/CD project** included by the [CNCF Landscape](https://landscape.cncf.io/?selected=bytebase&item=app-definition-and-development--continuous-integration-delivery--bytebase) and [Platform Engineering](https://platformengineering.org/tools/bytebase).

It offers a web-based collaboration workspace to help DBAs and Developers manage the lifecycle of application database schemas.

<h1 align="center">
 <img alt="Bytebase" src="https://raw.githubusercontent.com/bytebase/bytebase/main/docs/assets/fish.webp" />
</h1>

## Key Features

### 🔄 **Database CI/CD**

- **GitOps Integration**: Native GitHub/GitLab integration for database-as-code workflows
- **Migration Management**: Automated schema migration with rollback support
- **SQL Review**: 200+ lint rules to enforce SQL standards and best practices

### 🔒 **Security & Compliance**

- **Data Masking**: Advanced column-level masking for sensitive data protection
- **Access Control**: Fine-grained RBAC with project and workspace-level permissions
- **Audit Logging**: Complete audit trail of all database activities

### 🎯 **Developer Experience**

- **Web SQL Editor**: Feature-rich IDE for database development
- **Batch Changes**: Apply changes across multiple databases and tenants
- **API & Terraform**: Full API access and Terraform provider for automation

### 📊 **Operations**

- **Multi-Database Support**: PostgreSQL, MySQL, MongoDB, Redis, Snowflake, and more
- **Drift Detection**: Automatic detection of schema drift across environments
- **Admin Mode**: CLI-like experience without bastion setup

## Quick Start

### Docker

```bash
docker run --init \
 --name bytebase \
 --publish 8080:8080 \
 --volume ~/.bytebase/data:/var/opt/bytebase \
 bytebase/bytebase:latest
```

### Kubernetes

```bash
helm install bytebase bytebase/bytebase
```

Visit [http://localhost:8080](http://localhost:8080) and follow the setup wizard.

## Documentation

- [Installation Guide](https://docs.bytebase.com/get-started/self-host-vs-cloud)
- [Tutorials](https://docs.bytebase.com/tutorials)
- [API Reference](https://docs.bytebase.com/api/overview)
- [FAQ](https://docs.bytebase.com/faq)

## The Bytebase Family

- **[Bytebase Console](https://www.bytebase.com)**: Web-based GUI for database lifecycle management
- **[SQL Review Action](https://github.com/bytebase/sql-review-action)**: GitHub Action for PR-time SQL review
- **[Terraform Provider](https://registry.terraform.io/providers/bytebase/bytebase/latest/docs)**: Infrastructure as code for Bytebase resources

## Use Cases

### For Development Teams

- Implement database schema version control
- Automate database deployments through CI/CD pipelines
- Collaborate on database changes with review workflows

### For DBAs

- Centralize database management across all environments
- Enforce organization-wide SQL standards and policies
- Monitor and audit all database activities

### For Security Teams

- Control data access with column-level permissions
- Implement data masking for sensitive information
- Maintain compliance with audit trails

## Supported Databases

PostgreSQL, MySQL, MariaDB, TiDB, Snowflake, ClickHouse, MongoDB, Redis, Oracle, SQL Server, Spanner, and [more](https://docs.bytebase.com/introduction/supported-databases).

## Community & Support

- 💬 [Discord Community](https://discord.gg/huyw7gRsyA)
- 🐦 [Twitter](https://twitter.com/Bytebase)
- 📧 [Email Support](mailto:support@bytebase.com)
- 🐛 [Issue Tracker](https://github.com/bytebase/bytebase/issues)

## Contributing

We welcome contributions!

```bash
# Setup a postgres database with user bbdev and database bbdev
export PG_URL=postgresql://bbdev@localhost/bbdev

# Start backend
alias r='go build -ldflags "-w -s" -p=16 -o ./bytebase-build/bytebase ./backend/bin/server/main.go && ./bytebase-build/bytebase --port 8080 --data . --debug'

# Start frontend
alias y="pnpm --dir frontend i && pnpm --dir frontend dev"
```

## Comparisons

- [Bytebase vs Liquibase](https://www.bytebase.com/blog/bytebase-vs-liquibase/)
- [Bytebase vs Flyway](https://www.bytebase.com/blog/bytebase-vs-flyway/)
- [Bytebase vs Jira](https://www.bytebase.com/blog/use-jira-for-database-change/)
- [Bytebase vs DBeaver](https://www.bytebase.com/blog/bytebase-vs-dbeaver/)
- [Bytebase vs DataGrip](https://www.bytebase.com/blog/bytebase-vs-datagrip/)
- [Bytebase vs Navicat](https://www.bytebase.com/blog/bytebase-vs-navicat/)
- [Bytebase vs CloudBeaver](https://www.bytebase.com/blog/bytebase-vs-cloudbeaver/)

<a href="https://www.star-history.com/?repos=bytebase%2Fbytebase%2Cliquibase%2Fliquibase%2Cflyway%2Fflyway%2Cdbeaver%2Fcloudbeaver&type=date&legend=top-left">
 <picture>
 <source media="(prefers-color-scheme: dark)" srcset="https://api.star-history.com/chart?repos=bytebase/bytebase%2Cliquibase/liquibase%2Cflyway/flyway%2Cdbeaver/cloudbeaver&type=date&theme=dark&legend=top-left" />
 <source media="(prefers-color-scheme: light)" srcset="https://api.star-history.com/chart?repos=bytebase/bytebase%2Cliquibase/liquibase%2Cflyway/flyway%2Cdbeaver/cloudbeaver&type=date&legend=top-left" />
 <img alt="Star History Chart" src="https://api.star-history.com/chart?repos=bytebase/bytebase%2Cliquibase/liquibase%2Cflyway/flyway%2Cdbeaver/cloudbeaver&type=date&legend=top-left" />
 </picture>
</a>

---


 Join us in revolutionizing database management! 
 <a href="https://cal.com/bytebase/product-walkthrough">Book a demo</a>

bytebase

[![Build Status](https://travis-ci.org/GuiaBolso/darwin.svg?branch=master)](https://travis-ci.org/GuiaBolso/darwin)
[![Go Report Card](https://goreportcard.com/badge/github.com/GuiaBolso/darwin)](https://goreportcard.com/report/github.com/GuiaBolso/darwin)
[![GoDoc](https://godoc.org/github.com/GuiaBolso/darwin?status.svg)](https://godoc.org/github.com/GuiaBolso/darwin)

Try browsing [the code on Sourcegraph](https://sourcegraph.com/github.com/GuiaBolso/darwin)!

# Darwin

Database schema evolution library for Go

# Example

```go
package main

import (
	"database/sql"
	"log"

	"github.com/GuiaBolso/darwin"
	_ "github.com/go-sql-driver/mysql"
)

var (
	migrations = []darwin.Migration{
		{
			Version:     1,
			Description: "Creating table posts",
			Script: `CREATE TABLE posts (
						id INT 		auto_increment, 
						title 		VARCHAR(255),
						PRIMARY KEY (id)
					 ) ENGINE=InnoDB CHARACTER SET=utf8;`,
		},
		{
			Version:     2,
			Description: "Adding column body",
			Script:      "ALTER TABLE posts ADD body TEXT AFTER title;",
		},
	}
)

func main() {
	database, err := sql.Open("mysql", "root:@/darwin")

	if err != nil {
		log.Fatal(err)
	}

	driver := darwin.NewGenericDriver(database, darwin.MySQLDialect{})

	d := darwin.New(driver, migrations, nil)
	err = d.Migrate()

	if err != nil {
		log.Println(err)
	}
}
```

# Questions

Q. Why there is not a command line utility?

A. The purpose of this library is just be a library.

Q. How can I read migrations from file system?

A. You can read with the standard library and build the migration list.

Q. Can I put more than one statement in the same Script migration?

A. I do not recommend. Put one database change per migration, if some migration fail, you exactly what statement caused the error. Also only postgres correctly handle rollback in DDL transactions. 

To be less annoying you can organize your migrations like? 1.0, 1.1, 1.2 and so on.

Q. Why does not exists downgrade migrations?

A. Please read https://flywaydb.org/documentation/faq#downgrade

Q. Does Darwin perform a roll back if a migration fails?

A. Please read https://flywaydb.org/documentation/faq#rollback

Q. What is the best strategy for dealing with hot fixes?

A. Plese read https://flywaydb.org/documentation/faq#hot-fixes


# LICENSE

The MIT License (MIT)

Copyright (c) 2016 Claudemiro

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.


darwin

A lightweight, framework-agnostic database migration tool

dbmate

Pure-SQL schema management system for MySQL, with support for sharding and external online schema change tools

[![Skeema](https://www.skeema.io/img/logo.png)](https://www.skeema.io)

[![build status](https://img.shields.io/github/actions/workflow/status/skeema/skeema/tests.yml?branch=main)](https://github.com/skeema/skeema/actions)
[![code coverage](https://img.shields.io/coveralls/skeema/skeema.svg)](https://coveralls.io/r/skeema/skeema)
[![downloads](https://img.shields.io/github/downloads/skeema/skeema/total.svg)](https://github.com/skeema/skeema/releases)
[![latest release](https://img.shields.io/github/release/skeema/skeema.svg)](https://github.com/skeema/skeema/releases)

Skeema is a tool for managing MySQL and MariaDB schema changes in a [declarative](https://www.skeema.io/blog/2019/01/18/declarative/) fashion using SQL `CREATE` statements. The Skeema CLI tool allows you to:

* Export `CREATE` statements from your DB and track them in an organized repo
  * Each table/proc/object is placed in its own file for easy browsing and reference
  * Use standard `git` commands to see history on a per-object basis
  * Provides vital context to AI tools, without wasteful intermediate `ALTER` migrations
* Auto-generate DDL by performing diffs between live DBs and your schema repo
  * Battle-tested from a decade of use, and trusted by some of the [biggest names in tech](https://www.skeema.io/about/), including GitHub itself
  * Skeema's [internal safety mechanisms](https://www.skeema.io/docs/features/safety/#internal-correctness-guardrails) ensure that the generated DDL is absolutely always correct
* Manage multiple environments (e.g. dev, staging, prod) and keep them in sync with ease
* Configure use of [online schema change tools](https://www.skeema.io/docs/features/osc/), such as `pt-online-schema-change` or `gh-ost`, for performing non-disruptive `ALTER TABLE`
  * Skeema's configuration system even allows you to *conditionally* use OSC depending on table size, environment, schema, etc
* Apply 20+ [configurable linter rules](https://www.skeema.io/docs/features/safety/) to proactively catch schema design problems and enforce company policies
  * Easily lint [all objects](https://www.skeema.io/docs/commands/lint/) in a dir/schema, or just [modified objects](https://www.skeema.io/docs/options/#lint) in a diff
  * Perfect for CI use, with [well-defined exit code behaviors](https://www.skeema.io/docs/automation/)

Skeema supports a pull-request-based workflow for schema change submission, review, and execution. This permits your team to manage schema changes in exactly the same way as you manage code changes.

## Products and downloads

This repo is the free open source Community edition of the Skeema command-line tool. The Community edition supports management of [**tables**](https://www.skeema.io/docs/features/tables/) and [**routines**](https://www.skeema.io/docs/features/routines/) (procs/funcs). Builds are provided for Linux and MacOS.

The paid [Premium edition](https://www.skeema.io/download/) of the Skeema CLI adds support for managing [**views**](https://www.skeema.io/docs/features/views/), [**triggers**](https://www.skeema.io/docs/features/triggers/), and [**events**](https://www.skeema.io/docs/features/events/). It also includes a native **Windows build**, [**seed data**](https://www.skeema.io/docs/features/seeddata/) management, [**enhanced TLS options**](https://www.skeema.io/docs/features/tls/) for environments requiring client-side certs or CA verification, and many other improvements.

For download links and more information, visit [skeema.io](https://www.skeema.io/download/).

## Documentation

Page | Description
--- | ---
[Installation](https://www.skeema.io/docs/install/) | How to install the Skeema CLI tool
[Getting started](https://www.skeema.io/docs/examples/) | Usage examples and screencasts
[Requirements](https://www.skeema.io/docs/requirements/) | Supported database systems and required database privileges
[Features](https://www.skeema.io/docs/features/) | How Skeema interacts with each type of database object, and various feature-specific topics
[Configuration guide](https://www.skeema.io/docs/config/) | Option handling, config file format, and command-line option usage
[Command reference](https://www.skeema.io/docs/commands/) | Usage instructions for each command in the Skeema CLI
[Option reference](https://www.skeema.io/docs/options/) | Detailed information on every Skeema option
[Schema change workflow](https://www.skeema.io/docs/workflow/) | Recommended flow for pull-request-driven schema changes
[Pipelines and automation](https://www.skeema.io/docs/automation/) | Integrating Skeema into automated workflows
[Recipes](https://www.skeema.io/docs/recipes/) | Using Skeema to achieve common schema management tasks
[FAQ](https://www.skeema.io/docs/faq/) | Frequently asked questions about Skeema

## Credits

Created by [@evanelias](https://github.com/evanelias) at [Index Hint LLC](https://www.indexhint.com/), and developed with assistance from our many [contributors](https://github.com/skeema/skeema/graphs/contributors) and [users](https://www.skeema.io/about/).

Support for stored procedures and functions generously sponsored by [Psyonix](https://psyonix.com), creators of [Rocket League](https://www.rocketleague.com).

Support for partitioned tables generously sponsored by [Etsy](https://www.etsy.com).

## License

**Skeema Community Edition source code copyright 2026 Skeema LLC and the Skeema authors**

```text
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
```


skeema

Database migration tool. Allows embedding migrations into the application using go-bindata

# sql-migrate

> SQL Schema migration tool for [Go](https://golang.org/). Based on [gorp](https://github.com/go-gorp/gorp) and [goose](https://bitbucket.org/liamstask/goose).

[![Test](https://github.com/rubenv/sql-migrate/actions/workflows/test.yml/badge.svg)](https://github.com/rubenv/sql-migrate/actions/workflows/test.yml) [![Go Reference](https://pkg.go.dev/badge/github.com/rubenv/sql-migrate.svg)](https://pkg.go.dev/github.com/rubenv/sql-migrate)

## Features

- Usable as a CLI tool or as a library
- Supports SQLite, PostgreSQL, MySQL, MSSQL and Oracle databases (through [gorp](https://github.com/go-gorp/gorp))
- Can embed migrations into your application
- Migrations are defined with SQL for full flexibility
- Atomic migrations
- Up/down migrations to allow rollback
- Supports multiple database types in one project
- Works great with other libraries such as [sqlx](https://jmoiron.github.io/sqlx/)
- Supported on go1.13+

## Installation

To install the library and command line program, use the following:

```bash
go get -v github.com/rubenv/sql-migrate/...
```

For Go version from 1.18, use:

```bash
go install github.com/rubenv/sql-migrate/...@latest
```

## Usage

### As a standalone tool

```
$ sql-migrate --help
usage: sql-migrate [--version] [--help] <command> [<args>]

Available commands are:
 down Undo a database migration
 new Create a new migration
 redo Reapply the last migration
 status Show migration status
 up Migrates the database to the most recent version available
```

Each command requires a configuration file (which defaults to `dbconfig.yml`, but can be specified with the `-config` flag). This config file should specify one or more environments:

```yml
development:
 dialect: sqlite3
 datasource: test.db
 dir: migrations/sqlite3

production:
 dialect: postgres
 datasource: dbname=myapp sslmode=disable
 dir: migrations/postgres
 table: migrations
```

(See more examples for different set ups [here](test-integration/dbconfig.yml))

Also one can obtain env variables in datasource field via `os.ExpandEnv` embedded call for the field.
This may be useful if one doesn't want to store credentials in file:

```yml
production:
 dialect: postgres
 datasource: host=prodhost dbname=proddb user=${DB_USER} password=${DB_PASSWORD} sslmode=require
 dir: migrations
 table: migrations
```

The `table` setting is optional and will default to `gorp_migrations`.

The environment that will be used can be specified with the `-env` flag (defaults to `development`).

Use the `--help` flag in combination with any of the commands to get an overview of its usage:

```
$ sql-migrate up --help
Usage: sql-migrate up [options] ...

 Migrates the database to the most recent version available.

Options:

 -config=dbconfig.yml Configuration file to use.
 -env="development" Environment.
 -limit=0 Limit the number of migrations (0 = unlimited).
 -version Run migrate up to a specific version, eg: the version number of migration 1_initial.sql is 1.
 -dryrun Don't apply migrations, just print them.
```

The `new` command creates a new empty migration template using the following pattern `<current time>-<name>.sql`.

The `up` command applies all available migrations. By contrast, `down` will only apply one migration by default. This behavior can be changed for both by using the `-limit` parameter, and the `-version` parameter. Note `-version` has higher priority than `-limit` if you try to use them both.

The `redo` command will unapply the last migration and reapply it. This is useful during development, when you're writing migrations.

Use the `status` command to see the state of the applied migrations:

```bash
$ sql-migrate status
+---------------+-----------------------------------------+
| MIGRATION | APPLIED |
+---------------+-----------------------------------------+
| 1_initial.sql | 2014-09-13 08:19:06.788354925 +0000 UTC |
| 2_record.sql | no |
+---------------+-----------------------------------------+
```

#### Running Test Integrations

You can see how to run setups for different setups by executing the `.sh` files in [test-integration](test-integration/)

```bash
# Run mysql-env.sh example (you need to be in the project root directory)

./test-integration/mysql-env.sh
```

### MySQL Caveat

If you are using MySQL, you must append `?parseTime=true` to the `datasource` configuration. For example:

```yml
production:
 dialect: mysql
 datasource: root@/dbname?parseTime=true
 dir: migrations/mysql
 table: migrations
```

See [here](https://github.com/go-sql-driver/mysql#parsetime) for more information.

### Oracle (oci8)

Oracle Driver is [oci8](https://github.com/mattn/go-oci8), it is not pure Go code and relies on Oracle Office Client ([Instant Client](https://www.oracle.com/database/technologies/instant-client/downloads.html)), more detailed information is in the [oci8 repo](https://github.com/mattn/go-oci8).

#### Install with Oracle support

To install the library and command line program, use the following:

```bash
go get -tags oracle -v github.com/rubenv/sql-migrate/...
```

```yml
development:
 dialect: oci8
 datasource: user/password@localhost:1521/sid
 dir: migrations/oracle
 table: migrations
```

### Oracle (godror)

Oracle Driver is [godror](https://github.com/godror/godror), it is not pure Go code and relies on Oracle Office Client ([Instant Client](https://www.oracle.com/database/technologies/instant-client/downloads.html)), more detailed information is in the [godror repository](https://github.com/godror/godror).

#### Install with Oracle support

To install the library and command line program, use the following:

1. Install sql-migrate

```bash
go get -tags godror -v github.com/rubenv/sql-migrate/...
```

2. Download Oracle Office Client(e.g. macos, click [Instant Client](https://www.oracle.com/database/technologies/instant-client/downloads.html) if you are other system)

```bash
wget https://download.oracle.com/otn_software/mac/instantclient/193000/instantclient-basic-macos.x64-19.3.0.0.0dbru.zip
```

3. Configure environment variables `LD_LIBRARY_PATH`

```
export LD_LIBRARY_PATH=your_oracle_office_path/instantclient_19_3
```

```yml
development:
 dialect: godror
 datasource: user/password@localhost:1521/sid
 dir: migrations/oracle
 table: migrations
```

### As a library

Import sql-migrate into your application:

```go
import "github.com/rubenv/sql-migrate"
```

Set up a source of migrations, this can be from memory, from a set of files, from bindata (more on that later), or from any library that implements [`http.FileSystem`](https://godoc.org/net/http#FileSystem):

```go
// Hardcoded strings in memory:
migrations := &migrate.MemoryMigrationSource{
 Migrations: []*migrate.Migration{
 &migrate.Migration{
 Id: "123",
 Up: []string{"CREATE TABLE people (id int)"},
 Down: []string{"DROP TABLE people"},
 },
 },
}

// OR: Read migrations from a folder:
migrations := &migrate.FileMigrationSource{
 Dir: "db/migrations",
}

// OR: Use migrations from a packr box
// Note: Packr is no longer supported, your best option these days is [embed](https://pkg.go.dev/embed)
migrations := &migrate.PackrMigrationSource{
 Box: packr.New("migrations", "./migrations"),
}

// OR: Use pkger which implements `http.FileSystem`
migrationSource := &migrate.HttpFileSystemMigrationSource{
 FileSystem: pkger.Dir("/db/migrations"),
}

// OR: Use migrations from bindata:
migrations := &migrate.AssetMigrationSource{
 Asset: Asset,
 AssetDir: AssetDir,
 Dir: "migrations",
}

// OR: Read migrations from a `http.FileSystem`
migrationSource := &migrate.HttpFileSystemMigrationSource{
 FileSystem: httpFS,
}
```

Then use the `Exec` function to upgrade your database:

```go
db, err := sql.Open("sqlite3", filename)
if err != nil {
 // Handle errors!
}

n, err := migrate.Exec(db, "sqlite3", migrations, migrate.Up)
if err != nil {
 // Handle errors!
}
fmt.Printf("Applied %d migrations!\n", n)
```

Note that `n` can be greater than `0` even if there is an error: any migration that succeeded will remain applied even if a later one fails.

Check [the GoDoc reference](https://godoc.org/github.com/rubenv/sql-migrate) for the full documentation.

## Writing migrations

Migrations are defined in SQL files, which contain a set of SQL statements. Special comments are used to distinguish up and down migrations.

```sql
-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE people (id int);


-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE people;
```

You can put multiple statements in each block, as long as you end them with a semicolon (`;`).

You can alternatively set up a separator string that matches an entire line by setting `sqlparse.LineSeparator`. This
can be used to imitate, for example, MS SQL Query Analyzer functionality where commands can be separated by a line with
contents of `GO`. If `sqlparse.LineSeparator` is matched, it will not be included in the resulting migration scripts.

If you have complex statements which contain semicolons, use `StatementBegin` and `StatementEnd` to indicate boundaries:

```sql
-- +migrate Up
CREATE TABLE people (id int);

-- +migrate StatementBegin
CREATE OR REPLACE FUNCTION do_something()
returns void AS $$
DECLARE
 create_query text;
BEGIN
 -- Do something here
END;
$$
language plpgsql;
-- +migrate StatementEnd

-- +migrate Down
DROP FUNCTION do_something();
DROP TABLE people;
```

The order in which migrations are applied is defined through the filename: sql-migrate will sort migrations based on their name. It's recommended to use an increasing version number or a timestamp as the first part of the filename.

Normally each migration is run within a transaction in order to guarantee that it is fully atomic. However some SQL commands (for example creating an index concurrently in PostgreSQL) cannot be executed inside a transaction. In order to execute such a command in a migration, the migration can be run using the `notransaction` option:

```sql
-- +migrate Up notransaction
CREATE UNIQUE INDEX CONCURRENTLY people_unique_id_idx ON people (id);

-- +migrate Down
DROP INDEX people_unique_id_idx;
```

## Embedding migrations with [embed](https://pkg.go.dev/embed)

If you like your Go applications self-contained (that is: a single binary): use [embed](https://pkg.go.dev/embed) to embed the migration files.

Just write your migration files as usual, as a set of SQL files in a folder.

Import the embed package into your application and point it to your migrations:

```go
import "embed"

//go:embed migrations/*
var dbMigrations embed.FS
```

Use the `EmbedFileSystemMigrationSource` in your application to find the migrations:

```go
migrations := migrate.EmbedFileSystemMigrationSource{
	FileSystem: dbMigrations,
	Root: "migrations",
}
```

Other options such as [packr](https://github.com/gobuffalo/packr) or [go-bindata](https://github.com/shuLhan/go-bindata) are no longer recommended.

## Embedding migrations with libraries that implement `http.FileSystem`

You can also embed migrations with any library that implements `http.FileSystem`, like [`vfsgen`](https://github.com/shurcooL/vfsgen), [`parcello`](https://github.com/phogolabs/parcello), or [`go-resources`](https://github.com/omeid/go-resources).

```go
migrationSource := &migrate.HttpFileSystemMigrationSource{
 FileSystem: httpFS,
}
```

## Extending

Adding a new migration source means implementing `MigrationSource`.

```go
type MigrationSource interface {
 FindMigrations() ([]*Migration, error)
}
```

The resulting slice of migrations will be executed in the given order, so it should usually be sorted by the `Id` field.

## Usage with [sqlx](https://jmoiron.github.io/sqlx/)

This library is compatible with sqlx. When calling migrate just dereference the DB from your `*sqlx.DB`:

```
n, err := migrate.Exec(db.DB, "sqlite3", migrations, migrate.Up)
 // ^^^ <-- Here db is a *sqlx.DB, the db.DB field is the plain sql.DB
if err != nil {
 // Handle errors!
}
```

## Questions or Feedback?

You can use Github Issues for feedback or questions.

## License

This library is distributed under the [MIT](LICENSE) license.

sql-migrate

Database migration generator. Allows generate sql migration from model and existing sql by differ them

# SQLize

![github action](https://github.com/sunary/sqlize/actions/workflows/go.yml/badge.svg)

English | [中文](README_zh.md)

## Purpose

**SQLize** generates database migrations by comparing two schema sources: your **Go structs** (desired state) and your **existing migrations** (current state). Instead of writing migration SQL by hand, you define models in Go and SQLize produces the `ALTER TABLE`, `CREATE TABLE`, and related statements needed to bring your database up to date.

### What problem does it solve?

- **Manual migrations are error-prone** — Easy to forget columns, indexes, or foreign keys when writing `ALTER TABLE` by hand
- **Schema drift** — Go models and the database can get out of sync over time
- **Boilerplate** — Repetitive work creating up/down migrations for every schema change

### How it works

1. **Desired state** — Load schema from your Go structs (via `FromObjects`)
2. **Current state** — Load schema from your migration folder (via `FromMigrationFolder`)
3. **Diff** — SQLize compares them and computes the changes
4. **Output** — Get migration SQL (`StringUp` / `StringDown`) or write files directly (`WriteFilesWithVersion`)

```
Go structs (desired)  ──┐
                       ├──► Diff ──► Migration SQL (up/down)
Migration files (current) ─┘
```

## Features

- **Multi-database**: MySQL, PostgreSQL, SQLite, SQL Server
- **ORM-friendly**: Works with struct tags (`sql`, `gorm`), compatible with `golang-migrate/migrate`
- **Schema export**: Avro Schema (MySQL), ERD diagrams (MermaidJS)

## Installation

```bash
go get github.com/sunary/sqlize
```

## Quick Start

```golang
package main

import (
	"fmt"
	"log"
	"os"

	"github.com/sunary/sqlize"
)

func main() {
	migrationFolder := "migrations/"
	sqlLatest := sqlize.NewSqlize(
		sqlize.WithSqlTag("sql"),
		sqlize.WithMigrationFolder(migrationFolder),
		sqlize.WithCommentGenerate(),
	)

	ms := YourModels() // Return your Go models
	err := sqlLatest.FromObjects(ms...)
	if err != nil {
		log.Fatal("sqlize FromObjects", err)
	}
	sqlVersion := sqlLatest.HashValue()

	sqlMigrated := sqlize.NewSqlize(sqlize.WithMigrationFolder(migrationFolder))
	err = sqlMigrated.FromMigrationFolder()
	if err != nil {
		log.Fatal("sqlize FromMigrationFolder", err)
	}

	sqlLatest.Diff(*sqlMigrated)

	fmt.Println("sql version", sqlVersion)
	fmt.Println("\n### migration up")
	fmt.Println(sqlLatest.StringUp())
	fmt.Println("\n### migration down")
	fmt.Println(sqlLatest.StringDown())

	if len(os.Args) > 1 {
		err = sqlLatest.WriteFilesWithVersion(os.Args[1], sqlVersion, false)
		if err != nil {
			log.Fatal("sqlize WriteFilesWithVersion", err)
		}
	}
}
```

## Conventions

### Default Behaviors

- Database: `mysql` (use `sqlize.WithPostgresql()`, `sqlize.WithSqlite()`, etc.)
- SQL syntax: Uppercase (use `sqlize.WithSqlLowercase()` for lowercase)
- Table naming: Singular (use `sqlize.WithPluralTableName()` for plural)
- Comment generation: `sqlize.WithCommentGenerate()`

### SQL Tag Options

- Format: Supports both `snake_case` and `camelCase` (e.g., `sql:"primary_key"` equals `sql:"primaryKey"`)
- Custom column: `sql:"column:column_name"`
- Primary key: `sql:"primary_key"`
- Foreign key: `sql:"foreign_key:user_id;references:user_id"`
- Auto increment: `sql:"auto_increment"`
- Default value: `sql:"default:CURRENT_TIMESTAMP"`
- Override datatype: `sql:"type:VARCHAR(64)"`
- Ignore field: `sql:"-"`

### Indexing

- Basic index: `sql:"index"`
- Custom index name: `sql:"index:idx_col_name"`
- Unique index: `sql:"unique"`
- Custom unique index: `sql:"unique:idx_name"`
- Composite index: `sql:"index_columns:col1,col2"` (includes unique index and primary key)
- Index type: `sql:"index_type:btree"`

### Embedded Structs

- Use `sql:"embedded"` or `sql:"squash"`
- Cannot be a pointer
- Supports prefix: `sql:"embedded_prefix:base_"`
- Fields have lowest order, except for primary key (always first)

### Data Types

- MySQL data types are implicitly changed:

```sql
TINYINT => tinyint(4)
INT     => int(11)
BIGINT  => bigint(20)
```

- Pointer values must be declared in the struct or predefined data types:

```golang
// your struct
type Record struct {
	ID        int
	DeletedAt *time.Time
}

// =>
// the struct is declared with a value
now := time.Now()
Record{DeletedAt: &now}

// or predefined data type
type Record struct {
	ID        int
	DeletedAt *time.Time `sql:"type:DATETIME"`
}

// or using struct supported by "database/sql"
type Record struct {
	ID        int
	DeletedAt sql.NullTime
}
```


sqlize

A Go package to help write migrations with go-pg/pg

# go-pg-migrations

[![Version](https://img.shields.io/badge/version-v3.1.0-green.svg)](https://github.com/robinjoseph08/go-pg-migrations/releases)
[![GoDoc](https://godoc.org/github.com/robinjoseph08/go-pg-migrations?status.svg)](http://godoc.org/github.com/robinjoseph08/go-pg-migrations)
[![Build Status](https://github.com/robinjoseph08/go-pg-migrations/actions/workflows/build.yml/badge.svg?branch=master)](https://github.com/robinjoseph08/go-pg-migrations/actions)
[![Coverage Status](https://coveralls.io/repos/github/robinjoseph08/go-pg-migrations/badge.svg?branch=master)](https://coveralls.io/github/robinjoseph08/go-pg-migrations?branch=master)
[![Go Report Card](https://goreportcard.com/badge/github.com/robinjoseph08/go-pg-migrations)](https://goreportcard.com/report/github.com/robinjoseph08/go-pg-migrations)

A Go package to help write migrations with [`go-pg/pg`](https://github.com/go-pg/pg).

## Usage

### Installation

Because `go-pg` [now has Go modules
support](https://github.com/go-pg/pg#get-started), `go-pg-migrations` also has
modules support; it currently depends on v10 of `go-pg`. To install it, use the
following command in a project with a `go.mod`:

```sh
$ go get github.com/robinjoseph08/go-pg-migrations/v3
```

If you are not yet using Go modules, you can still use v1 of this package.

### Running

To see how this package is intended to be used, you can look at the [example
directory](/example). All you need to do is have a `main` package (e.g.
`example`); call `migrations.Run` with the directory you want the migration
files to be saved in (which will be the same directory of the main package, e.g.
`example`), an instance of `*pg.DB`, and `os.Args`; and log any potential errors
that could be returned.

You can also call `migrations.RunWithOptions` to configure the way that the
migrations run (e.g. customize the name of the migration tables).

Once this has been set up, then you can use the `create`, `migrate`, `status`,
`rollback`, `help` commands like so:

```
$ go run example/*.go create create_users_table
Creating example/20180812001528_create_users_table.go...

$ go run example/*.go migrate
Running batch 1 with 1 migration(s)...
Finished running "20180812001528_create_users_table"

$ go run example/*.go status
+---------+-----------------------------------+-------+
| Applied | Migration                         | Batch |
+---------+-----------------------------------+-------+
|    √    | 20180812001528_create_users_table |     1 |
+---------+-----------------------------------+-------+

$ go run example/*.go rollback
Rolling back batch 1 with 1 migration(s)...
Finished rolling back "20180812001528_create_users_table"

$ go run example/*.go help
Usage:
  go run example/*.go [command]

Commands:
  create   - create a new migration in example with the provided name
  migrate  - run any migrations that haven't been run yet
  rollback - roll back the previous run batch of migrations
  status   - show the status of each migration
  help     - print this help text

Examples:
  go run example/*.go create create_users_table
  go run example/*.go migrate
  go run example/*.go rollback
  go run example/*.go status
  go run example/*.go help
```

While this works when you have the Go toolchain installed, there might be a
scenario where you have to run migrations and you don't have the toolchain
available (e.g. in a `scratch` or `alpine` Docker image deployed to production).
In that case, you should compile another binary (in addition to your actual
application) and copy it into the final image. This will include all of your
migrations and allow you to run it by overriding the command when running the
Docker container.

This would look something like this:

```dockerfile
# Dockerfile
FROM golang:1.13.3 as build

WORKDIR /app

COPY go.mod go.mod
COPY go.sum go.sum
RUN go mod download

COPY . .

RUN CGO_ENABLED=0 GOOS=linux go build -installsuffix cgo -ldflags '-w -s' -o ./bin/serve ./cmd/serve
RUN CGO_ENABLED=0 GOOS=linux go build -installsuffix cgo -ldflags '-w -s' -o ./bin/migrations ./cmd/migrations

FROM alpine:3.8

RUN apk --no-cache add ca-certificates
COPY --from=build /app/bin /bin

CMD ["serve"]
```

```sh
$ docker build -t service:latest .
$ docker run --rm service:latest migrations migrate
```

## Why?

While go-pg has [its own `migrations`
package](https://github.com/go-pg/migrations), it leaves a bit to be desired.
Some additional features that this package supports:

- Complete migration diffing to determine which migrations still need to be run.
  Since `go-pg/migrations` checks the current version of migrations and runs any
  migrations after that, some migrations can be missed, especially when several
  people are working on the same project.
- Timestamp-based prefixes to prevent two people creating a migration with the
  same version on two separate branches. If the current version is 3, and more
  than one person branches off and creates a new migration, all of them will be
  version 4.
- The ability to run migrations in a transaction on a case-by-case basis. Most
  of the time, running migrations within a transaction is desirable, so that if
  it errs out within the "up" function, the whole migration is reverted. But
  since some long-running migrations might have a statement with a relatively
  exclusive lock, you might opt out of running that specific migration within a
  transaction.
- A migration locking mechanism. This is to avoid two people (or an automated
  deployment system) attempting to run migrations at the same time against the
  same database, which could lead to undesired behavior.
- An expected workflow of how this package should be used within a project.
  While `go-pg/migrations` has some recommendations and examples, this package
  takes a more opinionated approach which makes it so you don't have to think
  about it as much, and there's less code for you to write and maintain.
- Batch-level rollbacks. When there are multiple migration files run during the
  same migration invocation, they are all grouped together into a "batch".
  During rollbacks, each batch gets rolled back together. This tends to be more
  desireable since this usually means the application is reverting back to a
  previous release, so the database should be in the state expected for that
  release.

Many of these features and expected behaviors come from using [Knex.js
migrations](https://knexjs.org/#Migrations) in production for many years. This
project is heavily inspired by Knex to provide a robust and safe migration
experience.

`go-pg` is a great and performant project, and hopefully, this makes it a little
better.

## Development

To develop on this project, you'll need to have Postgres running because the
tests depend on it.

If you have it running on your machine because it was installed through your
package manager (like `brew` or `apt-get`), you just need to run the following
to get it set up correctly:

```sh
make setup
```

If you don't have it on your laptop, you can run the following to start it
within Docker:

```sh
make postgres
```

That should start the container and keep it running while you develop. Once
you're done, you can `^C` out of it and it will stop the container.

To run the tests, you should run:

```sh
make test
```

To run the linter, you should run:

```sh
make lint
```


go-pg-migrations

A Go package that encodes and decodes Avro data

> [!IMPORTANT]
> Internally, most of LinkedIn has moved over to use https://github.com/hamba/avro for Avro serialization/deserialization needs as we found it to be significantly more performant in large-scale scenarios. goavro is in maintenance mode.

# goavro

Goavro is a library that encodes and decodes Avro data.

## Description

* Encodes to and decodes from both binary and textual JSON Avro data.
* `Codec` is stateless and is safe to use by multiple goroutines.

With the exception of features not yet supported, goavro attempts to
be fully compliant with the most recent version of the
[Avro specification](http://avro.apache.org/docs/1.8.2/spec.html).

## Dependency Notice

All usage of `gopkg.in` has been removed in favor of Go modules.
Please update your import paths to `github.com/linkedin/goavro/v2`.  v1
users can still use old versions of goavro by adding a constraint to
your `go.mod` or `Gopkg.toml` file.

```
require (
    github.com/linkedin/goavro v1.0.5
)
```

```toml
[[constraint]]
name = "github.com/linkedin/goavro"
version = "=1.0.5"
```

## Major Improvements in v2 over v1

### Avro namespaces

The original version of this library was written prior to my really
understanding how Avro namespaces ought to work. After using Avro for
a long time now, and after a lot of research, I think I grok Avro
namespaces properly, and the library now correctly handles every test
case the Apache Avro distribution has for namespaces, including being
able to refer to a previously defined data type later on in the same
schema.

### Getting Data into and out of Records

The original version of this library required creating `goavro.Record`
instances, and use of getters and setters to access a record's
fields. When schemas were complex, this required a lot of work to
debug and get right. The original version also required users to break
schemas in chunks, and have a different schema for each record
type. This was cumbersome, annoying, and error prone.

The new version of this library eliminates the `goavro.Record` type,
and accepts a native Go map for all records to be encoded. Keys are
the field names, and values are the field values. Nothing could be
more easy. Conversely, decoding Avro data yields a native Go map for
the upstream client to pull data back out of.

Furthermore, there is never a reason to ever have to break your schema
down into record schemas. Merely feed the entire schema into the
`NewCodec` function once when you create the `Codec`, then use
it. This library knows how to parse the data provided to it and ensure
data values for records and their fields are properly encoded and
decoded.

### 3x--4x Performance Improvement

The original version of this library was truly written with Go's idea
of `io.Reader` and `io.Writer` composition in mind. Although
composition is a powerful tool, the original library had to pull bytes
off the `io.Reader`--often one byte at a time--check for read errors,
decode the bytes, and repeat. This version, by using a native Go byte
slice, both decoding and encoding complex Avro data here at LinkedIn
is between three and four times faster than before.

### Avro JSON Support

The original version of this library did not support JSON encoding or
decoding, because it wasn't deemed useful for our internal use at the
time. When writing the new version of the library I decided to tackle
this issue once and for all, because so many engineers needed this
functionality for their work.

### Better Handling of Record Field Default Values

The original version of this library did not well handle default
values for record fields. This version of the library uses a default
value of a record field when encoding from native Go data to Avro data
and the record field is not specified. Additionally, when decoding
from Avro JSON data to native Go data, and a field is not specified,
the default value will be used to populate the field.

## Contrast With Code Generation Tools

If you have the ability to rebuild and redeploy your software whenever
data schemas change, code generation tools might be the best solution
for your application.

There are numerous excellent tools for generating source code to
translate data between native and Avro binary or textual data. One
such tool is linked below. If a particular application is designed to
work with a rarely changing schema, programs that use code generated
functions can potentially be more performant than a program that uses
goavro to create a `Codec` dynamically at run time.

* [gogen-avro](https://github.com/actgardner/gogen-avro)

I recommend benchmarking the resultant programs using typical data
using both the code generated functions and using goavro to see which
performs better. Not all code generated functions will out perform
goavro for all data corpuses.

If you don't have the ability to rebuild and redeploy software updates
whenever a data schema change occurs, goavro could be a great fit for
your needs. With goavro, your program can be given a new schema while
running, compile it into a `Codec` on the fly, and immediately start
encoding or decoding data using that `Codec`. Because Avro encoding
specifies that encoded data always be accompanied by a schema this is
not usually a problem. If the schema change is backwards compatible,
and the portion of your program that handles the decoded data is still
able to reference the decoded fields, there is nothing that needs to
be done when the schema change is detected by your program when using
goavro `Codec` instances to encode or decode data.

## Resources

* [Avro CLI Examples](https://github.com/miguno/avro-cli-examples)
* [Avro](https://avro.apache.org/)
* [Google Snappy](https://google.github.io/snappy/)
* [JavaScript Object Notation, JSON](https://www.json.org/)
* [Kafka](https://kafka.apache.org)

## Usage

Documentation is available via
[![GoDoc](https://godoc.org/github.com/linkedin/goavro?status.svg)](https://godoc.org/github.com/linkedin/goavro).

```Go
package main

import (
    "fmt"

    "github.com/linkedin/goavro/v2"
)

func main() {
    codec, err := goavro.NewCodec(`
        {
          "type": "record",
          "name": "LongList",
          "fields" : [
            {"name": "next", "type": ["null", "LongList"], "default": null}
          ]
        }`)
    if err != nil {
        fmt.Println(err)
    }

    // NOTE: May omit fields when using default value
    textual := []byte(`{"next":{"LongList":{}}}`)

    // Convert textual Avro data (in Avro JSON format) to native Go form
    native, _, err := codec.NativeFromTextual(textual)
    if err != nil {
        fmt.Println(err)
    }

    // Convert native Go form to binary Avro data
    binary, err := codec.BinaryFromNative(nil, native)
    if err != nil {
        fmt.Println(err)
    }

    // Convert binary Avro data back to native Go form
    native, _, err = codec.NativeFromBinary(binary)
    if err != nil {
        fmt.Println(err)
    }

    // Convert native Go form to textual Avro data
    textual, err = codec.TextualFromNative(nil, native)
    if err != nil {
        fmt.Println(err)
    }

    // NOTE: Textual encoding will show all fields, even those with values that
    // match their default values
    fmt.Println(string(textual))
    // Output: {"next":{"LongList":{"next":null}}}
}
```

Also please see the example programs in the `examples` directory for
reference.

## OCF file reading and writing

This library supports reading and writing data in [Object Container File (OCF)](https://avro.apache.org/docs/current/spec.html#Object+Container+Files) format

```go
package main

import (
	"bytes"
	"fmt"
	"strings"

	"github.com/linkedin/goavro/v2"
)

func main() {
	avroSchema := `
	{
	  "type": "record",
	  "name": "test_schema",
	  "fields": [
		{
		  "name": "time",
		  "type": "long"
		},
		{
		  "name": "customer",
		  "type": "string"
		}
	  ]
	}`

	// Writing OCF data
	var ocfFileContents bytes.Buffer
	writer, err := goavro.NewOCFWriter(goavro.OCFConfig{
		W:      &ocfFileContents,
		Schema: avroSchema,
	})
	if err != nil {
		fmt.Println(err)
	}
	err = writer.Append([]map[string]interface{}{
		{
			"time":     1617104831727,
			"customer": "customer1",
		},
		{
			"time":     1717104831727,
			"customer": "customer2",
		},
	})
	fmt.Println("ocfFileContents", ocfFileContents.String())

	// Reading OCF data
	ocfReader, err := goavro.NewOCFReader(strings.NewReader(ocfFileContents.String()))
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println("Records in OCF File");
	for ocfReader.Scan() {
		record, err := ocfReader.Read()
		if err != nil {
			fmt.Println(err)
		}
		fmt.Println("record", record)
	}
}
```    
The above code in [go playground](https://play.golang.org/p/RuHQONqBXeg)

### ab2t

The `ab2t` program is similar to the reference standard
`avrocat` program and converts Avro OCF files to Avro JSON
encoding.

### arw

The Avro-ReWrite program, `arw`, can be used to rewrite an
Avro OCF file while optionally changing the block counts, the
compression algorithm. `arw` can also upgrade the schema provided the
existing datum values can be encoded with the newly provided schema.

### avroheader

The Avro Header program, `avroheader`, can be used to print various
header information from an OCF file.

### splice

The `splice` program can be used to splice together an OCF file from
an Avro schema file and a raw Avro binary data file.

### Translating Data

A `Codec` provides four methods for translating between a byte slice
of either binary or textual Avro data and native Go data.

The following methods convert data between native Go data and byte
slices of the binary Avro representation:

    BinaryFromNative
    NativeFromBinary

The following methods convert data between native Go data and byte
slices of the textual Avro representation:

    NativeFromTextual
    TextualFromNative

Each `Codec` also exposes the `Schema` method to return a simplified
version of the JSON schema string used to create the `Codec`.

#### Translating From Avro to Go Data

Goavro does not use Go's structure tags to translate data between
native Go types and Avro encoded data.

When translating from either binary or textual Avro to native Go data,
goavro returns primitive Go data values for corresponding Avro data
values. The table below shows how goavro translates Avro types to Go
types.

| Avro               | Go                       |
| ------------------ | ------------------------ |
| `null`             | `nil`                    |
| `boolean`          | `bool`                   |
| `bytes`            | `[]byte`                 |
| `float`            | `float32`                |
| `double`           | `float64`                |
| `long`             | `int64`                  |
| `int`              | `int32`                  |
| `string`           | `string`                 |
| `array`            | `[]interface{}`          |
| `enum`             | `string`                 |
| `fixed`            | `[]byte`                 |
| `map` and `record` | `map[string]interface{}` |
| `union`            | *see below*              |

Because of encoding rules for Avro unions, when an union's value is
`null`, a simple Go `nil` is returned. However when an union's value
is non-`nil`, a Go `map[string]interface{}` with a single key is
returned for the union. The map's single key is the Avro type name and
its value is the datum's value.

#### Translating From Go to Avro Data

Goavro does not use Go's structure tags to translate data between
native Go types and Avro encoded data.

When translating from native Go to either binary or textual Avro data,
goavro generally requires the same native Go data types as the decoder
would provide, with some exceptions for programmer convenience. Goavro
will accept any numerical data type provided there is no precision
lost when encoding the value. For instance, providing `float64(3.0)`
to an encoder expecting an Avro `int` would succeed, while sending
`float64(3.5)` to the same encoder would return an error.

When providing a slice of items for an encoder, the encoder will
accept either `[]interface{}`, or any slice of the required type. For
instance, when the Avro schema specifies:
`{"type":"array","items":"string"}`, the encoder will accept either
`[]interface{}`, or `[]string`. If given `[]int`, the encoder will
return an error when it attempts to encode the first non-string array
value using the string encoder.

When providing a value for an Avro union, the encoder will accept
`nil` for a `null` value. If the value is non-`nil`, it must be a
`map[string]interface{}` with a single key-value pair, where the key
is the Avro type name and the value is the datum's value. As a
convenience, the `Union` function wraps any datum value in a map as
specified above.

```Go
func ExampleUnion() {
    codec, err := goavro.NewCodec(`["null","string","int"]`)
    if err != nil {
        fmt.Println(err)
    }
    buf, err := codec.TextualFromNative(nil, goavro.Union("string", "some string"))
    if err != nil {
        fmt.Println(err)
    }
    fmt.Println(string(buf))
    // Output: {"string":"some string"}
}
```

## Limitations

Goavro is a fully featured encoder and decoder of binary and textual
JSON Avro data. It fully supports recursive data structures, unions,
and namespacing. It does have a few limitations that have yet to be
implemented.

### Aliases

The Avro specification allows an implementation to optionally map a
writer's schema to a reader's schema using aliases. Although goavro
can compile schemas with aliases, it does not yet implement this
feature.

### Kafka Streams

[Kafka](http://kafka.apache.org) is the reason goavro was
written. Similar to Avro Object Container Files being a layer of
abstraction above Avro Data Serialization format, Kafka's use of Avro
is a layer of abstraction that also sits above Avro Data Serialization
format, but has its own schema. Like Avro Object Container Files, this
has been implemented but removed until the API can be improved.

### Default Maximum Block Counts, and Block Sizes

When decoding arrays, maps, and OCF files, the Avro specification
states that the binary includes block counts and block sizes that
specify how many items are in the next block, and how many bytes are
in the next block. To prevent possible denial-of-service attacks on
clients that use this library caused by attempting to decode
maliciously crafted data, decoded block counts and sizes are compared
against public library variables MaxBlockCount and MaxBlockSize. When
the decoded values exceed these values, the decoder returns an error.

Because not every upstream client is the same, we've chosen some sane
defaults for these values, but left them as mutable variables, so that
clients are able to override if deemed necessary for their
purposes. Their initial default values are (`math.MaxInt32` or
~2.2GB).

### Schema Evolution

Please see [my reasons why schema evolution is broken for Avro
1.x](https://github.com/linkedin/goavro/blob/master/SCHEMA-EVOLUTION.md).

## License

### Goavro license

Copyright 2017 LinkedIn Corp. Licensed under the Apache License,
Version 2.0 (the "License"); you may not use this file except in
compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
implied.

### Google Snappy license

Copyright (c) 2011 The Snappy-Go Authors. All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are
met:

   * Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
   * Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following disclaimer
in the documentation and/or other materials provided with the
distribution.
   * Neither the name of Google Inc. nor the names of its
contributors may be used to endorse or promote products derived from
this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

## Third Party Dependencies

### Google Snappy

Goavro links with [Google Snappy](http://google.github.io/snappy/)
to provide Snappy compression and decompression support.


goavro

Database migration manager, works with native query language. Support for cassandra, mysql, postgres, sqlite3

# godfish

[![Go Reference](https://pkg.go.dev/badge/github.com/rafaelespinoza/godfish.svg)](https://pkg.go.dev/github.com/rafaelespinoza/godfish)
[![codecov](https://codecov.io/gh/rafaelespinoza/godfish/branch/main/graph/badge.svg?token=EoLelW4qiy)](https://codecov.io/gh/rafaelespinoza/godfish)
[![Go Report Card](https://goreportcard.com/badge/github.com/rafaelespinoza/godfish)](https://goreportcard.com/report/github.com/rafaelespinoza/godfish)

[![cassandra](https://github.com/rafaelespinoza/godfish/actions/workflows/build-cassandra.yml/badge.svg)](https://github.com/rafaelespinoza/godfish/actions/workflows/build-cassandra.yml)
[![mysql](https://github.com/rafaelespinoza/godfish/actions/workflows/build-mysql.yml/badge.svg)](https://github.com/rafaelespinoza/godfish/actions/workflows/build-mysql.yml)
[![postgres](https://github.com/rafaelespinoza/godfish/actions/workflows/build-postgres.yml/badge.svg)](https://github.com/rafaelespinoza/godfish/actions/workflows/build-postgres.yml)
[![sqlite3](https://github.com/rafaelespinoza/godfish/actions/workflows/build-sqlite3.yml/badge.svg)](https://github.com/rafaelespinoza/godfish/actions/workflows/build-sqlite3.yml)
[![sqlserver](https://github.com/rafaelespinoza/godfish/actions/workflows/build-sqlserver.yml/badge.svg)](https://github.com/rafaelespinoza/godfish/actions/workflows/sqlserver.yml)

`godfish` is a database migration manager, similar to the very good
[`dogfish`](https://github.com/dwb/dogfish), but written in golang.
It is a CLI and a library.

## goals

- use the native query language in the migration files, no other high-level DSLs
- interface with many DBs
- light on dependencies
- not terrible error messages

## releases

The Releases page of the GitHub repository has pre-built artifacts for supported platforms.
Each archive file contains an executable binary per driver. Each executable binary will only work
for the targeted DB. Pick the one(s) you need.

There is also an installation script at [scripts/install.sh](./scripts/install.sh). Check it out.

## build

An alternative to using a pre-built release to is to build your own.
NOTE: these require [just](https://just.systems).

Make a CLI binary for the DB you want to use. This tool comes with some driver
implementations. Build one like so:

```
just build-cassandra
just build-mysql
just build-postgres
just build-sqlite3
just build-sqlserver
```

From there you could move it to `$GOPATH/bin`, move it to your project or
whatever else you need to do.

## concepts

Like any db migration tool, this helps you and your team manage the shape of
your application's database throughout time. Migrations are written in the
native language of the database, and exist as files in a directory. All
migrations to consider should live in the same directory. A migration is one of
these files, which may have metadata components as part of the filename:

* "direction": Migrations that introduce new changes to the DB shape are
 considered to have a "forward" direction. A migration intended as the
 inverse of a corresponding forward migration is considered to have a
 "reverse" direction.
* "version": Describe where a migration exists relative to the other migrations
 with the same direction. By default, it's a timestamp in the layout
 `YYYYMMDDHHmmss`.
* "name": A label you give to describe the migration's contents.

The delimiter of each part is a `-`. Each migration filename has this format:
```
${direction}-${version}-${name}.sql
```

There is an implicit ordering to migrations, denoted by the "version", where
subsequent migrations may build upon the shape created by preceding migrations.
When a migration is successfully applied, a new row is inserted into the schema
migrations table. By default its name is `schema_migrations`. A migration
that has not yet been applied will be a file in the directory, but without a
corresponding entry in the DB table.

The shape of the schema migrations table is roughly:

| column | type | description |
|----------------|---------|------------------------------------------------------|
| `migration_id` | varchar | primary key timestamp, derived from filename version |
| `label` | varchar | describes the migration, also derived from filename |
| `executed_at` | integer | unix epoch of when migration was applied |

## usage

Not only is this tool a CLI, it's also a database migration library. Most of the
time, you'll probably want to use it as a CLI.

### command line usage

This section describes basic usage of a CLI binary. For details on getting a CLI
binary, see the [releases](#releases) section. Golang is not required here.

```
godfish help
godfish -h
godfish <command> -h
```

Configuration options are read from command line flags first. If those are not
set, then it checks the configuration file.

#### connecting to the db

Database connection parameters may be read from an environment variable. Set:
```
DB_DSN=
```
When using one of the pre-built binaries, the database may also be specified
with a command line flag, `-dsn`. If specified in both places, then the command
line value will have higher precedence than the environment variable.

#### configure file paths

Manually set path to db migration files.

```sh
godfish -files db/migrations <command>
```

Make your life easier by creating a configuration file by invoking `godfish
init`. This creates a file at `.godfish.json`, where you can configure things.

Change the path to the configuration file.

```sh
mv .godfish.json foo.json
godfish -conf foo.json
```

#### everything else

```sh
cat .godfish.json
# { "path_to_files": "db/migrations" }

godfish create-migration -name alpha
# outputs:
# db/migrations/forward-20200128070010-alpha.sql
# db/migrations/reverse-20200128070010-alpha.sql

godfish create-migration -name bravo -reversible=false
# outputs:
# db/migrations/forward-20200128070106-bravo.sql

#
# ... write the sql in those files ...
#

# apply migrations
godfish migrate
# apply migrations to up a specific version
godfish migrate -version 20060102150405

# show status
godfish info

# apply a reverse migration
godfish rollback

# rollback and re-apply the last migration
godfish remigrate

# show build metadata
godfish version
godfish version -json
```

### library usage

Though most of the time you'll probably want to use one of the pre-built
binaries, you could also use this as a golang library.

```
go get github.com/rafaelespinoza/godfish
```

#### embed migrations

An issue that may arise with deployments is that the migration files must be
deployed alongside the godfish binary. Migrations data and the behavior provided
by the `godfish` library can be combined into a single self-contained binary by
using the [`embed`](https://pkg.go.dev/embed) package.
See the [go doc](https://pkg.go.dev/github.com/rafaelespinoza/godfish?tab=doc)
page for an example.

### upgrading schema migrations

If you have data created with `v0.14.0` or lower and then later on use a newer
version, then you may run into an error message like:
```
schema migrations table is missing columns; run the upgrade command to fix this
```

A schema migrations table created with versions <= `v0.14.0` will lack the
`label` and `executed_at` columns. The `upgrade` command adds them.

## other minutiae

Here are some notable differences between `dogfish` and `godfish`:

Filenames:

- dogfish: `migrate-${date}-${name}.sql`, or `rollback-${date}-${name}.sql`
- godfish: `forward-${date}-${name}.sql`, or `reverse-${date}-${name}.sql`

Note, dogfish uses the words, "migrate" and "rollback" to describe the
migration's direction whereas godfish uses "forward" and "reverse". They are
the same in that they are two complementaries.

```sh
ls -1 /path/to/db/migrations

forward-20191112050547-init_foos.sql
forward-20221067051242-add_bars.sql
forward-20250805031405-update_more_stuff.sql
reverse-20191112050547-init_foos.sql
reverse-20221067051242-add_bars.sql
reverse-20250805031405-update_more_stuff.sql
```

## project organization

One of the goals of this project is to minimize the amount of dependencies.
Driver-specific code is placed in `drivers/` so that building a binary for one
database does not require building code for another database.

The `godfish` package defines library functions, interfaces needed to build a
driver implementation.

Test infrastructure mostly lives in the `.ci/` and `.github/` directories. Many
integration tests may be run in isolation on your local machine without GitHub
actions.

## tests

Docker (or equivalent) is used to create environments and run the tests against
a live database. Each database has a separate configuration. All of this lives
in `ci.Justfile` and the `.ci/` directory.

Using an OCI-compatible tool other than `docker` (ie: `podman`)?
```sh
just --set CONTAINER_TOOL podman -f ci.Justfile
```

Build environments and run tests
```sh
just -f ci.Justfile cassandra3-up
just -f ci.Justfile cassandra4-up

just -f ci.Justfile sqlserver-up

just -f ci.Justfile mariadb-up

just -f ci.Justfile postgres14-up
just -f ci.Justfile postgres15-up

just -f ci.Justfile sqlite3-up
```

Teardown
```sh
just -f ci.Justfile cassandra3-down
just -f ci.Justfile cassandra4-down

just -f ci.Justfile sqlserver-down

just -f ci.Justfile mariadb-down

just -f ci.Justfile postgres14-down
just -f ci.Justfile postgres15-down

just -f ci.Justfile sqlite3-down
```

godfish

Database migration tool. You can manage your database's evolution by creating incremental SQL or Go scripts

# goose

<img align="right" width="125" src="assets/goose_logo.png">

[![Goose
CI](https://github.com/pressly/goose/actions/workflows/ci.yaml/badge.svg)](https://github.com/pressly/goose/actions/workflows/ci.yaml)
[![Go
Reference](https://pkg.go.dev/badge/github.com/pressly/goose/v3.svg)](https://pkg.go.dev/github.com/pressly/goose/v3)
[![Go Report
Card](https://goreportcard.com/badge/github.com/pressly/goose/v3)](https://goreportcard.com/report/github.com/pressly/goose/v3)

Goose is a database migration tool. Both a CLI and a library.

Manage your **database schema** by creating incremental SQL changes or Go functions.

#### Features

- Works against multiple databases:
 - Postgres, MySQL, Spanner, SQLite, YDB, ClickHouse, MSSQL, Vertica, and
 more.
- Supports Go migrations written as plain functions.
- Supports [embedded](https://pkg.go.dev/embed/) migrations.
- Out-of-order migrations.
- Seeding data.
- Environment variable substitution in SQL migrations.
- ... and more.

# Install

```shell
go install github.com/pressly/goose/v3/cmd/goose@latest
```

This will install the `goose` binary to your `$GOPATH/bin` directory.

Binary too big? Build a lite version by excluding the drivers you don't need:

```shell
go build -tags='no_postgres no_mysql no_sqlite3 no_ydb' -o goose ./cmd/goose

# Available build tags:
# no_clickhouse no_libsql no_mssql no_mysql
# no_postgres no_sqlite3 no_vertica no_ydb
```

For macOS users `goose` is available as a [Homebrew
Formulae](https://formulae.brew.sh/formula/goose#default):

```shell
brew install goose
```

See [installation documentation](https://pressly.github.io/goose/installation/) for more details.

# Usage

<details>
<summary>Click to show <code>goose help</code> output</summary>

```
Usage: goose DRIVER DBSTRING [OPTIONS] COMMAND

or

Set environment key
GOOSE_DRIVER=DRIVER
GOOSE_DBSTRING=DBSTRING
GOOSE_MIGRATION_DIR=MIGRATION_DIR

Usage: goose [OPTIONS] COMMAND

Drivers:
 postgres
 mysql
 sqlite3
 spanner
 mssql
 redshift
 tidb
 clickhouse
 ydb
 starrocks
 turso

Examples:
 goose sqlite3 ./foo.db status
 goose sqlite3 ./foo.db create init sql
 goose sqlite3 ./foo.db create add_some_column sql
 goose sqlite3 ./foo.db create fetch_user_data go
 goose sqlite3 ./foo.db up

 goose postgres "user=postgres dbname=postgres sslmode=disable" status
 goose mysql "user:password@/dbname?parseTime=true" status
 goose spanner "projects/project/instances/instance/databases/database" status
 goose redshift "postgres://user:password@qwerty.us-east-1.redshift.amazonaws.com:5439/db" status
 goose tidb "user:password@/dbname?parseTime=true" status
 goose mssql "sqlserver://user:password@hostname:1433?database=master" status
 goose clickhouse "tcp://127.0.0.1:9000" status
 goose ydb "grpcs://localhost:2135/local?go_query_mode=scripting&go_fake_tx=scripting&go_query_bind=declare,numeric" status
 goose starrocks "user:password@/dbname?parseTime=true&interpolateParams=true" status

 GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db goose status
 GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db goose create init sql
 GOOSE_DRIVER=postgres GOOSE_DBSTRING="user=postgres dbname=postgres sslmode=disable" goose status
 GOOSE_DRIVER=mysql GOOSE_DBSTRING="user:password@/dbname" goose status
 GOOSE_DRIVER=redshift GOOSE_DBSTRING="postgres://user:password@qwerty.us-east-1.redshift.amazonaws.com:5439/db" goose status
 GOOSE_DRIVER=clickhouse GOOSE_DBSTRING="clickhouse://user:password@qwerty.clickhouse.cloud:9440/dbname?secure=true&skip_verify=false" goose status

Options:

 -allow-missing
 applies missing (out-of-order) migrations
 -certfile string
 file path to root CA's certificates in pem format (only support on mysql)
 -dir string
 directory with migration files (default ".", can be set via the GOOSE_MIGRATION_DIR env variable).
 -h print help
 -no-color
 disable color output (NO_COLOR env variable supported)
 -no-versioning
 apply migration commands with no versioning, in file order, from directory pointed to
 -s use sequential numbering for new migrations
 -ssl-cert string
 file path to SSL certificates in pem format (only support on mysql)
 -ssl-key string
 file path to SSL key in pem format (only support on mysql)
 -table string
 migrations table name (default "goose_db_version"). If you use a schema that is not `public`, you should set `schemaname.goose_db_version` when running commands.
 -timeout duration
 maximum allowed duration for queries to run; e.g., 1h13m
 -v enable verbose mode
 -version
 print version

Commands:
 up Migrate the DB to the most recent version available
 up-by-one Migrate the DB up by 1
 up-to VERSION Migrate the DB to a specific VERSION
 down Roll back the version by 1
 down-to VERSION Roll back to a specific VERSION
 redo Re-run the latest migration
 reset Roll back all migrations
 status Dump the migration status for the current DB
 version Print the current version of the database
 create NAME [sql|go] Creates new migration file with the current timestamp
 fix Apply sequential ordering to migrations
 validate Check migration files without running them
```

</details>

Commonly used commands:

[create](#create)&nbsp;•&nbsp; [up](#up)&nbsp;•&nbsp; [up-to](#up-to)&nbsp;•&nbsp; [down](#down)&nbsp;•&nbsp; [down-to](#down-to)&nbsp;•&nbsp; [status](#status)&nbsp;•&nbsp; [version](#version)

## create

Create a new SQL migration.

 $ goose create add_some_column sql
 $ Created new file: 20170506082420_add_some_column.sql

 $ goose -s create add_some_column sql
 $ Created new file: 00001_add_some_column.sql

Edit the newly created file to define the behavior of your migration.

You can also create a Go migration, if you then invoke it with [your own goose
binary](#go-migrations):

 $ goose create fetch_user_data go
 $ Created new file: 20170506082421_fetch_user_data.go

## up

Apply all available migrations.

 $ goose up
 $ OK 001_basics.sql
 $ OK 002_next.sql
 $ OK 003_and_again.go

## up-to

Migrate up to a specific version.

 $ goose up-to 20170506082420
 $ OK 20170506082420_create_table.sql

## up-by-one

Migrate up a single migration from the current version

 $ goose up-by-one
 $ OK 20170614145246_change_type.sql

## down

Roll back a single migration from the current version.

 $ goose down
 $ OK 003_and_again.go

## down-to

Roll back migrations to a specific version.

 $ goose down-to 20170506082527
 $ OK 20170506082527_alter_column.sql

Or, roll back all migrations (careful!):

 $ goose down-to 0

## status

Print the status of all migrations:

 $ goose status
 $ Applied At Migration
 $ =======================================
 $ Sun Jan 6 11:25:03 2013 -- 001_basics.sql
 $ Sun Jan 6 11:25:03 2013 -- 002_next.sql
 $ Pending -- 003_and_again.go

Note: for MySQL [parseTime flag](https://github.com/go-sql-driver/mysql#parsetime) must be enabled.

Note: for MySQL
[`multiStatements`](https://github.com/go-sql-driver/mysql?tab=readme-ov-file#multistatements) must
be enabled. This is required when writing multiple queries separated by ';' characters in a single
sql file.

## version

Print the current version of the database:

 $ goose version
 $ goose: version 002

# Environment Variables

If you prefer to use environment variables, instead of passing the driver and database string as
arguments, you can set the following environment variables:

**1. Via environment variables:**

```shell
export GOOSE_DRIVER=DRIVER
export GOOSE_DBSTRING=DBSTRING
export GOOSE_MIGRATION_DIR=MIGRATION_DIR
export GOOSE_TABLE=TABLENAME
```

**2. Via `.env` files with corresponding variables. `.env` file example**:

```env
GOOSE_DRIVER=postgres
GOOSE_DBSTRING=postgres://admin:admin@localhost:5432/admin_db
GOOSE_MIGRATION_DIR=./migrations
GOOSE_TABLE=custom.goose_migrations
```

Loading from `.env` files is enabled by default. To disable this feature, set the `-env=none` flag.
If you want to load from a specific file, set the `-env` flag to the file path.

For more details about environment variables, see the [official documentation on environment
variables](https://pressly.github.io/goose/documentation/environment-variables/).

# Migrations

goose supports migrations written in SQL or in Go.

## SQL Migrations

A sample SQL migration looks like:

```sql
-- +goose Up
CREATE TABLE post (
 id int NOT NULL,
 title text,
 body text,
 PRIMARY KEY(id)
);

-- +goose Down
DROP TABLE post;
```

Each migration file must have exactly one `-- +goose Up` annotation. The `-- +goose Down` annotation
is optional. If the file has both annotations, then the `-- +goose Up` annotation **must** come
first.

Notice the annotations in the comments. Any statements following `-- +goose Up` will be executed as
part of a forward migration, and any statements following `-- +goose Down` will be executed as part
of a rollback.

By default, all migrations are run within a transaction. Some statements like `CREATE DATABASE`,
however, cannot be run within a transaction. You may optionally add `-- +goose NO TRANSACTION` to
the top of your migration file in order to skip transactions within that specific migration file.
Both Up and Down migrations within this file will be run without transactions.

By default, SQL statements are delimited by semicolons - in fact, query statements must end with a
semicolon to be properly recognized by goose.

By default, all migrations are run on the public schema. If you want to use a different schema,
specify the schema name using the table option like `-table='schemaname.goose_db_version`.

More complex statements (PL/pgSQL) that have semicolons within them must be annotated with `--
+goose StatementBegin` and `-- +goose StatementEnd` to be properly recognized. For example:

```sql
-- +goose Up
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
 create_query text;
BEGIN
 FOR create_query IN SELECT
 'CREATE TABLE IF NOT EXISTS histories_'
 || TO_CHAR( d, 'YYYY_MM' )
 || ' ( CHECK( created_at >= timestamp '''
 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
 || ''' AND created_at < timestamp '''
 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
 || ''' ) ) inherits ( histories );'
 FROM generate_series( $1, $2, '1 month' ) AS d
 LOOP
 EXECUTE create_query;
 END LOOP; -- LOOP END
END; -- FUNCTION END
$$
language plpgsql;
-- +goose StatementEnd
```

Goose supports environment variable substitution in SQL migrations through annotations. To enable
this feature, use the `-- +goose ENVSUB ON` annotation before the queries where you want
substitution applied. It stays active until the `-- +goose ENVSUB OFF` annotation is encountered.
You can use these annotations multiple times within a file.

This feature is disabled by default for backward compatibility with existing scripts.

For `PL/pgSQL` functions or other statements where substitution is not desired, wrap the annotations
explicitly around the relevant parts. For example, to exclude escaping the `$$` characters:

```sql
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION test_func()
RETURNS void AS $$
-- +goose ENVSUB ON
BEGIN
	RAISE NOTICE '${SOME_ENV_VAR}';
END;
-- +goose ENVSUB OFF
$$ LANGUAGE plpgsql;
-- +goose StatementEnd
```

<details>
<summary>Supported expansions (click here to expand):</summary>

- `${VAR}` or $VAR - expands to the value of the environment variable `VAR`
- `${VAR:-default}` - expands to the value of the environment variable `VAR`, or `default` if `VAR`
 is unset or null
- `${VAR-default}` - expands to the value of the environment variable `VAR`, or `default` if `VAR`
 is unset
- `${VAR?err_msg}` - expands to the value of the environment variable `VAR`, or prints `err_msg` and
 error if `VAR` unset
- ~~`${VAR:?err_msg}` - expands to the value of the environment variable `VAR`, or prints `err_msg`
 and error if `VAR` unset or null.~~ **THIS IS NOT SUPPORTED**

See
[mfridman/interpolate](https://github.com/mfridman/interpolate?tab=readme-ov-file#supported-expansions)
for more details on supported expansions.

</details>

## Embedded sql migrations

Go 1.16 introduced new feature: [compile-time embedding](https://pkg.go.dev/embed/) files into
binary and corresponding [filesystem abstraction](https://pkg.go.dev/io/fs/).

This feature can be used only for applying existing migrations. Modifying operations such as `fix`
and `create` will continue to operate on OS filesystem even if using embedded files. This is
expected behaviour because `io/fs` interfaces allows read-only access.

Make sure to configure the correct SQL dialect, see [dialect.go](./dialect.go) for supported SQL
dialects.

Example usage, assuming that SQL migrations are placed in the `migrations` directory:

```go
package main

import (
 "database/sql"
 "embed"

 "github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var embedMigrations embed.FS

func main() {
 var db *sql.DB
 // setup database

 goose.SetBaseFS(embedMigrations)

 if err := goose.SetDialect("postgres"); err != nil {
 panic(err)
 }

 if err := goose.Up(db, "migrations"); err != nil {
 panic(err)
 }

 // run app
}
```

Note that we pass `"migrations"` as directory argument in `Up` because embedding saves directory
structure.

## Go Migrations

1. Create your own goose binary, see [example](./examples/go-migrations)
2. Import `github.com/pressly/goose`
3. Register your migration functions
4. Include your `migrations` package into Go build: in `main.go`, `import _ "github.com/me/myapp/migrations"`
5. Run goose command, ie. `goose.Up(db *sql.DB, dir string)`

A [sample Go migration 00002_users_add_email.go file](./examples/go-migrations/00002_rename_root.go)
looks like:

```go
package migrations

import (
	"database/sql"

	"github.com/pressly/goose/v3"
)

func init() {
	goose.AddMigration(Up, Down)
}

func Up(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='admin' WHERE username='root';")
	if err != nil {
		return err
	}
	return nil
}

func Down(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='root' WHERE username='admin';")
	if err != nil {
		return err
	}
	return nil
}
```

Note that Go migration files must begin with a numeric value, followed by an underscore, and must
not end with `*_test.go`.

# Hybrid Versioning

Please, read the [versioning
problem](https://github.com/pressly/goose/issues/63#issuecomment-428681694) first.

By default, if you attempt to apply missing (out-of-order) migrations `goose` will raise an error.
However, If you want to apply these missing migrations pass goose the `-allow-missing` flag, or if
using as a library supply the functional option `goose.WithAllowMissing()` to Up, UpTo or UpByOne.

However, we strongly recommend adopting a hybrid versioning approach, using both timestamps and
sequential numbers. Migrations created during the development process are timestamped and sequential
versions are ran on production. We believe this method will prevent the problem of conflicting
versions when writing software in a team environment.

To help you adopt this approach, `create` will use the current timestamp as the migration version.
When you're ready to deploy your migrations in a production environment, we also provide a helpful
`fix` command to convert your migrations into sequential order, while preserving the timestamp
ordering. We recommend running `fix` in the CI pipeline, and only when the migrations are ready for
production.

## Credit

The gopher mascot was designed by [Renée French](https://reneefrench.blogspot.com/) / [CC
3.0.](https://creativecommons.org/licenses/by/3.0/) For more info check out the [Go
Blog](https://go.dev/blog/gopher). Adapted by Ellen.

## License

Licensed under [MIT License](./LICENSE)

goose

Database schema migration helper for Gorm ORM

# Gormigrate

[![Latest Release](https://img.shields.io/github/release/go-gormigrate/gormigrate.svg)](https://github.com/go-gormigrate/gormigrate/releases)
[![Go Reference](https://pkg.go.dev/badge/github.com/go-gormigrate/gormigrate/v2.svg)](https://pkg.go.dev/github.com/go-gormigrate/gormigrate/v2)
[![Go Report Card](https://goreportcard.com/badge/github.com/go-gormigrate/gormigrate/v2)](https://goreportcard.com/report/github.com/go-gormigrate/gormigrate/v2)
[![CI | Lint](https://github.com/go-gormigrate/gormigrate/actions/workflows/lint.yml/badge.svg)](https://github.com/go-gormigrate/gormigrate/actions)
[![CI | Test](https://github.com/go-gormigrate/gormigrate/actions/workflows/integration-test.yml/badge.svg)](https://github.com/go-gormigrate/gormigrate/actions)

Gormigrate is a minimalistic migration helper for [Gorm](http://gorm.io).
Gorm already has useful [migrate functions](https://gorm.io/docs/migration.html), just misses
proper schema versioning and migration rollback support.

> IMPORTANT: If you need support to Gorm v1 (which uses
> `github.com/jinzhu/gorm` as its import path), please import Gormigrate by
> using the `gopkg.in/gormigrate.v1` import path.
>
> The current Gorm version (v2) is supported by using the
> `github.com/go-gormigrate/gormigrate/v2` import path as described in the
> documentation below.

## Supported databases

It supports any of the [databases Gorm supports](https://gorm.io/docs/connecting_to_the_database.html):

- MySQL
- MariaDB
- PostgreSQL
- SQLite
- Microsoft SQL Server
- TiDB
- Clickhouse

## Usage

```go
package main

import (
	"log"

	"github.com/go-gormigrate/gormigrate/v2"
	"github.com/google/uuid"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func main() {
	db, err := gorm.Open(sqlite.Open("./data.db"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatal(err)
	}

	m := gormigrate.New(db, gormigrate.DefaultOptions, []*gormigrate.Migration{{
		// create `users` table
		ID: "201608301400",
		Migrate: func(tx *gorm.DB) error {
			// it's a good pratice to copy the struct inside the function,
			// so side effects are prevented if the original struct changes during the time
			type user struct {
				ID   uuid.UUID `gorm:"type:uuid;primaryKey;uniqueIndex"`
				Name string
			}
			return tx.Migrator().CreateTable(&user{})
		},
		Rollback: func(tx *gorm.DB) error {
			return tx.Migrator().DropTable("users")
		},
	}, {
		// add `age` column to `users` table
		ID: "201608301415",
		Migrate: func(tx *gorm.DB) error {
			// when table already exists, define only columns that are about to change
			type user struct {
				Age int
			}
			return tx.Migrator().AddColumn(&user{}, "Age")
		},
		Rollback: func(tx *gorm.DB) error {
			type user struct {
				Age int
			}
			return db.Migrator().DropColumn(&user{}, "Age")
		},
	}, {
		// create `organizations` table where users belong to
		ID: "201608301430",
		Migrate: func(tx *gorm.DB) error {
			type organization struct {
				ID      uuid.UUID `gorm:"type:uuid;primaryKey;uniqueIndex"`
				Name    string
				Address string
			}
			if err := tx.Migrator().CreateTable(&organization{}); err != nil {
				return err
			}
			type user struct {
				OrganizationID uuid.UUID `gorm:"type:uuid"`
			}
			return tx.Migrator().AddColumn(&user{}, "OrganizationID")
		},
		Rollback: func(tx *gorm.DB) error {
			type user struct {
				OrganizationID uuid.UUID `gorm:"type:uuid"`
			}
			if err := db.Migrator().DropColumn(&user{}, "OrganizationID"); err != nil {
				return err
			}
			return tx.Migrator().DropTable("organizations")
		},
	}})

	if err := m.Migrate(); err != nil {
		log.Fatalf("Migration failed: %v", err)
	}
	log.Println("Migration did run successfully")
}
```

## Having a separate function for initializing the schema

If you have a lot of migrations, it can be a pain to run all them, as example,
when you are deploying a new instance of the app, in a clean database.
To prevent this, you can set a function that will run if no migration was run
before (in a new clean database). Remember to create everything here, all tables,
foreign keys and what more you need in your app.

```go
type Organization struct {
	gorm.Model
	Name    string
	Address string
}

type User struct {
	gorm.Model
	Name string
	Age int
	OrganizationID uint
}

m := gormigrate.New(db, gormigrate.DefaultOptions, []*gormigrate.Migration{
    // your migrations here
})

m.InitSchema(func(tx *gorm.DB) error {
	err := tx.AutoMigrate(
		&Organization{},
		&User{},
		// all other tables of you app
	)
	if err != nil {
		return err
	}

	if err := tx.Exec("ALTER TABLE users ADD CONSTRAINT fk_users_organizations FOREIGN KEY (organization_id) REFERENCES organizations (id)").Error; err != nil {
		return err
	}
	// all other constraints, indexes, etc...
	return nil
})
```

## Options

This is the options struct, in case you don't want the defaults:

```go
type Options struct {
	// TableName is the migration table.
	TableName string
	// IDColumnName is the name of column where the migration id will be stored.
	IDColumnName string
	// IDColumnSize is the length of the migration id column
	IDColumnSize int
	// UseTransaction makes Gormigrate execute migrations inside a single transaction.
	// Keep in mind that not all databases support DDL commands inside transactions.
	UseTransaction bool
	// ValidateUnknownMigrations will cause migrate to fail if there's unknown migration
	// IDs in the database
	ValidateUnknownMigrations bool
}
```

## Who is Gormigrate for?

Gormigrate was born to be a simple and minimalistic migration tool for small
projects that uses [Gorm](http://gorm.io). You may want to take a look at more advanced
solutions like [golang-migrate/migrate](https://github.com/golang-migrate/migrate)
if you plan to scale.

Be aware that Gormigrate has no builtin lock mechanism, so if you're running
it automatically and have a distributed setup (i.e. more than one executable
running at the same time), you might want to use a
[distributed lock/mutex mechanism](https://redis.io/topics/distlock) to
prevent race conditions while running migrations.

## Contributing

To run integration tests, some preparations are needed. Please ensure you
have [task](https://taskfile.dev/installation) and [docker](https://docs.docker.com/engine/install) installed.
Then:

1. Ensure target or all databases are available and ready to accept connections.
   You can start databases locally with `task docker:compose:up`
2. Copy `integration-test/.example.env` as `integration-test/.env` and
   adjust the database connection ports and credentials when needed.
3. Run integration test for single database or for all

```bash
# run test for MySQL
task test:mysql

# run test for MariaDB
task test:mariadb

# run test for PostgreSQL
task test:postgres

# run test for SQLite
task test:sqlite

# run test for Microsoft SQL Server
task test:sqlserver

# run test for all databases
task test:all
```

Alternatively, you can run everything in one step: `task docker:test`


gormigrate

Define your migrations separately in each library. Migrations for open source libraries. MySQL & PostgreSQL

# libschema - database schema migration for libraries

[![GoDoc](https://godoc.org/github.com/muir/libschema?status.png)](https://pkg.go.dev/github.com/muir/libschema)
![unit tests](https://github.com/muir/libschema/actions/workflows/go.yml/badge.svg)
![pg tests](https://github.com/muir/libschema/actions/workflows/pg.yml/badge.svg)
![mysql tests](https://github.com/muir/libschema/actions/workflows/mysql.yml/badge.svg)
![singlestore tests](https://github.com/muir/libschema/actions/workflows/s2.yml/badge.svg)
[![report card](https://goreportcard.com/badge/github.com/muir/libschema)](https://goreportcard.com/report/github.com/muir/libschema)
[![codecov](https://codecov.io/gh/muir/libschema/branch/main/graph/badge.svg)](https://codecov.io/gh/muir/libschema)

Install:

	go get github.com/muir/libschema

---

## Libraries

Libschema provides a way for Go libraries to manage their own database migrations.

Tying migrations to libraries supports two things: the first is
source code locality: the migrations can be next to the code that
uses the tables that the migrations address.

The second is support for migrations in third-party libraries. This
is a relatively unexplored and unsolved problem: how can an open
source (or proprietary) library specify and maintain a database
schema? Libschema hopes to start solving this problem. 

## Register and execute

Migrations are registered:

```go
schema := libschema.New(ctx, libschema.Options{})

sqlDB, err := sql.Open("postgres", "....")

database, err := lspostgres.New(logger, "main-db", schema, sqlDB) 
// or
database, singlestoreHelper, err := lssinglestore.New(logger, "main-db", schema, sqlDB)
// or
database, mysqlHelper, err := lssinglestore.New(logger, "main-db", schema, sqlDB)

database.Migrations("MyLibrary",
	lspostgres.Script("createUserTable", `
		CREATE TABLE users (
			name	text,
			id	bigint
		)`
	}),
	lspostgres.Script("addLastLogin", `
		ALTER TABLE users
			ADD COLUMN last_login timestamp
		`
	}),
)
```

Migrations are then run run later in the order that they were registered.

```go
err := schema.Migrate(context)
```

## Computed Migrations

Migrations may be SQL strings or migrations can be done in Go:

```go
database.Migrations("MyLibrary", 
	lspostgres.Computed("importUsers", func(_ context.Context, _ Migration, tx *sql.Tx) error {
		// code to import users here
	}),
)
```

## Asynchronous migrations 

The normal mode for migrations is to run the migrations synchronously
when `schema.Migrate()` is called. Asynchronous migrations are
started when `schema.Migrate()` is called but they're run in the
background in a go-routine. If there are later migrations, after
the asynchronous migration, they'll force the asynchronous migration
to be synchronous unless they're also asynchronous.

## Version blocking

Migrations can be tied to specific code versions so that they are
not run until conditions are met. This is done with `SkipRemainingIf`.
This be used to backfill data.

```go
database.Migrations("MyLibrary",
	...
	lspostgres.Script("addColumn", `
			ALTER TABLE users
				ADD COLUMN rating`,
	libschema.SkipThisAndFollowingIf(func() bool {
		return semver.Compare(version(), "3.11.3") < 1
	})),
	lspostgres.Script("fillInRatings", `
			UPDATE	users
			SET	rating = ...
			WHERE	rating IS NULL;

			ALTER TABLE users
				MODIFY COLUMN rating SET NOT NULL;`,
	libschema.Asychronous),
)
```

## Cross-library dependencies

Although it is best if the schema from one library is independent
of the schema for another, sometimes that's not possible, especially
if you want to enforce foriegn key constraints.

Use `After()` to specify a cross-library dependency.

```go
database.Migrations("users",
	...
	lspostgres.Script("addOrg", `
			ALTER TABLE users
				ADD COLUMN org TEXT,
				ADD ADD CONSTRAINT orgfk FOREIGN KEY (org)
					REFERENCES org (name) `, 
		libschema.After("orgs", "createOrgTable")),
)

database.Migrations("orgs",
	...
	lspostgres.Script("createOrgTable", `
		...
	`),
)
```

## Transactions

For databases that support transactions on metadata, all migrations
will be wrapped with a `BEGIN` and `COMMIT`. For databases that
do not support transactions on metadata, migrations will be split
into individual commands and run one at a time. If only some of
the commands succeed, the migration will be marked as partially
complete. If the migration is revised, then the later parts can
be re-tried as long as the earlier parts are not modified. This
does not apply to `Compute()`ed migrations.

### PostgreSQL non-transactional (idempotent) DDL

Some PostgreSQL statements (e.g. `CREATE INDEX CONCURRENTLY`, `DROP
INDEX CONCURRENTLY`, `REFRESH MATERIALIZED VIEW CONCURRENTLY`)
cannot run inside an explicit transaction block. Libschema auto-detects
common patterns in `Script(...)` and will execute those outside a
transaction; you can also opt in explicitly by using a generic type
that is *not* a `*sql.Tx` (e.g. `Generate[*sql.DB]`). Non-transactional
migrations must be idempotent: safe to retry after partial failure.
A fuller rationale, supported patterns, and enum/value guidance
live in `lspostgres/NON_TRANSACTIONAL.md`.

### RepeatUntilNoOp (brief)

`RepeatUntilNoOp()` re-runs a single DML statement until
`RowsAffected()==0`. Use only for pure data updates where the driver
reports accurate counts. Avoid DDL, guarded `CREATE ... IF NOT
EXISTS`, concurrent index / materialized view commands, or
multi-statement scripts. For anything more complex, prefer a
`Computed` migration and loop explicitly. See the Go doc comment
on `RepeatUntilNoOp` for detailed guidance.

## Command line

The `OverrideOptions` can be added as command line flags that 
change the behavior of calling `schema.Migrate()`
	
	--migrate-only			Call os.Exit() after completing migrations
	--migrate-database		Migrate only one logical database (must match NewDatabase)
	--migrate-dsn			Override *sql.DB 
	--no-migrate			Skip all migrations
	--error-if-migrate-needed	Return error if there are outstanding synchronous migrations
	--migrate-all-synchronously	Treat asychronous migrations as synchronous

## Ordering and pull requests

Migrations are run the order that they're defined. If the set of
migrations is updated so that there are new migrations that are
earlier in the table than migrations that have already run, this
is not considered an error and the new migrations will be run anyway.
This allows multiple branches of code with migrations to be merged
into a combined branch without hassle.

Migrations can have explicit dependencies and these dependencies
can cross between libraries so that one library's migrations can
depend on anothers.

## Code structure

Registering the migrations before executing them is easier if using
library singletons. Library singletons can be supported by using
[nserve](https://github.com/muir/nject/nserve) or
[fx](https://github.com/uber-go/fx). With nserve, migrations can
be given their own hook.

## Driver inclusion and database support

Like database/sql, libschema requires database-specific drivers:

- PostgreSQL support is in `"github.com/muir/libschema/lspostgres"`
- MySQL support in `"github.com/muir/libschema/lsmysql"`
- SingleStore support `"github.com/muir/libschema/lssinglestore"`

libschema currently supports: PostgreSQL, SingleStore, MySQL.
It is relatively easy to add additional databases.

## Forward only

Libschema does not support reverse migrations. If you need to fix
a migration, fix forward. The history behind this is that reverse
migrations are rarely the right answer for production systems and
the extra work for maintaining reverse migrations is does not have
enough of a payoff during development to be worth the effort.

One way to get the benefits of reverse migrations for development
is to put enough enough reverse migrations to reverse to the last
production schema at the end of the migration list but protected
by a gateway:

```go
libschema.SkipThisAndRemainingIf(func() bool {
	return os.Getenv("LIBMIGRATE_REVERSE_TO_PROD") != "true"
}),
```

This set of reverse migrations would always be small since it would
just be enough to take you back to the current production release.

## Patterns for applying migrations

When using a migration tool like libschema there are several
reasonable patterns one can follow to apply migrations to produciton
code.

### Down-Up deploys

The simplist pattern is to deploy migrations synchronously when
rolling out updates. If you take your service down to do deploys
then your migrations do not have to be backwards compatible. This
has the huge upside of allowing your schema to eveolve easily and
avoid the build up of technical debt. For example, if you have a
column whose name is sub-optimal, you can simply rename it and 
change the code that uses it at the same time.

To minimimize downtime so that the downtime doesn't matter in
practice, run expensive migrations asynchronously. Asychronous
migrations are harder to define because they should be broken up
into a whole bunch of smallish transactions. The `RepeatUntilNoOp()`
decorator may be useful.

### Green-Blue deploys

When you decide to run without downtime, one consequence is that
all migrations must be backwards compatible with the deployed
code.

DDL operations that are backwards compatible include:

- adding a column, table, or view
- removing a column, table, or view that is no longer accessed
- adding a default value to a column
- remvoing a constraint
- adding a constraint as long as there are no violations and won't be any new ones

From a coding point-of-view, the simplest way to manage developing
with these restrictions is to separate the migration into a separate
pull request from any other code changes. Tests must still pass in
the pull request that just has the migration. Local and CI testing
should apply the migration and validate that the the existing code
isn't broken by the change in database schema.

Only after the migration has been deployed can code that uses the 
migration be deployed. When using git, this can be done by having
layered side branches: 

```mermaid
graph LR;
 mob(migration-only branch)
 code(code branch)
 cleanup(cleanup migration branch)
 main --> mob --> code --> cleanup;
```

### Kubernetes and slow migrations

One issue with using libschema to deploy changes is that servers can take
a long time to come up if there are expensive migrations that need to be
deployed first. A solution for this is to use `OverrideOptions` to separate
the migrations into a separate step and run them in an 
[init container](https://kubernetes.io/docs/concepts/workloads/pods/init-containers/).

To do this use the `MigrateOnly` / `--migrate-only` option on your main program
when running it in the init container.

Then use the `ErrorIfMigrateNeeded` / `--error-if-migrate-needed` option on your main
program when it starts up for normal use.

## Code Stability

Libschema is still subject to changes. Anything that is not backwards compatible
will be clearly documented and will fail in a way that does not cause hidden problems.
For example, switching from using "flag" to using OverrideOptions will trigger
an obvious breakage if you try to use a flag that no longer works.

Anticipated changes:

- API tweaks
- Support for additional databases
- Support for additional logging APIs
- Support for tracing spans (per migration)

libschema

Database migrations. CLI and Golang library

[![GitHub Workflow Status (branch)](https://img.shields.io/github/actions/workflow/status/golang-migrate/migrate/ci.yaml?branch=master)](https://github.com/golang-migrate/migrate/actions/workflows/ci.yaml?query=branch%3Amaster)
[![GoDoc](https://pkg.go.dev/badge/github.com/golang-migrate/migrate)](https://pkg.go.dev/github.com/golang-migrate/migrate/v4)
[![Coverage Status](https://img.shields.io/coveralls/github/golang-migrate/migrate/master.svg)](https://coveralls.io/github/golang-migrate/migrate?branch=master)
[![packagecloud.io](https://img.shields.io/badge/deb-packagecloud.io-844fec.svg)](https://packagecloud.io/golang-migrate/migrate?filter=debs)
[![Docker Pulls](https://img.shields.io/docker/pulls/migrate/migrate.svg)](https://hub.docker.com/r/migrate/migrate/)
![Supported Go Versions](https://img.shields.io/badge/Go-1.24%2C%201.25-lightgrey.svg)
[![GitHub Release](https://img.shields.io/github/release/golang-migrate/migrate.svg)](https://github.com/golang-migrate/migrate/releases)
[![Go Report Card](https://goreportcard.com/badge/github.com/golang-migrate/migrate/v4)](https://goreportcard.com/report/github.com/golang-migrate/migrate/v4)

# migrate

__Database migrations written in Go. Use as [CLI](#cli-usage) or import as [library](#use-in-your-go-project).__

* Migrate reads migrations from [sources](#migration-sources)
   and applies them in correct order to a [database](#databases).
* Drivers are "dumb", migrate glues everything together and makes sure the logic is bulletproof.
   (Keeps the drivers lightweight, too.)
* Database drivers don't assume things or try to correct user input. When in doubt, fail.

Forked from [mattes/migrate](https://github.com/mattes/migrate)

## Databases

Database drivers run migrations. [Add a new database?](database/driver.go)

* [PostgreSQL](database/postgres)
* [PGX v4](database/pgx)
* [PGX v5](database/pgx/v5)
* [Redshift](database/redshift)
* [Ql](database/ql)
* [Cassandra / ScyllaDB](database/cassandra)
* [SQLite](database/sqlite)
* [SQLite3](database/sqlite3) ([todo #165](https://github.com/mattes/migrate/issues/165))
* [SQLCipher](database/sqlcipher)
* [MySQL / MariaDB](database/mysql)
* [Neo4j](database/neo4j)
* [MongoDB](database/mongodb)
* [CrateDB](database/crate) ([todo #170](https://github.com/mattes/migrate/issues/170))
* [Shell](database/shell) ([todo #171](https://github.com/mattes/migrate/issues/171))
* [Google Cloud Spanner](database/spanner)
* [CockroachDB](database/cockroachdb)
* [YugabyteDB](database/yugabytedb)
* [ClickHouse](database/clickhouse)
* [Firebird](database/firebird)
* [MS SQL Server](database/sqlserver)
* [rqlite](database/rqlite)

### Database URLs

Database connection strings are specified via URLs. The URL format is driver dependent but generally has the form: `dbdriver://username:password@host:port/dbname?param1=true&param2=false`

Any [reserved URL characters](https://en.wikipedia.org/wiki/Percent-encoding#Percent-encoding_reserved_characters) need to be escaped. Note, the `%` character also [needs to be escaped](https://en.wikipedia.org/wiki/Percent-encoding#Percent-encoding_the_percent_character)

Explicitly, the following characters need to be escaped:
`!`, `#`, `$`, `%`, `&`, `'`, `(`, `)`, `*`, `+`, `,`, `/`, `:`, `;`, `=`, `?`, `@`, `[`, `]`

It's easiest to always run the URL parts of your DB connection URL (e.g. username, password, etc) through an URL encoder. See the example Python snippets below:

```bash
$ python3 -c 'import urllib.parse; print(urllib.parse.quote(input("String to encode: "), ""))'
String to encode: FAKEpassword!#$%&'()*+,/:;=?@[]
FAKEpassword%21%23%24%25%26%27%28%29%2A%2B%2C%2F%3A%3B%3D%3F%40%5B%5D
$ python2 -c 'import urllib; print urllib.quote(raw_input("String to encode: "), "")'
String to encode: FAKEpassword!#$%&'()*+,/:;=?@[]
FAKEpassword%21%23%24%25%26%27%28%29%2A%2B%2C%2F%3A%3B%3D%3F%40%5B%5D
$
```

## Migration Sources

Source drivers read migrations from local or remote sources. [Add a new source?](source/driver.go)

* [Filesystem](source/file) - read from filesystem
* [io/fs](source/iofs) - read from a Go [io/fs](https://pkg.go.dev/io/fs#FS)
* [Go-Bindata](source/go_bindata) - read from embedded binary data ([jteeuwen/go-bindata](https://github.com/jteeuwen/go-bindata))
* [pkger](source/pkger) - read from embedded binary data ([markbates/pkger](https://github.com/markbates/pkger))
* [GitHub](source/github) - read from remote GitHub repositories
* [GitHub Enterprise](source/github_ee) - read from remote GitHub Enterprise repositories
* [Bitbucket](source/bitbucket) - read from remote Bitbucket repositories
* [Gitlab](source/gitlab) - read from remote Gitlab repositories
* [AWS S3](source/aws_s3) - read from Amazon Web Services S3
* [Google Cloud Storage](source/google_cloud_storage) - read from Google Cloud Platform Storage

## CLI usage

* Simple wrapper around this library.
* Handles ctrl+c (SIGINT) gracefully.
* No config search paths, no config files, no magic ENV var injections.

[CLI Documentation](cmd/migrate) (includes CLI install instructions)

### Basic usage

```bash
$ migrate -source file://path/to/migrations -database postgres://localhost:5432/database up 2
```

### Docker usage

```bash
$ docker run -v {{ migration dir }}:/migrations --network host migrate/migrate
    -path=/migrations/ -database postgres://localhost:5432/database up 2
```

## Use in your Go project

* API is stable and frozen for this release (v3 & v4).
* Uses [Go modules](https://golang.org/cmd/go/#hdr-Modules__module_versions__and_more) to manage dependencies.
* To help prevent database corruptions, it supports graceful stops via `GracefulStop chan bool`.
* Bring your own logger.
* Uses `io.Reader` streams internally for low memory overhead.
* Thread-safe and no goroutine leaks.

__[Go Documentation](https://pkg.go.dev/github.com/golang-migrate/migrate/v4)__

```go
import (
    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/github"
)

func main() {
    m, err := migrate.New(
        "github://mattes:personal-access-token@mattes/migrate_test",
        "postgres://localhost:5432/database?sslmode=enable")
    m.Steps(2)
}
```

Want to use an existing database client?

```go
import (
    "database/sql"
    _ "github.com/lib/pq"
    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    db, err := sql.Open("postgres", "postgres://localhost:5432/database?sslmode=enable")
    driver, err := postgres.WithInstance(db, &postgres.Config{})
    m, err := migrate.NewWithDatabaseInstance(
        "file:///migrations",
        "postgres", driver)
    m.Up() // or m.Steps(2) if you want to explicitly set the number of migrations to run
}
```

## Getting started

Go to [getting started](GETTING_STARTED.md)

## Tutorials

* [CockroachDB](database/cockroachdb/TUTORIAL.md)
* [PostgreSQL](database/postgres/TUTORIAL.md)

(more tutorials to come)

## Migration files

Each migration has an up and down migration. [Why?](FAQ.md#why-two-separate-files-up-and-down-for-a-migration)

```bash
1481574547_create_users_table.up.sql
1481574547_create_users_table.down.sql
```

[Best practices: How to write migrations.](MIGRATIONS.md)

## Coming from another db migration tool?

Check out [migradaptor](https://github.com/musinit/migradaptor/).
*Note: migradaptor is not affiliated or supported by this project*

## Versions

Version | Supported? | Import | Notes
--------|------------|--------|------
**master** | :white_check_mark: | `import "github.com/golang-migrate/migrate/v4"` | New features and bug fixes arrive here first |
**v4** | :white_check_mark: | `import "github.com/golang-migrate/migrate/v4"` | Used for stable releases |
**v3** | :x: | `import "github.com/golang-migrate/migrate"` (with package manager) or `import "gopkg.in/golang-migrate/migrate.v3"` (not recommended) | **DO NOT USE** - No longer supported |

## Development and Contributing

Yes, please! [`Makefile`](Makefile) is your friend,
read the [development guide](CONTRIBUTING.md).

Also have a look at the [FAQ](FAQ.md).

---

Looking for alternatives? [https://awesome-go.com/#database](https://awesome-go.com/#database).


migrate

Dead simple Go database migration library

<img src="https://github.com/lopezator/migrator/blob/master/logo.png" width="360">

 <a href="https://cloud.drone.io/lopezator/migrator"><img src="https://cloud.drone.io/api/badges/lopezator/migrator/status.svg?branch=master" alt="DroneCI" /></a>
 <a href="https://goreportcard.com/report/github.com/lopezator/migrator"><img src="https://goreportcard.com/badge/github.com/lopezator/migrator" alt="Go Report Card" /></a>
 <a href="https://codecov.io/gh/lopezator/migrator"><img src="https://codecov.io/gh/lopezator/migrator/branch/master/graph/badge.svg" alt="codecov" /></a>
 <a href="https://github.com/avelino/awesome-go#database"><img src="https://awesome.re/mentioned-badge.svg" alt="DroneCI" /></a>
 <a href="https://godoc.org/github.com/lopezator/migrator"><img src="https://godoc.org/github.com/lopezator/migrator/go?status.svg" alt="GoDoc" /></a>
 <a href="https://opensource.org/licenses/Apache-2.0"><img src="https://img.shields.io/badge/License-Apache%202.0-blue.svg" alt="License: Apache 2.0" /></a>


# migrator

Dead simple Go database migration library.

# Features

* Simple code
* Usage as a library, embeddable and extensible on your behalf
* Support of any database supported by `database/sql`
* Go code migrations, either transactional or transaction-less, using `*sql.Tx` (`migrator.Migration`) or `*sql.DB` (`migrator.MigrationNoTx`)
* No need to use `//go:embed` or others, since all migrations are just Go code

# Compatibility

Although any database supported by [database/sql](https://golang.org/pkg/database/sql/) and one of its recommended drivers [SQLDrivers](https://github.com/golang/go/wiki/SQLDrivers) should work OK, at the moment only `PostgreSQL` and `MySQL` are being explicitly tested.

If you find any issues with any of the databases included under the umbrella of `database/sql`, feel free to [contribute](#Contribute) by opening an issue or sending a pull request.

# Usage

The following example assumes:

- A working `postgres` DB conn on localhost, with a user named `postgres`, empty password, and db named `foo`

Customize this to your needs by changing the driver and/or connection settings.

### QuickStart:

```go
package main

import (
	"database/sql"
	"log"

	_ "github.com/jackc/pgx/v4/stdlib" // postgres driver
	"github.com/lopezator/migrator"
)

func main() {
 // Configure migrations
 m, err := migrator.New(
 migrator.Migrations(
 &migrator.Migration{
 Name: "Create table foo",
 Func: func(tx *sql.Tx) error {
 if _, err := tx.Exec("CREATE TABLE foo (id INT PRIMARY KEY)"); err != nil {
 return err
 }
 return nil
 },
 },
 ),
 )
 if err != nil {
 log.Fatal(err)
 }
 
 // Open database connection
 db, err := sql.Open("pgx", "postgres://postgres@localhost/foo?sslmode=disable")
 if err != nil {
 log.Fatal(err)
 }
 
 // Migrate up
 if err := m.Migrate(db); err != nil {
 log.Fatal(err)
 }
}
```

Notes on examples above:

- Migrator creates/manages a table named `migrations` to keep track of the applied versions. However, if you want to customize the table name `migrator.TableName("my_migrations")` can be passed to `migrator.New` function as an additional option. 

### Logging

By default, migrator prints applying/applied migration info to stdout. 
If that's enough for you, you can skip this section.

If you need some special formatting or want to use a 3rd party logging library, this could be done by using `WithLogger` option as follows:

```go
logger := migrator.WithLogger(migrator.LoggerFunc(func(msg string, args ...interface{}) {
	// Your code here 
})))
```

Then you will only need to pass the logger as an option to `migrator.New`. 

### Looking for more examples?

Just examine the [migrator_test.go](migrator_test.go) file.

### But I don't want to write complex migrations in strings! 😥

You still can use your favorite embedding tool to write your migrations inside `.sql` files and load them into migrator!

I provide a simple example using `//go:embed` on the `Using tx, one embedded query` test here: [migrator_test](https://github.com/lopezator/migrator/blob/master/migrator_test.go)

### Erm... Where are the ID's of the migrations to know their order? 🤔

In order to avoid problems with different identifiers, ID collisions, etc... the order of the migrations is just the order being passed to the migrator.

### Wait... no down migrations? 😱

Adding the functionality to reverse a migration introduces complexity to the API, the code, and the risk of losing the synchrony between the defined list of migrations and current state of the database. In addition to this, depending on the case, not all the migrations are easily reversible, or they cannot be reversed.

We also think that it's a good idea to follow an "append-only" philosophy when coming to database migrations, so correcting a defective migration comes in the form of adding a new migration instead of reversing it.

e.g. After a `CREATE TABLE foo` we'll simply add a new `DROP TABLE foo` instead of reverting the first migration, so both states are reflected both in the code and the database. 

### Caveats

- The name of the migrations must be SQL-safe for your engine of choice. Avoiding conflicting characters like `'` is recommended, otherwise, you will have to escape them by yourself e.g. `''` for PostgreSQL and `\'` for MySQL.

# Motivation

Why another migration library?

* Lightweight dummy implementation with just `database/sql` support. Migrator doesn't need any ORM or other heavy libraries as a dependency. It's just made from a [single file](migrator.go) in less than 200 lines of code!
* Easily embedabble into your application, no need to install/use a separate binary
* Supports Go migrations, either transactional or transaction-less
* Flexible usage

# These are not migrator objectives

* Add support to databases outside `database/sql`
* Complicate the code/logic to add functionality that could be accomplished easily on userland, like view current version, list of applied versions, etc.
* Add a bunch of dependencies just to provide a CLI/standalone functionality

# Comparison with other tools

* [rubenv/sql-migrate](https://github.com/rubenv/sql-migrate) doesn't support Go migrations. Sometimes you need Go code to accomplish complex tasks that can't be done using just SQL.

* [Boostport/migration](https://github.com/Boostport/migration) is a nice tool with support for many databases. Migrator code is inspired by its codebase. It supports both Go and SQL migrations. Unfortunately, when using Go migrations you have to write your own logic to retrieve and update version info in the database. Additionally I didn't find a nice way to encapsulate both migration and version logic queries inside the same transaction.

* [golang-migrate/migrate](https://github.com/golang-migrate/migrate) doesn't support Go migrations. Sometimes you need Go code to accomplish complex tasks that couldn't be done using just SQL. Additionally it feels a little heavy for the task.

* [pressly/goose](https://github.com/pressly/goose) supports both Go and SQL migrations. Unfortunately it doesn't support transaction-less Go migrations. Sometimes using transactions is either not possible with the combination of queries you need in a single migration, or others could be very slow and you simply don't need them for that specific case. It's also pretty big, with internals that are difficult to follow. It's crowded with a lot of functionality that could be done in userland pretty fast.

# Contribute

Pull requests are welcome, this is an early implementation and work is needed in all areas: docs, examples, tests, ci...

The easiest way to contribute is by installing [docker](https://docs.docker.com/install/) and [docker-compose](https://docs.docker.com/compose/install/), and ensure you comply with code standards and pass all the tests before submitting a PR by running:

```bash
$> docker-compose up -d --build
$> docker-compose exec migrator make prepare
$> docker-compose exec migrator make sanity-check
$> docker-compose exec migrator make test
$> docker-compose down
```

Make sure you also provide relevant information in your PR as detailed in the [pull request template](https://github.com/lopezator/migrator/blob/master/pull_request_template.md).

## Logo

The logo was taken from @ashleymcnamara's [gophers repo](https://github.com/ashleymcnamara/gophers). I've just applied slight modifications to it.

migrator

MySQL database migrator designed to run migrations to your features and manage database schema update with intuitive go code

# MySQL database migrator

<img align="right" width="159px" src="https://github.com/larapulse/migrator/blob/master/logo.png">

[![Build Status](https://travis-ci.org/larapulse/migrator.svg)](https://travis-ci.org/larapulse/migrator)
[![Software License](https://img.shields.io/badge/license-MIT-brightgreen.svg)](LICENSE.md)
[![codecov](https://codecov.io/gh/larapulse/migrator/branch/master/graph/badge.svg)](https://codecov.io/gh/larapulse/migrator)
[![Go Report Card](https://goreportcard.com/badge/github.com/larapulse/migrator)](https://goreportcard.com/report/github.com/larapulse/migrator)
[![GoDoc](https://godoc.org/github.com/larapulse/migrator?status.svg)](https://pkg.go.dev/github.com/larapulse/migrator?tab=doc)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go)
[![Release](https://img.shields.io/github/release/larapulse/migrator.svg)](https://github.com/larapulse/migrator/releases)
[![TODOs](https://badgen.net/https/api.tickgit.com/badgen/github.com/larapulse/migrator)](https://www.tickgit.com/browse?repo=github.com/larapulse/migrator)

MySQL database migrator designed to run migrations to your features and manage database schema update with intuitive go code. It is compatible with the latest MySQL v8.

## Installation

To install `migrator` package, you need to install Go and set your Go workspace first.

1. The first need [Go](https://golang.org/) installed (**version 1.13+ is required**), then you can use the below Go command to install `migrator`.

```sh
$ go get -u github.com/larapulse/migrator
```

2. Import it in your code:

```go
import "github.com/larapulse/migrator"
```

## Quick start

Initialize migrator with migration entries:

```go
var migrations = []migrator.Migration{
	{
		Name: "19700101_0001_create_posts_table",
		Up: func() migrator.Schema {
			var s migrator.Schema
			posts := migrator.Table{Name: "posts"}

			posts.UniqueID("id")
			posts.Varchar("title", 64)
			posts.Text("content", false)
			posts.Timestamps()

			s.CreateTable(posts)

			return s
		},
		Down: func() migrator.Schema {
			var s migrator.Schema

			s.DropTableIfExists("posts")

			return s
		},
	},
	{
		Name: "19700101_0002_create_comments_table",
		Up: func() migrator.Schema {
			var s migrator.Schema
			comments := migrator.Table{Name: "comments"}

			comments.UniqueID("id")
			comments.UUID("post_id", "", false)
			comments.Varchar("name", 64)
			comments.Column("email", migrator.String{Default: "<nil>"})
			comments.Text("content", false)
			comments.Timestamps()

			comments.Foreign("post_id", "id", "posts", "RESTRICT", "RESTRICT")

			s.CreateTable(comments)

			return s
		},
		Down: func() migrator.Schema {
			var s migrator.Schema

			s.DropTableIfExists("comments")

			return s
		},
	},
	{
		Name: "19700101_0003_rename_foreign_key",
		Up: func() migrator.Schema {
			var s migrator.Schema

			keyName := migrator.BuildForeignNameOnTable("comments", "post_id")
			newKeyName := migrator.BuildForeignNameOnTable("comments", "article_id")

			s.AlterTable("comments", migrator.TableCommands{
				migrator.DropForeignCommand(keyName),
				migrator.DropIndexCommand(keyName),
				migrator.RenameColumnCommand{"post_id", "article_id"},
				migrator.AddIndexCommand{newKeyName, []string{"article_id"}},
				migrator.AddForeignCommand{migrator.Foreign{
					Key: newKeyName,
					Column: "article_id",
					Reference: "id",
					On: "posts",
				}},
			})

			return s
		},
		Down: func() migrator.Schema {
			var s migrator.Schema

			keyName := migrator.BuildForeignNameOnTable("comments", "article_id")
			newKeyName := migrator.BuildForeignNameOnTable("comments", "post_id")

			s.AlterTable("comments", migrator.TableCommands{
				migrator.DropForeignCommand(keyName),
				migrator.DropIndexCommand(keyName),
				migrator.RenameColumnCommand{"article_id", "post_id"},
				migrator.AddIndexCommand{newKeyName, []string{"post_id"}},
				migrator.AddForeignCommand{migrator.Foreign{
					Key: newKeyName,
					Column: "post_id",
					Reference: "id",
					On: "posts",
				}},
			})

			return s
	},
}

m := migrator.Migrator{Pool: migrations}
migrated, err = m.Migrate(db)

if err != nil {
	log.Errorf("Could not migrate: %v", err)
	os.Exit(1)
}

if len(migrated) == 0 {
	log.Print("Nothing were migrated.")
}

for _, m := range migrated {
	log.Printf("Migration: %s was migrated ✅", m)
}

log.Print("Migration did run successfully")
```

After the first migration run, `migrations` table will be created:

```
+----+-------------------------------------+-------+----------------------------+
| id | name | batch | applied_at |
+----+-------------------------------------+-------+----------------------------+
| 1 | 19700101_0001_create_posts_table | 1 | 2020-06-27 00:00:00.000000 |
| 2 | 19700101_0002_create_comments_table | 1 | 2020-06-27 00:00:00.000000 |
| 3 | 19700101_0003_rename_foreign_key | 1 | 2020-06-27 00:00:00.000000 |
+----+-------------------------------------+-------+----------------------------+
```

If you want to use another name for migration table, change it `Migrator` before running migrations:

```go
m := migrator.Migrator{TableName: "_my_app_migrations"}
```

### Transactional migration

In case you have multiple commands within one migration and you want to be sure it is migrated properly, you might enable transactional execution per migration:

```go
var migration = migrator.Migration{
	Name: "19700101_0001_create_posts_and_users_tables",
	Up: func() migrator.Schema {
		var s migrator.Schema
		posts := migrator.Table{Name: "posts"}
		posts.UniqueID("id")
		posts.Timestamps()

		users := migrator.Table{Name: "users"}
		users.UniqueID("id")
		users.Timestamps()

		s.CreateTable(posts)
		s.CreateTable(users)

		return s
	},
	Down: func() migrator.Schema {
		var s migrator.Schema

		s.DropTableIfExists("users")
		s.DropTableIfExists("posts")

		return s
	},
	Transaction: true,
}
```

### Rollback and revert

In case you need to revert your deploy and DB, you can revert last migrated batch:

```go
m := migrator.Migrator{Pool: migrations}
reverted, err := m.Rollback(db)

if err != nil {
	log.Errorf("Could not roll back migrations: %v", err)
	os.Exit(1)
}

if len(reverted) == 0 {
	log.Print("Nothing were rolled back.")
}

for _, m := range reverted {
	log.Printf("Migration: %s was rolled back ✅", m)
}
```

To revert all migrated items back, you have to call `Revert()` on your `migrator`:

```go
m := migrator.Migrator{Pool: migrations}
reverted, err := m.Revert(db)
```

## Customize queries

You may add any column definition to the database on your own, just be sure you implement `columnType` interface:

```go
type customType string

func (ct customType) buildRow() string {
	return string(ct)
}

posts := migrator.Table{Name: "posts"}
posts.UniqueID("id")
posts.Column("data", customType("json not null"))
posts.Timestamps()
```

The same logic is for adding custom commands to the Schema to be migrated or reverted, just be sure you implement `command` interface:

```go
type customCommand string

func (cc customCommand) toSQL() string {
	return string(cc)
}

var s migrator.Schema

c := customCommand("DROP PROCEDURE abc")
s.CustomCommand(c)
```

Library to embed schema migrations for database/sql-compatible databases inside your Go binaries

# Schema - Database Migrations for Go

[![Go Reference](https://pkg.go.dev/badge/github.com/adlio/schema.svg)](https://pkg.go.dev/github.com/adlio/schema)
[![CI](https://github.com/adlio/schema/actions/workflows/ci.yml/badge.svg)](https://github.com/adlio/schema/actions/workflows/ci.yml)
[![codecov](https://codecov.io/gh/adlio/schema/graph/badge.svg)](https://codecov.io/gh/adlio/schema)
[![Go Report Card](https://goreportcard.com/badge/github.com/adlio/schema)](https://goreportcard.com/report/github.com/adlio/schema)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)

An embeddable library for applying changes to your Go application's
`database/sql` schema.

## Features

- Cloud-friendly design tolerates embedded use in clusters
- Supports migrations in embed.FS (requires go:embed in Go 1.16+)
- [Depends only on Go standard library](https://pkg.go.dev/github.com/adlio/schema?tab=imports) (Note that all go.mod dependencies are used only in tests)
- Unidirectional migrations (no "down" migration complexity)

# Usage Instructions

Create a `schema.Migrator` in your bootstrap/config/database connection code,
then call its `Apply()` method with your database connection and a slice of
`*schema.Migration` structs.

The `.Apply()` function figures out which of the supplied Migrations have not
yet been executed in the database (based on the ID), and executes the `Script`
for each in **alphabetical order by IDe**.

The `[]*schema.Migration` can be created manually, but the package
has some utility functions to make it easier to parse .sql files into structs
with the filename as the `ID` and the file contents as the `Script`.

## Using go:embed (requires Go 1.16+)

Go 1.16 added features to embed a directory of files into the binary as an
embedded filesystem (`embed.FS`).

Assuming you have a directory of SQL files called `my-migrations/` next to your
main.go file, you'll run something like this:

```go
//go:embed my-migrations
var MyMigrations embed.FS

func main() {
   db, err := sql.Open(...) // Or however you get a *sql.DB

   migrations, err := schema.FSMigrations(MyMigrations, "my-migrations/*.sql")
   migrator := schema.NewMigrator(schema.WithDialect(schema.MySQL))
   err = migrator.Apply(db, migrations)
}
```

The `WithDialect()` option accepts: `schema.MySQL`, `schema.Postgres`,
`schema.SQLite` or `schema.MSSQL`. These dialects all use only `database/sql`
calls, so you may have success with other databases which are SQL-compatible
with the above dialects.

You can also provide your own custom `Dialect`. See `dialect.go` for the
definition of the `Dialect` interface, and the optional `Locker` interface. Note
that `Locker` is critical for clustered operation to ensure that only one of
many processes is attempting to run migrations simultaneously.

## Using Inline Migration Structs

If you're running in an earlier version of Go, Migration{} structs will need to
be created manually:

```go
db, err := sql.Open(...)

migrator := schema.NewMigrator() // Postgres is the default Dialect
migrator.Apply(db, []*schema.Migration{
   &schema.Migration{
      ID: "2019-09-24 Create Albums",
      Script: `
      CREATE TABLE albums (
         id SERIAL PRIMARY KEY,
         title CHARACTER VARYING (255) NOT NULL
      )
      `
   },
})
```

## Constructor Options

The `NewMigrator()` function accepts option arguments to customize the dialect
and the name of the migration tracking table. By default, the tracking table
will be named `schema_migrations`. To change it
to `my_migrations` instead:

```go
migrator := schema.NewMigrator(schema.WithTableName("my_migrations"))
```

It is theoretically possible to create multiple Migrators and to use mutliple
migration tracking tables within the same application and database.

It is also OK for multiple processes to run `Apply` on identically configured
migrators simultaneously. The `Migrator` only creates the tracking table if it
does not exist, and then locks it to modifications while building and running
the migration plan. This means that the first-arriving process will **win** and
will perform its migrations on the database.

## Supported Databases

This package was extracted from a PostgreSQL project. Other databases have solid
automated test coverage, but should be considered somewhat experimental in
production use cases. [Contributions](#contributions) are welcome for
additional databases or feature enhancements / bug fixes.

- [x] PostgreSQL (database/sql driver only, see [adlio/pgxschema](https://github.com/adlio/pgxschema) if you use `jack/pgx`)
- [x] SQLite (thanks [kalafut](https://github.com/kalafut)!)
- [x] MySQL / MariaDB
- [x] SQL Server
- [ ] CockroachDB, Redshift, Snowflake, etc (open a Pull Request)

## Package Opinions

There are many other schema migration tools. This one exists because of a
particular set of opinions:

1. Database credentials are runtime configuration details, but database
   schema is a **build-time applicaton dependency**, which means it should be
   "compiled in" to the build, and should not rely on external tools.
2. Using an external command-line tool for schema migrations needlessly
   complicates testing and deployment.
3. SQL is the best language to use to specify changes to SQL schemas.
4. "Down" migrations add needless complication, aren't often used, and are
   tedious to properly test when they are used. In the unlikely event you need
   to migrate backwards, it's possible to write the "rollback" migration as
   a separate "up" migration.
5. Deep dependency chains should be avoided, especially in a compiled
   binary. We don't want to import an ORM into our binaries just to get SQL
   the features of this package. The `schema` package imports only
   [standard library packages](https://godoc.org/github.com/adlio/schema?imports)
   (**NOTE** \*We do import `ory/dockertest` in our tests).
6. Sequentially-numbered integer migration IDs will create too many unnecessary
   schema collisions on a distributed, asynchronously-communicating team
   (this is not yet strictly enforced, but may be later).

## Rules of Applying Migrations

1.  **Never, ever change** the `ID` (filename) or `Script` (file contents)
    of a Migration which has already been executed on your database. If you've
    made a mistake, you'll need to correct it in a subsequent migration.
2.  Use a consistent, but descriptive format for migration `ID`s/filenames.
    Consider prefixing them with today's timestamp. Examples:

         ID: "2019-01-01T13:45:00 Creates Users"
         ID: "2001-12-18 001 Changes the Default Value of User Affiliate ID"

    Do not use simple sequentialnumbers like `ID: "1"`.

## Migration Ordering

Migrations **are not** executed in the order they are specified in the slice.
They will be re-sorted alphabetically by their IDs before executing them.

## Contributions

... are welcome. Please include tests with your contribution. We've integrated
[dockertest](https://github.com/ory/dockertest) to automate the process of
creating clean test databases.

Before contributing, please read the [package opinions](#package-opinions)
section. If your contribution is in disagreement with those opinions, then
there's a good chance a different schema migration tool is more appropriate.

## Roadmap

- [x] Enhancements and documentation to facilitate asset embedding via go:embed
- [ ] Add a `Validate()` method to allow checking migration names for
      consistency and to detect problematic changes in the migrations list.
- [x] SQL Server support
- [x] SQL Server support for the Locker interface to protect against simultaneous
      migrations from clusters of servers.

## Version History

### 1.4.0 - Feb 21, 2026

- **Breaking:** Go 1.24+ now required (was 1.22)
- **Breaking:** MSSQL driver changed from `denisenkom/go-mssqldb` to `microsoft/go-mssqldb`
- Update all dependencies to latest versions
- Fix data race in MSSQL Unlock function
- Migrate CI from CircleCI to GitHub Actions
- Add automated release workflow

### 1.3.9 - Jul 21, 2025

- Update Go version requirement to 1.21 to resolve CircleCI build issues with slices package dependency
- Update CircleCI configuration to use Go 1.21

### 1.3.8 - Jul 19, 2025

- Update golang.org/x/crypto to v0.40.0 to address security vulnerabilities
- Update golang.org/x/net to v0.42.0 to address security vulnerabilities

### 1.3.7 - Jul 19, 2025

- Add SQL Server support for the Locker interface using sp_getapplock/sp_releaseapplock
- Fix SQL Server transaction handling for concurrent migrations

### 1.3.4 - Apr 9, 2023

- Update downstream dependencies to address vulnerabilities in test dependencies.

### 1.3.3 - Jun 19, 2022

- Update downstream dependencies of ory/dockertest due to security issues.

### 1.3.0 - Mar 25, 2022

- Basic SQL Server support (no locking, not recommended for use in clusters)
- Improved support for running tests on ARM64 machines (M1 Macs)

### 1.2.3 - Dec 10, 2021

- BUGFIX: Restore the ability to chain NewMigrator().Apply

### 1.2.2 - Dec 9, 2021

- Add support for migrations in an embed.FS (`FSMigrations(filesystem fs.FS, glob string)`)
- Add MySQL/MariaDB support (experimental)
- Add SQLite support (experimental)
- Update go.mod to `go 1.17`.

### 1.1.14 - Nov 18, 2021

Security patches in upstream dependencies.

### 1.1.13 - May 22, 2020

Bugfix for error with advisory lock being held open. Improved test coverage for
simultaneous execution.

### 1.1.11 - May 19, 2020

Use a database-held lock for all migrations not just the initial table creation.

### 1.1.9 - May 17, 2020

Add the ability to attach a logger.

### 1.1.8 - Nov 24, 2019

Switch to `filepath` package for improved cross-platform filesystem support.

### 1.1.7 - Oct 1, 2019

Began using pg_advisory_lock() to prevent race conditions when multiple
processes/machines try to simultaneously create the migrations table.

### 1.1.1 - Sep 28, 2019

First published version.


schema

Django style fixtures for Golang's excellent built-in database/sql library

## go-fixtures

Django style fixtures for Golang's excellent built-in `database/sql` library. Currently only `YAML` fixtures are supported.

[![Travis Status for RichardKnop/go-fixtures](https://travis-ci.org/RichardKnop/go-fixtures.svg?branch=master&label=linux+build)](https://travis-ci.org/RichardKnop/go-fixtures)
[![godoc for RichardKnop/go-fixtures](https://godoc.org/github.com/nathany/looper?status.svg)](http://godoc.org/github.com/RichardKnop/go-fixtures)
[![codecov for RichardKnop/go-fixtures](https://codecov.io/gh/RichardKnop/go-fixtures/branch/master/graph/badge.svg)](https://codecov.io/gh/RichardKnop/go-fixtures)

---


There are two reserved values you can use for `datetime` fields:

* `ON_INSERT_NOW()` will only be used when a row is being inserted
* `ON_UPDATE_NOW()` will only be used when a row is being updated

Example YAML fixture:

```yaml
---

- table: 'some_table'
  pk:
    id: 1
  fields:
    string_field: 'foobar'
    boolean_field: true
    created_at: 'ON_INSERT_NOW()'
    updated_at: 'ON_UPDATE_NOW()'

- table: 'other_table'
  pk:
    id: 2
  fields:
    int_field: 123
    boolean_field: false
    created_at: 'ON_INSERT_NOW()'
    updated_at: 'ON_UPDATE_NOW()'

- table: 'join_table'
  pk:
    some_id: 1
    other_id: 2
```

Example integration for your project:

```go
package main

import (
	"database/sql"
	"io/ioutil"
	"log"

	"github.com/RichardKnop/go-fixtures"
	"github.com/urfave/cli"
	// Drivers
	_ "github.com/lib/pq"
)

var (
	cliApp *cli.App
)

func init() {
	cliApp = cli.NewApp()
	cliApp.Name = "your-project"
	cliApp.Usage = "Project's usage"
	cliApp.Author = "Your Name"
	cliApp.Email = "your@email"
	cliApp.Version = "0.0.0"
}

func main() {
	db, err := sql.Connect("postgres", "user=foo dbname=bar sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	cliApp.Commands = []cli.Command{
		{
			Name:  "loaddata",
			Usage: "load data from fixture",
			Action: func(c *cli.Context) error {
				data, err := ioutil.ReadFile(c.Args().First())
				if err != nil {
					return err
				}

				if err := fixtures.Load(data, db, "postgres"); err != nil {
					return err
				}
			},
		},
		{
			Name:  "runserver",
			Usage: "run web server",
			Action: func(c *cli.Context) error {
				// Run your web server here
				return nil
			},
		},
	}

	cliApp.Run(os.Args)
}
```


go-fixtures

CLI-friendly package for go-pg migrations management

![GolangCI](https://github.com/lawzava/go-pg-migrate/workflows/golangci/badge.svg?branch=main)
[![Version](https://img.shields.io/badge/version-v2.0.0-green.svg)](https://github.com/lawzava/go-pg-migrate/releases)
[![Go Report Card](https://goreportcard.com/badge/github.com/lawzava/go-pg-migrate)](https://goreportcard.com/report/github.com/lawzava/go-pg-migrate)
[![Coverage Status](https://coveralls.io/repos/github/lawzava/go-pg-migrate/badge.svg?branch=main)](https://coveralls.io/github/lawzava/go-pg-migrate?branch=main)
[![Go Reference](https://pkg.go.dev/badge/github.com/lawzava/go-pg-migrate.svg)](https://pkg.go.dev/github.com/lawzava/go-pg-migrate)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://awesome-go.com)


# go-pg-migrate

CLI-friendly package for PostgreSQL migrations management.

## Installation

Requires Go Modules enabled.

```
go get github.com/lawzava/go-pg-migrate/v2
```

## Usage

Initialize the `migrate` with options payload where choices are:

- `DatabaseURI` database connection string. In a format of `postgres://user:password@host:port/database?sslmode=disable`.

- `VersionNumberToApply` uint value of a migration number up to which the migrations should be applied. 
When the requested migration number is lower than currently applied migration number it will run backward migrations, otherwise it will run forward migrations.
  
- `PrintVersionAndExit` if true, the currently applied version number will be printed into stdout and the migrations will not be applied.

- `ForceVersionWithoutMigrations` if true, the migrations will not be applied, but they will be registered as applied up to the specified version number.

- `RefreshSchema` if true, public schema will be dropped and recreated before the migrations are applied. Useful for frequent testing and CI environments.

## Example

You will find the example in [examples](examples) directory. The example is CLI-friendly and can be used as a base for CLI-based migrations utility.




go-pg-migrate

# Simple Gorm seeder
[![Build Status](https://app.travis-ci.com/Kachit/gorm-seeder.svg?branch=master)](https://app.travis-ci.com/Kachit/gorm-seeder)
[![Codecov](https://codecov.io/gh/Kachit/gorm-seeder/branch/master/graph/badge.svg)](https://codecov.io/gh/Kachit/gorm-seeder)
[![Go Report Card](https://goreportcard.com/badge/github.com/kachit/gorm-seeder)](https://goreportcard.com/report/github.com/kachit/gorm-seeder)
[![Version](https://img.shields.io/github/go-mod/go-version/Kachit/gorm-seeder)](https://go.dev/doc/go1.14)
[![Release](https://img.shields.io/github/v/release/Kachit/gorm-seeder.svg)](https://github.com/Kachit/gorm-seeder/releases)
[![License](https://img.shields.io/github/license/mashape/apistatus.svg)](https://github.com/kachit/gorm-seeder/blob/master/LICENSE)
[![GoDoc](https://pkg.go.dev/badge/github.com/kachit/gorm-seeder)](https://pkg.go.dev/github.com/kachit/gorm-seeder)
[![Mentioned in Awesome Go](https://awesome.re/mentioned-badge.svg)](https://github.com/avelino/awesome-go#third-party-apis)

## Description
Simple Gorm seeder package

## Download
```shell
go get -u github.com/kachit/gorm-seeder
```

## Usage
```go
package main

import (
 "fmt"
 "github.com/kachit/gorm-seeder"
 "gorm.io/driver/postgres"
 "gorm.io/gorm"
 "time"
)

//Init model
type User struct {
 Id uint64 `json:"id" gorm:"primaryKey"`
 Name string `json:"name"`
 Email string `json:"email"`
 Password string `json:"password"`
 CreatedAt time.Time `json:"created_at"`
}

func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
 u.CreatedAt = time.Now().UTC()
 return
}

// TableName overrides
func (User) TableName() string {
 return "users"
}

//Create new seeder instance of gorm_seeder.SeederInterface
type UsersSeeder struct {
 gorm_seeder.SeederAbstract
}

func NewUsersSeeder(cfg gorm_seeder.SeederConfiguration) UsersSeeder {
 return UsersSeeder{gorm_seeder.NewSeederAbstract(cfg)}
}

//Implement Seed method
func (s *UsersSeeder) Seed(db *gorm.DB) error {
 var users []User
 for i := 0; i < s.Configuration.Rows; i++ {
 indexStr := fmt.Sprint(i)
 user := User{
 Name: "Name LastName" + indexStr,
 Email: "foo" + indexStr + "@bar.gov",
 Password: "password-hash-string" + indexStr,
 }
 users = append(users, user)
 }
 return db.CreateInBatches(users, s.Configuration.Rows).Error
}

//Implement Clear method
func (s *UsersSeeder) Clear(db *gorm.DB) error {
 entity := User{}
 return s.SeederAbstract.Delete(db, entity.TableName())
}

func main(){
 //Init DB connection
 db, _ := gorm.Open(postgres.New(postgres.Config{
 DSN: "DSN connection string",
 }))

 //Build seeders stack
 usersSeeder := NewUsersSeeder(gorm_seeder.SeederConfiguration{Rows: 10})
 seedersStack := gorm_seeder.NewSeedersStack(db)
 seedersStack.AddSeeder(&usersSeeder)

 //Apply seed
 err := seedersStack.Seed()
 fmt.Println(err)

 //Apply clear
 err = seedersStack.Clear()
 fmt.Println(err)
}
```

gorm-seeder

Database migration, creation, ORM, etc... for MySQL, PostgreSQL, and SQLite

<img src="logo.png" width="150" height="150">


 <a href="https://pkg.go.dev/github.com/gobuffalo/pop/v6"><img src="https://godoc.org/github.com/gobuffalo/pop?status.svg" alt="GoDoc" /></a>
 <a href="https://github.com/gobuffalo/pop/actions?query=workflow%3ATests+branch%3Amain"><img src="https://github.com/gobuffalo/pop/workflows/Tests/badge.svg" alt="Build Status" /></a>


# POP

## A Tasty Treat For All Your Database Needs

So what does Pop do exactly? Well, it wraps the absolutely amazing [https://github.com/jmoiron/sqlx](https://github.com/jmoiron/sqlx) library. It cleans up some of the common patterns and work flows usually associated with dealing with databases in Go.

Pop makes it easy to do CRUD operations, run migrations, and build/execute queries.

Pop, by default, follows conventions that were influenced by the [ActiveRecord](http://www.rubyonrails.org) Ruby gem. What does this mean?

* Tables must have an "id" column and a corresponding "ID" field on the `struct` being used.
* If there is a `timestamp` column named `created_at`, and a `CreatedAt time.Time` attribute on the `struct`, it will be set with the current time when the record is created.
* If there is a `timestamp` column named `updated_at`, and a `UpdatedAt time.Time` attribute on the `struct`, it will be set with the current time when the record is updated.
* Default database table names are lowercase, plural, and underscored versions of the `struct` name. Examples: User{} is "users", FooBar{} is "foo_bars", etc...

Want to know more? Take a look at the documentation!

## Documentation

Please visit [http://gobuffalo.io](https://gobuffalo.io/docs/db/getting-started) for the latest documentation, examples, and more.

### Quick Start
* [CLI Installation](https://gobuffalo.io/docs/db/toolbox)
* [Configuration](https://gobuffalo.io/docs/db/configuration)

## Shoulders of Giants

Pop would not be possible if not for all of the great projects it depends on. Please see [SHOULDERS.md](SHOULDERS.md) to see a list of them.

## Contributing

First, thank you so much for wanting to contribute! It means so much that you care enough to want to contribute. We appreciate every PR from the smallest of typos to the be biggest of features.

To contribute, please read the contribution guidelines: [CONTRIBUTING](.github/CONTRIBUTING.md)

The Best Go Libraries For Database - Database Schema Migration (22)

atlas

avro

bytebase

darwin

dbmate

skeema

sql-migrate

sqlize

go-pg-migrations

goavro

godfish

goose

gormigrate

libschema

migrate

migrator

migrator

schema

go-fixtures

go-pg-migrate

gorm-seeder

soda