[Epic] Introduce schema_id for all object that use schema_name

[bobbyiliev]

As a follow-up task for #334 we can work on adding the schema_id when creating views, tables, etc.

That way schema_name = materialize_schema.schema.name could be schema_id = materialize_schema.schema.id which wouldn't change after a schema is renamed.

Resources that currently rely on the schema_name:

• pkg/resources/resource_connection_aws_privatelink.go
• pkg/resources/resource_connection_confluent_schema_registry.go
• pkg/resources/resource_connection_kafka.go
• pkg/resources/resource_connection_postgres.go
• pkg/resources/resource_connection_ssh_tunnel.go
• pkg/resources/resource_grant_connection.go
• pkg/resources/resource_grant_connection_default_privilege.go
• pkg/resources/resource_grant_materialized_view.go
• pkg/resources/resource_grant_schema.go
• pkg/resources/resource_grant_secret.go
• pkg/resources/resource_grant_secret_default_privilege.go
• pkg/resources/resource_grant_source.go
• pkg/resources/resource_grant_table.go
• pkg/resources/resource_grant_table_default_privilege.go
• pkg/resources/resource_grant_type.go
• pkg/resources/resource_grant_type_default_privilege.go
• pkg/resources/resource_grant_view.go
• pkg/resources/resource_index.go
• pkg/resources/resource_materialized_view.go
• pkg/resources/resource_secret.go
• pkg/resources/resource_sink_kafka.go
• pkg/resources/resource_source_kafka.go
• pkg/resources/resource_source_load_generator.go
• pkg/resources/resource_source_postgres.go
• pkg/resources/resource_source_webhook.go
• pkg/resources/resource_table.go
• pkg/resources/resource_type.go
• pkg/resources/resource_view.go

Data sources:

• pkg/datasources/datasource_connection.go
• pkg/datasources/datasource_index.go
• pkg/datasources/datasource_materialized_view.go
• pkg/datasources/datasource_secret.go
• pkg/datasources/datasource_sink.go
• pkg/datasources/datasource_source.go
• pkg/datasources/datasource_table.go
• pkg/datasources/datasource_type.go
• pkg/datasources/datasource_view.go

[benesch]

I've been wondering about this for a while. Thanks for starting this conversation with the cloud team, @bobbyiliev!

This is a larger conversation, but I do wonder if we ought to require use of schema_id and database_id instead of schema_name and database_name. Like, arguably it was just a mistake to use names (a mistake we inherited from the Snowflake provider), because those names can change. Using IDs is much more in line with what other Terraform providers (e.g., AWS) do to link resources together.

The one downside is that using IDs instead of names is substantially less ergonomic if you're managing databases and schemas outside of Terraform. If all I want to do is use Terraform to create a source in a schema named my_team.public that I created outside Terraform, it's much nicer to just say database_name = "my_team"; schema_name = "public" than it is to have to create a data source to look up the IDs of my_team and public.

That said, I think most people use Terraform specifically to manage databases and schemas, and then using IDs is approximately as ergonomic as IDs since you would just say database_id = materialize_database.my_team.id instead of database_name = materialize_database.my_team.name.

@morsapaes we've been connected with the folks who built the Snowflake Terraform provider, right? It'd be neat to try to talk to them to understand whether they also think using database/schema names instead of IDs was a mistake.

[jubrad]

@benesch I think we could use a data provider to get the id from the name in the case that databases are managed outside of TF. It's not that gross to do something like this if you don't know the DB id.

data "materialize_database" "mydb" {
   name = "mydb"
}

resource "materialize_table" "test_table" {
  name = "test_table"
  database = data.materialize_database.mydb.id
}

[benesch]

@jubrad yes I agree! I just think it's a little more of a hassle:

If all I want to do is use Terraform to create a source in a schema named my_team.public that I created outside Terraform, it's much nicer to just say database_name = "my_team"; schema_name = "public" than it is to have to create a data source to look up the IDs of my_team and public.