Repository: schemahero/schemahero
Pull request #155 (2020-04-11 16:38:45)

Apply null with defaults
This PR addresses #114 

If a not null constraint is added at the same time as a default, include a command to update the existing data before applying the DDL statement to add the constraint to the table.

- [x] Add support in postgres driver
- [x] Add a postgres integration test
- [x] Add support in mysql driver
- [x] Add a mysql integration test


Patch:
diff --git a/config/dev/github/mysql.yaml b/config/dev/github/mysql.yaml
index 03199678..88faa7bc 100644
--- a/config/dev/github/mysql.yaml
+++ b/config/dev/github/mysql.yaml
@@ -1,12 +1,12 @@
-apiVersion: databases.schemahero.io/v1alpha2
+apiVersion: databases.schemahero.io/v1alpha3
 kind: Database
 metadata:
-  labels:
-    controller-tools.k8s.io: "1.0"
   name: testdb
-schemahero:
-  image: "localhost:32000/schemahero/schemahero"
-connection:
-  mysql:
-    uri:
-      value: testuser:password@tcp(mysql:3306)/testdb?tls=false
+spec:
+  schemahero:
+    image: "localhost:32000/schemahero/schemahero"
+  immediateDeploy: true
+  connection:
+    mysql:
+      uri:
+        value: testuser:password@tcp(mysql:3306)/testdb?tls=false
diff --git a/config/dev/github/users-table-mysql.yaml b/config/dev/github/users-table-mysql.yaml
index cdc07d87..b1d5f55d 100644
--- a/config/dev/github/users-table-mysql.yaml
+++ b/config/dev/github/users-table-mysql.yaml
@@ -1,13 +1,10 @@
-apiVersion: schemas.schemahero.io/v1alpha2
+apiVersion: schemas.schemahero.io/v1alpha3
 kind: Table
 metadata:
-  labels:
-    controller-tools.k8s.io: "1.0"
   name: users
 spec:
   database: testdb
   name: users
-  requires: []
   schema:
     mysql:
       primaryKey: [id]
@@ -18,3 +15,8 @@ spec:
           type: varchar(255)
         - name: name
           type: varchar(255)
+        - name: description
+          type: varchar(255)
+          default: "unset"
+          constraints:
+            notNull: true
diff --git a/config/dev/github/users-table-pg.yaml b/config/dev/github/users-table-pg.yaml
index 52e168c3..6ec30cbd 100644
--- a/config/dev/github/users-table-pg.yaml
+++ b/config/dev/github/users-table-pg.yaml
@@ -17,3 +17,6 @@ spec:
           type: varchar(255)
         - name: description
           type: text
+          default: "nothing"
+          constraints:
+            notNull: true
diff --git a/go.mod b/go.mod
index 2b266b27..05a877e1 100644
--- a/go.mod
+++ b/go.mod
@@ -23,7 +23,7 @@ require (
 	k8s.io/apimachinery v0.18.0
 	k8s.io/cli-runtime v0.18.0
 	k8s.io/client-go v0.18.0
-	k8s.io/code-generator v0.18.1-beta.0 // indirect
+	k8s.io/code-generator v0.18.2-beta.0 // indirect
 	sigs.k8s.io/controller-runtime v0.5.1-0.20200402191424-df180accb901
 	sigs.k8s.io/controller-tools v0.2.8 // indirect
 	sigs.k8s.io/structured-merge-diff v1.0.1-0.20191108220359-b1b620dd3f06 // indirect
diff --git a/go.sum b/go.sum
index 506ba141..942a6c64 100644
--- a/go.sum
+++ b/go.sum
@@ -534,6 +534,8 @@ k8s.io/code-generator v0.18.0 h1:0xIRWzym+qMgVpGmLESDeMfz/orwgxwxFFAo1xfGNtQ=
 k8s.io/code-generator v0.18.0/go.mod h1:+UHX5rSbxmR8kzS+FAv7um6dtYrZokQvjHpDSYRVkTc=
 k8s.io/code-generator v0.18.1-beta.0 h1:T87V5TNhNuceelOI55/0jTuNqinWyEyjcHvCescogzA=
 k8s.io/code-generator v0.18.1-beta.0/go.mod h1:+UHX5rSbxmR8kzS+FAv7um6dtYrZokQvjHpDSYRVkTc=
+k8s.io/code-generator v0.18.2-beta.0 h1:+kftU0GtjLbXJ1q3xK5CnQ0/795Ew6zT41xkBAuCVYw=
+k8s.io/code-generator v0.18.2-beta.0/go.mod h1:+UHX5rSbxmR8kzS+FAv7um6dtYrZokQvjHpDSYRVkTc=
 k8s.io/component-base v0.17.0/go.mod h1:rKuRAokNMY2nn2A6LP/MiwpoaMRHpfRnrPaUJJj1Yoc=
 k8s.io/component-base v0.17.2/go.mod h1:zMPW3g5aH7cHJpKYQ/ZsGMcgbsA/VyhEugF3QT1awLs=
 k8s.io/component-base v0.18.0/go.mod h1:u3BCg0z1uskkzrnAKFzulmYaEpZF7XC9Pf/uFyb1v2c=
diff --git a/integration/Makefile b/integration/Makefile
index 6a219066..93ef0cd3 100644
--- a/integration/Makefile
+++ b/integration/Makefile
@@ -17,6 +17,7 @@ postgres:
 	make -C tests/postgres/foreign-key-drop run
 	make -C tests/postgres/foreign-key-alter run
 	make -C tests/postgres/not-null run
+	make -C tests/postgres/not-null-with-default run
 	make -C tests/postgres/index-create run
 	make -C tests/postgres/primary-key-add run
 	make -C tests/postgres/primary-key-drop run
diff --git a/pkg/database/mysql/alter.go b/pkg/database/mysql/alter.go
index b1d73622..db5403c2 100644
--- a/pkg/database/mysql/alter.go
+++ b/pkg/database/mysql/alter.go
@@ -5,13 +5,13 @@ import (
 	"github.com/schemahero/schemahero/pkg/database/types"
 )
 
-func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns []*schemasv1alpha3.SQLTableColumn, existingColumn *types.Column) (string, error) {
+func AlterColumnStatements(tableName string, primaryKeys []string, desiredColumns []*schemasv1alpha3.SQLTableColumn, existingColumn *types.Column) ([]string, error) {
 	// this could be an alter or a drop column command
 	for _, desiredColumn := range desiredColumns {
 		if desiredColumn.Name == existingColumn.Name {
 			column, err := schemaColumnToColumn(desiredColumn)
 			if err != nil {
-				return "", err
+				return nil, err
 			}
 
 			isPrimaryKey := false
@@ -27,13 +27,14 @@ func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns
 			}
 
 			if columnsMatch(existingColumn, column) {
-				return "", nil
+				return []string{}, nil
 			}
 
 			return AlterModifyColumnStatement{
-				TableName: tableName,
-				Column:    *column,
-			}.String(), nil
+				TableName:      tableName,
+				ExistingColumn: *existingColumn,
+				Column:         *column,
+			}.DDL(), nil
 		}
 	}
 
@@ -41,7 +42,7 @@ func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns
 	return AlterDropColumnStatement{
 		TableName: tableName,
 		Column:    types.Column{Name: existingColumn.Name},
-	}.String(), nil
+	}.DDL(), nil
 }
 
 func columnsMatch(col1 *types.Column, col2 *types.Column) bool {
diff --git a/pkg/database/mysql/alter_statements.go b/pkg/database/mysql/alter_statements.go
index e7272408..e584119a 100644
--- a/pkg/database/mysql/alter_statements.go
+++ b/pkg/database/mysql/alter_statements.go
@@ -8,25 +8,91 @@ import (
 )
 
 type AlterModifyColumnStatement struct {
-	TableName string
-	Column    types.Column
+	TableName      string
+	ExistingColumn types.Column
+	Column         types.Column
 }
 
-func (s AlterModifyColumnStatement) String() string {
+func (s AlterModifyColumnStatement) DDL() []string {
+	isAddingNotNull := false
+	if s.Column.Constraints != nil && s.Column.Constraints.NotNull != nil && *s.Column.Constraints.NotNull == true {
+		if s.ExistingColumn.Constraints == nil {
+			isAddingNotNull = true
+		} else if s.ExistingColumn.Constraints.NotNull == nil {
+			isAddingNotNull = true
+		} else if *s.ExistingColumn.Constraints.NotNull == false {
+			isAddingNotNull = true
+		}
+	}
+
+	if isAddingNotNull {
+		return s.ddlWithNotNull()
+	}
+
+	// we will go ahead and apply the new constraints here because mysql is pretty flexible about
+	// letting you alter a table adding a not null constraint without applying a default
+	// so this will be familiar behavior for mysql users
+	return s.ddl(false)
+}
+
+// ddlIgnoringNotNull will NOT change the "nullability" of the colume
+func (s AlterModifyColumnStatement) ddl(useConstraintsFromExistingColumn bool) []string {
 	stmts := []string{
 		fmt.Sprintf("alter table `%s` modify column `%s` %s", s.TableName, s.Column.Name, s.Column.DataType),
 	}
-	if s.Column.Constraints != nil && s.Column.Constraints.NotNull != nil {
-		if *s.Column.Constraints.NotNull {
-			stmts = append(stmts, "not null")
-		} else {
-			stmts = append(stmts, "null")
+
+	if useConstraintsFromExistingColumn {
+		if s.ExistingColumn.Constraints != nil {
+			if *s.ExistingColumn.Constraints.NotNull {
+				stmts = append(stmts, "not null")
+			} else {
+				stmts = append(stmts, "null")
+			}
+		}
+	} else {
+		if s.Column.Constraints != nil {
+			if *s.Column.Constraints.NotNull {
+				stmts = append(stmts, "not null")
+			} else {
+				stmts = append(stmts, "null")
+			}
 		}
 	}
+
 	if s.Column.ColumnDefault != nil {
 		stmts = append(stmts, fmt.Sprintf("default \"%s\"", *s.Column.ColumnDefault))
 	}
-	return strings.Join(stmts, " ")
+	return []string{strings.Join(stmts, " ")}
+}
+
+func (s AlterModifyColumnStatement) ddlWithNotNull() []string {
+	// 1. update to add the default
+	// 2. update existing values with the default
+	// 3. update to set not null
+
+	statements := []string{}
+
+	// set the default (and change any types as necessary)
+	if s.Column.ColumnDefault != nil {
+		if s.ExistingColumn.ColumnDefault == nil || *s.ExistingColumn.ColumnDefault != *s.Column.ColumnDefault {
+			statements = append(statements, s.ddl(true)...)
+		}
+	}
+
+	// update existing values
+	if s.Column.ColumnDefault != nil {
+		localStatement := fmt.Sprintf("update `%s` set `%s`=%q where `%s` is null",
+			s.TableName,
+			s.Column.Name,
+			*s.Column.ColumnDefault,
+			s.Column.Name)
+		statements = append(statements, localStatement)
+	}
+
+	// update including the not null
+	statements = append(statements, s.ddl(false)...)
+
+	return statements
 }
 
 type AlterDropColumnStatement struct {
@@ -34,8 +100,10 @@ type AlterDropColumnStatement struct {
 	Column    types.Column
 }
 
-func (s AlterDropColumnStatement) String() string {
-	return fmt.Sprintf("alter table `%s` drop column `%s`", s.TableName, s.Column.Name)
+func (s AlterDropColumnStatement) DDL() []string {
+	return []string{
+		fmt.Sprintf("alter table `%s` drop column `%s`", s.TableName, s.Column.Name),
+	}
 }
 
 type AlterRemoveConstrantStatement struct {
diff --git a/pkg/database/mysql/deploy.go b/pkg/database/mysql/deploy.go
index 8591d3aa..d1075a2e 100644
--- a/pkg/database/mysql/deploy.go
+++ b/pkg/database/mysql/deploy.go
@@ -154,14 +154,12 @@ where TABLE_NAME = ?`
 			existingColumn.ColumnDefault = &columnDefault.String
 		}
 
-		columnStatement, err := AlterColumnStatement(tableName, mysqlTableSchema.PrimaryKey, mysqlTableSchema.Columns, &existingColumn)
+		columnStatement, err := AlterColumnStatements(tableName, mysqlTableSchema.PrimaryKey, mysqlTableSchema.Columns, &existingColumn)
 		if err != nil {
 			return nil, errors.Wrap(err, "failed to create alter column statement")
 		}
 
-		if columnStatement != "" {
-			alterAndDropStatements = append(alterAndDropStatements, columnStatement)
-		}
+		alterAndDropStatements = append(alterAndDropStatements, columnStatement...)
 	}
 
 	for _, desiredColumn := range mysqlTableSchema.Columns {
diff --git a/pkg/database/postgres/alter.go b/pkg/database/postgres/alter.go
index 34549557..ebb63127 100644
--- a/pkg/database/postgres/alter.go
+++ b/pkg/database/postgres/alter.go
@@ -10,7 +10,7 @@ import (
 	"github.com/schemahero/schemahero/pkg/database/types"
 )
 
-func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns []*schemasv1alpha3.SQLTableColumn, existingColumn *types.Column) (string, error) {
+func AlterColumnStatements(tableName string, primaryKeys []string, desiredColumns []*schemasv1alpha3.SQLTableColumn, existingColumn *types.Column) ([]string, error) {
 	alterStatement := fmt.Sprintf("alter column %s", pq.QuoteIdentifier(existingColumn.Name))
 
 	// this could be an alter or a drop column command
@@ -18,17 +18,64 @@ func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns
 		if desiredColumn.Name == existingColumn.Name {
 			column, err := schemaColumnToColumn(desiredColumn)
 			if err != nil {
-				return "", err
+				return nil, err
 			}
 
 			if columnsMatch(existingColumn, column) {
-				return "", nil
+				return []string{}, nil
 			}
 
-			if column.Name == "org_ids" {
-				fmt.Printf("existingColumn = %#v\n", existingColumn)
-				fmt.Printf("column = %#v\n", column)
+			// If the request is to modify a column to add a not null contraint to an existing column
+			// handle that part here
+			if column.Constraints != nil && column.Constraints.NotNull != nil && *column.Constraints.NotNull == true {
+				isAddingNotNull := false
+				if existingColumn.Constraints == nil {
+					isAddingNotNull = true
+				} else if existingColumn.Constraints.NotNull == nil {
+					isAddingNotNull = true
+				} else if *existingColumn.Constraints.NotNull == false {
+					isAddingNotNull = true
+				}
+
+				if isAddingNotNull {
+					// the best plan here is:
+					//   1. add default
+					//   2. update values with default
+					//   3. set not null
+
+					statements := []string{}
+
+					// add default
+					if column.ColumnDefault != nil {
+						if existingColumn.ColumnDefault == nil || *existingColumn.ColumnDefault != *column.ColumnDefault {
+							localStatement := fmt.Sprintf("alter table %s alter column %s set default '%s'",
+								pq.QuoteIdentifier(tableName),
+								pq.QuoteIdentifier(existingColumn.Name),
+								*column.ColumnDefault)
+							statements = append(statements, localStatement)
+						}
+					}
+
+					// update existing values
+					if column.ColumnDefault != nil {
+						localStatement := fmt.Sprintf("update %s set %s='%s' where %s is null",
+							pq.QuoteIdentifier(tableName),
+							pq.QuoteIdentifier(existingColumn.Name),
+							*column.ColumnDefault,
+							pq.QuoteIdentifier(existingColumn.Name))
+						statements = append(statements, localStatement)
+					}
+
+					// set not null
+					localStatement := fmt.Sprintf("alter table %s alter column %s set not null",
+						pq.QuoteIdentifier(tableName),
+						pq.QuoteIdentifier(existingColumn.Name))
+					statements = append(statements, localStatement)
+
+					return statements, nil
+				}
 			}
+
 			changes := []string{}
 			if existingColumn.DataType != column.DataType {
 				changes = append(changes, fmt.Sprintf("%s type %s", alterStatement, column.DataType))
@@ -48,15 +95,6 @@ func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns
 
 			// too much complexity below!
 			if column.Constraints != nil || existingColumn.Constraints != nil {
-				// Add not null
-				if column.Constraints != nil && column.Constraints.NotNull != nil && *column.Constraints.NotNull == true {
-					if existingColumn.Constraints != nil || existingColumn.Constraints.NotNull != nil {
-						if *existingColumn.Constraints.NotNull == false {
-							changes = append(changes, fmt.Sprintf("%s set not null", alterStatement))
-						}
-					}
-				}
-
 				isPrimaryKey := false
 				for _, primaryKey := range primaryKeys {
 					if column.Name == primaryKey {
@@ -65,7 +103,7 @@ func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns
 				}
 
 				if !isPrimaryKey {
-					if existingColumn.Constraints.NotNull != nil && *existingColumn.Constraints.NotNull == true {
+					if existingColumn.Constraints != nil && existingColumn.Constraints.NotNull != nil && *existingColumn.Constraints.NotNull == true {
 						if column.Constraints == nil || column.Constraints.NotNull == nil || *column.Constraints.NotNull == false {
 							changes = append(changes, fmt.Sprintf("%s drop not null", alterStatement))
 						}
@@ -75,14 +113,14 @@ func AlterColumnStatement(tableName string, primaryKeys []string, desiredColumns
 
 			if len(changes) == 0 {
 				// no changes
-				return "", nil
+				return []string{}, nil
 			}
 
-			return fmt.Sprintf(`alter table %s %s`, pq.QuoteIdentifier(tableName), strings.Join(changes, ", ")), nil
+			return []string{fmt.Sprintf(`alter table %s %s`, pq.QuoteIdentifier(tableName), strings.Join(changes, ", "))}, nil
 		}
 	}
 
-	return fmt.Sprintf(`alter table %s drop column %s`, pq.QuoteIdentifier(tableName), pq.QuoteIdentifier(existingColumn.Name)), nil
+	return []string{fmt.Sprintf(`alter table %s drop column %s`, pq.QuoteIdentifier(tableName), pq.QuoteIdentifier(existingColumn.Name))}, nil
 }
 
 func columnsMatch(col1 *types.Column, col2 *types.Column) bool {
diff --git a/pkg/database/postgres/deploy.go b/pkg/database/postgres/deploy.go
index ed5c8282..740cc054 100644
--- a/pkg/database/postgres/deploy.go
+++ b/pkg/database/postgres/deploy.go
@@ -154,14 +154,12 @@ where table_name = $1`
 			existingColumn.DataType = fmt.Sprintf("%s (%d)", existingColumn.DataType, charMaxLength.Int64)
 		}
 
-		columnStatement, err := AlterColumnStatement(tableName, postgresTableSchema.PrimaryKey, postgresTableSchema.Columns, &existingColumn)
+		columnStatement, err := AlterColumnStatements(tableName, postgresTableSchema.PrimaryKey, postgresTableSchema.Columns, &existingColumn)
 		if err != nil {
 			return nil, errors.Wrap(err, "failed to create alter column statement")
 		}
 
-		if columnStatement != "" {
-			alterAndDropStatements = append(alterAndDropStatements, columnStatement)
-		}
+		alterAndDropStatements = append(alterAndDropStatements, columnStatement...)
 	}
 
 	for _, desiredColumn := range postgresTableSchema.Columns {


Test patch:
diff --git a/integration/tests/mysql/column-set-default/expect.sql b/integration/tests/mysql/column-set-default/expect.sql
index 6452bbd9..4f736bf1 100644
--- a/integration/tests/mysql/column-set-default/expect.sql
+++ b/integration/tests/mysql/column-set-default/expect.sql
@@ -1,2 +1,6 @@
+alter table `users` modify column `account_type` varchar (10) null default "trial";
+update `users` set `account_type`="trial" where `account_type` is null;
 alter table `users` modify column `account_type` varchar (10) not null default "trial";
+alter table `users` modify column `num_seats` int (11) null default "5";
+update `users` set `num_seats`="5" where `num_seats` is null;
 alter table `users` modify column `num_seats` int (11) not null default "5";
diff --git a/integration/tests/mysql/not-null-with-default/Dockerfile b/integration/tests/mysql/not-null-with-default/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/not-null-with-default/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/not-null-with-default/Makefile b/integration/tests/mysql/not-null-with-default/Makefile
new file mode 100644
index 00000000..b330affb
--- /dev/null
+++ b/integration/tests/mysql/not-null-with-default/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-not-null
+SPEC_FILE := ./specs/projects.yaml
diff --git a/integration/tests/mysql/not-null-with-default/expect.sql b/integration/tests/mysql/not-null-with-default/expect.sql
new file mode 100644
index 00000000..79210a9b
--- /dev/null
+++ b/integration/tests/mysql/not-null-with-default/expect.sql
@@ -0,0 +1,3 @@
+alter table `projects` modify column `name` varchar (255) null default "name me";
+update `projects` set `name`="name me" where `name` is null;
+alter table `projects` modify column `name` varchar (255) not null default "name me";
diff --git a/integration/tests/mysql/not-null-with-default/fixtures.sql b/integration/tests/mysql/not-null-with-default/fixtures.sql
new file mode 100644
index 00000000..7bc4dfda
--- /dev/null
+++ b/integration/tests/mysql/not-null-with-default/fixtures.sql
@@ -0,0 +1,10 @@
+create table users (
+  id integer primary key not null,
+  email varchar(255) not null
+);
+
+create table projects (
+  id integer primary key not null,
+  name varchar(255),
+  icon_uri varchar(255)
+);
diff --git a/integration/tests/mysql/not-null-with-default/specs/projects.yaml b/integration/tests/mysql/not-null-with-default/specs/projects.yaml
new file mode 100644
index 00000000..97a14370
--- /dev/null
+++ b/integration/tests/mysql/not-null-with-default/specs/projects.yaml
@@ -0,0 +1,16 @@
+database: schemahero
+name: projects
+schema:
+  mysql:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+      - name: name
+        type: varchar(255)
+        default: "name me"
+        constraints:
+          notNull: true
+      - name: icon_uri
+        type: varchar(255)
+
diff --git a/integration/tests/postgres/column-set-default/expect.sql b/integration/tests/postgres/column-set-default/expect.sql
index 16bca831..ede1bb39 100644
--- a/integration/tests/postgres/column-set-default/expect.sql
+++ b/integration/tests/postgres/column-set-default/expect.sql
@@ -1,2 +1,2 @@
-alter table "users" alter column "account_type" set default 'trial', alter column "account_type" set not null;
-alter table "users" alter column "num_seats" set default '5', alter column "num_seats" set not null;
+alter table "users" alter column "account_type" set default 'trial';
+alter table "users" alter column "num_seats" set default '5';
diff --git a/integration/tests/postgres/column-set-default/specs/users.yaml b/integration/tests/postgres/column-set-default/specs/users.yaml
index 5f29e5b6..12c8ae8f 100644
--- a/integration/tests/postgres/column-set-default/specs/users.yaml
+++ b/integration/tests/postgres/column-set-default/specs/users.yaml
@@ -13,11 +13,7 @@ schema:
           notNull: true
       - name: account_type
         type: varchar(10)
-        constraints:
-          notNull: true
         default: trial
       - name: num_seats
         type: integer
-        constraints:
-          notNull: true
         default: "5"
diff --git a/integration/tests/postgres/not-null-with-default/Dockerfile b/integration/tests/postgres/not-null-with-default/Dockerfile
new file mode 100644
index 00000000..a36827ed
--- /dev/null
+++ b/integration/tests/postgres/not-null-with-default/Dockerfile
@@ -0,0 +1,8 @@
+FROM postgres:10.7
+
+ENV POSTGRES_USER=schemahero
+ENV POSTGRES_PASSWORD=password
+ENV POSTGRES_DB=schemahero
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/postgres/not-null-with-default/Makefile b/integration/tests/postgres/not-null-with-default/Makefile
new file mode 100644
index 00000000..bdf98804
--- /dev/null
+++ b/integration/tests/postgres/not-null-with-default/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := postgres-not-null-with-defaults
+SPEC_FILE := ./specs/projects.yaml
diff --git a/integration/tests/postgres/not-null-with-default/expect.sql b/integration/tests/postgres/not-null-with-default/expect.sql
new file mode 100644
index 00000000..03dbd200
--- /dev/null
+++ b/integration/tests/postgres/not-null-with-default/expect.sql
@@ -0,0 +1,3 @@
+alter table "projects" alter column "name" set default 'unnamed';
+update "projects" set "name"='unnamed' where "name" is null;
+alter table "projects" alter column "name" set not null;
diff --git a/integration/tests/postgres/not-null-with-default/fixtures.sql b/integration/tests/postgres/not-null-with-default/fixtures.sql
new file mode 100644
index 00000000..7bc4dfda
--- /dev/null
+++ b/integration/tests/postgres/not-null-with-default/fixtures.sql
@@ -0,0 +1,10 @@
+create table users (
+  id integer primary key not null,
+  email varchar(255) not null
+);
+
+create table projects (
+  id integer primary key not null,
+  name varchar(255),
+  icon_uri varchar(255)
+);
diff --git a/integration/tests/postgres/not-null-with-default/specs/projects.yaml b/integration/tests/postgres/not-null-with-default/specs/projects.yaml
new file mode 100644
index 00000000..7bf463a7
--- /dev/null
+++ b/integration/tests/postgres/not-null-with-default/specs/projects.yaml
@@ -0,0 +1,18 @@
+database: schemahero
+name: projects
+schema:
+  postgres:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
+      - name: name
+        type: varchar(255)
+        default: "unnamed"
+        constraints:
+          notNull: true
+      - name: icon_uri
+        type: varchar(255)
+
diff --git a/pkg/apis/databases/v1alpha3/v1alpha1_suite_test.go b/pkg/apis/databases/v1alpha3/v1alpha1_suite_test.go
deleted file mode 100644
index b4d3175e..00000000
--- a/pkg/apis/databases/v1alpha3/v1alpha1_suite_test.go
+++ /dev/null
@@ -1,55 +0,0 @@
-/*
-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 v1alpha3
-
-import (
-	"log"
-	"os"
-	"path/filepath"
-	"testing"
-
-	"k8s.io/client-go/kubernetes/scheme"
-	"k8s.io/client-go/rest"
-	"sigs.k8s.io/controller-runtime/pkg/client"
-	"sigs.k8s.io/controller-runtime/pkg/envtest"
-)
-
-var cfg *rest.Config
-var c client.Client
-
-func TestMain(m *testing.M) {
-	t := &envtest.Environment{
-		CRDDirectoryPaths: []string{filepath.Join("..", "..", "..", "..", "config", "crds")},
-	}
-
-	err := SchemeBuilder.AddToScheme(scheme.Scheme)
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	if cfg, err = t.Start(); err != nil {
-		log.Fatal(err)
-	}
-
-	if c, err = client.New(cfg, client.Options{Scheme: scheme.Scheme}); err != nil {
-		log.Fatal(err)
-	}
-
-	code := m.Run()
-	t.Stop()
-	os.Exit(code)
-}
diff --git a/pkg/apis/schemas/v1alpha3/migration_types_test.go b/pkg/apis/schemas/v1alpha3/migration_types_test.go
deleted file mode 100644
index 572ecc36..00000000
--- a/pkg/apis/schemas/v1alpha3/migration_types_test.go
+++ /dev/null
@@ -1,58 +0,0 @@
-/*
-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 v1alpha3
-
-import (
-	"testing"
-
-	"github.com/onsi/gomega"
-	"golang.org/x/net/context"
-	metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
-	"k8s.io/apimachinery/pkg/types"
-)
-
-func TestStorageMigration(t *testing.T) {
-	key := types.NamespacedName{
-		Name:      "foo",
-		Namespace: "default",
-	}
-	created := &Migration{
-		ObjectMeta: metav1.ObjectMeta{
-			Name:      "foo",
-			Namespace: "default",
-		}}
-	g := gomega.NewGomegaWithT(t)
-
-	// Test Create
-	fetched := &Migration{}
-	g.Expect(c.Create(context.TODO(), created)).NotTo(gomega.HaveOccurred())
-
-	g.Expect(c.Get(context.TODO(), key, fetched)).NotTo(gomega.HaveOccurred())
-	g.Expect(fetched).To(gomega.Equal(created))
-
-	// Test Updating the Labels
-	updated := fetched.DeepCopy()
-	updated.Labels = map[string]string{"hello": "world"}
-	g.Expect(c.Update(context.TODO(), updated)).NotTo(gomega.HaveOccurred())
-
-	g.Expect(c.Get(context.TODO(), key, fetched)).NotTo(gomega.HaveOccurred())
-	g.Expect(fetched).To(gomega.Equal(updated))
-
-	// Test Delete
-	g.Expect(c.Delete(context.TODO(), fetched)).NotTo(gomega.HaveOccurred())
-	g.Expect(c.Get(context.TODO(), key, fetched)).To(gomega.HaveOccurred())
-}
diff --git a/pkg/apis/schemas/v1alpha3/table_types_test.go b/pkg/apis/schemas/v1alpha3/table_types_test.go
deleted file mode 100644
index 58786a0f..00000000
--- a/pkg/apis/schemas/v1alpha3/table_types_test.go
+++ /dev/null
@@ -1,77 +0,0 @@
-/*
-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 v1alpha3
-
-import (
-	"testing"
-
-	"github.com/onsi/gomega"
-	"golang.org/x/net/context"
-	metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
-	"k8s.io/apimachinery/pkg/types"
-)
-
-func TestStorageTable(t *testing.T) {
-	key := types.NamespacedName{
-		Name:      "foo",
-		Namespace: "default",
-	}
-
-	column := SQLTableColumn{
-		Name: "c",
-		Type: "t",
-	}
-
-	created := &Table{
-		ObjectMeta: metav1.ObjectMeta{
-			Name:      "foo",
-			Namespace: "default",
-		},
-		Spec: TableSpec{
-			Database: "d",
-			Name:     "n",
-			Schema: &TableSchema{
-				Postgres: &SQLTableSchema{
-					PrimaryKey: []string{"pk"},
-					Columns: []*SQLTableColumn{
-						&column,
-					},
-				},
-			},
-		},
-	}
-	g := gomega.NewGomegaWithT(t)
-
-	// Test Create
-	fetched := &Table{}
-	g.Expect(c.Create(context.TODO(), created)).NotTo(gomega.HaveOccurred())
-
-	g.Expect(c.Get(context.TODO(), key, fetched)).NotTo(gomega.HaveOccurred())
-	g.Expect(fetched).To(gomega.Equal(created))
-
-	// Test Updating the Labels
-	updated := fetched.DeepCopy()
-	updated.Labels = map[string]string{"hello": "world"}
-	g.Expect(c.Update(context.TODO(), updated)).NotTo(gomega.HaveOccurred())
-
-	g.Expect(c.Get(context.TODO(), key, fetched)).NotTo(gomega.HaveOccurred())
-	g.Expect(fetched).To(gomega.Equal(updated))
-
-	// Test Delete
-	g.Expect(c.Delete(context.TODO(), fetched)).NotTo(gomega.HaveOccurred())
-	g.Expect(c.Get(context.TODO(), key, fetched)).To(gomega.HaveOccurred())
-}
diff --git a/pkg/apis/schemas/v1alpha3/v1alpha1_suite_test.go b/pkg/apis/schemas/v1alpha3/v1alpha1_suite_test.go
deleted file mode 100644
index b4d3175e..00000000
--- a/pkg/apis/schemas/v1alpha3/v1alpha1_suite_test.go
+++ /dev/null
@@ -1,55 +0,0 @@
-/*
-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 v1alpha3
-
-import (
-	"log"
-	"os"
-	"path/filepath"
-	"testing"
-
-	"k8s.io/client-go/kubernetes/scheme"
-	"k8s.io/client-go/rest"
-	"sigs.k8s.io/controller-runtime/pkg/client"
-	"sigs.k8s.io/controller-runtime/pkg/envtest"
-)
-
-var cfg *rest.Config
-var c client.Client
-
-func TestMain(m *testing.M) {
-	t := &envtest.Environment{
-		CRDDirectoryPaths: []string{filepath.Join("..", "..", "..", "..", "config", "crds")},
-	}
-
-	err := SchemeBuilder.AddToScheme(scheme.Scheme)
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	if cfg, err = t.Start(); err != nil {
-		log.Fatal(err)
-	}
-
-	if c, err = client.New(cfg, client.Options{Scheme: scheme.Scheme}); err != nil {
-		log.Fatal(err)
-	}
-
-	code := m.Run()
-	t.Stop()
-	os.Exit(code)
-}
diff --git a/pkg/controller/table/objects_test.go b/pkg/controller/table/objects_test.go
index 5fc46eb9..8935ef42 100644
--- a/pkg/controller/table/objects_test.go
+++ b/pkg/controller/table/objects_test.go
@@ -31,9 +31,7 @@ func Test_planConfigMap(t *testing.T) {
 			expect: `database: db
 name: name
 schema:
-  postgres:
-    primaryKey: []
-    columns: []
+  postgres: {}
 `,
 		},
 	}
diff --git a/pkg/database/mysql/alter_test.go b/pkg/database/mysql/alter_test.go
index ef58ee07..21abcf95 100644
--- a/pkg/database/mysql/alter_test.go
+++ b/pkg/database/mysql/alter_test.go
@@ -15,21 +15,21 @@ func Test_AlterColumnStatment(t *testing.T) {
 	defaultEmpty := ""
 
 	tests := []struct {
-		name              string
-		tableName         string
-		desiredColumns    []*schemasv1alpha3.SQLTableColumn
-		existingColumn    *types.Column
-		expectedStatement string
+		name               string
+		tableName          string
+		desiredColumns     []*schemasv1alpha3.SQLTableColumn
+		existingColumn     *types.Column
+		expectedStatements []string
 	}{
 		{
 			name:      "no change",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 				},
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "b",
 					Type: "integer",
 				},
@@ -39,17 +39,17 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "int (11)",
 				ColumnDefault: nil,
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "change data type",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 				},
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "b",
 					Type: "integer",
 				},
@@ -59,13 +59,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "varchar(255)",
 				ColumnDefault: nil,
 			},
-			expectedStatement: "alter table `t` modify column `b` int (11)",
+			expectedStatements: []string{"alter table `t` modify column `b` int (11)"},
 		},
 		{
 			name:      "drop column",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 				},
@@ -75,13 +75,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "varchar (255)",
 				ColumnDefault: nil,
 			},
-			expectedStatement: "alter table `t` drop column `b`",
+			expectedStatements: []string{"alter table `t` drop column `b`"},
 		},
 		{
 			name:      "add not null constraint",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 					Constraints: &schemasv1alpha3.SQLTableColumnConstraints{
@@ -97,13 +97,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &falseValue,
 				},
 			},
-			expectedStatement: "alter table `t` modify column `a` int (11) not null",
+			expectedStatements: []string{"alter table `t` modify column `a` int (11) not null"},
 		},
 		{
 			name:      "drop not null constraint",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 					Constraints: &schemasv1alpha3.SQLTableColumnConstraints{
@@ -119,13 +119,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &trueValue,
 				},
 			},
-			expectedStatement: "alter table `t` modify column `a` int (11) null",
+			expectedStatements: []string{"alter table `t` modify column `a` int (11) null"},
 		},
 		{
 			name:      "no change to not null constraint",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "t",
 					Type: "text",
 				},
@@ -138,13 +138,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &falseValue,
 				},
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "type change, constraint no change",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 					Constraints: &schemasv1alpha3.SQLTableColumnConstraints{
@@ -160,13 +160,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &trueValue,
 				},
 			},
-			expectedStatement: "alter table `t` modify column `a` int (11) not null",
+			expectedStatements: []string{"alter table `t` modify column `a` int (11) not null"},
 		},
 		{
 			name:      "default set",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name:    "a",
 					Type:    "integer",
 					Default: &defaultEleven,
@@ -176,13 +176,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 				Name:     "a",
 				DataType: "integer",
 			},
-			expectedStatement: "alter table `t` modify column `a` int (11) default \"11\"",
+			expectedStatements: []string{"alter table `t` modify column `a` int (11) default \"11\""},
 		},
 		{
 			name:      "default unset",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 				},
@@ -192,13 +192,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "integer",
 				ColumnDefault: &defaultEleven,
 			},
-			expectedStatement: "alter table `t` modify column `a` int (11)",
+			expectedStatements: []string{"alter table `t` modify column `a` int (11)"},
 		},
 		{
 			name:      "default empty string",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name:    "a",
 					Type:    "varchar (32)",
 					Default: &defaultEmpty,
@@ -208,8 +208,9 @@ func Test_AlterColumnStatment(t *testing.T) {
 				Name:     "a",
 				DataType: "varchar (32)",
 			},
-			expectedStatement: "alter table `t` modify column `a` varchar (32) default \"\"",
+			expectedStatements: []string{"alter table `t` modify column `a` varchar (32) default \"\""},
 		},
+
 		// {
 		// 	name:      "no change to not nullable timestamp using short column type",
 		// 	tableName: "ts",
@@ -254,15 +255,38 @@ func Test_AlterColumnStatment(t *testing.T) {
 		// 	},
 		// 	expectedStatement: "",
 		// },
+		{
+			name:      "add null and default",
+			tableName: "t",
+			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
+				{
+					Name:    "a",
+					Type:    "varchar (255)",
+					Default: &defaultEleven,
+					Constraints: &schemasv1alpha3.SQLTableColumnConstraints{
+						NotNull: &trueValue,
+					},
+				},
+			},
+			existingColumn: &types.Column{
+				Name:     "a",
+				DataType: "varchar (255)",
+			},
+			expectedStatements: []string{
+				"alter table `t` modify column `a` varchar (255) default \"11\"",
+				"update `t` set `a`=\"11\" where `a` is null",
+				"alter table `t` modify column `a` varchar (255) not null default \"11\"",
+			},
+		},
 	}
 
 	for _, test := range tests {
 		t.Run(test.name, func(t *testing.T) {
 			req := require.New(t)
 
-			generatedStatement, err := AlterColumnStatement(test.tableName, []string{}, test.desiredColumns, test.existingColumn)
+			generatedStatements, err := AlterColumnStatements(test.tableName, []string{}, test.desiredColumns, test.existingColumn)
 			req.NoError(err)
-			assert.Equal(t, test.expectedStatement, generatedStatement)
+			assert.Equal(t, test.expectedStatements, generatedStatements)
 		})
 	}
 }
diff --git a/pkg/database/postgres/alter_test.go b/pkg/database/postgres/alter_test.go
index c9659b0b..816912e4 100644
--- a/pkg/database/postgres/alter_test.go
+++ b/pkg/database/postgres/alter_test.go
@@ -10,16 +10,16 @@ import (
 	"github.com/stretchr/testify/require"
 )
 
-func Test_AlterColumnStatment(t *testing.T) {
+func Test_AlterColumnStatments(t *testing.T) {
 	defaultEleven := "11"
 	defaultEmpty := ""
 
 	tests := []struct {
-		name              string
-		tableName         string
-		desiredColumns    []*schemasv1alpha3.SQLTableColumn
-		existingColumn    *types.Column
-		expectedStatement string
+		name               string
+		tableName          string
+		desiredColumns     []*schemasv1alpha3.SQLTableColumn
+		existingColumn     *types.Column
+		expectedStatements []string
 	}{
 		{
 			name:      "no change",
@@ -39,7 +39,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "integer",
 				ColumnDefault: nil,
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "no change varchar",
@@ -54,7 +54,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 				Name:     "a",
 				DataType: "character varying (32)",
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "change data type",
@@ -74,7 +74,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "varchar(255)",
 				ColumnDefault: nil,
 			},
-			expectedStatement: `alter table "t" alter column "b" type integer`,
+			expectedStatements: []string{`alter table "t" alter column "b" type integer`},
 		},
 		{
 			name:      "drop column",
@@ -90,13 +90,13 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "varchar(255)",
 				ColumnDefault: nil,
 			},
-			expectedStatement: `alter table "t" drop column "b"`,
+			expectedStatements: []string{`alter table "t" drop column "b"`},
 		},
 		{
 			name:      "add not null constraint",
 			tableName: "t",
 			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
-				&schemasv1alpha3.SQLTableColumn{
+				{
 					Name: "a",
 					Type: "integer",
 					Constraints: &schemasv1alpha3.SQLTableColumnConstraints{
@@ -112,7 +112,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &falseValue,
 				},
 			},
-			expectedStatement: `alter table "t" alter column "a" set not null`,
+			expectedStatements: []string{`alter table "t" alter column "a" set not null`},
 		},
 		{
 			name:      "drop not null constraint",
@@ -134,7 +134,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &trueValue,
 				},
 			},
-			expectedStatement: `alter table "t" alter column "a" drop not null`,
+			expectedStatements: []string{`alter table "t" alter column "a" drop not null`},
 		},
 		{
 			name:      "no change to not null constraint",
@@ -153,7 +153,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &falseValue,
 				},
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "no change to not nullable timestamp using short column type",
@@ -175,7 +175,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &trueValue,
 				},
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "no change to not nullable timestamp",
@@ -197,7 +197,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 					NotNull: &trueValue,
 				},
 			},
-			expectedStatement: "",
+			expectedStatements: []string{},
 		},
 		{
 			name:      "default set",
@@ -213,7 +213,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 				Name:     "a",
 				DataType: "integer",
 			},
-			expectedStatement: `alter table "t" alter column "a" set default '11'`,
+			expectedStatements: []string{`alter table "t" alter column "a" set default '11'`},
 		},
 		{
 			name:      "default unset",
@@ -229,7 +229,7 @@ func Test_AlterColumnStatment(t *testing.T) {
 				DataType:      "integer",
 				ColumnDefault: &defaultEleven,
 			},
-			expectedStatement: `alter table "t" alter column "a" drop default`,
+			expectedStatements: []string{`alter table "t" alter column "a" drop default`},
 		},
 		{
 			name:      "default empty string",
@@ -245,7 +245,30 @@ func Test_AlterColumnStatment(t *testing.T) {
 				Name:     "a",
 				DataType: "character varying (32)",
 			},
-			expectedStatement: `alter table "t" alter column "a" set default ''`,
+			expectedStatements: []string{`alter table "t" alter column "a" set default ''`},
+		},
+		{
+			name:      "add null and default",
+			tableName: "t",
+			desiredColumns: []*schemasv1alpha3.SQLTableColumn{
+				{
+					Name:    "a",
+					Type:    "varchar (32)",
+					Default: &defaultEleven,
+					Constraints: &schemasv1alpha3.SQLTableColumnConstraints{
+						NotNull: &trueValue,
+					},
+				},
+			},
+			existingColumn: &types.Column{
+				Name:     "a",
+				DataType: "character varying (32)",
+			},
+			expectedStatements: []string{
+				`alter table "t" alter column "a" set default '11'`,
+				`update "t" set "a"='11' where "a" is null`,
+				`alter table "t" alter column "a" set not null`,
+			},
 		},
 	}
 
@@ -253,9 +276,9 @@ func Test_AlterColumnStatment(t *testing.T) {
 		t.Run(test.name, func(t *testing.T) {
 			req := require.New(t)
 
-			generatedStatement, err := AlterColumnStatement(test.tableName, []string{}, test.desiredColumns, test.existingColumn)
+			generatedStatements, err := AlterColumnStatements(test.tableName, []string{}, test.desiredColumns, test.existingColumn)
 			req.NoError(err)
-			assert.Equal(t, test.expectedStatement, generatedStatement)
+			assert.Equal(t, test.expectedStatements, generatedStatements)
 		})
 	}
 }