Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #832 (2022-01-25 23:26:25)

feat: Add replication support
Add support for allowing replication of a database and creating a database out of available replica.

## Test Plan
<!-- detail ways in which this PR has been tested or needs to be tested -->
* [x] acceptance tests for database and replica
* [x] unit tests for database

## References
* https://docs.snowflake.com/en/user-guide/database-replication-config.html
* https://docs.snowflake.com/en/sql-reference/sql/alter-database.html#database-replication-and-failover-syntax
* https://docs.snowflake.com/en/sql-reference/sql/show-replication-databases.html
* Fixed version of https://github.com/chanzuckerberg/terraform-provider-snowflake/pull/672

alldoami: run `make-docs`
alldoami: /ok-to-test sha=3483cb7
github-actions[bot]: Integration tests failure for [3483cb7](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1748880404)
alldoami: Could you address the test failures?
yakui-lover: I believe I have addressed them shortly before your comment, hence the tests being green right now.
anton-chekanov: I believe I have addressed them shortly before your comment, hence the tests being green right now.
alldoami: /ok-to-test sha=e5be7ff
github-actions[bot]: Integration tests failure for [e5be7ff](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1774239770)
alldoami: ```
database_acceptance_test.go:56: Step 1/4 error: Check failed: Check 4/5 error: snowflake_database.db: Attribute 'replication_is_primary' expected "true", got "false"
```
alldoami: /ok-to-test sha=da962cd
github-actions[bot]: Integration tests success for [da962cd](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1780882740)
alldoami: run `make docs`
alldoami: /ok-to-test sha=8151fab
github-actions[bot]: Integration tests success for [8151fab](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1791005435)

Patch:
diff --git a/docs/resources/database.md b/docs/resources/database.md
index de8d993e..d338c9b2 100644
--- a/docs/resources/database.md
+++ b/docs/resources/database.md
@@ -37,6 +37,7 @@ resource "snowflake_database" "test2" {
 - **comment** (String)
 - **data_retention_time_in_days** (Number)
 - **from_database** (String) Specify a database to create a clone from.
+- **from_replica** (String) Specify a fully-qualified path to a database to create a replica from.
 - **from_share** (Map of String) Specify a provider and a share in this map to create a database from a share.
 - **id** (String) The ID of this resource.
 - **tag** (Block List) Definitions of a tag to associate with the resource. (see [below for nested schema](#nestedblock--tag))
diff --git a/pkg/resources/database.go b/pkg/resources/database.go
index fae960ec..881066b3 100644
--- a/pkg/resources/database.go
+++ b/pkg/resources/database.go
@@ -32,14 +32,21 @@ var databaseSchema = map[string]*schema.Schema{
 		Description:   "Specify a provider and a share in this map to create a database from a share.",
 		Optional:      true,
 		ForceNew:      true,
-		ConflictsWith: []string{"from_database"},
+		ConflictsWith: []string{"from_database", "from_replica"},
 	},
 	"from_database": {
 		Type:          schema.TypeString,
 		Description:   "Specify a database to create a clone from.",
 		Optional:      true,
 		ForceNew:      true,
-		ConflictsWith: []string{"from_share"},
+		ConflictsWith: []string{"from_share", "from_replica"},
+	},
+	"from_replica": {
+		Type:          schema.TypeString,
+		Description:   "Specify a fully-qualified path to a database to create a replica from.",
+		Optional:      true,
+		ForceNew:      true,
+		ConflictsWith: []string{"from_share", "from_database"},
 	},
 	"tag": tagReferenceSchema,
 }
@@ -71,6 +78,10 @@ func CreateDatabase(d *schema.ResourceData, meta interface{}) error {
 		return createDatabaseFromDatabase(d, meta)
 	}
 
+	if _, ok := d.GetOk("from_replica"); ok {
+		return createDatabaseFromReplica(d, meta)
+	}
+
 	return CreateResource("database", databaseProperties, databaseSchema, snowflake.Database, ReadDatabase)(d, meta)
 }
 
@@ -114,6 +125,23 @@ func createDatabaseFromDatabase(d *schema.ResourceData, meta interface{}) error
 	return ReadDatabase(d, meta)
 }
 
+func createDatabaseFromReplica(d *schema.ResourceData, meta interface{}) error {
+	sourceDb := d.Get("from_replica").(string)
+
+	db := meta.(*sql.DB)
+	name := d.Get("name").(string)
+	builder := snowflake.DatabaseFromReplica(name, sourceDb)
+
+	err := snowflake.Exec(db, builder.Create())
+	if err != nil {
+		return errors.Wrapf(err, "error creating a secondary database %v from database %v", name, sourceDb)
+	}
+
+	d.SetId(name)
+
+	return ReadDatabase(d, meta)
+}
+
 func ReadDatabase(d *schema.ResourceData, meta interface{}) error {
 	db := meta.(*sql.DB)
 	name := d.Id()
@@ -147,12 +175,10 @@ func ReadDatabase(d *schema.ResourceData, meta interface{}) error {
 		return err
 	}
 
-	err = d.Set("data_retention_time_in_days", i)
-	return err
+	return d.Set("data_retention_time_in_days", i)
 }
 
 func UpdateDatabase(d *schema.ResourceData, meta interface{}) error {
-	log.Printf("[DEBUG] updating database %v", d.Id())
 	return UpdateResource("database", databaseProperties, databaseSchema, snowflake.Database, ReadDatabase)(d, meta)
 }
 
diff --git a/pkg/snowflake/database.go b/pkg/snowflake/database.go
index ff40dd91..b2340979 100644
--- a/pkg/snowflake/database.go
+++ b/pkg/snowflake/database.go
@@ -57,6 +57,25 @@ func (dsb *DatabaseCloneBuilder) Create() string {
 	return fmt.Sprintf(`CREATE DATABASE "%v" CLONE "%v"`, dsb.name, dsb.database)
 }
 
+// DatabaseReplicaBuilder is a basic builder that just creates databases from an avilable replication source
+type DatabaseReplicaBuilder struct {
+	name    string
+	replica string
+}
+
+// DatabaseFromReplica returns a pointer to a builder that can create a database from an avilable replication source
+func DatabaseFromReplica(name, replica string) *DatabaseReplicaBuilder {
+	return &DatabaseReplicaBuilder{
+		name:    name,
+		replica: replica,
+	}
+}
+
+// Create returns the SQL statement required to create a database from an avilable replication source
+func (dsb *DatabaseReplicaBuilder) Create() string {
+	return fmt.Sprintf(`CREATE DATABASE "%v" AS REPLICA OF "%v"`, dsb.name, dsb.replica)
+}
+
 type database struct {
 	CreatedOn     sql.NullString `db:"created_on"`
 	DBName        sql.NullString `db:"name"`
diff --git a/pkg/snowflake/generic.go b/pkg/snowflake/generic.go
index 83507f26..e955c7bb 100644
--- a/pkg/snowflake/generic.go
+++ b/pkg/snowflake/generic.go
@@ -16,6 +16,7 @@ const (
 	ResourceMonitorType         EntityType = "RESOURCE MONITOR"
 	RoleType                    EntityType = "ROLE"
 	ShareType                   EntityType = "SHARE"
+	ReplicationType             EntityType = "REPLICATION"
 	StorageIntegrationType      EntityType = "STORAGE INTEGRATION"
 	NotificationIntegrationType EntityType = "NOTIFICATION INTEGRATION"
 	SecurityIntegrationType     EntityType = "SECURITY INTEGRATION"
diff --git a/pkg/snowflake/replication.go b/pkg/snowflake/replication.go
new file mode 100644
index 00000000..f1391619
--- /dev/null
+++ b/pkg/snowflake/replication.go
@@ -0,0 +1,56 @@
+package snowflake
+
+import (
+	"database/sql"
+	"fmt"
+
+	"github.com/jmoiron/sqlx"
+)
+
+// Replication returns a pointer to a Builder that abstracts the DDL operations for a replication.
+//
+// Supported DDL operations are:
+//   - SHOW REPLICATION DATABASES
+//
+// [Snowflake Reference](https://docs.snowflake.com/en/user-guide/database-replication-config.html)
+
+// ReplicationBuilder is a basic builder that enables replication on databases
+type ReplicationBuilder struct {
+	database string
+}
+
+// DatabaseFromDatabase returns a pointer to a builder that can create a database from a source database
+func Replication(database string) *ReplicationBuilder {
+	return &ReplicationBuilder{
+		database: database,
+	}
+}
+
+type replication struct {
+	Region           sql.NullString `db:"snowflake_region"`
+	CreatedOn        sql.NullString `db:"created_on"`
+	AccountName      sql.NullString `db:"account_name"`
+	DBName           sql.NullString `db:"name"`
+	Comment          sql.NullString `db:"comment"`
+	IsPrimary        sql.NullBool   `db:"is_primary"`
+	Primary          sql.NullString `db:"primary"`
+	ReplAccounts     sql.NullString `db:"replication_allowed_to_accounts"`
+	FailoverAccounts sql.NullString `db:"failover_allowed_to_accounts"`
+	Org              sql.NullString `db:"organization_name"`
+	AccountLocator   sql.NullString `db:"account_locator"`
+}
+
+func ScanReplication(rows *sqlx.Rows, AccName string) (*replication, error) {
+	for rows.Next() {
+		r := &replication{}
+		err := rows.StructScan(r)
+		if r.AccountName.String == AccName {
+			return r, err
+		}
+	}
+	return nil, sql.ErrNoRows
+}
+
+func (rb *ReplicationBuilder) Show() string {
+	return fmt.Sprintf(`SHOW REPLICATION DATABASES LIKE '%s'`, rb.database)
+}


Test patch:
diff --git a/pkg/resources/database_test.go b/pkg/resources/database_test.go
index cf73bd74..4b65ce65 100644
--- a/pkg/resources/database_test.go
+++ b/pkg/resources/database_test.go
@@ -37,14 +37,16 @@ func TestDatabaseCreate(t *testing.T) {
 }
 
 func expectRead(mock sqlmock.Sqlmock) {
-	rows := sqlmock.NewRows([]string{"created_on", "name", "is_default", "is_current", "origin", "owner", "comment", "options", "retention_time"}).AddRow("created_on", "good_name", "is_default", "is_current", "origin", "owner", "mock comment", "options", "1")
-	mock.ExpectQuery("SHOW DATABASES LIKE 'good_name'").WillReturnRows(rows)
+	dbRows := sqlmock.NewRows([]string{"created_on", "name", "is_default", "is_current", "origin", "owner", "comment", "options", "retention_time"}).AddRow("created_on", "good_name", "is_default", "is_current", "origin", "owner", "mock comment", "options", "1")
+	mock.ExpectQuery("SHOW DATABASES LIKE 'good_name'").WillReturnRows(dbRows)
 }
 
 func TestDatabaseRead(t *testing.T) {
 	r := require.New(t)
 
-	d := database(t, "good_name", map[string]interface{}{"name": "good_name"})
+	d := database(t, "good_name", map[string]interface{}{
+		"name": "good_name",
+	})
 
 	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
 		expectRead(mock)
@@ -106,3 +108,21 @@ func TestDatabaseCreateFromDatabase(t *testing.T) {
 		r.NoError(err)
 	})
 }
+
+func TestDatabaseCreateFromReplica(t *testing.T) {
+	r := require.New(t)
+
+	in := map[string]interface{}{
+		"name":         "good_name",
+		"from_replica": "abc123",
+	}
+	d := schema.TestResourceDataRaw(t, resources.Database().Schema, in)
+	r.NotNil(d)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`CREATE DATABASE "good_name" AS REPLICA OF "abc123"`).WillReturnResult(sqlmock.NewResult(1, 1))
+		expectRead(mock)
+		err := resources.CreateDatabase(d, db)
+		r.NoError(err)
+	})
+}
diff --git a/pkg/snowflake/database_test.go b/pkg/snowflake/database_test.go
index 8c8ed6be..f86cdb5c 100644
--- a/pkg/snowflake/database_test.go
+++ b/pkg/snowflake/database_test.go
@@ -63,6 +63,13 @@ func TestDatabaseCreateFromDatabase(t *testing.T) {
 	r.Equal(`CREATE DATABASE "db1" CLONE "abc123"`, q)
 }
 
+func TestDatabaseCreateFromReplica(t *testing.T) {
+	r := require.New(t)
+	db := snowflake.DatabaseFromReplica("db1", "abc123")
+	q := db.Create()
+	r.Equal(`CREATE DATABASE "db1" AS REPLICA OF "abc123"`, q)
+}
+
 func TestListDatabases(t *testing.T) {
 	r := require.New(t)
 	mockDB, mock, err := sqlmock.New()