Repository: schemahero/schemahero
Pull request #256 (2020-08-24 13:17:49)

Add support for managing the charset and collation of mysql columns, tables and databases
This PR introduces support in MySQL for setting specifying the charset and collate in the schema definition manifest.

- [x] setting charset for a column
- [x] setting collate for a column
- [x] setting charset for a table
- [x] setting collate for a table
- [x] setting charset for a database
- [x] setting collate for a database
- [x] support for generate command


Patch:
diff --git a/config/crds/v1/databases.schemahero.io_databases.yaml b/config/crds/v1/databases.schemahero.io_databases.yaml
index 65b9e49a..0bdd16dc 100644
--- a/config/crds/v1/databases.schemahero.io_databases.yaml
+++ b/config/crds/v1/databases.schemahero.io_databases.yaml
@@ -653,6 +653,8 @@ spec:
                     type: object
                   mysql:
                     properties:
+                      collation:
+                        type: string
                       dbname:
                         properties:
                           value:
@@ -740,6 +742,8 @@ spec:
                                 type: object
                             type: object
                         type: object
+                      defaultCharset:
+                        type: string
                       disableTLS:
                         type: boolean
                       host:
diff --git a/config/crds/v1/schemas.schemahero.io_tables.yaml b/config/crds/v1/schemas.schemahero.io_tables.yaml
index 23a7b348..ecfe529c 100644
--- a/config/crds/v1/schemas.schemahero.io_tables.yaml
+++ b/config/crds/v1/schemas.schemahero.io_tables.yaml
@@ -126,6 +126,8 @@ spec:
                     type: object
                   mysql:
                     properties:
+                      collation:
+                        type: string
                       columns:
                         items:
                           properties:
@@ -134,6 +136,10 @@ spec:
                                 autoIncrement:
                                   type: boolean
                               type: object
+                            charset:
+                              type: string
+                            collation:
+                              type: string
                             constraints:
                               properties:
                                 notNull:
@@ -150,6 +156,8 @@ spec:
                           - type
                           type: object
                         type: array
+                      defaultCharset:
+                        type: string
                       foreignKeys:
                         items:
                           properties:
diff --git a/config/crds/v1beta1/databases.schemahero.io_databases.yaml b/config/crds/v1beta1/databases.schemahero.io_databases.yaml
index d7919899..2917f603 100644
--- a/config/crds/v1beta1/databases.schemahero.io_databases.yaml
+++ b/config/crds/v1beta1/databases.schemahero.io_databases.yaml
@@ -653,6 +653,8 @@ spec:
                   type: object
                 mysql:
                   properties:
+                    collation:
+                      type: string
                     dbname:
                       properties:
                         value:
@@ -740,6 +742,8 @@ spec:
                               type: object
                           type: object
                       type: object
+                    defaultCharset:
+                      type: string
                     disableTLS:
                       type: boolean
                     host:
diff --git a/config/crds/v1beta1/schemas.schemahero.io_tables.yaml b/config/crds/v1beta1/schemas.schemahero.io_tables.yaml
index 6430cba4..554e6ab6 100644
--- a/config/crds/v1beta1/schemas.schemahero.io_tables.yaml
+++ b/config/crds/v1beta1/schemas.schemahero.io_tables.yaml
@@ -124,6 +124,8 @@ spec:
                   type: object
                 mysql:
                   properties:
+                    collation:
+                      type: string
                     columns:
                       items:
                         properties:
@@ -132,6 +134,10 @@ spec:
                               autoIncrement:
                                 type: boolean
                             type: object
+                          charset:
+                            type: string
+                          collation:
+                            type: string
                           constraints:
                             properties:
                               notNull:
@@ -148,6 +154,8 @@ spec:
                         - type
                         type: object
                       type: array
+                    defaultCharset:
+                      type: string
                     foreignKeys:
                       items:
                         properties:
diff --git a/config/dev/github/users-table-mysql.yaml b/config/dev/github/users-table-mysql.yaml
index 8707d4ab..1702966a 100644
--- a/config/dev/github/users-table-mysql.yaml
+++ b/config/dev/github/users-table-mysql.yaml
@@ -8,6 +8,7 @@ spec:
   schema:
     mysql:
       primaryKey: [id]
+      # defaultCharset: "utf16"
       columns:
         - name: id
           type: integer
diff --git a/go.mod b/go.mod
index 209644bf..a86d4655 100644
--- a/go.mod
+++ b/go.mod
@@ -12,6 +12,7 @@ require (
 	github.com/jmespath/go-jmespath v0.3.0 // indirect
 	github.com/onsi/gomega v1.8.1
 	github.com/pkg/errors v0.9.1
+	github.com/prometheus/common v0.4.1
 	github.com/spf13/cobra v0.0.5
 	github.com/spf13/viper v1.4.0
 	github.com/stretchr/testify v1.5.1
diff --git a/go.sum b/go.sum
index aba9e383..c5925fcd 100644
--- a/go.sum
+++ b/go.sum
@@ -23,7 +23,9 @@ github.com/PuerkitoBio/urlesc v0.0.0-20160726150825-5bd2802263f2/go.mod h1:uGdko
 github.com/PuerkitoBio/urlesc v0.0.0-20170810143723-de5bf2ad4578 h1:d+Bc7a5rLufV/sSk/8dngufqelfh6jnri85riMAaF/M=
 github.com/PuerkitoBio/urlesc v0.0.0-20170810143723-de5bf2ad4578/go.mod h1:uGdkoq3SwY9Y+13GIhn11/XLaGBb4BfwItxLd5jeuXE=
 github.com/agnivade/levenshtein v1.0.1/go.mod h1:CURSv5d9Uaml+FovSIICkLbAUZ9S4RqaHDIsdSBg7lM=
+github.com/alecthomas/template v0.0.0-20160405071501-a0175ee3bccc h1:cAKDfWh5VpdgMhJosfJnn5/FoN2SRZ4p7fJNX58YPaU=
 github.com/alecthomas/template v0.0.0-20160405071501-a0175ee3bccc/go.mod h1:LOuyumcjzFXgccqObfd/Ljyb9UuFJ6TxHnclSeseNhc=
+github.com/alecthomas/units v0.0.0-20151022065526-2efee857e7cf h1:qet1QNfXsQxTZqLG4oE62mJzwPIB8+Tee4RNCL9ulrY=
 github.com/alecthomas/units v0.0.0-20151022065526-2efee857e7cf/go.mod h1:ybxpYRFXyAe+OPACYpWeL0wqObRcbAqCMya13uyzqw0=
 github.com/andreyvit/diff v0.0.0-20170406064948-c7f18ee00883/go.mod h1:rCTlJbsFo29Kk6CurOXKm700vrz8f0KW0JNfpkRJY/8=
 github.com/armon/consul-api v0.0.0-20180202201655-eb2c6b5be1b6/go.mod h1:grANhF5doyWs3UAsr3K4I6qtAmlQcZDesFNEHPZAzj8=
@@ -396,6 +398,7 @@ github.com/shopspring/decimal v0.0.0-20200227202807-02e2044944cc h1:jUIKcSPO9MoM
 github.com/shopspring/decimal v0.0.0-20200227202807-02e2044944cc/go.mod h1:DKyhrW/HYNuLGql+MJL6WCR6knT2jwCFRcu2hWCYk4o=
 github.com/sirupsen/logrus v1.2.0/go.mod h1:LxeOpSwHxABJmUn/MG1IvRgCAasNZTLOkJPxbbu5VWo=
 github.com/sirupsen/logrus v1.4.1/go.mod h1:ni0Sbl8bgC9z8RoU9G6nDWqqs/fq4eDPysMBDgk/93Q=
+github.com/sirupsen/logrus v1.4.2 h1:SPIRibHv4MatM3XXNO2BJeFLZwZ2LvZgfQ5+UNI2im4=
 github.com/sirupsen/logrus v1.4.2/go.mod h1:tLMulIdttU9McNUspp0xgXVQah82FyeX6MwdIuYE2rE=
 github.com/soheilhy/cmux v0.1.4/go.mod h1:IM3LyeVVIOuxMH7sFAkER9+bJ4dT7Ms6E4xg4kGIyLM=
 github.com/spaolacci/murmur3 v0.0.0-20180118202830-f09979ecbc72/go.mod h1:JwIasOWyU6f++ZhiEuf87xNszmSA2myDM2Kzu9HwQUA=
@@ -604,6 +607,7 @@ google.golang.org/grpc v1.21.0/go.mod h1:oYelfM1adQP15Ek0mdvEgi9Df8B9CZIaU1084ij
 google.golang.org/grpc v1.23.0/go.mod h1:Y5yQAOtifL1yxbo5wqy6BxZv8vAUGQwXBOALyacEbxg=
 google.golang.org/grpc v1.23.1/go.mod h1:Y5yQAOtifL1yxbo5wqy6BxZv8vAUGQwXBOALyacEbxg=
 google.golang.org/grpc v1.26.0/go.mod h1:qbnxyOmOxrQa7FizSgH+ReBfzJrCY1pSN7KXBS8abTk=
+gopkg.in/alecthomas/kingpin.v2 v2.2.6 h1:jMFz6MfLP0/4fUyZle81rXUoxOBFi19VUFKVDOQfozc=
 gopkg.in/alecthomas/kingpin.v2 v2.2.6/go.mod h1:FMv+mEhP44yOT+4EoQTLFTRgOQ1FBLkstjWtayDeSgw=
 gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
 gopkg.in/check.v1 v1.0.0-20180628173108-788fd7840127/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
diff --git a/pkg/apis/databases/v1alpha4/mysql_types.go b/pkg/apis/databases/v1alpha4/mysql_types.go
index 0e6c05cf..6d3a3c21 100644
--- a/pkg/apis/databases/v1alpha4/mysql_types.go
+++ b/pkg/apis/databases/v1alpha4/mysql_types.go
@@ -26,4 +26,7 @@ type MysqlConnection struct {
 	DBName   ValueOrValueFrom `json:"dbname,omitempty"`
 	// +kubebuilder:validation:Optional
 	DisableTLS bool `json:"disableTLS"`
+
+	DefaultCharset string `json:"defaultCharset,omitempty"`
+	Collation      string `json:"collation,omitempty"`
 }
diff --git a/pkg/apis/schemas/v1alpha4/mysql.go b/pkg/apis/schemas/v1alpha4/mysql.go
new file mode 100644
index 00000000..5bb8ce24
--- /dev/null
+++ b/pkg/apis/schemas/v1alpha4/mysql.go
@@ -0,0 +1,37 @@
+/*
+Copyright 2019 Replicated, Inc.
+
+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.
+*/
+
+package v1alpha4
+
+type MysqlSQLTableColumn struct {
+	Name        string                     `json:"name" yaml:"name"`
+	Type        string                     `json:"type" yaml:"type"`
+	Constraints *SQLTableColumnConstraints `json:"constraints,omitempty" yaml:"constraints,omitempty"`
+	Attributes  *SQLTableColumnAttributes  `json:"attributes,omitempty" yaml:"attributes,omitempty"`
+	Default     *string                    `json:"default,omitempty" yaml:"default,omitempty"`
+	Charset     string                     `json:"charset,omitempty" yaml:"charset,omitempty"`
+	Collation   string                     `json:"collation,omitempty" yaml:"collation,omitempty"`
+}
+
+type MysqlSQLTableSchema struct {
+	PrimaryKey     []string               `json:"primaryKey,omitempty" yaml:"primaryKey,omitempty"`
+	ForeignKeys    []*SQLTableForeignKey  `json:"foreignKeys,omitempty" yaml:"foreignKeys,omitempty"`
+	Indexes        []*SQLTableIndex       `json:"indexes,omitempty" yaml:"indexes,omitempty"`
+	Columns        []*MysqlSQLTableColumn `json:"columns,omitempty" yaml:"columns,omitempty"`
+	IsDeleted      bool                   `json:"isDeleted,omitempty" yaml:"isDeleted,omitempty"`
+	DefaultCharset string                 `json:"defaultCharset,omitempty" yaml:"defaultCharset,omitempty"`
+	Collation      string                 `json:"collation,omitempty" yaml:"collation,omitempty"`
+}
diff --git a/pkg/apis/schemas/v1alpha4/table_types.go b/pkg/apis/schemas/v1alpha4/table_types.go
index cd4c6a45..c49dd977 100644
--- a/pkg/apis/schemas/v1alpha4/table_types.go
+++ b/pkg/apis/schemas/v1alpha4/table_types.go
@@ -26,9 +26,9 @@ import (
 )
 
 type TableSchema struct {
-	Postgres    *SQLTableSchema `json:"postgres,omitempty" yaml:"postgres,omitempty"`
-	Mysql       *SQLTableSchema `json:"mysql,omitempty" yaml:"mysql,omitempty"`
-	CockroachDB *SQLTableSchema `json:"cockroachdb,omitempty" yaml:"cockroachdb,omitempty"`
+	Postgres    *SQLTableSchema      `json:"postgres,omitempty" yaml:"postgres,omitempty"`
+	Mysql       *MysqlSQLTableSchema `json:"mysql,omitempty" yaml:"mysql,omitempty"`
+	CockroachDB *SQLTableSchema      `json:"cockroachdb,omitempty" yaml:"cockroachdb,omitempty"`
 }
 
 // TableSpec defines the desired state of Table
diff --git a/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go b/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go
index 9f2e69c5..cd89ae27 100644
--- a/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go
+++ b/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go
@@ -113,6 +113,89 @@ func (in *MigrationStatus) DeepCopy() *MigrationStatus {
 	return out
 }
 
+// DeepCopyInto is an autogenerated deepcopy function, copying the receiver, writing into out. in must be non-nil.
+func (in *MysqlSQLTableColumn) DeepCopyInto(out *MysqlSQLTableColumn) {
+	*out = *in
+	if in.Constraints != nil {
+		in, out := &in.Constraints, &out.Constraints
+		*out = new(SQLTableColumnConstraints)
+		(*in).DeepCopyInto(*out)
+	}
+	if in.Attributes != nil {
+		in, out := &in.Attributes, &out.Attributes
+		*out = new(SQLTableColumnAttributes)
+		(*in).DeepCopyInto(*out)
+	}
+	if in.Default != nil {
+		in, out := &in.Default, &out.Default
+		*out = new(string)
+		**out = **in
+	}
+}
+
+// DeepCopy is an autogenerated deepcopy function, copying the receiver, creating a new MysqlSQLTableColumn.
+func (in *MysqlSQLTableColumn) DeepCopy() *MysqlSQLTableColumn {
+	if in == nil {
+		return nil
+	}
+	out := new(MysqlSQLTableColumn)
+	in.DeepCopyInto(out)
+	return out
+}
+
+// DeepCopyInto is an autogenerated deepcopy function, copying the receiver, writing into out. in must be non-nil.
+func (in *MysqlSQLTableSchema) DeepCopyInto(out *MysqlSQLTableSchema) {
+	*out = *in
+	if in.PrimaryKey != nil {
+		in, out := &in.PrimaryKey, &out.PrimaryKey
+		*out = make([]string, len(*in))
+		copy(*out, *in)
+	}
+	if in.ForeignKeys != nil {
+		in, out := &in.ForeignKeys, &out.ForeignKeys
+		*out = make([]*SQLTableForeignKey, len(*in))
+		for i := range *in {
+			if (*in)[i] != nil {
+				in, out := &(*in)[i], &(*out)[i]
+				*out = new(SQLTableForeignKey)
+				(*in).DeepCopyInto(*out)
+			}
+		}
+	}
+	if in.Indexes != nil {
+		in, out := &in.Indexes, &out.Indexes
+		*out = make([]*SQLTableIndex, len(*in))
+		for i := range *in {
+			if (*in)[i] != nil {
+				in, out := &(*in)[i], &(*out)[i]
+				*out = new(SQLTableIndex)
+				(*in).DeepCopyInto(*out)
+			}
+		}
+	}
+	if in.Columns != nil {
+		in, out := &in.Columns, &out.Columns
+		*out = make([]*MysqlSQLTableColumn, len(*in))
+		for i := range *in {
+			if (*in)[i] != nil {
+				in, out := &(*in)[i], &(*out)[i]
+				*out = new(MysqlSQLTableColumn)
+				(*in).DeepCopyInto(*out)
+			}
+		}
+	}
+}
+
+// DeepCopy is an autogenerated deepcopy function, copying the receiver, creating a new MysqlSQLTableSchema.
+func (in *MysqlSQLTableSchema) DeepCopy() *MysqlSQLTableSchema {
+	if in == nil {
+		return nil
+	}
+	out := new(MysqlSQLTableSchema)
+	in.DeepCopyInto(out)
+	return out
+}
+
 // DeepCopyInto is an autogenerated deepcopy function, copying the receiver, writing into out. in must be non-nil.
 func (in *SQLTableColumn) DeepCopyInto(out *SQLTableColumn) {
 	*out = *in
@@ -366,7 +449,7 @@ func (in *TableSchema) DeepCopyInto(out *TableSchema) {
 	}
 	if in.Mysql != nil {
 		in, out := &in.Mysql, &out.Mysql
-		*out = new(SQLTableSchema)
+		*out = new(MysqlSQLTableSchema)
 		(*in).DeepCopyInto(*out)
 	}
 	if in.CockroachDB != nil {
diff --git a/pkg/cli/managercli/run.go b/pkg/cli/managercli/run.go
index 3ed19e55..17d8438b 100644
--- a/pkg/cli/managercli/run.go
+++ b/pkg/cli/managercli/run.go
@@ -74,6 +74,11 @@ func RunCmd() *cobra.Command {
 			}
 
 			if len(v.GetStringSlice("database-name")) > 0 {
+				if err := databasecontroller.AddForDatabaseSchemasOnly(mgr, v.GetStringSlice("database-name")); err != nil {
+					logger.Error(err)
+					os.Exit(1)
+				}
+
 				if err := tablecontroller.Add(mgr, v.GetStringSlice("database-name")); err != nil {
 					logger.Error(err)
 					os.Exit(1)
diff --git a/pkg/controller/database/database_controller.go b/pkg/controller/database/database_controller.go
index 68f94229..6ca4d9a7 100644
--- a/pkg/controller/database/database_controller.go
+++ b/pkg/controller/database/database_controller.go
@@ -32,54 +32,10 @@ import (
 	"k8s.io/apimachinery/pkg/runtime"
 	"k8s.io/apimachinery/pkg/types"
 	"sigs.k8s.io/controller-runtime/pkg/client"
-	"sigs.k8s.io/controller-runtime/pkg/controller"
 	"sigs.k8s.io/controller-runtime/pkg/controller/controllerutil"
-	"sigs.k8s.io/controller-runtime/pkg/handler"
-	"sigs.k8s.io/controller-runtime/pkg/manager"
 	"sigs.k8s.io/controller-runtime/pkg/reconcile"
-	"sigs.k8s.io/controller-runtime/pkg/source"
 )
 
-var tenSeconds = int64(10)
-var defaultMode = int32(420)
-
-// Add creates a new Database Controller and adds it to the Manager with default RBAC. The Manager will set fields on the Controller
-// and Start it when the Manager is Started.
-func Add(mgr manager.Manager, managerImage string, managerTag string) error {
-	return add(mgr, newReconciler(mgr, managerImage, managerTag))
-}
-
-// newReconciler returns a new reconcile.Reconciler
-func newReconciler(mgr manager.Manager, managerImage string, managerTag string) reconcile.Reconciler {
-	return &ReconcileDatabase{
-		Client:       mgr.GetClient(),
-		scheme:       mgr.GetScheme(),
-		managerImage: managerImage,
-		managerTag:   managerTag,
-	}
-}
-
-// add adds a new Controller to mgr with r as the reconcile.Reconciler
-func add(mgr manager.Manager, r reconcile.Reconciler) error {
-	logger.Debug("adding database controller to manager")
-
-	// Create a new controller
-	c, err := controller.New("database-controller", mgr, controller.Options{Reconciler: r})
-	if err != nil {
-		return err
-	}
-
-	// Watch for changes to Database kinds
-	err = c.Watch(&source.Kind{
-		Type: &databasesv1alpha4.Database{},
-	}, &handler.EnqueueRequestForObject{})
-	if err != nil {
-		return err
-	}
-
-	return nil
-}
-
 var _ reconcile.Reconciler = &ReconcileDatabase{}
 
 // ReconcileDatabase reconciles a Database object
diff --git a/pkg/controller/database/database_schema_controller.go b/pkg/controller/database/database_schema_controller.go
new file mode 100644
index 00000000..81f4f8ca
--- /dev/null
+++ b/pkg/controller/database/database_schema_controller.go
@@ -0,0 +1,207 @@
+/*
+Copyright 2019 Replicated, Inc.
+
+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.
+*/
+
+package database
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+
+	mysqldriver "github.com/go-sql-driver/mysql"
+	"github.com/pkg/errors"
+	databasesv1alpha4 "github.com/schemahero/schemahero/pkg/apis/databases/v1alpha4"
+	"github.com/schemahero/schemahero/pkg/logger"
+	"k8s.io/apimachinery/pkg/runtime"
+	"sigs.k8s.io/controller-runtime/pkg/client"
+	"sigs.k8s.io/controller-runtime/pkg/reconcile"
+)
+
+var _ reconcile.Reconciler = &ReconcileDatabaseSchema{}
+
+// ReconcileDatabaseSchema reconciles a Database object
+type ReconcileDatabaseSchema struct {
+	client.Client
+	scheme        *runtime.Scheme
+	databaseNames []string
+}
+
+// Reconcile reads that state of the cluster for a Database object and makes changes based on the state read
+// and what is in the Database.Spec for schemas
+// +kubebuilder:rbac:groups=databases.schemahero.io,resources=databases,verbs=get;list;watch;create;update;patch;delete
+// +kubebuilder:rbac:groups=databases.schemahero.io,resources=databases/status,verbs=get;update;patch
+func (r *ReconcileDatabaseSchema) Reconcile(request reconcile.Request) (reconcile.Result, error) {
+	databaseInstance, err := r.getInstance(request)
+	if err != nil {
+		return reconcile.Result{}, err
+	}
+
+	// SchemaHero does not current support any database-wide schema properties in postgresql
+	if databaseInstance.Spec.Connection.Postgres != nil {
+		logger.Debug("ignoring postgres database schema reconcile request")
+		return reconcile.Result{}, nil
+	}
+
+	// SchemaHero does not current support any database-wide schema properties in cockroachdb
+	if databaseInstance.Spec.Connection.CockroachDB != nil {
+		logger.Debug("ignoring cockroachdb database schema reconcile request")
+		return reconcile.Result{}, nil
+	}
+
+	if databaseInstance.Spec.Connection.Mysql != nil {
+		result, err := r.reconcileMysqlDatabaseSchema(databaseInstance)
+		if err != nil {
+			logger.Error(err)
+		}
+		return result, err
+	}
+
+	return reconcile.Result{}, nil
+}
+
+func (r *ReconcileDatabaseSchema) getInstance(request reconcile.Request) (*databasesv1alpha4.Database, error) {
+	instance := &databasesv1alpha4.Database{}
+	err := r.Get(context.Background(), request.NamespacedName, instance)
+	if err != nil {
+		return nil, errors.Wrap(err, "failed to get databasesv1alpha4 instance")
+	}
+
+	return instance, nil
+}
+
+func (r *ReconcileDatabaseSchema) reconcileMysqlDatabaseSchema(databaseInstance *databasesv1alpha4.Database) (reconcile.Result, error) {
+	logger.Debug("reconciling mysql database schema")
+
+	ctx := context.Background()
+
+	_, connectionURI, err := databaseInstance.GetConnection(ctx)
+	if err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to get database connection")
+	}
+
+	cfg, err := mysqldriver.ParseDSN(connectionURI)
+	if err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to parse mysql connection uri")
+	}
+
+	// compare the databaseInstance defaultCharset and collation to the running database
+	db, err := sql.Open("mysql", connectionURI)
+	if err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to open connection to mysql")
+	}
+	defer db.Close()
+
+	db.SetMaxIdleConns(0)
+	db.SetMaxOpenConns(1)
+
+	query := `SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA 
+	WHERE schema_name = ?`
+	row := db.QueryRow(query, cfg.DBName)
+
+	var defaultCharset, defaultCollation string
+	if err := row.Scan(&defaultCharset, &defaultCollation); err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to scan")
+	}
+
+	if databaseInstance.Spec.Connection.Mysql.DefaultCharset == defaultCharset && databaseInstance.Spec.Connection.Mysql.Collation == defaultCollation {
+		logger.Debug("no action needed as the charset and collation already match")
+		return reconcile.Result{}, nil
+	}
+
+	// if they are different, it might be the server default, but that's hard to tell from a connection
+	// if the user passed "", then we want the database set to the default
+	// https://dev.mysql.com/doc/refman/8.0/en/charset-database.html  <-- see this for application
+	query = `show variables like 'collation_server'`
+	row = db.QueryRow(query)
+
+	var variableName string
+	var defaultServerCollation, defaultServerCharset string
+	if err := row.Scan(&variableName, &defaultServerCollation); err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to scan server default collation")
+	}
+
+	query = `show variables like 'character_set_server'`
+	row = db.QueryRow(query)
+	if err := row.Scan(&variableName, &defaultServerCharset); err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to scan server default charset")
+	}
+
+	/*
+		from https://dev.mysql.com/doc/refman/8.0/en/charset-database.html
+
+		MySQL chooses the database character set and database collation in the following manner:
+
+			If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.
+
+			If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.
+
+			If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.
+
+			Otherwise (neither CHARACTER SET nor COLLATE is specified), the server character set and server collation are used.
+
+	*/
+
+	if databaseInstance.Spec.Connection.Mysql.DefaultCharset != "" && databaseInstance.Spec.Connection.Mysql.Collation != "" {
+		// If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.
+		query = fmt.Sprintf("alter database `%s` character set '%s' collate '%s'", cfg.DBName, databaseInstance.Spec.Connection.Mysql.DefaultCharset, databaseInstance.Spec.Connection.Mysql.Collation)
+		if _, err := db.Exec(query); err != nil {
+			return reconcile.Result{}, errors.Wrap(err, "failed to update database character set and collation")
+		}
+
+		return reconcile.Result{}, nil
+	}
+
+	if databaseInstance.Spec.Connection.Mysql.DefaultCharset != "" && databaseInstance.Spec.Connection.Mysql.Collation == "" {
+		// If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.
+		query = `select DEFAULT_COLLATE_NAME from information_schema.character_sets where CHARACTER_SET_NAME = ?`
+		row := db.QueryRow(query, databaseInstance.Spec.Connection.Mysql.DefaultCharset)
+		var charsetDefaultCollation string
+		if err := row.Scan(&charsetDefaultCollation); err != nil {
+			return reconcile.Result{}, errors.Wrap(err, "failed to select default collation for character set")
+		}
+
+		query = fmt.Sprintf("alter database `%s` character set '%s' collate '%s'", cfg.DBName, databaseInstance.Spec.Connection.Mysql.DefaultCharset, charsetDefaultCollation)
+		if _, err := db.Exec(query); err != nil {
+			return reconcile.Result{}, errors.Wrap(err, "failed to update database character set and collation")
+		}
+
+		return reconcile.Result{}, nil
+	}
+
+	if databaseInstance.Spec.Connection.Mysql.DefaultCharset == "" && databaseInstance.Spec.Connection.Mysql.Collation != "" {
+		// If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.
+		query = `select CHARACTER_SET_NAME from information_schema.collations where COLLATION_NAME = ?`
+		row := db.QueryRow(query, databaseInstance.Spec.Connection.Mysql.Collation)
+		var collationCharset string
+		if err := row.Scan(&collationCharset); err != nil {
+			return reconcile.Result{}, errors.Wrap(err, "failed to select charset for collaction")
+		}
+
+		query = fmt.Sprintf("alter database `%s` character set '%s' collate '%s'", cfg.DBName, collationCharset, databaseInstance.Spec.Connection.Mysql.Collation)
+		if _, err := db.Exec(query); err != nil {
+			return reconcile.Result{}, errors.Wrap(err, "failed to update database character set and collation")
+		}
+
+		return reconcile.Result{}, nil
+	}
+
+	query = fmt.Sprintf("alter database `%s` character set '%s' collate '%s'", cfg.DBName, defaultServerCharset, defaultServerCollation)
+	if _, err := db.Exec(query); err != nil {
+		return reconcile.Result{}, errors.Wrap(err, "failed to update database character set and collation")
+	}
+
+	return reconcile.Result{}, nil
+}
diff --git a/pkg/controller/database/setup.go b/pkg/controller/database/setup.go
new file mode 100644
index 00000000..c7ef92a1
--- /dev/null
+++ b/pkg/controller/database/setup.go
@@ -0,0 +1,81 @@
+/*
+Copyright 2019 Replicated, Inc.
+
+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.
+*/
+
+package database
+
+import (
+	databasesv1alpha4 "github.com/schemahero/schemahero/pkg/apis/databases/v1alpha4"
+	"github.com/schemahero/schemahero/pkg/logger"
+	"sigs.k8s.io/controller-runtime/pkg/controller"
+	"sigs.k8s.io/controller-runtime/pkg/handler"
+	"sigs.k8s.io/controller-runtime/pkg/manager"
+	"sigs.k8s.io/controller-runtime/pkg/reconcile"
+	"sigs.k8s.io/controller-runtime/pkg/source"
+)
+
+var tenSeconds = int64(10)
+var defaultMode = int32(420)
+
+// Add creates a new Database Controller and adds it to the Manager with default RBAC. The Manager will set fields on the Controller
+// and Start it when the Manager is Started.
+func Add(mgr manager.Manager, managerImage string, managerTag string) error {
+	return add(mgr, newReconciler(mgr, managerImage, managerTag), "databaseController")
+}
+
+// AddForDatabaseSchemasOnly creates a new Database Controller to watch for changes to a specific database object. This is how database-level schema
+// changes are populated. Table and migration reconcile loops are separate, this is used for database options (charset, collate, etc)
+func AddForDatabaseSchemasOnly(mgr manager.Manager, databaseNames []string) error {
+	return add(mgr, newDatabaseSchemaReconciler(mgr, databaseNames), "databaseSchemaController")
+}
+
+// newReconciler returns a new reconcile.Reconciler
+func newReconciler(mgr manager.Manager, managerImage string, managerTag string) reconcile.Reconciler {
+	return &ReconcileDatabase{
+		Client:       mgr.GetClient(),
+		scheme:       mgr.GetScheme(),
+		managerImage: managerImage,
+		managerTag:   managerTag,
+	}
+}
+
+func newDatabaseSchemaReconciler(mgr manager.Manager, databaseNames []string) reconcile.Reconciler {
+	return &ReconcileDatabaseSchema{
+		Client:        mgr.GetClient(),
+		scheme:        mgr.GetScheme(),
+		databaseNames: databaseNames,
+	}
+}
+
+// add adds a new Controller to mgr with r as the reconcile.Reconciler
+func add(mgr manager.Manager, r reconcile.Reconciler, name string) error {
+	logger.Debugf("adding %s to manager", name)
+
+	// Create a new controller
+	c, err := controller.New("database-controller", mgr, controller.Options{Reconciler: r})
+	if err != nil {
+		return err
+	}
+
+	// Watch for changes to Database kinds
+	err = c.Watch(&source.Kind{
+		Type: &databasesv1alpha4.Database{},
+	}, &handler.EnqueueRequestForObject{})
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
diff --git a/pkg/database/interfaces/connection.go b/pkg/database/interfaces/connection.go
index 12ffcc18..defd0fd3 100644
--- a/pkg/database/interfaces/connection.go
+++ b/pkg/database/interfaces/connection.go
@@ -10,7 +10,7 @@ type SchemaHeroDatabaseConnection interface {
 	DatabaseName() string
 	EngineVersion() string
 
-	ListTables() ([]string, error)
+	ListTables() ([]*types.Table, error)
 	ListTableForeignKeys(string, string) ([]*types.ForeignKey, error)
 	ListTableIndexes(string, string) ([]*types.Index, error)
 
diff --git a/pkg/database/mysql/alter.go b/pkg/database/mysql/alter.go
index f7515126..15f6f17d 100644
--- a/pkg/database/mysql/alter.go
+++ b/pkg/database/mysql/alter.go
@@ -5,7 +5,7 @@ import (
 	"github.com/schemahero/schemahero/pkg/database/types"
 )
 
-func AlterColumnStatements(tableName string, primaryKeys []string, desiredColumns []*schemasv1alpha4.SQLTableColumn, existingColumn *types.Column) ([]string, error) {
+func AlterColumnStatements(tableName string, primaryKeys []string, desiredColumns []*schemasv1alpha4.MysqlSQLTableColumn, existingColumn *types.Column) ([]string, error) {
 	// this could be an alter or a drop column command
 	for _, desiredColumn := range desiredColumns {
 		if desiredColumn.Name == existingColumn.Name {
@@ -50,6 +50,14 @@ func columnsMatch(col1 *types.Column, col2 *types.Column) bool {
 		return false
 	}
 
+	if col1.Charset != col2.Charset {
+		return false
+	}
+
+	if col1.Collation != col2.Collation {
+		return false
+	}
+
 	if col1.ColumnDefault != nil && col2.ColumnDefault == nil {
 		return false
 	} else if col1.ColumnDefault == nil && col2.ColumnDefault != nil {
diff --git a/pkg/database/mysql/alter_statements.go b/pkg/database/mysql/alter_statements.go
index dcd840f2..cb1f98d4 100644
--- a/pkg/database/mysql/alter_statements.go
+++ b/pkg/database/mysql/alter_statements.go
@@ -67,6 +67,10 @@ func (s AlterModifyColumnStatement) ddl(useConstraintsFromExistingColumn bool) [
 		stmts = append(stmts, fmt.Sprintf("default \"%s\"", *s.Column.ColumnDefault))
 	}
 
+	if s.Column.Charset != s.ExistingColumn.Charset || s.Column.Collation != s.ExistingColumn.Collation {
+		stmts = append(stmts, fmt.Sprintf("character set %s collate %s", s.Column.Charset, s.Column.Collation))
+	}
+
 	return []string{strings.Join(stmts, " ")}
 }
 
diff --git a/pkg/database/mysql/column.go b/pkg/database/mysql/column.go
index 18b3c7c0..6714639a 100644
--- a/pkg/database/mysql/column.go
+++ b/pkg/database/mysql/column.go
@@ -7,10 +7,12 @@ import (
 	"github.com/schemahero/schemahero/pkg/database/types"
 )
 
-func schemaColumnToColumn(schemaColumn *schemasv1alpha4.SQLTableColumn) (*types.Column, error) {
+func schemaColumnToColumn(schemaColumn *schemasv1alpha4.MysqlSQLTableColumn) (*types.Column, error) {
 	column := &types.Column{
 		Name:          schemaColumn.Name,
 		ColumnDefault: schemaColumn.Default,
+		Charset:       schemaColumn.Charset,
+		Collation:     schemaColumn.Collation,
 	}
 
 	if schemaColumn.Constraints != nil {
@@ -54,7 +56,7 @@ func schemaColumnToColumn(schemaColumn *schemasv1alpha4.SQLTableColumn) (*types.
 	return nil, fmt.Errorf("unknown column type. cannot validate column type %q", schemaColumn.Type)
 }
 
-func mysqlColumnAsInsert(column *schemasv1alpha4.SQLTableColumn) (string, error) {
+func mysqlColumnAsInsert(column *schemasv1alpha4.MysqlSQLTableColumn) (string, error) {
 	mysqlColumn, err := schemaColumnToColumn(column)
 	if err != nil {
 		return "", err
@@ -90,10 +92,17 @@ func mysqlColumnAsInsert(column *schemasv1alpha4.SQLTableColumn) (string, error)
 		}
 	}
 
+	if mysqlColumn.Charset != "" {
+		formatted = fmt.Sprintf("%s character set %s", formatted, mysqlColumn.Charset)
+	}
+	if mysqlColumn.Collation != "" {
+		formatted = fmt.Sprintf("%s collate %s", formatted, mysqlColumn.Collation)
+	}
+
 	return formatted, nil
 }
 
-func InsertColumnStatement(tableName string, desiredColumn *schemasv1alpha4.SQLTableColumn) (string, error) {
+func InsertColumnStatement(tableName string, desiredColumn *schemasv1alpha4.MysqlSQLTableColumn) (string, error) {
 	columnFields, err := mysqlColumnAsInsert(desiredColumn)
 	if err != nil {
 		return "", err
diff --git a/pkg/database/mysql/create.go b/pkg/database/mysql/create.go
index 08b67748..24784afe 100644
--- a/pkg/database/mysql/create.go
+++ b/pkg/database/mysql/create.go
@@ -7,7 +7,7 @@ import (
 	schemasv1alpha4 "github.com/schemahero/schemahero/pkg/apis/schemas/v1alpha4"
 )
 
-func CreateTableStatement(tableName string, tableSchema *schemasv1alpha4.SQLTableSchema) (string, error) {
+func CreateTableStatement(tableName string, tableSchema *schemasv1alpha4.MysqlSQLTableSchema) (string, error) {
 	columns := []string{}
 	for _, desiredColumn := range tableSchema.Columns {
 		columnFields, err := mysqlColumnAsInsert(desiredColumn)
@@ -35,5 +35,12 @@ func CreateTableStatement(tableName string, tableSchema *schemasv1alpha4.SQLTabl
 
 	query := fmt.Sprintf("create table `%s` (%s)", tableName, strings.Join(columns, ", "))
 
+	if tableSchema.DefaultCharset != "" {
+		query = fmt.Sprintf("%s default character set %s", query, tableSchema.DefaultCharset)
+	}
+	if tableSchema.Collation != "" {
+		query = fmt.Sprintf("%s collate %s", query, tableSchema.Collation)
+	}
+
 	return query, nil
 }
diff --git a/pkg/database/mysql/deploy.go b/pkg/database/mysql/deploy.go
index 52ba4c41..09bf0c01 100644
--- a/pkg/database/mysql/deploy.go
+++ b/pkg/database/mysql/deploy.go
@@ -11,7 +11,7 @@ import (
 	"github.com/schemahero/schemahero/pkg/database/types"
 )
 
-func PlanMysqlTable(uri string, tableName string, mysqlTableSchema *schemasv1alpha4.SQLTableSchema) ([]string, error) {
+func PlanMysqlTable(uri string, tableName string, mysqlTableSchema *schemasv1alpha4.MysqlSQLTableSchema) ([]string, error) {
 	m, err := Connect(uri)
 	if err != nil {
 		return nil, errors.Wrap(err, "failed to connect to mysql")
@@ -46,6 +46,13 @@ func PlanMysqlTable(uri string, tableName string, mysqlTableSchema *schemasv1alp
 
 	statements := []string{}
 
+	// first, if the table charset or collation changed, add
+	charsetAndCollationStatements, err := buildTableCharsetAndCollationStatements(m, tableName, mysqlTableSchema)
+	if err != nil {
+		return nil, errors.Wrap(err, "failed to build table charset and collation statements")
+	}
+	statements = append(statements, charsetAndCollationStatements...)
+
 	// table needs to be altered?
 	columnStatements, err := buildColumnStatements(m, tableName, mysqlTableSchema)
 	if err != nil {
@@ -106,7 +113,93 @@ func executeStatements(m *MysqlConnection, statements []string) error {
 	return nil
 }
 
-func buildColumnStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.SQLTableSchema) ([]string, error) {
+func buildTableCharsetAndCollationStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.MysqlSQLTableSchema) ([]string, error) {
+	query := `select 
+t.TABLE_COLLATION,
+c.character_set_name FROM information_schema.TABLES t,
+information_schema.COLLATION_CHARACTER_SET_APPLICABILITY c
+WHERE c.collation_name = t.table_collation
+AND t.table_schema = ?
+AND t.table_name = ?;`
+	row := m.db.QueryRow(query, m.databaseName, tableName)
+
+	var existingTableCollation, existingTableCharset string
+	if err := row.Scan(&existingTableCollation, &existingTableCharset); err != nil {
+		return nil, errors.Wrap(err, "failed to read existing table charset and collate")
+	}
+
+	// get the default for the database also
+	query = `SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA 
+WHERE schema_name = ?`
+	row = m.db.QueryRow(query, m.databaseName)
+
+	var databaseCollation, databaseCharset string
+	if err := row.Scan(&databaseCharset, &databaseCollation); err != nil {
+		return nil, errors.Wrap(err, "failed to read existing database charset and collate")
+	}
+
+	charsetMatches := false
+	collationMatches := false
+
+	if mysqlTableSchema.DefaultCharset == existingTableCharset {
+		charsetMatches = true
+	} else if mysqlTableSchema.DefaultCharset == "" && existingTableCharset == databaseCharset {
+		charsetMatches = true
+	}
+
+	if mysqlTableSchema.Collation == existingTableCollation {
+		collationMatches = true
+	}
+	if mysqlTableSchema.Collation == existingTableCollation {
+		collationMatches = true
+	}
+
+	if charsetMatches && collationMatches {
+		return []string{}, nil
+	}
+
+	if mysqlTableSchema.Collation == "" && mysqlTableSchema.DefaultCharset == "" {
+		if existingTableCollation == databaseCollation {
+			if existingTableCharset == databaseCharset {
+				return []string{}, nil
+			}
+		}
+	}
+
+	// fill in defaults where needed
+	if mysqlTableSchema.Collation == "" {
+		if mysqlTableSchema.DefaultCharset == "" {
+			mysqlTableSchema.Collation = databaseCollation
+			mysqlTableSchema.DefaultCharset = databaseCharset
+		} else {
+			// get the default collation for the charset
+			query = `select DEFAULT_COLLATE_NAME from information_schema.character_sets where CHARACTER_SET_NAME = ?`
+			row = m.db.QueryRow(query, mysqlTableSchema.DefaultCharset)
+			var defaultCollationForCharset string
+			if err := row.Scan(&defaultCollationForCharset); err != nil {
+				return nil, errors.Wrap(err, "failed to read default collation for charset")
+			}
+			mysqlTableSchema.Collation = defaultCollationForCharset
+		}
+	}
+	if mysqlTableSchema.DefaultCharset == "" {
+		// here the collation must have been set, but not the charset
+		// get the charset associated with the collation
+		query = `select CHARACTER_SET_NAME from information_schema.collations where COLLATION_NAME = ?`
+		row = m.db.QueryRow(query, mysqlTableSchema.Collation)
+		var collationCharset string
+		if err := row.Scan(&collationCharset); err != nil {
+			return nil, errors.Wrap(err, "failed to read charset for collation")
+		}
+		mysqlTableSchema.DefaultCharset = collationCharset
+	}
+
+	return []string{
+		fmt.Sprintf("alter table %s convert to character set %s collate %s", tableName, mysqlTableSchema.DefaultCharset, mysqlTableSchema.Collation),
+	}, nil
+}
+
+func buildColumnStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.MysqlSQLTableSchema) ([]string, error) {
 	query := `select
 COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, EXTRA, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH
 from information_schema.COLUMNS
@@ -121,6 +214,7 @@ where TABLE_NAME = ?`
 		var columnName, dataType, isNullable, extra string
 		var columnDefault sql.NullString
 		var charMaxLength sql.NullInt64
+		var charset, collation string
 
 		if err := rows.Scan(&columnName, &columnDefault, &isNullable, &extra, &dataType, &charMaxLength); err != nil {
 			return nil, errors.Wrap(err, "failed to scan")
@@ -149,6 +243,8 @@ where TABLE_NAME = ?`
 			DataType:    dataType,
 			Constraints: &types.ColumnConstraints{},
 			Attributes:  &types.ColumnAttributes{},
+			Charset:     charset,
+			Collation:   collation,
 		}
 
 		if isNullable == "NO" {
@@ -196,7 +292,7 @@ where TABLE_NAME = ?`
 	return alterAndDropStatements, nil
 }
 
-func buildPrimaryKeyStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.SQLTableSchema) ([]string, error) {
+func buildPrimaryKeyStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.MysqlSQLTableSchema) ([]string, error) {
 	currentPrimaryKey, err := m.GetTablePrimaryKey(tableName)
 	if err != nil {
 		return nil, err
@@ -231,7 +327,7 @@ func buildPrimaryKeyStatements(m *MysqlConnection, tableName string, mysqlTableS
 	return statements, nil
 }
 
-func buildForeignKeyStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.SQLTableSchema) ([]string, error) {
+func buildForeignKeyStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.MysqlSQLTableSchema) ([]string, error) {
 	foreignKeyStatements := []string{}
 	currentForeignKeys, err := m.ListTableForeignKeys(m.databaseName, tableName)
 	if err != nil {
@@ -279,7 +375,7 @@ func buildForeignKeyStatements(m *MysqlConnection, tableName string, mysqlTableS
 	return foreignKeyStatements, nil
 }
 
-func buildIndexStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.SQLTableSchema) ([]string, error) {
+func buildIndexStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.MysqlSQLTableSchema) ([]string, error) {
 	indexStatements := []string{}
 	currentIndexes, err := m.ListTableIndexes(m.databaseName, tableName)
 	if err != nil {
diff --git a/pkg/database/mysql/tables.go b/pkg/database/mysql/tables.go
index ff2475b9..fb373986 100644
--- a/pkg/database/mysql/tables.go
+++ b/pkg/database/mysql/tables.go
@@ -5,28 +5,54 @@ import (
 	"fmt"
 	"strings"
 
+	"github.com/pkg/errors"
 	"github.com/schemahero/schemahero/pkg/database/types"
 )
 
-func (m *MysqlConnection) ListTables() ([]string, error) {
-	query := "select table_name from information_schema.TABLES where TABLE_SCHEMA = ?"
+func (m *MysqlConnection) ListTables() ([]*types.Table, error) {
+	query := "select default_character_set_name, default_collation_name from information_schema.schemata where schema_name = ?"
+	row := m.db.QueryRow(query, m.databaseName)
+
+	var databaseDefaultCharset, databaseDefaultCollation string
+	if err := row.Scan(&databaseDefaultCharset, &databaseDefaultCollation); err != nil {
+		return nil, errors.Wrap(err, "failed to select database default charset and collection")
+	}
+
+	query = `select 
+t.table_name,
+t.TABLE_COLLATION,
+c.character_set_name FROM information_schema.TABLES t,
+information_schema.COLLATION_CHARACTER_SET_APPLICABILITY c
+WHERE c.collation_name = t.table_collation
+AND t.table_schema = ?`
 
 	rows, err := m.db.Query(query, m.databaseName)
 	if err != nil {
 		return nil, err
 	}
 
-	tableNames := make([]string, 0, 0)
+	tables := []*types.Table{}
 	for rows.Next() {
-		tableName := ""
-		if err := rows.Scan(&tableName); err != nil {
+		var tableName, tableCollation, tableCharset string
+		if err := rows.Scan(&tableName, &tableCollation, &tableCharset); err != nil {
 			return nil, err
 		}
 
-		tableNames = append(tableNames, tableName)
+		table := types.Table{
+			Name: tableName,
+		}
+
+		if tableCollation != databaseDefaultCollation {
+			table.Collation = tableCollation
+		}
+		if tableCharset != databaseDefaultCharset {
+			table.Charset = tableCharset
+		}
+
+		tables = append(tables, &table)
 	}
 
-	return tableNames, nil
+	return tables, nil
 }
 
 func (m *MysqlConnection) ListTableIndexes(databaseName string, tableName string) ([]*types.Index, error) {
diff --git a/pkg/database/postgres/tables.go b/pkg/database/postgres/tables.go
index d65ff1b8..f65e7f5a 100644
--- a/pkg/database/postgres/tables.go
+++ b/pkg/database/postgres/tables.go
@@ -14,7 +14,7 @@ var (
 	falseValue = false
 )
 
-func (p *PostgresConnection) ListTables() ([]string, error) {
+func (p *PostgresConnection) ListTables() ([]*types.Table, error) {
 	query := "select table_name from information_schema.tables where table_catalog = $1 and table_schema = $2"
 
 	rows, err := p.conn.Query(context.Background(), query, p.databaseName, "public")
@@ -22,17 +22,19 @@ func (p *PostgresConnection) ListTables() ([]string, error) {
 		return nil, errors.Wrap(err, "failed to list tables")
 	}
 
-	tableNames := make([]string, 0, 0)
+	tables := []*types.Table{}
 	for rows.Next() {
 		tableName := ""
 		if err := rows.Scan(&tableName); err != nil {
 			return nil, errors.Wrap(err, "failed to scan row")
 		}
 
-		tableNames = append(tableNames, tableName)
+		tables = append(tables, &types.Table{
+			Name: tableName,
+		})
 	}
 
-	return tableNames, nil
+	return tables, nil
 }
 
 func (p *PostgresConnection) ListTableConstraints(databaseName string, tableName string) ([]string, error) {
diff --git a/pkg/database/types/column.go b/pkg/database/types/column.go
index d6c758c3..02ce4cb6 100644
--- a/pkg/database/types/column.go
+++ b/pkg/database/types/column.go
@@ -26,6 +26,34 @@ type Column struct {
 	Constraints   *ColumnConstraints
 	Attributes    *ColumnAttributes
 	IsArray       bool
+	Charset       string
+	Collation     string
+}
+
+func ColumnToMysqlSchemaColumn(column *Column) (*schemasv1alpha4.MysqlSQLTableColumn, error) {
+	schemaColumn := &schemasv1alpha4.MysqlSQLTableColumn{
+		Name: column.Name,
+		Type: column.DataType,
+	}
+
+	if column.Constraints != nil {
+		schemaColumn.Constraints = &schemasv1alpha4.SQLTableColumnConstraints{
+			NotNull: column.Constraints.NotNull,
+		}
+	}
+
+	if column.Attributes != nil {
+		schemaColumn.Attributes = &schemasv1alpha4.SQLTableColumnAttributes{
+			AutoIncrement: column.Attributes.AutoIncrement,
+		}
+	}
+
+	schemaColumn.Default = column.ColumnDefault
+
+	schemaColumn.Charset = column.Charset
+	schemaColumn.Collation = column.Collation
+
+	return schemaColumn, nil
 }
 
 func ColumnToSchemaColumn(column *Column) (*schemasv1alpha4.SQLTableColumn, error) {
diff --git a/pkg/database/types/table.go b/pkg/database/types/table.go
new file mode 100644
index 00000000..3f7c5ee4
--- /dev/null
+++ b/pkg/database/types/table.go
@@ -0,0 +1,7 @@
+package types
+
+type Table struct {
+	Name      string
+	Charset   string
+	Collation string
+}
diff --git a/pkg/generate/generate.go b/pkg/generate/generate.go
index bc797185..5cf2840d 100644
--- a/pkg/generate/generate.go
+++ b/pkg/generate/generate.go
@@ -41,29 +41,29 @@ func (g *Generator) RunSync() error {
 		db = mysqlDb
 	}
 
-	tableNames, err := db.ListTables()
+	tables, err := db.ListTables()
 	if err != nil {
 		return errors.Wrap(err, "failed to list tables")
 	}
 
 	filesWritten := make([]string, 0, 0)
-	for _, tableName := range tableNames {
-		primaryKey, err := db.GetTablePrimaryKey(tableName)
+	for _, table := range tables {
+		primaryKey, err := db.GetTablePrimaryKey(table.Name)
 		if err != nil {
 			return errors.Wrap(err, "failed to get table primary key")
 		}
 
-		foreignKeys, err := db.ListTableForeignKeys(g.DBName, tableName)
+		foreignKeys, err := db.ListTableForeignKeys(g.DBName, table.Name)
 		if err != nil {
 			return errors.Wrap(err, "failed to list table foreign keys")
 		}
 
-		indexes, err := db.ListTableIndexes(g.DBName, tableName)
+		indexes, err := db.ListTableIndexes(g.DBName, table.Name)
 		if err != nil {
 			return errors.Wrap(err, "failed to list table indexes")
 		}
 
-		columns, err := db.GetTableSchema(tableName)
+		columns, err := db.GetTableSchema(table.Name)
 		if err != nil {
 			return errors.Wrap(err, "failed to get table schema")
 		}
@@ -72,7 +72,7 @@ func (g *Generator) RunSync() error {
 		if primaryKey != nil {
 			primaryKeyColumns = primaryKey.Columns
 		}
-		tableYAML, err := generateTableYAML(g.Driver, g.DBName, tableName, primaryKeyColumns, foreignKeys, indexes, columns)
+		tableYAML, err := generateTableYAML(g.Driver, g.DBName, table, primaryKeyColumns, foreignKeys, indexes, columns)
 		if err != nil {
 			return errors.Wrap(err, "failed to generate table yaml")
 		}
@@ -91,11 +91,11 @@ func (g *Generator) RunSync() error {
 
 		// If there was a outputdir set, write it, else print it
 		if g.OutputDir != "" {
-			if err := ioutil.WriteFile(filepath.Join(g.OutputDir, fmt.Sprintf("%s.yaml", sanitizeName(tableName))), []byte(tableYAML), 0644); err != nil {
+			if err := ioutil.WriteFile(filepath.Join(g.OutputDir, fmt.Sprintf("%s.yaml", sanitizeName(table.Name))), []byte(tableYAML), 0644); err != nil {
 				return err
 			}
 
-			filesWritten = append(filesWritten, fmt.Sprintf("./%s.yaml", sanitizeName(tableName)))
+			filesWritten = append(filesWritten, fmt.Sprintf("./%s.yaml", sanitizeName(table.Name)))
 		} else {
 
 			fmt.Println(tableYAML)
@@ -123,7 +123,82 @@ func (g *Generator) RunSync() error {
 	return nil
 }
 
-func generateTableYAML(driver string, dbName string, tableName string, primaryKey []string, foreignKeys []*types.ForeignKey, indexes []*types.Index, columns []*types.Column) (string, error) {
+func generateTableYAML(driver string, dbName string, table *types.Table, primaryKey []string, foreignKeys []*types.ForeignKey, indexes []*types.Index, columns []*types.Column) (string, error) {
+	if driver == "mysql" {
+		return generateMysqlTableYAML(dbName, table, primaryKey, foreignKeys, indexes, columns)
+	}
+
+	return generateGenericTableYAML(driver, dbName, table, primaryKey, foreignKeys, indexes, columns)
+}
+
+func generateMysqlTableYAML(dbName string, table *types.Table, primaryKey []string, foreignKeys []*types.ForeignKey, indexes []*types.Index, columns []*types.Column) (string, error) {
+	schemaForeignKeys := make([]*schemasv1alpha4.SQLTableForeignKey, 0, 0)
+	for _, foreignKey := range foreignKeys {
+		schemaForeignKey := types.ForeignKeyToSchemaForeignKey(foreignKey)
+		schemaForeignKeys = append(schemaForeignKeys, schemaForeignKey)
+	}
+
+	schemaIndexes := make([]*schemasv1alpha4.SQLTableIndex, 0, 0)
+	for _, index := range indexes {
+		schemaIndex := types.IndexToSchemaIndex(index)
+		schemaIndexes = append(schemaIndexes, schemaIndex)
+	}
+
+	schemaTableColumns := make([]*schemasv1alpha4.MysqlSQLTableColumn, 0, 0)
+	for _, column := range columns {
+		schemaTableColumn, err := types.ColumnToMysqlSchemaColumn(column)
+		if err != nil {
+			fmt.Printf("%#v\n", err)
+			return "", err
+		}
+
+		schemaTableColumns = append(schemaTableColumns, schemaTableColumn)
+	}
+
+	tableSchema := &schemasv1alpha4.MysqlSQLTableSchema{
+		PrimaryKey:     primaryKey,
+		Columns:        schemaTableColumns,
+		ForeignKeys:    schemaForeignKeys,
+		Indexes:        schemaIndexes,
+		DefaultCharset: table.Charset,
+		Collation:      table.Collation,
+	}
+
+	schema := &schemasv1alpha4.TableSchema{}
+
+	schema.Mysql = tableSchema
+
+	schemaHeroResource := schemasv1alpha4.TableSpec{
+		Database: dbName,
+		Name:     table.Name,
+		Requires: []string{},
+		Schema:   schema,
+	}
+
+	specDoc := struct {
+		Spec schemasv1alpha4.TableSpec `yaml:"spec"`
+	}{
+		schemaHeroResource,
+	}
+
+	b, err := yaml.Marshal(&specDoc)
+	if err != nil {
+		fmt.Printf("%#v\n", err)
+		return "", err
+	}
+
+	// TODO consider marshaling this instead of inline
+	tableDoc := fmt.Sprintf(`apiVersion: schemas.schemahero.io/v1alpha4
+kind: Table
+metadata:
+  name: %s
+%s`, sanitizeName(table.Name), b)
+
+	return tableDoc, nil
+
+}
+
+func generateGenericTableYAML(driver string, dbName string, table *types.Table, primaryKey []string, foreignKeys []*types.ForeignKey, indexes []*types.Index, columns []*types.Column) (string, error) {
 	schemaForeignKeys := make([]*schemasv1alpha4.SQLTableForeignKey, 0, 0)
 	for _, foreignKey := range foreignKeys {
 		schemaForeignKey := types.ForeignKeyToSchemaForeignKey(foreignKey)
@@ -158,13 +233,13 @@ func generateTableYAML(driver string, dbName string, tableName string, primaryKe
 
 	if driver == "postgres" {
 		schema.Postgres = tableSchema
-	} else if driver == "mysql" {
-		schema.Mysql = tableSchema
+	} else if driver == "cockroachdb" {
+		schema.CockroachDB = tableSchema
 	}
 
 	schemaHeroResource := schemasv1alpha4.TableSpec{
 		Database: dbName,
-		Name:     tableName,
+		Name:     table.Name,
 		Requires: []string{},
 		Schema:   schema,
 	}
@@ -186,7 +261,7 @@ func generateTableYAML(driver string, dbName string, tableName string, primaryKe
 kind: Table
 metadata:
   name: %s
-%s`, sanitizeName(tableName), b)
+%s`, sanitizeName(table.Name), b)
 
 	return tableDoc, nil
 
diff --git a/pkg/installer/database_objects.go b/pkg/installer/database_objects.go
index 0714d685..e87c7ccd 100644
--- a/pkg/installer/database_objects.go
+++ b/pkg/installer/database_objects.go
@@ -655,6 +655,8 @@ spec:
                   type: object
                 mysql:
                   properties:
+                    collation:
+                      type: string
                     dbname:
                       properties:
                         value:
@@ -742,6 +744,8 @@ spec:
                               type: object
                           type: object
                       type: object
+                    defaultCharset:
+                      type: string
                     disableTLS:
                       type: boolean
                     host:
@@ -2488,6 +2492,8 @@ spec:
                     type: object
                   mysql:
                     properties:
+                      collation:
+                        type: string
                       dbname:
                         properties:
                           value:
@@ -2575,6 +2581,8 @@ spec:
                                 type: object
                             type: object
                         type: object
+                      defaultCharset:
+                        type: string
                       disableTLS:
                         type: boolean
                       host:
diff --git a/pkg/installer/table_objects.go b/pkg/installer/table_objects.go
index 7d0d024c..30a19bf7 100644
--- a/pkg/installer/table_objects.go
+++ b/pkg/installer/table_objects.go
@@ -126,6 +126,8 @@ spec:
                   type: object
                 mysql:
                   properties:
+                    collation:
+                      type: string
                     columns:
                       items:
                         properties:
@@ -134,6 +136,10 @@ spec:
                               autoIncrement:
                                 type: boolean
                             type: object
+                          charset:
+                            type: string
+                          collation:
+                            type: string
                           constraints:
                             properties:
                               notNull:
@@ -150,6 +156,8 @@ spec:
                         - type
                         type: object
                       type: array
+                    defaultCharset:
+                      type: string
                     foreignKeys:
                       items:
                         properties:
@@ -437,6 +445,8 @@ spec:
                     type: object
                   mysql:
                     properties:
+                      collation:
+                        type: string
                       columns:
                         items:
                           properties:
@@ -445,6 +455,10 @@ spec:
                                 autoIncrement:
                                   type: boolean
                               type: object
+                            charset:
+                              type: string
+                            collation:
+                              type: string
                             constraints:
                               properties:
                                 notNull:
@@ -461,6 +475,8 @@ spec:
                           - type
                           type: object
                         type: array
+                      defaultCharset:
+                        type: string
                       foreignKeys:
                         items:
                           properties:


Test patch:
diff --git a/integration/tests/mysql/Makefile b/integration/tests/mysql/Makefile
index 7bc8d5d1..8d6b0194 100644
--- a/integration/tests/mysql/Makefile
+++ b/integration/tests/mysql/Makefile
@@ -28,6 +28,9 @@ run: 5.6.49 5.7.31 8.0.21
 	make -C auto-increment-create run
 	make -C auto-increment-add run
 	make -C auto-increment-drop run
+	make -C add-charset run
+	make -C add-collation run
+	make -C remove-charset run
 
 .PHONY: 5.7.31
 5.7.31: export MYSQL_VERSION = 5.7.31
@@ -50,7 +53,10 @@ run: 5.6.49 5.7.31 8.0.21
 	make -C auto-increment-create run
 	make -C auto-increment-add run
 	make -C auto-increment-drop run
-
+	make -C add-charset run
+	make -C add-collation run
+	make -C remove-charset run
+	
 .PHONY: 8.0.21
 8.0.21: export MYSQL_VERSION = 8.0.21
 8.0.21:
@@ -72,29 +78,10 @@ run: 5.6.49 5.7.31 8.0.21
 	make -C auto-increment-create run
 	make -C auto-increment-add run
 	make -C auto-increment-drop run
-
-
-.PHONY: 8.0
-8.0: export MYSQL_VERSION = 8.0
-8.0:
-	make -C decimal run
-	make -C medium-text run
-	make -C medium-blob run
-	make -C column-set-default run
-	make -C column-unset-default run
-	make -C create-table run
-	make -C foreign-key-create run
-	make -C foreign-key-action run
-	make -C foreign-key-drop run
-	make -C foreign-key-alter run
-	make -C not-null run
-	make -C index-create run
-	make -C primary-key-add run
-	make -C primary-key-drop run
-	make -C auto-increment-create run
-	make -C auto-increment-add run
-	make -C auto-increment-drop run
-
+	make -C add-charset run
+	make -C add-collation run
+	make -C remove-charset run
+	
 .PHONY: build
 build: docker-build
 	docker push $(IMAGE)
diff --git a/integration/tests/mysql/add-charset/Dockerfile b/integration/tests/mysql/add-charset/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/add-charset/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/add-charset/Makefile b/integration/tests/mysql/add-charset/Makefile
new file mode 100644
index 00000000..ef0960c6
--- /dev/null
+++ b/integration/tests/mysql/add-charset/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-add-charset
+SPEC_FILE := ./specs/projects.yaml
diff --git a/integration/tests/mysql/add-charset/expect.sql b/integration/tests/mysql/add-charset/expect.sql
new file mode 100644
index 00000000..2570c2ec
--- /dev/null
+++ b/integration/tests/mysql/add-charset/expect.sql
@@ -0,0 +1,2 @@
+alter table projects convert to character set utf16 collate utf16_general_ci;
+
diff --git a/integration/tests/mysql/add-charset/fixtures.sql b/integration/tests/mysql/add-charset/fixtures.sql
new file mode 100644
index 00000000..b91143c0
--- /dev/null
+++ b/integration/tests/mysql/add-charset/fixtures.sql
@@ -0,0 +1,8 @@
+create table users (
+  id integer primary key not null,
+  email varchar(255) not null
+);
+
+create table projects (
+  id integer primary key not null
+);
diff --git a/integration/tests/mysql/add-charset/specs/projects.yaml b/integration/tests/mysql/add-charset/specs/projects.yaml
new file mode 100644
index 00000000..9afd78a0
--- /dev/null
+++ b/integration/tests/mysql/add-charset/specs/projects.yaml
@@ -0,0 +1,11 @@
+database: schemahero
+name: projects
+schema:
+  mysql:
+    primaryKey: [id]
+    defaultCharset: utf16
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
diff --git a/integration/tests/mysql/add-collation/Dockerfile b/integration/tests/mysql/add-collation/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/add-collation/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/add-collation/Makefile b/integration/tests/mysql/add-collation/Makefile
new file mode 100644
index 00000000..29278e18
--- /dev/null
+++ b/integration/tests/mysql/add-collation/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-add-collation
+SPEC_FILE := ./specs/projects.yaml
diff --git a/integration/tests/mysql/add-collation/expect.sql b/integration/tests/mysql/add-collation/expect.sql
new file mode 100644
index 00000000..f01c063a
--- /dev/null
+++ b/integration/tests/mysql/add-collation/expect.sql
@@ -0,0 +1,1 @@
+alter table projects convert to character set utf16 collate utf16_german2_ci;
diff --git a/integration/tests/mysql/add-collation/fixtures.sql b/integration/tests/mysql/add-collation/fixtures.sql
new file mode 100644
index 00000000..b91143c0
--- /dev/null
+++ b/integration/tests/mysql/add-collation/fixtures.sql
@@ -0,0 +1,8 @@
+create table users (
+  id integer primary key not null,
+  email varchar(255) not null
+);
+
+create table projects (
+  id integer primary key not null
+);
diff --git a/integration/tests/mysql/add-collation/specs/projects.yaml b/integration/tests/mysql/add-collation/specs/projects.yaml
new file mode 100644
index 00000000..cfed2ebf
--- /dev/null
+++ b/integration/tests/mysql/add-collation/specs/projects.yaml
@@ -0,0 +1,11 @@
+database: schemahero
+name: projects
+schema:
+  mysql:
+    primaryKey: [id]
+    collation: utf16_german2_ci
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
diff --git a/integration/tests/mysql/remove-charset/Dockerfile b/integration/tests/mysql/remove-charset/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/remove-charset/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/remove-charset/Makefile b/integration/tests/mysql/remove-charset/Makefile
new file mode 100644
index 00000000..b798a470
--- /dev/null
+++ b/integration/tests/mysql/remove-charset/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-remove-charset
+SPEC_FILE := ./specs/projects.yaml
diff --git a/integration/tests/mysql/remove-charset/expect.sql b/integration/tests/mysql/remove-charset/expect.sql
new file mode 100644
index 00000000..5ca412e6
--- /dev/null
+++ b/integration/tests/mysql/remove-charset/expect.sql
@@ -0,0 +1,1 @@
+alter table projects convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
diff --git a/integration/tests/mysql/remove-charset/fixtures.sql b/integration/tests/mysql/remove-charset/fixtures.sql
new file mode 100644
index 00000000..f1ec3a7d
--- /dev/null
+++ b/integration/tests/mysql/remove-charset/fixtures.sql
@@ -0,0 +1,8 @@
+create table users (
+  id integer primary key not null,
+  email varchar(255) not null
+);
+
+create table projects (
+  id integer primary key not null
+) default charset=macroman;
diff --git a/integration/tests/mysql/remove-charset/specs/projects.yaml b/integration/tests/mysql/remove-charset/specs/projects.yaml
new file mode 100644
index 00000000..4f939f68
--- /dev/null
+++ b/integration/tests/mysql/remove-charset/specs/projects.yaml
@@ -0,0 +1,10 @@
+database: schemahero
+name: projects
+schema:
+  mysql:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
diff --git a/pkg/controller/table/reconcile_table_test.go b/pkg/controller/table/reconcile_table_test.go
index 165b46cf..f8b5ec93 100644
--- a/pkg/controller/table/reconcile_table_test.go
+++ b/pkg/controller/table/reconcile_table_test.go
@@ -39,7 +39,7 @@ func Test_checkDatabaseTypeMatches(t *testing.T) {
 				},
 			},
 			tableSchema: schemasv1alpha4.TableSchema{
-				Mysql: &schemasv1alpha4.SQLTableSchema{},
+				Mysql: &schemasv1alpha4.MysqlSQLTableSchema{},
 			},
 			expect: true,
 		},
@@ -67,7 +67,7 @@ func Test_checkDatabaseTypeMatches(t *testing.T) {
 				},
 			},
 			tableSchema: schemasv1alpha4.TableSchema{
-				Mysql: &schemasv1alpha4.SQLTableSchema{},
+				Mysql: &schemasv1alpha4.MysqlSQLTableSchema{},
 			},
 			expect: false,
 		},
diff --git a/pkg/database/mysql/alter_statements_test.go b/pkg/database/mysql/alter_statements_test.go
index 744441b2..5b29b87b 100644
--- a/pkg/database/mysql/alter_statements_test.go
+++ b/pkg/database/mysql/alter_statements_test.go
@@ -4,8 +4,52 @@ import (
 	"testing"
 
 	"github.com/schemahero/schemahero/pkg/database/types"
+	"github.com/stretchr/testify/assert"
 )
 
+func TestAlterDDL(t *testing.T) {
+	tests := []struct {
+		name           string
+		tableName      string
+		existingColumn types.Column
+		column         types.Column
+		expect         []string
+	}{
+		{
+			name:      "change charset and collation only",
+			tableName: "t",
+			existingColumn: types.Column{
+				Name:      "col",
+				DataType:  "datatype",
+				Charset:   "charset",
+				Collation: "collation",
+			},
+			column: types.Column{
+				Name:      "col",
+				DataType:  "datatype",
+				Charset:   "charset_new",
+				Collation: "collation_new",
+			},
+			expect: []string{
+				"alter table `t` modify column `col` datatype character set charset_new collate collation_new",
+			},
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			s := AlterModifyColumnStatement{
+				TableName:      tt.tableName,
+				Column:         tt.column,
+				ExistingColumn: tt.existingColumn,
+			}
+
+			actual := s.DDL()
+			assert.Equal(t, tt.expect, actual)
+		})
+	}
+}
+
 func TestAlterAddConstrantStatement_String(t *testing.T) {
 	tests := []struct {
 		name       string
diff --git a/pkg/database/mysql/alter_test.go b/pkg/database/mysql/alter_test.go
index 924bf8de..4e027eec 100644
--- a/pkg/database/mysql/alter_test.go
+++ b/pkg/database/mysql/alter_test.go
@@ -17,14 +17,14 @@ func Test_AlterColumnStatment(t *testing.T) {
 	tests := []struct {
 		name               string
 		tableName          string
-		desiredColumns     []*schemasv1alpha4.SQLTableColumn
+		desiredColumns     []*schemasv1alpha4.MysqlSQLTableColumn
 		existingColumn     *types.Column
 		expectedStatements []string
 	}{
 		{
 			name:      "no change",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -44,7 +44,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "change data type",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -64,7 +64,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "drop column",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -80,7 +80,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "add not null constraint",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -102,7 +102,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "drop not null constraint",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -124,7 +124,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "no change to not null constraint",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "t",
 					Type: "text",
@@ -143,7 +143,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "type change, constraint no change",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -165,7 +165,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "default set",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name:    "a",
 					Type:    "integer",
@@ -181,7 +181,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "default unset",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "a",
 					Type: "integer",
@@ -197,7 +197,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "default empty string",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name:    "a",
 					Type:    "varchar (32)",
@@ -214,8 +214,8 @@ func Test_AlterColumnStatment(t *testing.T) {
 		// {
 		// 	name:      "no change to not nullable timestamp using short column type",
 		// 	tableName: "ts",
-		// 	desiredColumns: []*schemasv1alpha4.SQLTableColumn{
-		// 		&schemasv1alpha4.SQLTableColumn{
+		// 	desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
+		// 		&schemasv1alpha4.MysqlSQLTableColumn{
 		// 			Name: "ts",
 		// 			Type: "timestamp",
 		// 			Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
@@ -236,8 +236,8 @@ func Test_AlterColumnStatment(t *testing.T) {
 		// {
 		// 	name:      "no change to not nullable timestamp",
 		// 	tableName: "ts",
-		// 	desiredColumns: []*schemasv1alpha4.SQLTableColumn{
-		// 		&schemasv1alpha4.SQLTableColumn{
+		// 	desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
+		// 		&schemasv1alpha4.MysqlSQLTableColumn{
 		// 			Name: "ts",
 		// 			Type: "timestamp with time zone",
 		// 			Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
@@ -258,7 +258,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "add null and default",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name:    "a",
 					Type:    "varchar (255)",
@@ -281,7 +281,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "add auto_increment attibute",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "c",
 					Type: "integer",
@@ -307,7 +307,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 		{
 			name:      "drop auto_increment attibute",
 			tableName: "t",
-			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+			desiredColumns: []*schemasv1alpha4.MysqlSQLTableColumn{
 				{
 					Name: "c",
 					Type: "integer",
diff --git a/pkg/database/mysql/column_test.go b/pkg/database/mysql/column_test.go
index 421e1726..f8d926c8 100644
--- a/pkg/database/mysql/column_test.go
+++ b/pkg/database/mysql/column_test.go
@@ -14,12 +14,12 @@ func Test_mysqlColumnAsInsert(t *testing.T) {
 	default11 := "11"
 	tests := []struct {
 		name              string
-		column            *schemasv1alpha4.SQLTableColumn
+		column            *schemasv1alpha4.MysqlSQLTableColumn
 		expectedStatement string
 	}{
 		{
 			name: "simple",
-			column: &schemasv1alpha4.SQLTableColumn{
+			column: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "c",
 				Type: "integer",
 				Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
@@ -31,7 +31,7 @@ func Test_mysqlColumnAsInsert(t *testing.T) {
 		},
 		{
 			name: "auto_increment",
-			column: &schemasv1alpha4.SQLTableColumn{
+			column: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "c",
 				Type: "integer",
 				Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
@@ -43,6 +43,16 @@ func Test_mysqlColumnAsInsert(t *testing.T) {
 			},
 			expectedStatement: "`c` int (11) not null auto_increment",
 		},
+		{
+			name: "charset and collation",
+			column: &schemasv1alpha4.MysqlSQLTableColumn{
+				Name:      "c",
+				Type:      "varchar(255)",
+				Charset:   "latin1",
+				Collation: "latin1_danish_ci",
+			},
+			expectedStatement: "`c` varchar (255) character set latin1 collate latin1_danish_ci",
+		},
 	}
 
 	for _, test := range tests {
@@ -60,13 +70,13 @@ func Test_InsertColumnStatement(t *testing.T) {
 	tests := []struct {
 		name              string
 		tableName         string
-		desiredColumn     *schemasv1alpha4.SQLTableColumn
+		desiredColumn     *schemasv1alpha4.MysqlSQLTableColumn
 		expectedStatement string
 	}{
 		{
 			name:      "add column",
 			tableName: "t",
-			desiredColumn: &schemasv1alpha4.SQLTableColumn{
+			desiredColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "a",
 				Type: "integer",
 			},
@@ -75,7 +85,7 @@ func Test_InsertColumnStatement(t *testing.T) {
 		{
 			name:      "add not null column",
 			tableName: "t",
-			desiredColumn: &schemasv1alpha4.SQLTableColumn{
+			desiredColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "a",
 				Type: "integer",
 				Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
@@ -87,7 +97,7 @@ func Test_InsertColumnStatement(t *testing.T) {
 		{
 			name:      "add null column",
 			tableName: "t",
-			desiredColumn: &schemasv1alpha4.SQLTableColumn{
+			desiredColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "a",
 				Type: "integer",
 				Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
@@ -99,7 +109,7 @@ func Test_InsertColumnStatement(t *testing.T) {
 		{
 			name:      "add auto_increment column",
 			tableName: "t",
-			desiredColumn: &schemasv1alpha4.SQLTableColumn{
+			desiredColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "a",
 				Type: "integer",
 				Attributes: &schemasv1alpha4.SQLTableColumnAttributes{
@@ -124,12 +134,12 @@ func Test_InsertColumnStatement(t *testing.T) {
 func Test_schemaColumnToMysqlColumn(t *testing.T) {
 	tests := []struct {
 		name           string
-		schemaColumn   *schemasv1alpha4.SQLTableColumn
+		schemaColumn   *schemasv1alpha4.MysqlSQLTableColumn
 		expectedColumn *types.Column
 	}{
 		{
 			name: "varchar (10)",
-			schemaColumn: &schemasv1alpha4.SQLTableColumn{
+			schemaColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "vc",
 				Type: "varchar (10)",
 			},
@@ -141,7 +151,7 @@ func Test_schemaColumnToMysqlColumn(t *testing.T) {
 		},
 		{
 			name: "bool",
-			schemaColumn: &schemasv1alpha4.SQLTableColumn{
+			schemaColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "b",
 				Type: "bool",
 			},
@@ -153,7 +163,7 @@ func Test_schemaColumnToMysqlColumn(t *testing.T) {
 		},
 		{
 			name: "auto_increment",
-			schemaColumn: &schemasv1alpha4.SQLTableColumn{
+			schemaColumn: &schemasv1alpha4.MysqlSQLTableColumn{
 				Name: "c",
 				Type: "integer (11)",
 				Attributes: &schemasv1alpha4.SQLTableColumnAttributes{
diff --git a/pkg/database/mysql/create_test.go b/pkg/database/mysql/create_test.go
index 2fe2b85b..549b6c7d 100644
--- a/pkg/database/mysql/create_test.go
+++ b/pkg/database/mysql/create_test.go
@@ -12,18 +12,18 @@ import (
 func Test_CreateTableStatement(t *testing.T) {
 	tests := []struct {
 		name              string
-		tableSchema       *schemasv1alpha4.SQLTableSchema
+		tableSchema       *schemasv1alpha4.MysqlSQLTableSchema
 		tableName         string
 		expectedStatement string
 	}{
 		{
 			name: "simple",
-			tableSchema: &schemasv1alpha4.SQLTableSchema{
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
 				PrimaryKey: []string{
 					"id",
 				},
-				Columns: []*schemasv1alpha4.SQLTableColumn{
-					&schemasv1alpha4.SQLTableColumn{
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
 						Name: "id",
 						Type: "integer",
 					},
@@ -34,17 +34,17 @@ func Test_CreateTableStatement(t *testing.T) {
 		},
 		{
 			name: "varchar composite primary key",
-			tableSchema: &schemasv1alpha4.SQLTableSchema{
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
 				PrimaryKey: []string{
 					"col_a",
 					"col_b",
 				},
-				Columns: []*schemasv1alpha4.SQLTableColumn{
-					&schemasv1alpha4.SQLTableColumn{
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
 						Name: "col_a",
 						Type: "char (36)",
 					},
-					&schemasv1alpha4.SQLTableColumn{
+					{
 						Name: "col_b",
 						Type: "varchar (255)",
 					},
@@ -55,21 +55,21 @@ func Test_CreateTableStatement(t *testing.T) {
 		},
 		{
 			name: "composite primary key",
-			tableSchema: &schemasv1alpha4.SQLTableSchema{
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
 				PrimaryKey: []string{
 					"one",
 					"two",
 				},
-				Columns: []*schemasv1alpha4.SQLTableColumn{
-					&schemasv1alpha4.SQLTableColumn{
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
 						Name: "one",
 						Type: "integer",
 					},
-					&schemasv1alpha4.SQLTableColumn{
+					{
 						Name: "two",
 						Type: "integer",
 					},
-					&schemasv1alpha4.SQLTableColumn{
+					{
 						Name: "three",
 						Type: "varchar(255)",
 					},
@@ -80,16 +80,16 @@ func Test_CreateTableStatement(t *testing.T) {
 		},
 		{
 			name: "decimal (8, 2) column",
-			tableSchema: &schemasv1alpha4.SQLTableSchema{
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
 				PrimaryKey: []string{
 					"one",
 				},
-				Columns: []*schemasv1alpha4.SQLTableColumn{
-					&schemasv1alpha4.SQLTableColumn{
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
 						Name: "one",
 						Type: "integer",
 					},
-					&schemasv1alpha4.SQLTableColumn{
+					{
 						Name: "bee",
 						Type: "decimal (8, 2)",
 					},
@@ -98,6 +98,58 @@ func Test_CreateTableStatement(t *testing.T) {
 			tableName:         "decimal_8_2",
 			expectedStatement: "create table `decimal_8_2` (`one` int (11), `bee` decimal (8, 2), primary key (`one`))",
 		},
+		{
+			name: "table with default charset and collate",
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
+				PrimaryKey: []string{
+					"id",
+				},
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
+						Name: "id",
+						Type: "integer",
+					},
+				},
+				DefaultCharset: "latin1",
+				Collation:      "latin1_german1_ci",
+			},
+			tableName:         "test",
+			expectedStatement: "create table `test` (`id` int (11), primary key (`id`)) default character set latin1 collate latin1_german1_ci",
+		},
+		{
+			name: "table with default charset without collate",
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
+				PrimaryKey: []string{
+					"id",
+				},
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
+						Name: "id",
+						Type: "integer",
+					},
+				},
+				DefaultCharset: "latin1",
+			},
+			tableName:         "test",
+			expectedStatement: "create table `test` (`id` int (11), primary key (`id`)) default character set latin1",
+		},
+		{
+			name: "table with collate and no default character set",
+			tableSchema: &schemasv1alpha4.MysqlSQLTableSchema{
+				PrimaryKey: []string{
+					"id",
+				},
+				Columns: []*schemasv1alpha4.MysqlSQLTableColumn{
+					{
+						Name: "id",
+						Type: "integer",
+					},
+				},
+				Collation: "latin1_german1_ci",
+			},
+			tableName:         "test",
+			expectedStatement: "create table `test` (`id` int (11), primary key (`id`)) collate latin1_german1_ci",
+		},
 	}
 
 	for _, test := range tests {
diff --git a/pkg/generate/generate_test.go b/pkg/generate/generate_test.go
index 934fb659..5282586e 100644
--- a/pkg/generate/generate_test.go
+++ b/pkg/generate/generate_test.go
@@ -31,7 +31,7 @@ func Test_writeTableFile(t *testing.T) {
 		name         string
 		driver       string
 		dbName       string
-		tableName    string
+		table        types.Table
 		primaryKey   []string
 		foreignKeys  []*types.ForeignKey
 		indexes      []*types.Index
@@ -39,15 +39,17 @@ func Test_writeTableFile(t *testing.T) {
 		expectedYAML string
 	}{
 		{
-			name:        "pg 1 col",
-			driver:      "postgres",
-			dbName:      "db",
-			tableName:   "simple",
+			name:   "pg 1 col",
+			driver: "postgres",
+			dbName: "db",
+			table: types.Table{
+				Name: "simple",
+			},
 			primaryKey:  []string{"one"},
 			foreignKeys: []*types.ForeignKey{},
 			indexes:     []*types.Index{},
 			columns: []*types.Column{
-				&types.Column{
+				{
 					Name:     "id",
 					DataType: "integer",
 				},
@@ -69,13 +71,15 @@ spec:
 `,
 		},
 		{
-			name:       "pg foreign key",
-			driver:     "postgres",
-			dbName:     "db",
-			tableName:  "withfk",
+			name:   "pg foreign key",
+			driver: "postgres",
+			dbName: "db",
+			table: types.Table{
+				Name: "withfk",
+			},
 			primaryKey: []string{"pk"},
 			foreignKeys: []*types.ForeignKey{
-				&types.ForeignKey{
+				{
 					ChildColumns:  []string{"cc"},
 					ParentTable:   "p",
 					ParentColumns: []string{"pc"},
@@ -84,11 +88,11 @@ spec:
 			},
 			indexes: []*types.Index{},
 			columns: []*types.Column{
-				&types.Column{
+				{
 					Name:     "pk",
 					DataType: "integer",
 				},
-				&types.Column{
+				{
 					Name:     "cc",
 					DataType: "integer",
 				},
@@ -120,25 +124,27 @@ spec:
 `,
 		},
 		{
-			name:        "generating with index",
-			driver:      "postgres",
-			dbName:      "db",
-			tableName:   "simple",
+			name:   "generating with index",
+			driver: "postgres",
+			dbName: "db",
+			table: types.Table{
+				Name: "simple",
+			},
 			primaryKey:  []string{"id"},
 			foreignKeys: []*types.ForeignKey{},
 			indexes: []*types.Index{
-				&types.Index{
+				{
 					Columns:  []string{"other"},
 					Name:     "idx_simple_other",
 					IsUnique: true,
 				},
 			},
 			columns: []*types.Column{
-				&types.Column{
+				{
 					Name:     "id",
 					DataType: "integer",
 				},
-				&types.Column{
+				{
 					Name:     "other",
 					DataType: "varchar (255)",
 					Constraints: &types.ColumnConstraints{
@@ -172,13 +178,15 @@ spec:
 `,
 		},
 		{
-			name:       "generating with auto_increment",
-			driver:     "mysql",
-			dbName:     "db",
-			tableName:  "simple",
+			name:   "generating with auto_increment",
+			driver: "mysql",
+			dbName: "db",
+			table: types.Table{
+				Name: "simple",
+			},
 			primaryKey: []string{"id"},
 			columns: []*types.Column{
-				&types.Column{
+				{
 					Name:     "id",
 					DataType: "integer",
 					Attributes: &types.ColumnAttributes{
@@ -210,7 +218,7 @@ spec:
 		t.Run(test.name, func(t *testing.T) {
 			req := require.New(t)
 
-			y, err := generateTableYAML(test.driver, test.dbName, test.tableName, test.primaryKey, test.foreignKeys, test.indexes, test.columns)
+			y, err := generateTableYAML(test.driver, test.dbName, &test.table, test.primaryKey, test.foreignKeys, test.indexes, test.columns)
 			req.NoError(err)
 			assert.Equal(t, test.expectedYAML, y)
 		})