Creating a new database on AlloyDB using terraform

[sumitdesai]

I have created a new AlloyDB cluster and instance on GCP AlloyDB using erraform. As per the docs, the default database name is postgres and I am able to connect successfully to it using psql command.

Now, I need to create a new database with a different name using terraform. However, I am not able to find any documentation on what resource type can be used for doing so. I tried using google_sql_database but it did not work

[ms4446]

There is no direct resource type in Terraform for creating a new database within an AlloyDB cluster on Google Cloud. However, there are potential workarounds you can consider.

One approach is to use the Google Cloud SQL API directly, if AlloyDB supports it. You would typically use the POST /v1beta4/projects/{project}/instances/{instance}/databases endpoint to create a new database. However, this endpoint is specific to Google Cloud SQL, and its applicability to AlloyDB needs verification.

Another method is to utilize Terraform with the appropriate provider. The Terraform Provider for Google Cloud has a resource type named google_sql_database designed for Google Cloud SQL databases. If you've tried this for AlloyDB and encountered issues, it's likely because AlloyDB might not be directly supported by this Terraform resource. Here's an example of how you might use it for Google Cloud SQL:

resource "google_sql_database" "default" {
 name  = "my_new_database"
 instance = "my_instance_name"
}

After creating the new database, you'll need to set up access permissions. Again, using Google Cloud SQL as an example, you can create a new user with the google_sql_user resource:

resource "google_sql_user" "default" {
  name = "my_new_user"
  password = "my_new_password"
  instance = "my_instance_name"
}

To connect to the database, if AlloyDB is PostgreSQL-compatible, you can use the psql command:

psql -h my_instance_name -d my_new_database -U my_new_user

However, it's crucial to note that the steps and resources mentioned are based on Google Cloud SQL. For AlloyDB-specific operations, you should refer to its official documentation or other relevant resources to ensure accuracy.

[sumitdesai]

I already tried using "google_sql_database", but it did not work. Looks like it won't support AlloyDB

[ms4446 · Accepted solution]

Here. are a few workarounds that you can try:

• Use the gcloud CLI or the Google Cloud Console to create new databases in AlloyDB.
• If you need to automate the database creation, you can use a local-exec provisioner in Terraform to call the gcloud CLI commands.
• You could also write a custom script that uses the appropriate AlloyDB API calls, if such APIs are available.

[sumitdesai]

Yes, I am trying to do this using Alembic since AlloyDB provisioner is also not available yet in Terraform

[ms4446]

Yes, you can use Alembic to create a new database within an AlloyDB cluster. Alembic is a Python library for managing database migrations. It can be used to create, upgrade, and downgrade databases.

To use Alembic to create a new database in AlloyDB, you will need to do the following:

1. Install Alembic: Create a new Python project and install Alembic using pip install alembic.

2. Initialize Alembic: Run alembic init alembic to create a new Alembic environment. This will create a directory called alembic with a configuration file alembic.ini and a versions folder for migration scripts.

3. Configure Database URL: Edit the alembic.ini file to set the sqlalchemy.url to your AlloyDB instance's connection string.

4. Create Migration Script: Use the command alembic revision --autogenerate -m "create_my_table" to create a new migration script. This script will be placed in the versions directory.

5. Edit Migration Script: Modify the generated script to include the operations you want to perform, such as creating a new table.

6. Run Migrations: Apply the migration to your database with the command alembic upgrade head.

Please note that if you need to create a new database (as in a new database instance, not just new tables within an existing database), you would typically do this outside of Alembic, using your database's command-line tools or administrative interfaces.

If you're looking to manage the schema of an existing database within an AlloyDB cluster, then Alembic is a suitable tool for that purpose.