Repository: ariga/atlas
Pull request #1787 (2023-06-25 20:34:38)

sql/postgres: handle enums as top-level objects


Patch:
diff --git a/sql/postgres/inspect.go b/sql/postgres/inspect.go
index 8fb9368f..492f6891 100644
--- a/sql/postgres/inspect.go
+++ b/sql/postgres/inspect.go
@@ -36,13 +36,23 @@ func (i *inspect) InspectRealm(ctx context.Context, opts *schema.InspectRealmOpt
 	}
 	r := schema.NewRealm(schemas...).SetCollation(i.collate)
 	r.Attrs = append(r.Attrs, &CType{V: i.ctype})
-	if len(schemas) == 0 || !sqlx.ModeInspectRealm(opts).Is(schema.InspectTables) {
-		return sqlx.ExcludeRealm(r, opts.Exclude)
-	}
-	if err := i.inspectTables(ctx, r, nil); err != nil {
-		return nil, err
+	if len(schemas) > 0 {
+		mode := sqlx.ModeInspectRealm(opts)
+		if mode.Is(schema.InspectTables) {
+			if err := i.inspectTables(ctx, r, nil); err != nil {
+				return nil, err
+			}
+			sqlx.LinkSchemaTables(schemas)
+		}
+		if mode.Is(schema.InspectViews) {
+			if err := i.inspectViews(ctx, r, nil); err != nil {
+				return nil, err
+			}
+		}
+		if err := i.inspectEnums(ctx, r); err != nil {
+			return nil, err
+		}
 	}
-	sqlx.LinkSchemaTables(schemas)
 	return sqlx.ExcludeRealm(r, opts.Exclude)
 }
 
@@ -75,6 +85,9 @@ func (i *inspect) InspectSchema(ctx context.Context, name string, opts *schema.I
 			return nil, err
 		}
 	}
+	if err := i.inspectEnums(ctx, r); err != nil {
+		return nil, err
+	}
 	return sqlx.ExcludeSchema(r.Schemas[0], opts.Exclude)
 }
 
@@ -184,10 +197,7 @@ func (i *inspect) columns(ctx context.Context, s *schema.Schema, scope queryScop
 			return fmt.Errorf("postgres: %w", err)
 		}
 	}
-	if err := rows.Close(); err != nil {
-		return err
-	}
-	return i.enumValues(ctx, s)
+	return rows.Close()
 }
 
 // addColumn scans the current row and adds a new column from it to the scope (table or view).
@@ -253,58 +263,73 @@ func (i *inspect) addColumn(s *schema.Schema, rows *sql.Rows, scope queryScope)
 }
 
 // enumValues fills enum columns with their values from the database.
-func (i *inspect) enumValues(ctx context.Context, s *schema.Schema) error {
+func (i *inspect) inspectEnums(ctx context.Context, r *schema.Realm) error {
 	var (
-		args  []any
-		ids   = make(map[int64][]*schema.EnumType)
-		query = "SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN (%s)"
-		newE  = func(e1 *enumType) *schema.EnumType {
-			if _, ok := ids[e1.ID]; !ok {
-				args = append(args, e1.ID)
+		ids  = make(map[int64]*schema.EnumType)
+		args = make([]any, 0, len(r.Schemas))
+		newE = func(e1 *enumType) *schema.EnumType {
+			e2, ok := ids[e1.ID]
+			if ok {
+				return e2
 			}
-			// Convert the intermediate type to
-			// the standard schema.EnumType.
-			e2 := &schema.EnumType{T: e1.T, Schema: s}
-			if e1.Schema != "" && e1.Schema != s.Name {
-				e2.Schema = schema.New(e1.Schema)
-			}
-			ids[e1.ID] = append(ids[e1.ID], e2)
+			e2 = &schema.EnumType{T: e1.T}
+			ids[e1.ID] = e2
 			return e2
 		}
-	)
-	for _, t := range s.Tables {
-		for _, c := range t.Columns {
-			switch t := c.Type.Type.(type) {
-			case *enumType:
-				e := newE(t)
-				c.Type.Type = e
-				c.Type.Raw = e.T
-			case *ArrayType:
-				if e, ok := t.Type.(*enumType); ok {
-					t.Type = newE(e)
+		scanC = func(cs []*schema.Column) {
+			for _, c := range cs {
+				switch t := c.Type.Type.(type) {
+				case *enumType:
+					e := newE(t)
+					c.Type.Type = e
+					c.Type.Raw = e.T
+				case *ArrayType:
+					if e, ok := t.Type.(*enumType); ok {
+						t.Type = newE(e)
+					}
 				}
 			}
 		}
+	)
+	for _, s := range r.Schemas {
+		args = append(args, s.Name)
+		for _, t := range s.Tables {
+			scanC(t.Columns)
+		}
+		for _, v := range s.Views {
+			scanC(v.Columns)
+		}
 	}
-	if len(ids) == 0 {
+	if len(args) == 0 {
 		return nil
 	}
-	rows, err := i.QueryContext(ctx, fmt.Sprintf(query, nArgs(0, len(args))), args...)
+	rows, err := i.QueryContext(ctx, fmt.Sprintf(enumsQuery, nArgs(0, len(args))), args...)
 	if err != nil {
 		return fmt.Errorf("postgres: querying enum values: %w", err)
 	}
 	defer rows.Close()
 	for rows.Next() {
 		var (
-			id int64
-			v  string
+			id       int64
+			ns, n, v string
 		)
-		if err := rows.Scan(&id, &v); err != nil {
+		if err := rows.Scan(&ns, &id, &n, &v); err != nil {
 			return fmt.Errorf("postgres: scanning enum label: %w", err)
 		}
-		for _, enum := range ids[id] {
-			enum.Values = append(enum.Values, v)
+		e, ok := ids[id]
+		if !ok {
+			e = &schema.EnumType{T: n}
+			ids[id] = e
+		}
+		if e.Schema == nil {
+			s, ok := r.Schema(ns)
+			if !ok {
+				return fmt.Errorf("postgres: schema %q for enum %q was not found in inspection", ns, e.T)
+			}
+			e.Schema = s
+			s.Objects = append(s.Objects, e)
 		}
+		e.Values = append(e.Values, v)
 	}
 	return nil
 }
@@ -1185,7 +1210,23 @@ WHERE
 ORDER BY
 	t1.table_name, t1.ordinal_position
 `
-
+	// Query to list enum values.
+	enumsQuery = `
+SELECT
+	n.nspname AS schema_name,
+	e.enumtypid AS enum_id,
+	t.typname AS enum_name,
+	e.enumlabel AS enum_value
+FROM
+	pg_enum e
+	JOIN pg_type t ON e.enumtypid = t.oid
+	JOIN pg_namespace n ON t.typnamespace = n.oid
+WHERE
+    n.nspname IN (%s)
+ORDER BY
+    n.nspname, e.enumtypid, e.enumsortorder
+`
+	// Query to list foreign-keys.
 	fksQuery = `
 SELECT 
     fk.constraint_name,
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index a3a6d6a5..7987a7dc 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -427,16 +427,26 @@ func convertColumnType(spec *sqlspec.Column) (schema.Type, error) {
 // convertEnums converts possibly referenced column types (like enums) to
 // an actual schema.Type and sets it on the correct schema.Column.
 func convertEnums(tables []*sqlspec.Table, enums []*Enum, r *schema.Realm) error {
-	var (
-		used   = make(map[*Enum]struct{})
-		byName = make(map[string]*Enum)
-	)
+	byName := make(map[string]*schema.EnumType)
 	for _, e := range enums {
-		byName[e.Name] = e
+		if byName[e.Name] != nil {
+			return fmt.Errorf("duplicate enum %q", e.Name)
+		}
+		ns, err := specutil.SchemaName(e.Schema)
+		if err != nil {
+			return fmt.Errorf("extract schema name from enum reference: %w", err)
+		}
+		es, ok := r.Schema(ns)
+		if !ok {
+			return fmt.Errorf("schema %q defined on enum %q was not found in realm", ns, e.Name)
+		}
+		e1 := &schema.EnumType{T: e.Name, Schema: es, Values: e.Values}
+		es.Objects = append(es.Objects, e1)
+		byName[e.Name] = e1
 	}
 	for _, t := range tables {
 		for _, c := range t.Columns {
-			var enum *Enum
+			var enum *schema.EnumType
 			switch {
 			case c.Type.IsRef:
 				n, err := enumName(c.Type)
@@ -445,7 +455,7 @@ func convertEnums(tables []*sqlspec.Table, enums []*Enum, r *schema.Realm) error
 				}
 				e, ok := byName[n]
 				if !ok {
-					return fmt.Errorf("enum %q was not found", n)
+					return fmt.Errorf("enum %q was not found in realm", n)
 				}
 				enum = e
 			default:
@@ -455,15 +465,6 @@ func convertEnums(tables []*sqlspec.Table, enums []*Enum, r *schema.Realm) error
 				}
 				enum = byName[n]
 			}
-			used[enum] = struct{}{}
-			schemaE, err := specutil.SchemaName(enum.Schema)
-			if err != nil {
-				return fmt.Errorf("extract schema name from enum reference: %w", err)
-			}
-			es, ok := r.Schema(schemaE)
-			if !ok {
-				return fmt.Errorf("schema %q not found in realm for table %q", schemaE, t.Name)
-			}
 			schemaT, err := specutil.SchemaName(t.Schema)
 			if err != nil {
 				return fmt.Errorf("extract schema name from table reference: %w", err)
@@ -480,20 +481,14 @@ func convertEnums(tables []*sqlspec.Table, enums []*Enum, r *schema.Realm) error
 			if !ok {
 				return fmt.Errorf("column %q not found in table %q", c.Name, t.Name)
 			}
-			e := &schema.EnumType{T: enum.Name, Schema: es, Values: enum.Values}
 			switch t := cc.Type.Type.(type) {
 			case *ArrayType:
-				t.Type = e
+				t.Type = enum
 			default:
-				cc.Type.Type = e
+				cc.Type.Type = enum
 			}
 		}
 	}
-	for _, e := range enums {
-		if _, ok := used[e]; !ok {
-			return fmt.Errorf("enum %q declared but not used", e.Name)
-		}
-	}
 	return nil
 }
 
@@ -514,8 +509,8 @@ func enumRef(n string) *schemahcl.Ref {
 }
 
 // schemaSpec converts from a concrete Postgres schema to Atlas specification.
-func schemaSpec(schem *schema.Schema) (*doc, error) {
-	spec, err := specutil.FromSchema(schem, tableSpec, viewSpec)
+func schemaSpec(s *schema.Schema) (*doc, error) {
+	spec, err := specutil.FromSchema(s, tableSpec, viewSpec)
 	if err != nil {
 		return nil, err
 	}
@@ -523,26 +518,15 @@ func schemaSpec(schem *schema.Schema) (*doc, error) {
 		Tables:  spec.Tables,
 		Views:   spec.Views,
 		Schemas: []*sqlspec.Schema{spec.Schema},
+		Enums:   make([]*Enum, 0, len(s.Objects)),
 	}
-	enums := make(map[string]bool)
-	mayAdd := func(c *schema.Column) {
-		if e, ok := hasEnumType(c); ok && !enums[e.T] {
+	for _, o := range s.Objects {
+		if e, ok := o.(*schema.EnumType); ok {
 			d.Enums = append(d.Enums, &Enum{
 				Name:   e.T,
-				Schema: specutil.SchemaRef(spec.Schema.Name),
 				Values: e.Values,
+				Schema: specutil.SchemaRef(spec.Schema.Name),
 			})
-			enums[e.T] = true
-		}
-	}
-	for _, t := range schem.Tables {
-		for _, c := range t.Columns {
-			mayAdd(c)
-		}
-	}
-	for _, t := range schem.Views {
-		for _, c := range t.Columns {
-			mayAdd(c)
 		}
 	}
 	return d, nil
diff --git a/sql/schema/dsl.go b/sql/schema/dsl.go
index e9ae9e52..fb6d7f5d 100644
--- a/sql/schema/dsl.go
+++ b/sql/schema/dsl.go
@@ -79,6 +79,12 @@ func (s *Schema) AddViews(views ...*View) *Schema {
 	return s
 }
 
+// AddObjects adds the given objects to the schema.
+func (s *Schema) AddObjects(objs ...Object) *Schema {
+	s.Objects = append(s.Objects, objs...)
+	return s
+}
+
 // NewRealm creates a new Realm.
 func NewRealm(schemas ...*Schema) *Realm {
 	r := &Realm{Schemas: schemas}
@@ -250,6 +256,12 @@ func (v *View) AddAttrs(attrs ...Attr) *View {
 	return v
 }
 
+// AddDeps adds the given objects as dependencies to the view.
+func (v *View) AddDeps(objs ...Object) *View {
+	v.Deps = append(v.Deps, objs...)
+	return v
+}
+
 // NewColumn creates a new column with the given name.
 func NewColumn(name string) *Column {
 	return &Column{Name: name}
diff --git a/sql/schema/migrate.go b/sql/schema/migrate.go
index f19330aa..ec8fd340 100644
--- a/sql/schema/migrate.go
+++ b/sql/schema/migrate.go
@@ -110,6 +110,30 @@ type (
 		From, To *View
 	}
 
+	// AddObject describes a generic object creation change.
+	AddObject struct {
+		O     Object
+		Extra []Clause // Extra clauses and options.
+	}
+
+	// DropObject describes a generic object removal change.
+	DropObject struct {
+		O     Object
+		Extra []Clause // Extra clauses.
+	}
+
+	// ModifyObject describes a generic object modification change.
+	// Unlike tables changes, the diffing types are implemented by
+	// the underlying driver.
+	ModifyObject struct {
+		From, To Object
+	}
+
+	// RenameObject describes a generic object rename change.
+	RenameObject struct {
+		From, To Object
+	}
+
 	// AddColumn describes a column creation change.
 	AddColumn struct {
 		C *Column
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 9e6d70c7..42fa4234 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -14,11 +14,19 @@ type (
 
 	// A Schema describes a database schema (i.e. named database).
 	Schema struct {
-		Name   string
-		Realm  *Realm
-		Tables []*Table
-		Views  []*View
-		Attrs  []Attr // Attrs and options.
+		Name    string
+		Realm   *Realm
+		Tables  []*Table
+		Views   []*View
+		Attrs   []Attr   // Attrs and options.
+		Objects []Object // Driver specific objects.
+	}
+
+	// An Object represents a generic database object.
+	// Note that this interface is implemented by some top-level types
+	// to describe their relationship, and by driver specific types.
+	Object interface {
+		obj()
 	}
 
 	// A Table represents a table definition.
@@ -38,7 +46,8 @@ type (
 		Def     string
 		Schema  *Schema
 		Columns []*Column
-		Attrs   []Attr // Attrs and options.
+		Attrs   []Attr   // Attrs and options.
+		Deps    []Object // Tables and views used in view definition.
 	}
 
 	// A Column represents a column definition.
@@ -125,6 +134,16 @@ func (s *Schema) View(name string) (*View, bool) {
 	return nil, false
 }
 
+// Object returns the first object that matched the given predicate.
+func (s *Schema) Object(f func(Object) bool) (Object, bool) {
+	for _, o := range s.Objects {
+		if f(o) {
+			return o, true
+		}
+	}
+	return nil, false
+}
+
 // Column returns the first column that matched the given name.
 func (t *Table) Column(name string) (*Column, bool) {
 	for _, c := range t.Columns {
@@ -344,6 +363,11 @@ type (
 	}
 )
 
+// objects.
+func (*Table) obj()    {}
+func (*View) obj()     {}
+func (*EnumType) obj() {}
+
 // expressions.
 func (*Literal) expr() {}
 func (*RawExpr) expr() {}


Test patch:
diff --git a/sql/postgres/inspect_test.go b/sql/postgres/inspect_test.go
index 217dc18b..4f358845 100644
--- a/sql/postgres/inspect_test.go
+++ b/sql/postgres/inspect_test.go
@@ -10,6 +10,7 @@ import (
 	"testing"
 
 	"ariga.io/atlas/sql/internal/sqltest"
+	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
 
@@ -20,6 +21,7 @@ import (
 // Single table queries used by the different tests.
 var (
 	queryFKs         = sqltest.Escape(fmt.Sprintf(fksQuery, "$2"))
+	queryEnums       = sqltest.Escape(fmt.Sprintf(enumsQuery, "$1"))
 	queryTables      = sqltest.Escape(fmt.Sprintf(tablesQuery, "$1"))
 	queryChecks      = sqltest.Escape(fmt.Sprintf(checksQuery, "$2"))
 	queryColumns     = sqltest.Escape(fmt.Sprintf(columnsQuery, "$2"))
@@ -77,7 +79,7 @@ func TestDriver_InspectTable(t *testing.T) {
  users       |  c30         | interval                    | interval            | NO          |                                        |                          |                   |                  6 |               |        MONTH        |                    |                | NO          |                |                    |                  |                     |                       |         | b       |         |         |  1269
  users       |  c31         | interval                    | interval            | NO          |                                        |                          |                   |                  6 |               | MINUTE TO SECOND(6) |                    |                | NO          |                |                    |                  |                     |                       |         | b       |         |         |  1233
  users       |  c32         | bigint                      | int4                | NO          | nextval('public.t1_c32_seq'::regclass) |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |         |         |    23
- users       |  c33         | USER-DEFINED                | test."status""."    | NO          |  'unknown'::test."status""."           |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | e       |         |         | 16775
+ users       |  c33         | USER-DEFINED                | public."status""."  | NO          |  'unknown'::test."status""."           |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | e       |         |         | 16775
  users       |  c34         | ARRAY                       | state[]             | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  16774  |  e      | 16779
  users       |  c35         | character                   | domain_char         | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | d       |  16774  |  e      | 16779
  users       |  c36         | tsvector                    | tsvector            | NO          |                                        |                          |                   |                    |               |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |  16774  |         | 16779
@@ -87,25 +89,26 @@ func TestDriver_InspectTable(t *testing.T) {
  users       |  c40         | smallint                    | int4                | NO          | nextval('"Users_c40_seq"'::regclass)   |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |         |         |    23
  users       |  c41         | smallint                    | int4                | NO          | nextval('foo."T_C40_seq"'::regclass)   |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |         |         |    23
  users       |  c42         | smallint                    | int4                | NO          | nextval('"F"."T_C40_seq"'::regclass)   |                          |                32 |                    |             0 |                     |                    |                | NO          |                |                    |                  |                     |                       |         | b       |         |         |    23
-`))
-				m.ExpectQuery(sqltest.Escape(`SELECT enumtypid, enumlabel FROM pg_enum WHERE enumtypid IN ($1, $2)`)).
-					WithArgs(16774, 16775).
-					WillReturnRows(sqltest.Rows(`
- enumtypid | enumlabel
------------+-----------
-     16774 | on
-     16774 | off
-     16775 | unknown
 `))
 				m.noIndexes()
 				m.noFKs()
 				m.noChecks()
+				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(enumsQuery, "$1"))).
+					WithArgs("public").
+					WillReturnRows(sqltest.Rows(`
+ schema_name | enum_id | type    | enum_value
+-------------+---------+---------+------------
+ public      |   16774 |  state  | on
+ public      |   16774 |  state  | off
+ public      |   16775 |  status | unknown
+`))
 				m.noViews()
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
-				p := func(i int) *int { return &i }
 				require.NoError(err)
 				require.Equal("users", t.Name)
+				stateE := &schema.EnumType{T: "state", Values: []string{"on", "off"}, Schema: t.Schema}
+				statusE := &schema.EnumType{T: `status".` /* intentionally broken */, Values: []string{"unknown"}, Schema: t.Schema}
 				require.EqualValues([]*schema.Column{
 					{Name: "id", Type: &schema.ColumnType{Raw: "bigint", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&Identity{Generation: "BY DEFAULT", Sequence: &Sequence{Start: 100, Increment: 1, Last: 1}}}},
 					{Name: "rank", Type: &schema.ColumnType{Raw: "integer", Null: true, Type: &schema.IntegerType{T: "integer"}}, Attrs: []schema.Attr{&schema.Comment{Text: "rank"}}},
@@ -119,7 +122,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c8", Type: &schema.ColumnType{Raw: "cidr", Type: &NetworkType{T: "cidr"}}},
 					{Name: "c9", Type: &schema.ColumnType{Raw: "circle", Type: &schema.SpatialType{T: "circle"}}},
 					{Name: "c10", Type: &schema.ColumnType{Raw: "date", Type: &schema.TimeType{T: "date"}}},
-					{Name: "c11", Type: &schema.ColumnType{Raw: "time with time zone", Type: &schema.TimeType{T: "time with time zone", Precision: p(0)}}},
+					{Name: "c11", Type: &schema.ColumnType{Raw: "time with time zone", Type: &schema.TimeType{T: "time with time zone", Precision: sqlx.P(0)}}},
 					{Name: "c12", Type: &schema.ColumnType{Raw: "double precision", Type: &schema.FloatType{T: "double precision", Precision: 53}}},
 					{Name: "c13", Type: &schema.ColumnType{Raw: "real", Type: &schema.FloatType{T: "real", Precision: 24}}, Default: &schema.RawExpr{X: "random()"}},
 					{Name: "c14", Type: &schema.ColumnType{Raw: "json", Type: &schema.JSONType{T: "json"}}, Default: &schema.Literal{V: "'{}'"}},
@@ -132,17 +135,17 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c21", Type: &schema.ColumnType{Raw: "xml", Type: &XMLType{T: "xml"}}},
 					{Name: "c22", Type: &schema.ColumnType{Raw: "ARRAY", Null: true, Type: &ArrayType{Type: &schema.IntegerType{T: "integer"}, T: "integer[]"}}},
 					{Name: "c23", Type: &schema.ColumnType{Raw: "USER-DEFINED", Null: true, Type: &UserDefinedType{T: "ltree"}}},
-					{Name: "c24", Type: &schema.ColumnType{Raw: "state", Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}, Schema: t.Schema}}},
-					{Name: "c25", Type: &schema.ColumnType{Raw: "timestamp without time zone", Type: &schema.TimeType{T: "timestamp without time zone", Precision: p(4)}}, Default: &schema.RawExpr{X: "now()"}},
-					{Name: "c26", Type: &schema.ColumnType{Raw: "timestamp with time zone", Type: &schema.TimeType{T: "timestamp with time zone", Precision: p(6)}}},
-					{Name: "c27", Type: &schema.ColumnType{Raw: "time without time zone", Type: &schema.TimeType{T: "time without time zone", Precision: p(6)}}},
+					{Name: "c24", Type: &schema.ColumnType{Raw: "state", Type: stateE}},
+					{Name: "c25", Type: &schema.ColumnType{Raw: "timestamp without time zone", Type: &schema.TimeType{T: "timestamp without time zone", Precision: sqlx.P(4)}}, Default: &schema.RawExpr{X: "now()"}},
+					{Name: "c26", Type: &schema.ColumnType{Raw: "timestamp with time zone", Type: &schema.TimeType{T: "timestamp with time zone", Precision: sqlx.P(6)}}},
+					{Name: "c27", Type: &schema.ColumnType{Raw: "time without time zone", Type: &schema.TimeType{T: "time without time zone", Precision: sqlx.P(6)}}},
 					{Name: "c28", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&schema.GeneratedExpr{Expr: "(c1 + c2)"}}},
-					{Name: "c29", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", Precision: p(6)}}},
-					{Name: "c30", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MONTH", Precision: p(6)}}},
-					{Name: "c31", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MINUTE TO SECOND", Precision: p(6)}}},
+					{Name: "c29", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", Precision: sqlx.P(6)}}},
+					{Name: "c30", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MONTH", Precision: sqlx.P(6)}}},
+					{Name: "c31", Type: &schema.ColumnType{Raw: "interval", Type: &IntervalType{T: "interval", F: "MINUTE TO SECOND", Precision: sqlx.P(6)}}},
 					{Name: "c32", Type: &schema.ColumnType{Raw: "bigserial", Type: &SerialType{T: "bigserial", SequenceName: "t1_c32_seq"}}},
-					{Name: "c33", Type: &schema.ColumnType{Raw: `status".`, Type: &schema.EnumType{T: `status".`, Values: []string{"unknown"}, Schema: schema.New("test")}}, Default: &schema.Literal{V: "'unknown'"}},
-					{Name: "c34", Type: &schema.ColumnType{Raw: "ARRAY", Type: &ArrayType{T: "state[]", Type: &schema.EnumType{T: "state", Values: []string{"on", "off"}, Schema: t.Schema}}}},
+					{Name: "c33", Type: &schema.ColumnType{Raw: `status".`, Type: statusE}, Default: &schema.Literal{V: "'unknown'"}},
+					{Name: "c34", Type: &schema.ColumnType{Raw: "ARRAY", Type: &ArrayType{T: "state[]", Type: stateE}}},
 					{Name: "c35", Type: &schema.ColumnType{Raw: "character", Type: &UserDefinedType{T: "domain_char"}}},
 					{Name: "c36", Type: &schema.ColumnType{Raw: "tsvector", Type: &TextSearchType{T: "tsvector"}}},
 					{Name: "c37", Type: &schema.ColumnType{Raw: "tsquery", Type: &TextSearchType{T: "tsquery"}}},
@@ -152,6 +155,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c41", Type: &schema.ColumnType{Raw: "smallserial", Type: &SerialType{T: "smallserial", SequenceName: "T_C40_seq"}}},
 					{Name: "c42", Type: &schema.ColumnType{Raw: "smallserial", Type: &SerialType{T: "smallserial", SequenceName: "T_C40_seq"}}},
 				}, t.Columns)
+				require.Equal([]schema.Object{stateE, statusE}, t.Schema.Objects)
 			},
 		},
 		{
@@ -192,6 +196,7 @@ users           | tsx             | gist        | ts          | f        | f
 				m.noFKs()
 				m.noChecks()
 				m.noViews()
+				m.noEnums()
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
@@ -254,6 +259,7 @@ self_reference  | users      | uid         | public       | users
 `))
 				m.noChecks()
 				m.noViews()
+				m.noEnums()
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
@@ -305,6 +311,7 @@ users        | users_check1       | (((c2 + c1) + c3) > 10) | c1          | {2,1
 users        | users_check1       | (((c2 + c1) + c3) > 10) | c3          | {2,1,3}        | f
 `))
 				m.noViews()
+				m.noEnums()
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
@@ -390,6 +397,7 @@ logs3      | c5         | integer   | integer   | NO          |                |
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(checksQuery, "$2, $3, $4"))).
 		WillReturnRows(sqlmock.NewRows([]string{"table_name", "constraint_name", "expression", "column_name", "column_indexes"}))
 	mk.noViews()
+	mk.noEnums()
 	s, err := drv.InspectSchema(context.Background(), "", &schema.InspectOptions{})
 	require.NoError(t, err)
 
@@ -467,6 +475,7 @@ users       | idx5       | c           | false   | false  |                 | CR
 	mk.noFKs()
 	mk.noChecks()
 	mk.noViews()
+	mk.noEnums()
 	s, err := drv.InspectSchema(context.Background(), "public", nil)
 	require.NoError(t, err)
 	tbl := s.Tables[0]
@@ -509,6 +518,7 @@ test
 		WithArgs("test").
 		WillReturnRows(sqlmock.NewRows([]string{"table_schema", "table_name", "comment", "partition_attrs", "partition_strategy", "partition_exprs"}))
 	mk.noViews()
+	mk.noEnums()
 	s, err := drv.InspectSchema(context.Background(), "", &schema.InspectOptions{})
 	require.NoError(t, err)
 	require.EqualValues(t, func() *schema.Schema {
@@ -550,6 +560,8 @@ public
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(tablesQuery, "$1, $2"))).
 		WithArgs("test", "public").
 		WillReturnRows(sqlmock.NewRows([]string{"table_schema", "table_name", "comment", "partition_attrs", "partition_strategy", "partition_exprs"}))
+	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(enumsQuery, "$1, $2"))).
+		WillReturnRows(sqlmock.NewRows([]string{"schema_name", "enum_name", "comment", "enum_type", "enum_value"}))
 	realm, err := drv.InspectRealm(context.Background(), &schema.InspectRealmOption{})
 	require.NoError(t, err)
 	require.EqualValues(t, func() *schema.Realm {
@@ -588,6 +600,8 @@ public
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(tablesQuery, "$1, $2"))).
 		WithArgs("test", "public").
 		WillReturnRows(sqlmock.NewRows([]string{"table_schema", "table_name", "comment", "partition_attrs", "partition_strategy", "partition_exprs"}))
+	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(enumsQuery, "$1, $2"))).
+		WillReturnRows(sqlmock.NewRows([]string{"schema_name", "enum_name", "comment", "enum_type", "enum_value"}))
 	realm, err = drv.InspectRealm(context.Background(), &schema.InspectRealmOption{Schemas: []string{"test", "public"}})
 	require.NoError(t, err)
 	require.EqualValues(t, func() *schema.Realm {
@@ -625,6 +639,7 @@ public
 	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(tablesQuery, "$1"))).
 		WithArgs("test").
 		WillReturnRows(sqlmock.NewRows([]string{"table_schema", "table_name", "comment", "partition_attrs", "partition_strategy", "partition_exprs"}))
+	mk.noEnums()
 	realm, err = drv.InspectRealm(context.Background(), &schema.InspectRealmOption{Schemas: []string{"test"}})
 	require.NoError(t, err)
 	require.EqualValues(t, func() *schema.Realm {
@@ -662,6 +677,8 @@ test
 public
 `))
 	drv, err := Open(db)
+	m.ExpectQuery(sqltest.Escape(fmt.Sprintf(enumsQuery, "$1, $2"))).
+		WillReturnRows(sqlmock.NewRows([]string{"schema_name", "enum_name", "comment", "enum_type", "enum_value"}))
 	realm, err := drv.InspectRealm(context.Background(), &schema.InspectRealmOption{Mode: schema.InspectSchemas})
 	require.NoError(t, err)
 	require.EqualValues(t, func() *schema.Realm {
@@ -743,6 +760,11 @@ func (m mock) noChecks() {
 		WillReturnRows(sqlmock.NewRows([]string{"table_name", "constraint_name", "expression", "column_name", "column_indexes"}))
 }
 
+func (m mock) noEnums() {
+	m.ExpectQuery(queryEnums).
+		WillReturnRows(sqlmock.NewRows([]string{"schema_name", "enum_name", "comment", "enum_type", "enum_value"}))
+}
+
 func (m mock) noViews() {
 	if queryViews != "" {
 		m.ExpectQuery(queryViews).
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 5c0008ba..fa8c9722 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -102,9 +102,8 @@ enum "account_type" {
 	var s schema.Schema
 	err := EvalHCLBytes([]byte(f), &s, nil)
 	require.NoError(t, err)
-	exp := &schema.Schema{
-		Name: "schema",
-	}
+	exp := schema.New("schema")
+	exp.AddObjects(&schema.EnumType{T: "account_type", Values: []string{"private", "business"}, Schema: exp})
 	exp.Tables = []*schema.Table{
 		{
 			Name:   "table",
@@ -1118,7 +1117,18 @@ table "users" {
 }
 
 func TestMarshalSpec_Enum(t *testing.T) {
+	stateE := &schema.EnumType{
+		T:      "state",
+		Values: []string{"on", "off"},
+	}
+	typeE := &schema.EnumType{
+		T:      "account_type",
+		Values: []string{"private", "business"},
+	}
 	s := schema.New("test").
+		AddObjects(
+			typeE, stateE,
+		).
 		AddTables(
 			schema.NewTable("account").
 				AddColumns(
@@ -1128,19 +1138,14 @@ func TestMarshalSpec_Enum(t *testing.T) {
 					),
 					schema.NewColumn("account_states").
 						SetType(&ArrayType{
-							T: "states[]",
-							Type: &schema.EnumType{
-								T:      "state",
-								Values: []string{"on", "off"},
-							},
+							T:    "states[]",
+							Type: stateE,
 						}),
 				),
 			schema.NewTable("table2").
 				AddColumns(
-					schema.NewEnumColumn("account_type",
-						schema.EnumName("account_type"),
-						schema.EnumValues("private", "business"),
-					),
+					schema.NewColumn("account_type").
+						SetType(typeE),
 				),
 		)
 	buf, err := MarshalSpec(s, hclState)