All Collections
Getting Started
Working with AlloyDB
Working with AlloyDB

Setting up AlloyDB projects in SqlDBM

S
Written by Serge Gershkovich
Updated over a week ago

AlloyDB is a PostgreSQL-compatible database service for demanding enterprise database workloads that combines the best of Google Cloud Platform with one of the most popular open-source database engines. Taking advantage of GCP architecture, AlloyDB boasts impressive performance stats over standard PostgreSQL:

  • 4X faster for transactional workloads

  • 100X faster for analytical queries

Now fully supported as a SqlDBM project type, you can harness the power of AlloyDB modeling with familiar features like Reverse and Forward Engineering, Alter Scripts, Revision Compare, and others.

Starting a project

After authentication, you are taken to the Dashboard page. You can manage and create new projects from here.

Alternatively, create a new project from the main SqlDBM menu in an existing project.

Click Create Project button.

Look for AlloyDB from the available project types and hover to bring up the New, Existing, and Sample project options.

  • New - develop a model from scratch

  • Existing - bring your database by reverse engineering from DDL

  • Sample - start with pre-loaded tables and sample diagrams

Reverse Engineer an existing database

The Reverse Engineering feature allows SqlDBM to automatically identify, relate, and diagram your AlloyDB database using a DDL script. To begin the process, select the "Reverse Engineering" option on the left pane.

Reverse Engineer Diagrams

Follow the instructions provided to generate DDL from your AlloyDB instance and import it to SqlDBM using the upload function or pasting it into the editor.

For reference, the menu items for this feature are as follows:

º Back

º Upload SQL script (from file or text box)

º Direct DB Connection

º Clear

º Import

Click the upload (up arrow) or the "Upload It" button to allow SqlDBM to parse the script.

Review the metadata summary of the objects to be imported on the top of the screen. You can see a breakdown of the tables and optionally select or unselect specific elements on the left.

Review the "Add Tables" options on the left to select which diagrams will be generated.

  • "Reverse Engineer Diagram" - generates an ER diagram with all the objects in the script and their relationships.

  • "RE: Schema Diagrams" - generates an ER Diagram per schema object in the DDL

  • "Diagram Name" diagram - adds the objects to the currently selected diagram in case of an existing project.

When you're ready, press the "Import" button or triangle icon on the top menu bar to finish Reverse Engineering your database.

Working with tables on a diagram

SqlDBM allows users to see tables and their columns on the canvas

A table will display certain metadata and identifiers:

• Schema name

• Table name

• Line of Primary key indicator

• Column name

• Datatype

• Primary or Foreign key indicators

• Null Indicator

Create table

Create a new table using:

1. The top bar. Click on the "Add Table" icon

2. Clicking on an existing table. Click on the icon near a table. The icon by the left of the table creates a child table, an icon by the right of the table creates a parent table.

3. Using the explorer context menu

4. Using the explorer bottom bar

5. Using the Shortcuts. Use Insert key

6. Using the diagram context menu. Press right key of your mouse by free area of canvas

Edit mode

For creating or editing columns, use the edit mode. You can call it by double-clicking on the table or pressing the Enter key.

Notice, that In edit mode all columns that are represented above the solid line are considered as Primary keys.

Table in Explorer

You can find all tables along with other database objects in the Diagram explorer and Database explorer panel. The table icons may be colored if the color indicator was set in the Table properties.

Table Properties

Use the table properties to configure your tables. The properties will depend on your database/project type, where applicable. These are the general properties that can be set regardless of table type and database type:

• Table

• Columns

• Indexes and keys

• Check constraints

• Options

• Storage Options

• Toast Storage Options

• Post Script

• Description

• Format

• Related objects

Table

Type:

• Schema name,

• Logical table name

• Physical table name

Use Override button to make the physical name identical to the logical one. Pay attention to naming conventions, they will determine what types of names are permitted.

Columns

Here you can see full list of columns. You also may create, remove, and copy them using the Column bottom bar.

Click on the column name to be redirected to column properties.

Indexes & keys

This tab is dedicated to indexes, primary keys, foreign keys, alternative keys and clustering keys. Click on Key icon to hide or show Foreign keys.

Post Script

You may add post-deployment script into specific input (up to 10000 characters). It will be injected after CREATE TABLE script section. This is useful for granting privileges after a table is created.

Description

Add a description to make your database more understandable for other team members. It will be injected as a comment in the generated script and you also will description in "Database Documentation" option.

Format

Use a specific palette to colorize the table. Then you can see the same color for icon that near object name in Explorer panel. Notice, by default, the table has the same color indicator as the related schema.

Related objects

This options helps users bring parent and child tables, if they were hidden from current diagram. You also can easily navigate to them to understand clearer relations in your projects.

Forward Engineering and generating SQL

The Forward Engineering function in SqlDBM allows for the export of the database objects created in the SqlDBM project by generating DDL. The Forward Engineering options allow you to specify which objects you want to export and whether they should be created as new objects or altered from a previous version.

Note, SqlDBM will not make any changes directly to your database. The generated script should be run manually once it has been reviewed.

To get started, click on the Forward Engineer button from the left pane.

Generation Options

Generation options (on the right panel) apply at the project level. Familiarize yourself and set them accordingly before generating the SQL script.

Main

  • Safe Scripts - The script checks if an object exists before deleting or creating it, so there is no error when executing the script. It applies to CREATE and DROP statements (described below).

Database

  • Create schema - use only when creating the schema for the first time. The CREATE will fail if the schema already exists.

  • Drop schema - used to delete an existing schema. Be careful if objects already exist in the schema. Depending on your database parameters, this will either delete all objects in the schema or fail with a warning.

Table

  • Create - select to generate the CREATE table SQL for corresponding tables.

  • Drop - select to generate the DROP table SQL for corresponding tables.

Quote Options

  • Double Quotes - select to enable double quotes when generating object names (e.g., "My_Schema"."My_Table")

  • Empty - select to generate object names without quotes (e.g., My_Schema.My_Table)

Views

  • Create - select to generate the CREATE view SQL for corresponding views.

  • Drop - select to generate the DROP view SQL for corresponding views.

Generate SQL Script

To generate the DDL for the database objects created in your project, follow these three steps.

  1. Set the options (as described above)

  2. Select all or some of the objects you wish to generate

  3. Press the "Generate SQL" icon or button as indicated

Review the generated script on the next screen and run it in your database environment.

Use the broom icon to return to the "Forward Engineering" screen and start again.

Use the copy icon to copy the script to the clipboard for export.

Generate Alter Script

Use the Forward Engineering feature to generate an alter script with all the changes made between the current and any previous revisions.

Note that any unsaved changes will not be reflected until they are saved to a revision.

To generate the DDL for altered objects, follow these three steps.

  1. Set the options (as described above)

  2. Press the "Generate Alter SQL" icon or button as indicated (all modifications will be considered)

Select the "Generate Alter SQL" option.

Choose any previous revision to generate the later script.

Verify the script and proceed,

Use the broom icon to return to the "Forward Engineering" screen and start again.

Use the copy icon to copy the script to the clipboard for export.


​
​

Did this answer your question?