Repository: ariga/atlas
Pull request #3139 (2024-09-23 08:44:35)

sql/sqlite: literal default values are alterable


Patch:
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
index 1badb9bf..36b4eba1 100644
--- a/sql/sqlite/migrate.go
+++ b/sql/sqlite/migrate.go
@@ -7,6 +7,7 @@ package sqlite
 import (
 	"context"
 	"fmt"
+	"slices"
 	"strings"
 
 	"ariga.io/atlas/sql/internal/sqlx"
@@ -557,7 +558,17 @@ func alterable(modify *schema.ModifyTable) bool {
 		switch change := change.(type) {
 		case *schema.RenameColumn, *schema.RenameIndex, *schema.DropIndex, *schema.AddIndex:
 		case *schema.AddColumn:
-			if len(change.C.Indexes) > 0 || len(change.C.ForeignKeys) > 0 || change.C.Default != nil {
+			if len(change.C.Indexes) > 0 || len(change.C.ForeignKeys) > 0 {
+				return false
+			}
+			// If the column has a DEFAULT clause, it must be a constant value.
+			// See: https://www.sqlite.org/lang_altertable.html#alter_table_add_column.
+			switch x := change.C.Default.(type) {
+			case *schema.Literal:
+				if slices.Contains([]string{"CURRENT_TIME", "CURRENT_DATE", "CURRENT_TIMESTAMP"}, x.V) {
+					return false
+				}
+			case *schema.RawExpr:
 				return false
 			}
 			// Only VIRTUAL generated columns can be added using ALTER TABLE.


Test patch:
diff --git a/sql/sqlite/migrate_test.go b/sql/sqlite/migrate_test.go
index 9608aedf..a4c2f402 100644
--- a/sql/sqlite/migrate_test.go
+++ b/sql/sqlite/migrate_test.go
@@ -166,6 +166,64 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		// Add column with constant default value.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						AddColumns(schema.NewIntColumn("id", "bigint"))
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.AddColumn{
+								C: schema.NewStringColumn("name", "text").
+									SetDefault(&schema.Literal{V: "a8m"}),
+							},
+						},
+					}
+				}(),
+			},
+			plan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{Cmd: "ALTER TABLE `users` ADD COLUMN `name` text NOT NULL DEFAULT 'a8m'", Reverse: "ALTER TABLE `users` DROP COLUMN `name`"},
+				},
+			},
+		},
+		// Add column with expression default value.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						AddColumns(
+							schema.NewIntColumn("id", "bigint"),
+							schema.NewStringColumn("name", "text").
+								SetDefault(&schema.RawExpr{X: "NOW()::TEXT"}),
+						)
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.AddColumn{
+								C: users.Columns[1],
+							},
+						},
+					}
+				}(),
+			},
+			plan: &migrate.Plan{
+				Reversible:    false,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{Cmd: "PRAGMA foreign_keys = off"},
+					{Cmd: "CREATE TABLE `new_users` (`id` bigint NOT NULL, `name` text NOT NULL DEFAULT (NOW()::TEXT))", Reverse: "DROP TABLE `new_users`"},
+					{Cmd: "INSERT INTO `new_users` (`id`) SELECT `id` FROM `users`"},
+					{Cmd: "DROP TABLE `users`"},
+					{Cmd: "ALTER TABLE `new_users` RENAME TO `users`"},
+					{Cmd: "PRAGMA foreign_keys = on"},
+				},
+			},
+		},
 		// Add VIRTUAL column.
 		{
 			changes: []schema.Change{