pg-database-create.js

// Copyright 2024 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

// sample-metadata:
//  title: Creates a PostgreSQL Database.
//  usage: node pg-database-create.js <INSTANCE_ID> <DATABASE_ID> <PROJECT_ID>

'use strict';

function main(
  instanceId = 'my-instance',
  databaseId = 'my-database',
  projectId = 'my-project-id'
) {
  // [START spanner_postgresql_create_database]
  /**
   * TODO(developer): Uncomment these variables before running the sample.
   */
  // const instanceId = 'my-instance';
  // const databaseId = 'my-database';
  // const projectId = 'my-project-id';

  // Imports the Google Cloud client library
  const {Spanner, protos} = require('@google-cloud/spanner');

  // creates a client
  const spanner = new Spanner({
    projectId: projectId,
  });

  const databaseAdminClient = spanner.getDatabaseAdminClient();

  async function createPgDatabase() {
    // Creates a PostgreSQL database. PostgreSQL create requests may not contain any additional
    // DDL statements. We need to execute these separately after the database has been created.
    const [operationCreate] = await databaseAdminClient.createDatabase({
      createStatement: 'CREATE DATABASE "' + databaseId + '"',
      parent: databaseAdminClient.instancePath(projectId, instanceId),
      databaseDialect:
        protos.google.spanner.admin.database.v1.DatabaseDialect.POSTGRESQL,
    });

    console.log(`Waiting for operation on ${databaseId} to complete...`);
    await operationCreate.promise();
    const [metadata] = await databaseAdminClient.getDatabase({
      name: databaseAdminClient.databasePath(projectId, instanceId, databaseId),
    });
    console.log(
      `Created database ${databaseId} on instance ${instanceId} with dialect ${metadata.databaseDialect}.`
    );

    // Create a couple of tables using a separate request. We must use PostgreSQL style DDL as the
    // database has been created with the PostgreSQL dialect.
    const statements = [
      `CREATE TABLE Singers 
        (SingerId   bigint NOT NULL,
        FirstName   varchar(1024),
        LastName    varchar(1024),
        SingerInfo  bytea,
        FullName    character varying(2048) GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED,
        PRIMARY KEY (SingerId)
        );
        CREATE TABLE Albums 
        (AlbumId    bigint NOT NULL,
        SingerId    bigint NOT NULL REFERENCES Singers (SingerId),
        AlbumTitle  text,
        PRIMARY KEY (AlbumId)
        );`,
    ];
    const [operationUpdateDDL] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId
      ),
      statements: [statements],
    });
    await operationUpdateDDL.promise();
    console.log('Updated schema');
  }
  createPgDatabase();
  // [END spanner_postgresql_create_database]
}

process.on('unhandledRejection', err => {
  console.error(err.message);
  process.exitCode = 1;
});
main(...process.argv.slice(2));