Forem: Adela

⚙️🔥 Automating Database Schema Change workflow Using GitHub Actions 🐙🚀

Adela — Wed, 20 Mar 2024 10:20:04 +0000

Tutorial repository https://github.com/bytebase/ci-example

Developers like to keep their schema migration scripts in Git along with the application code. Thus the migration scripts will be reviewed and version-controlled in the same way as the application code.
However, developers still have to manually paste the migration script into their SQL client or ask the DBAs to run it against the target database. This is inefficient and error-prone:

What if you paste the wrong script / miss the script?
What if you run the script against the wrong database?

This tutorial teaches how to get rid of this manual process and fully automate database schema change
using GitHub Actions and Bytebase API.

Above shows a typical workflow:

Developer creates a PR containing the migration script. After PR is created, it triggers a GitHub
Action to lint the SQL by calling Bytebase SQL Review API.
TL approves the PR.
Upon PR approval, it triggers a GitHub Action to create a Bytebase rollout issue.
The issue contains the migration script changes.
Depending on the configured approval and rollout policy, it may require manual approval and rollout from DBA. Another GitHub Action is configured to block the PR merge until Bytebase rolls out the schema migration. Sometimes, a PR contains both code and schema changes. This setup guarantees the schema migration is applied before the code deployment.
Bytebase deploys the schema change and marks the issue as Done.
PR re-runs the migration status check and now it turns green.
Now PR can be merged.

Prepare Bytebase

Suppose Bytebase is running at https://bytebase.example.com/. To begin, we'll first set up the necessary data to support our API interactions.

Service account: As an admin, we add a service account ci@service.bytebase.com with the Workspace DBA role, which will be used to authenticate the API calls.

To limit the service account permissions, you can choose to grant Workspace Member instead of Workspace DBA.
And then in the particular project, grant the account the permission to create an issue.

A database in a project: We have a project called Example, and a database: example.

Prepare GitHubs Actions

Go and check the example on GitHub https://github.com/bytebase/ci-example.

The repository contains several GitHub Action workflows, you may go to .github/workflows to view.

We will use the following workflows:

bytebase-sql-review.yml: Triggered on PR change. Thus any SQL review violation will block the PR.
bytebase-upsert-migration.yml Triggered on PR approval. Creates the Bytebase migration issue after approval. And whenever the migration scripts change afterwards, the migration issue will also be updated accordingly.
bytebase-check-migration-status.yml: Triggered on PR change. Thus PR will be blocked until migration completes.

Sample Workflow, Four Phases

To illustrate the workflow, we have divided it into four phases to showcase the database schema change process.

Phase 1: Not passing SQL review on GitHub

Before we delve into the workflow, let's set up the SQL Review policy in Bytebase. The example database is on the Prod environment, where we will configure SQL review policy. Here we have a policy that checks for NOT NULL constraints, which we will violate in the PR.

Returning to GitHub Actions and digging into the code, the bytebase-sql-review.yml workflow is triggered on PR change. It scans the SQL files named following the pattern **.up.sql in the PR and reports any SQL review policy violations.

Configure the environment.

   bytebase-sql-review:
      runs-on: ubuntu-latest
      env:
         BYTEBASE_URL: "https://bytebase-ci.zeabur.app"
         BYTEBASE_SERVICE_ACCOUNT: "ci@service.bytebase.com"
         DATABASE: "instances/prod-instance/databases/example"
   ...

After authentication, we call the Bytebase API /sql/check to lint the migration files. We parse the response
and emit GitHub inline annotations for each advice and mark the check as failed if any ERROR or WARNING is found.

     name: SQL Review
    steps:
      - name: Checkout
        uses: actions/checkout@v4
      - name: Login to Bytebase
        ...
      - name: Review
        id: review
        uses: ./.github/actions/sql-review
        with:
          github-token: ${{ secrets.GITHUB_TOKEN }}
          pattern: "**/*.up.sql"
          url: ${{ env.BYTEBASE_URL }}
          token: ${{ steps.login.outputs.token }}
          headers: '{"Accept-Encoding": "deflate, gzip"}'
          database: ${{ env.DATABASE }}
      ...

We create a PR with several SQL files, and it triggers both bytebase-sql-review.yml and bytebase-check-migration-status.yml. After these checks are completed, the PR is blocked due to failures.

Click on the Details for SQL Review.

You may also go to Files changed to view the annotations.

Phase 2: Passing SQL review and waiting for TL's approval on GitHub

We then fix the SQL files and pushes. After completing these checks, the PR is still blocked due to failures, but this time SQL review has passed.

In real-life scenarios, the PR also encompasses application code. Because the SQL migration has passed the basic SQL review checks, it is now time for a tech leader to approve this PR.

The developer who creates the PR assigns the tech leader to review on GitHub.

Phase 3: TL approves on GitHub and migration issue is created in Bytebase

The assigned tech leader approves the PR, and another workflow bytebase-upsert-migration.yml is triggered.

It checks the SQL files named like **.up.sql within the pull request and creates a rollout issue in Bytebase.

   bytebase-upsert-migration:
    runs-on: ubuntu-latest
    # Runs only if PR is approved and target branch is main
    if: github.event.review.state == 'approved' && github.event.pull_request.base.ref == 'main'
    env:
      BYTEBASE_URL: "https://bytebase-ci.zeabur.app"
      BYTEBASE_SERVICE_ACCOUNT: "ci@service.bytebase.com"
      PROJECT: "example"
      DATABASE: "instances/prod-instance/databases/example"
      ISSUE_TITLE: "[${{ github.repository }}#${{ github.event.pull_request.number }}] ${{ github.event.pull_request.title }}"
      DESCRIPTION: "Triggered by ${{ github.event.repository.html_url }}/pull/${{ github.event.pull_request.number }} ${{ github.event.pull_request.title }}"
    name: Upsert Migration
    steps:
    ...

Go to Bytebase and view the created issue, which consists of two tasks corresponding to the presence of two **.up.sql files in the PR.

You may notice there is an approval flow attached to the created issue, that's because we set up a default custom approval flow for DDL.

Phase 4: Migration completed and PR is mergable on GitHub

After the DBA approves and rolls out the migrations, the issue status will become Done.

Go back to GitHub, click Details for the failed bytebase-check-migration-status.yml workflow, and then click Re-run all jobs.

It checks the migration status in Bytebase and return pass if it's Done, indicating the database migration has been completed. The PR is now ready to be merged, which means the application code is ready to be deployed.

Summary

Keep in mind that workflows can be tuned according to your organization's needs:

You can attach the workflow to different branches depending on your branching strategy (e.g. trunk-based or not).
You can use different migration file formats and different migration file structures.
You can determine when to create the migration issue, upon PR approval or creation.

Whatever workflow you choose, with the help of GitHub Actions and Bytebase API, you can now keep your migration scripts in the repository, let the migration scripts go through the same code review process and automate the schema migration deployment.

🔄 How to Synchronize Database Schemas🔥🚀

Adela — Fri, 01 Mar 2024 10:21:45 +0000

This tutorial will walk you through the typical use cases for database schema synchronization in Bytebase with pure UI operations.

The Sync Schema feature in Bytebase supports copying a specific schema version from one database to multiple others. Without it, developers have to write SQL statements cautiously and apply them manually. Additionally, this feature may also be used for rollback purpose.

Preparation

Make sure you installed Docker.

Copy and paste the commands to start one Bytebase via Docker.

   docker run --init \
   --name bytebase \
   --publish 8080:8080 \
   --volume ~/.bytebase/data:/var/opt/bytebase \
   bytebase/bytebase:%%bb_version%%

Case 1 - Sync Schema to a New Database

When you set up a new environment or find the current database is nearing capacity, it becomes necessary to establish a new database and synchronize the schema from the original database.

Go into Sample Project, there are two databases hr_test on Test environment and hr_prod on Prod environment. Let's create another one on Prod.
Click New DB, fill in the form as follows and click Create:
- Name: hr_prod_2
- Environment: Prod
- Instance: Prod Sample Instance
- Database owner name: bbsample
An issue will be created and executed automatically, and the database hr_prod_2 will be ready when the issue is Done.
Within the project, click Sync Schema, choose hr_prod as the source schema, and click Next.
You may select as many target databases as you like to sync to. Here we select all (including the source) and click Select.
Bytebase will calculate the schema differences between the source and target databases, and generate the suggested DDL statements. Click Preview Issue.
An issue with the generated DDL is created automatically. Click Rollout ignoring the SQL review warning. After the issue execution is Done, the schema synchronization is completed.

Case 2 - Sync Schema from One to Many

During the product release process, for different environments, such as dev, staging and prod, you'll need to do schema synchronization. Furthermore, for the prod environment, databases with identical schemas, such as SaaS, and multi-region game deployment, you may also need to sync the schema to multiple databases at the same time.

Within the project, select hr_test and click Edit Schema. With the help of Schema Editor, add a new column email as follows and click Preview issue:
- Name: email
- Type: text
- Default: Empty string
- Not Null: checked

An issue is created and executed automatically. After it's Done, the new column email is added.
Within the project, click Sync Schema, choose hr_test as the source schema and click Next.
Select hr_prod and hr_prod_2 as the target databases. Schema differences are calculated and the suggested DDL statements are generated. Click Preview issue.
An issue with the generated DDL is created automatically. Because two databases need to be synced, there're two task blocks. After they execute one by one, the issue is Done, and email is added.

Case 3 - Rollback to Previous Schema Versions

In Bytebase Community Plan, you can choose the latest history version; In Pro Plan or Enterprise Plan, you can choose an arbitrary schema version from the full migration history. Which means you can roll back to any previous schema version.

After upgrading to Enterprise Plan, go into Sample Project, click Sync Schema and select hr_test as the source schema. When you choose the schema version, you will see the following prompt. That's because we haven't assigned the enterprise license to database instances.
Click on the lock sign, select both two instances, and click Confirm.
After the license is assigned, you can choose the schema version freely, we'll choose the previous version and click Next.
Choose the same database hr_test as the target, and click Select.
Bytebase will calculate the schema differences between the source and target databases, and generate the suggested DDL statements. Here means if you want to roll back to the previous version, you'll need to delete this line. Click Preview Issue.
Click Create, an issue with the generated DDL is created and rollout automatically. After the issue execution is Done, the schema rolls back to the previous version.

Summary

Is the experience smooth? Or do you encounter any problems? Feel free to join our Discord Group to talk about it!

🛡️4 Top Database Security Tools in 2024 🏆🔥

Adela — Thu, 01 Feb 2024 10:04:03 +0000

In today's digital age, where data is as valuable as oil, protecting this crucial asset is essential for every organization. Threats arise from external sources like hackers and malware, as well as internal ones, including human errors and malicious insiders. This article will explore leading database security tools designed to shield your data from these varied risks.

Networking - Tailscale
Database credential management - Infisical
On-demand database access - Indent
All-in-one human-to-db operation - Bytebase

Tailscale

Tailscale is a VPN service that makes the devices and applications you own accessible anywhere in the world, securely and effortlessly. It enables encrypted point-to-point connections using the open source WireGuard® protocol, which means only devices on your private network can communicate with each other.

When it comes to databases, Tailscale can be used to connect to your database from any location globally, without having to expose your database to the public internet or setting up extra SSH tunnels.

Its free tier accommodates up to 3 users and 100 devices, which is for individuals or small businesses. For additional capabilities like Access control and Audit logs, paid plans start at $6 per user per month.

Infisical

Infisical is an open-source, end-to-end encrypted secret management platform for storing, managing, and syncing application configuration and secrets like API keys, database credentials, and environment variables across applications and infrastructure. It is an alternative to HashiCorp Vault and AWS Secrets Manager.

You have the option to host Infisical on your premises or utilize their cloud offering.

With its help, your database credentials can be stored in a secure vault and be accessed by your application only when needed. In this way, you can avoid storing your database credentials in your application code or configuration files. Access is facilitated through CLI, SDK, Docker, Kubernetes, or REST API.

The free tier supports up to 5 developers and 3 environments, catering to hobbyists. For features such as Access Controls and Audit Logs, paid plans start at $6 per developer per month.

Indent

Indent provides team members with on-demand access to cloud apps and infrastructure in seconds. They aptly describe this innovation as the request access button for any software or service utilized by the company.

In the context of database access management, Indent provides you the capability to grant time-bound, granular access to systems that handle customer data. It offers three methods of integration:

Zero Trust Network (ZTN) - e.g. Tailscale
Identity Group - e.g. Okta
Custom Integration

Indent provides a secure-by-default free tier suitable for small teams on a trial basis. For those requiring more, the paid plans begin at $8 per user per month.

Bytebase

Bytebase is an open-source database DevOps tool, it's the GitLab/GitHub for managing databases throughout the application development lifecycle. It offers a web-based collaboration workspace for DBAs, Developers and platform engineers. It consolidates disparate DB tools
such as DBeaver, Liquibase, Flyway into a single place.

It seeks to encompass all interactions between humans and databases across every database, including managing changes, querying data, handling access, and beyond. Thanks to its features such as SQL Lint, Database CI/CD, Data Access Control, Data Masking, and more, you can be confident that all your database activities are executed via standardized procedures and completely auditable.

With Community Plan, you can have up to 10 users and up to 5 databases for free. For medium to large organizations, the Enterprise plan offers advanced options like Data Access Control, Audit Log, and additional features.

Summary

In this article, we have explored four database security tools that can help you protect your database from external and internal threats. Each tool offers its unique strengths; by understanding your specific needs, you can choose the one that most aligns with your requirements.

🐣Your First Database Schema Change in 5 Minutes with Bytebase

Adela — Thu, 18 Jan 2024 06:42:04 +0000

In this tutorial, you'll use the sample databases Bytebase provides by default to get familiar with the product in the quickest way.

Step 1 - Run via Docker

Install and start Docker.
Open Terminal to run the command:

  docker run --init \
    --name bytebase \
    --publish 8080:8080 \
    --volume ~/.bytebase/data:/var/opt/bytebase \
    bytebase/bytebase:2.13.1

When the Terminal shows the following message, the execution is successful.

██████╗ ██╗   ██╗████████╗███████╗██████╗  █████╗ ███████╗███████╗
██╔══██╗╚██╗ ██╔╝╚══██╔══╝██╔════╝██╔══██╗██╔══██╗██╔════╝██╔════╝
██████╔╝ ╚████╔╝    ██║   █████╗  ██████╔╝███████║███████╗█████╗
██╔══██╗  ╚██╔╝     ██║   ██╔══╝  ██╔══██╗██╔══██║╚════██║██╔══╝
██████╔╝   ██║      ██║   ███████╗██████╔╝██║  ██║███████║███████╗
╚═════╝    ╚═╝      ╚═╝   ╚══════╝╚═════╝ ╚═╝  ╚═╝╚══════╝╚══════╝

  Version 2.13.1 has started on port 8080 🚀

Now you have Bytebase running in Docker.

Open Bytebase in localhost:8080, fill in the fields and click Create admin account. You'll be redirected to the workspace.

Follow the Quikstart guide on the bottom to click around or dismiss it by now. You can click your avatar on top right and click Quickstart on the dropdown menu to reopen it later.

Step 2 - One Issue with Two Stages

Click My Issues on the left sidebar, and click the issue SAM-101 which is created by default.

The issue is waiting to rollout. There's a pipeline consisting of two stages:
1. Test Stage: apply to database hr_test on Test Sample instance
2. Prod Stage: apply to database hr_prod on Prod Sample instance

Test stage is active by default.

Click Prod Stage to switch to it, and you will see the two stages share the same SQL but to different databases. You may also notice there's a warning sign for SQL review on the Prod stage. That's because when the issue is created, Bytebase will run task checks automatically. SQL review is one of them.

Click the warning sign to see the details. If you wonder why only Prod Stage has the warning sign, it's because by default SQL Review is only configured for Prod environment. You can click the Settings (gear) on the top right, and click Security & Policy > SQL Review to have a look.

Step 3 - Roll out on Test Stage

Switch back to Test Stage and click Rollout. Click Rollout on the confirmation dialog.

When the SQL is applied, there will be a checkmark on the Test Stage. Click View change and you'll see the diff.

Step 4 - Roll out on Prod Stage

There are two ways to roll out on Prod Stage regarding the SQL review result.

If you are confident with the SQL, you can click Rollout directly. Check the Rollout anyway, and click Rollout on the confirmation dialog.

Another way is to edit the SQL. Click Edit on top of the SQL, and add the NOT NULL. It will look like this:

   ALTER TABLE employee ADD COLUMN IF NOT EXISTS email TEXT NOT NULL DEFAULT '';

Click Save, the checks will be run again. This time the SQL review will pass and it will roll out automatically. The issue will become Done as well.

You may ask why it's rolling out automatically, it's because for Community Plan, the rollout policy is automatic if the SQL review passes. You may go to Environments to check.

Next Step

Now you have successfully performed your first schema change in Bytebase. It's the core part of Bytebase. You can continue to dig deeper by following Deploy Schema Migration with Rollout Policy.

🔥Top psql Commands with Examples🐘

Adela — Wed, 22 Nov 2023 03:10:10 +0000

PostgreSQL, or Postgres, is a free and open-source relational database management system (RDBMS) known for its reliability, feature robustness, and performance. It has been actively developed for over 35 years and is considered one of the most advanced open-source RDBMSs available.

psql is the official CLI client to command the PostgreSQL database. It supports interactive and noninteractive use.

Below list of the 10 most commonly used psql commands with examples.

1. Connect to a database - `psql -U xxx -p -h xxx -d xxx`

If you are using the default PostgreSQL username postgres and have not set a password, you can connect to the database using the following command. You will be prompted to enter the password for the postgres user.

psql

If you are using a different username or have set a password for the postgres user, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.

psql -U your_username

If the PostgreSQL server is running on a different port than the default (5432), you can connect to the database using the following command. You will be prompted to enter the password for the specified user.

psql -p 5433

If the PostgreSQL server is running on a remote host, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.

psql -h remote_host -p 5432

In all of these examples, you can also specify the name of the database you want to connect to using the -d option. For example, to connect to the database named my_database, you would use the following command:

psql -d my_database

2. Run a single command - `-c`

To run a single command in PostgreSQL using the psql CLI, you can use the -c option followed by the SQL command you want to execute.

psql -d my_database -c "SELECT * FROM my_table;"

List available databases - \l This command will all of the databases that are currently available on the server. This can be useful for getting an overview of the databases that are available to you.

postgres-# \l

   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(3 rows)

4. Switch to a specified database - `\c`

The \c command switches to the specified database. This is useful for working with a specific database after you have listed the available databases.

postgres-# \c postgres


psql (16.0, server 16.1 (Debian 16.1-1.pgdg120+1))
You are now connected to database "postgres" as user "postgres".

5. List tables - `\dt`

The \dt command lists all of the tables that are currently available in the current database. This can be useful for getting an overview of the tables that are available to you in the current database.

postgres-# \dt

List of relations
Schema | Name | Type | Owner
-------+---------+-------+--------
public | mytable | table | postgres
public | users | table | postgres
public | orders | table | postgres

6. Describe a specified table - `\d`

The \d command describes the specified table. This provides information about the table's columns, data types, and constraints.

postgres-# \d my_table

Table: my_table

Column | Data Type | Constraints
-------+------------+-------------------
id | SERIAL | PRIMARY KEY
name | VARCHAR(50) | NOT NULL
email | VARCHAR(100) | UNIQUE

7. Listing users - `\du`

The \du command lists all of the users that exist in the current database.

postgres-# \du

List of roles
Role name | Attributes
---------+------------
postgres | SUPERUSER, CREATEDB, CREATEROLE, INHERIT, REPLICATION
myuser | CREATEDB

The \du username command is used to list the roles (users) in the current database that have the specified username.

postgres-# \du myuser

List of roles
Role name | Attributes
---------+------------
myuser | CREATEDB

If the specified username does not match any roles in the current database, the output will be an empty table.

postgres-# \du youruser

List of roles
Role name | Attributes
---------+------------
 |

8. Show connections - `pg_stat_activity`

To list all active connections, execute the following SQL query.

postgres-# SELECT * FROM pg_stat_activity;

pid | datname | username | client_addr | client_port | backend_start | query_start | query | state
-------+---------+---------+-------------+-------------+---------------+---------------+-----------------+----------
1234 | mydb1   | postgres | 192.168.1.100 | 5432 | 2023-10-04 15:04:00 | 2023-10-04 15:04:05 | SELECT * FROM mytable; | active
5678 | mydb2   | user1    | 192.168.1.101 | 5432 | 2023-10-04 15:05:00 | 2023-10-04 15:05:03 | UPDATE mytable SET name = 'John Doe' WHERE id = 123; | idle in transaction
9012 | postgres | 192.168.1.102 | 5432 | 2023-10-04 15:06:00 |                |                | idle |

You can also filter the results to specific connections based on criteria such as username, database name, or state. For example, to list only active connections to the database mydb, use the following query:

postgres-# SELECT * FROM pg_stat_activity WHERE datname = 'mydb' AND state = 'active';

Similarly, to list connections currently executing queries, use the following query:

postgres-# SELECT * FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction');

9. Kill connection - `pg_terminate_backend(), kill`

Once you have identified the PID of the connection you want to kill, you can use the pg_terminate_backend() function to terminate it. For example, the following command will kill the connection with PID 1234:

postgres-# SELECT pg_terminate_backend(1234);

Using the kill command is a less common method for killing PostgreSQL connections, as it can be more dangerous and less reliable than using the pg_terminate_backend() function.

However, it may be necessary in certain situations, such as when the connection is unresponsive or the pg_terminate_backend() function fails.

kill -9 1234




## 10. Quit - `\q`,Ctrl+D/Ctrl+Z
Using the `\q` command or the keyboard shortcut Ctrl+D(or Ctrl+Z on Windows). This will immediately exit the `psql `CLI.👋

🚛 Deploy Database Schema Migrations with Bytebase

Adela — Fri, 10 Nov 2023 07:09:31 +0000

Bytebase offers a powerful GUI for schema migration deployments. This tutorial will show you how to use Bytebase to deploy schema migrations with features like SQL Review, custom approval, time scheduling, and more.

Bytebase offers Community, Pro and Enterprise Plans. Advanced plans
unlock new capabilities of deploying schema migrations and this tutorial will walk you through them progressively.

Features covered

Level 1 - Automatic SQL Review (available in all plans)
Level 2 - Rollout Policy (available in Pro and Enterprise)
Level 3 - Custom Approval (available in Enterprise)

Preparation

Make sure your Docker is running, and start the Bytebase Docker container with the following command:

docker run --init \
  --name bytebase \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:%%bb_version%% \
  --data /var/opt/bytebase \
  --port 8080

Bytebase is running successfully in Docker, and you can visit it via localhost:5678. Register an admin account and it will be granted the workspace owner role automatically.

Level 1: Automatic rollout with SQL review (Community Plan)

Go to Environments > Test and Environments > Prod, you'll see
1. The Rollout policy is automatic. Unless there's some warning or error, the rollout will be automatically executed after the issue is created.
2. SQL Review is enabled on Prod with a sample policy.

Click SQL Review Sample Policy to go to SQL Review policy, there're three rules activated. Let's pay attention to Enforce NOT NULL constraints on columns rule, and we'll try to violate it.

Go to Sample Project, click Edit Schema, choose both Test and Prod databases, and click Next. Paste the following SQL statements into Raw SQL, and click Preview issue.

ALTER TABLE "public"."employee"
    ADD COLUMN "country" text;

SQL Review checks will dry run before the issue is created. Here let's create the issue regardless of the dry run result.
After the issue is created, SQL Review will run automatically along with some other checks. You'll see there's a warning for the task on Prod.

Click Edit, paste this SQL statement and click Save. Apply this change to all tasks. The SQL checks will run again, and you'll see the warning disappear and it will roll out automatically.

ALTER TABLE "public"."employee"
ADD COLUMN "country" text NOT NULL DEFAULT '';

Click View change to see the diff or go to Change History to view all changes.

Level 2: Manual rollout with dedicated roles and scheduled time (Pro Plan)

With Pro Plan, you'll get two additional features:

Manual rollout policy. You can specify multiple pre-defined roles to manually roll out the change.
Time scheduling. You can specify a particular time to roll out the change.

To simplify the process, we'll use 14-day enterprise trial here. Click the Start free trial to upgrade.

Go to Environments > Prod, choose Manual rollout by dedicated roles and check all the roles. Click Update.

Go to Sample Project, click Edit Schema, choose both Test and Prod databases, and click Next. Paste the following SQL statements into Raw SQL, and click Preview issue.

ALTER TABLE "public"."employee"
    ADD COLUMN "city" text NOT NULL DEFAULT '';

Click Create, and after Task checks runs, you'll see the SQL running on Test automatically but waiting to run on Prod.

Click Rollout to trigger directly or set a Rollout time.

Level 3: Manual rollout with custom approval (Enterprise Plan)

If you want the approval flow to be more dynamic based on the context like the type of SQL statements, the affected rows and etc,
then you can configure custom approval flow.

Go to Instances and click Assign License for both instances. Without doing this, the enterprise plan required for custom approval won't be enabled on instances.

Click Settings (the gear icon) > Security & Policy > Custom Approval. Choose Project Owner -> DBA as High Risk for DDL.

Click the related risk rules or Settings (the gear icon) > Security & Policy > Risk Center. Click Add rule. Set High Risk and DDL as The risk for the production environment is considered to be high.

Click Settings (the gear icon) and add a DBA account. Click it in the Active members list, and edit its password. You'll need this account later to do the approval.
Go to Environments > Prod, you now unlock the third option for rollout policy Manual rollout by the last approver from the custom approval flow. Choose it.

Go to Sample Project, click Edit Schema, choose both Test and Prod databases, and click Next. Paste the following SQL statements into Raw SQL, and click Preview issue.

ALTER TABLE "public"."employee"
    ADD COLUMN "district" text NOT NULL DEFAULT '';

Create the issue and the approval flow is matched. Since it's in the pipeline, it will be brought forward to the Test stage to review earlier. Follow its order to approve. DBA will be the one to do the rollout.

Summary

You have now learned how to use Bytebase to deploy schema migration in a basic way. Bytebase also provides other advanced features for your interests:

GitOps - Observe Git code push events and trigger schema migration;
Batch changes - Change multiple databases in a single workflow;
Branching - Collaborate on schema changes using Git-like branching;
Changelist - Organize and apply changes sequentially, or export them for offline execution.

Join our Discord channel to discuss.

🐣 Step-by-Step Guide to Data Masking 🎭

Adela — Fri, 13 Oct 2023 09:50:51 +0000

Bytebase Dynamic Data Masking can mask sensitive data in the query result based on the context on the fly.
It helps organizations to protect sensitive data from being exposed to unauthorized users.

This tutorial will teach you how to set up and try out data masking in Bytebase within 30 mins.

Prerequisites

Before starting, make sure you have installed Docker.

Preparation

Make sure your Docker is running, and start the Bytebase Docker container with following command:

docker run --init \
  --name bytebase \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:%%bb_version%% \
  --data /var/opt/bytebase \
  --port 8080

Bytebase is running successfully in Docker, and you can visit it via localhost:5678. Register an admin account and it will be granted the workspace owner role automatically.
Go back to the Bytebase console, click Start free trial on the left bottom and upgrade to Enterprise plan.
Click Instances, and click Assign License. Select both instances, and click Confirm. Without doing this, the enterprise plan required for data masking won't be enabled on instances.

No Masking

Login to Bytebase,click SQL Editor, you'll be redirected to SQL Editor page. Choose (Prod) employee > public > employee, and then run SELECT * FROM employee;, you'll see the following result. Run the same query against (Test) employee, the result is the same.

Global Masking Rule

You may want to batch apply masking settings, for example, you want to mask all the birth_date columns in all the tables in the employee database. You can use Global Masking Rule to achieve this.

Click the Setting icon on the top right. Click Security & Policy > Data Masking. Click Global Masking Rule and then Add rule.
Name the rule as birth_date should be masked, and select Column name, ==. Fill birth_date in the input box, and click Confirm.
Go back to the SQL Editor page, Choose (Prod) employee > public > employee and run SELECT * FROM employee; again. You'll see the birth_date is masked. Choose (Test) employee, the result is the same.

Column Masking Rule

If you want to mask a specific column in a specific table, you can use Column Masking Rule.

Click Databases and choose employee on Prod, and select salary table.
Click the edit(pen) icon on the amount row, and click Full.
Go back to the SQL Editor page, Choose (Prod) employee > public > salary and run SELECT * FROM salary; again. You'll see the amount is masked. Choose (Test) employee, it's not.

Grant unmasked access to a user

What if you want to reveal the masked data to a specific user? You can grant unmasked access.

Click Settings icon on the top right, and click Members. Add a DBA user and click + Add. Click its link in the Active members section, click Edit to set a password, click Save.
Click Databases and choose employee on Prod, and select salary table.
Click the edit(pen) icon on the amount row, and click Grant Access. Select the DBA user, and click Confirm.
Login as the DBA user, go to SQL Editor, choose (Prod)Employee > public > salary and run SELECT * FROM salary; again. You'll see the amount is not masked.

Summary

You can now mask data in Bytebase using two methods: use Global Masking Rule to batch apply masking settings, and use Column Masking Rule to mask a specific column in a specific table. You can also grant access to a specific user to reveal the masked data.

Bytebase supports Data Masking for different databases.

If you have any questions, please join our discord.

How to Manage Data Access for Developers

Adela — Tue, 05 Sep 2023 03:15:22 +0000

When you have a team of developers, it is critical to restrict their access to data to only what they need to do their job. This ensures to protect sensitive data and prevent unauthorized access.

Bytebase provides several powerful features to help you achieve this:

You can restrict users to only view data in a specific environment.
Developer can require approval to access a specific table.
You can mask sensitive data, such as salary.
You can track who has accessed which data.
You can add watermarks to your data to deter unauthorized copying.

This tutorial will walk you through how to use these features in Bytebase.

All are Enterprise Plan only features. However, you can start a 14-day trial of the Enterprise Plan with one click without providing additional information (no credit card required).

Preparation

Make sure you have Docker installed, and if you don’t have important existing Bytebase data locally, you can start over from scratch by rm -rf ~/.bytebase/data.

You’ll need two Bytebase accounts – one Owner and one Developer.

Owner is the one who configures the settings.
Developer is the one who should only see information based on the configuration.

Step 1 - Run Bytebase via Docker

Make sure your docker daemon is running, and then start the Bytebase docker container by typing the following command in the terminal.

docker run --init \
--name bytebase \
--restart always \
--publish 5678:8080 \
--volume ~/.bytebase/data:/var/opt/bytebase \
bytebase/bytebase:2.7.0 \
--data /var/opt/bytebase \
--port 8080

Step 2 - Register accounts Owner and Developer

Visit localhost:5678 in the browser. Register an admin account, we’ll refer to it as Owner. This account will be granted Workspace Owner role. To keep it simple, we use this Owner instead of registerating another DBA in this tutorial. Check Roles and Permissions (RBAC).
Log out and register another normal account - we’ll refer to it as Developer. This account will be granted Workspace Developer role.
Switch back to Owner, now you can see there's a Sample Project with two embedded databases both named employee mapping to Test and Prod environments respectively.
Click Members and Grant Access to add Developer as Developer. Only after this, can he see the project.

Step 3 - Start Enterprise Plan trial and assign license to instances

Click Start free trial on the left bottom. Click Start 14 days trial (no credit card required).
Click Instances on the top navigation bar. You can see there are two instances. Click them one by one, and turn on Assign License for both. You may check Manage License for more details.

Data Access Control - Query Data

Log in as Developer, and you can see Sample Project. Click SQL Editor on the left bar. Within SQL Editor, you can't see anything. It's because you don't have any database access permission yet.

Step 1 - Developer can skip approvals to query data

Log in as Owner, and go to Settings > Data Access Control. Check Skip approval for Query Data in Test environment.
Log in as Developer and go into SQL Editor again. Now you can see database under Test environment. Select (Prod)employee, input SELECT * FROM salary; and run and you can see the result. Change salary to any other tables and run, you can see data as well.

Step 2 - Developer can request to query data

In practice, developers can access any database on Test environment, but they need to obtain permission to access Prod environment. In this step, we'll show you how to implement this.

Log in as Owner, and go to Settings > Custom Approval. Scroll down to Request Query, and choose Workspace Owner as Approval flow.
Go to Settings > Risk Center. Click Add Rule. Choose Request Query as Type. Choose High as Risk. Click Load for the first template on the right. Click Add.
Log in as Developer, and go to Sample Project, click Request Query. Choose Manual select and then employee table under Prod environment. Click OK.
A request issue is created with the approval flow Workspace Owner we just defined.
Log in as Owner, go to this issue and click Approve.
Log in as Developer, and go to SQL Editor. You can see salary table under Prod environment. Select (Prod)employee, input SELECT * FROM salary; and run, you'll see the result.
Input SELECT * FROM employee; and run, it'll show permission denied. You can click Request Query to request permission.

Step 3 - Owner can grant access to query data directly

Log in as Owner, and go to Sample Project. Click Members and then Grant Access. Choose Developer and assign the role Querier, All for Databases. Click Confirm.
Log in as Developer, and go to SQL Editor. You can see all databases under Test and Prod environment. Select (Prod)employee, input SELECT * FROM employee; and run, you can see the result. Change employee to any other tables and run, you can see data as well. ## Data Access Control - Export Data Export Data is similar to Query Data. Developer can skip approvals, request approvals to export data or be granted Exporter role directly by Owner.

Data Masking

Log in as Developer, and go to SQL Editor. Select (Prod)employee, input SELECT * FROM salary; and run, you can see all the information. We want to mask the amount column.
Login in as Owner, click Databases on the top navigation bar and then choose employee under Prod environment. Scroll down to Tables, click salary table, and check amount row as Sensitive.
Log in as Developer, and go to SQL Editor. Select (Prod)employee, input SELECT * FROM salary; and run, you can see the amount column is masked.

Audit Log

Bytebase records everything happened within the system. You may check the audit log.
Log in as Owner, and go to Settings > Audit Log. You can see all the queries you've run.

Watermark

Bytebase provides watermark feature to help you identify the data source. You can click Settings > General, and scroll to Security to enable it.

Summary

With Bytebase, you have now tried out the basic management of data access for developers. If you want to know more about database change as well, you can check DevOps: Database Change Management with PostgreSQL.

DevOps: Database Change Management with MariaDB and GitHub

Adela — Sat, 17 Jun 2023 16:46:33 +0000

This is a series of articles about DevOps: Database Change Management with MariaDB

DevOps: Database Change Management with MariaDB
DevOps: Database Change Management with MariaDB and GitHub (this one)

In the last article DevOps: Database Change Management with MariaDB, you have tried UI workflow in Bytebase.

This tutorial will bring you to the next level by introducing the GitOps workflow, where you commit the schema change script to the GitHub repository, which will in turn trigger the schema deployment pipeline in Bytebase.

You can use Bytebase free version to finish the tutorial.

Features included

GitOps Workflow
Change History

Prerequisites

Before you start this tutorial, make sure:

You have followed our previous UI-based change tutorial DevOps: Database Change Management with MariaDB.
You have a GitHub account.
You have a public GitHub repository, e.g test-bb-mariadb-gitops.
You have Docker installed locally.
You have a ngrok account. ngrok is a reverse proxy tunnel, and in our case, we need it for a public network address in order to receive webhooks from GitHub.com. We use ngrok here for demonstration purposes. For production use, we recommend using Caddy.

Step 1 - Run Bytebase in Docker with URL generated by ngrok

To make local-running Bytebase visible to GitHub, we’ll pass ngrok generated URL to --external-url.

   ngrok http 5678

and obtain the public URL:

Make sure your Docker daemon is running, if it’s running Bytebase container for the previous tutorial, there're two ways:

1a. Go to Settings > Workspace > General, fill in the External URL field and click Update.
1b. Stop and remove it. The data created in the last tutorial is stored under ~/.bytebase/data by default and will be restored if the system restarts. Start the Bytebase Docker container by typing the following command in the terminal. Pay attention to the last parameter --external-url https://1681-149-129-123-75.ngrok-free.app, which is generated by ngrok.

   docker run --init \
   --name bytebase \
   --platform linux/amd64 \
   --restart always \
   --publish 5678:8080 \
   --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
   --health-interval 5m \
   --health-timeout 60s \
   --volume ~/.bytebase/data:/var/opt/bytebase \
   bytebase/bytebase:%%bb_version%% \
   --data /var/opt/bytebase \
   --port 8080 \
   --external-url https://1681-149-129-123-75.ngrok-free.app

You can now visit Bytebase via https://70ca-154-9-204-36.ngrok.io/.

Step 2 - Find your MariaDB instance in Bytebase

Visit https://70ca-154-9-204-36.ngrok.io/in your browser, and log in using your admin account created from the previous article.
Click Projects on the top bar, and then click Create Project. Fill in Demo Git as Project Name, DGT as Key, Standard as Mode and click Create.
Go to the Demo Git project, and click New DB.
Fill the form with the following information and click Create.

Name: demo_db_git
Environment: Test
Instance: MariaDB test It will create an issue "CREATE DATABASE …" automatically. Because it’s for Test environment, the issue will automatically run then becomes Done. The database is created.

Click New DB again, fill the form with the following information and click Create.

Name: demo_db_git
Environment: Prod
Instance: MariaDB prod

It will create an issue "CREATE DATABASE …" automatically. Because it’s for Prod environment, the issue will require manual rollout. Click Rollout to run then becomes Done. The database is created.

Step 3 - Connect Bytebase with GitHub.com

Click Settings on the top bar, and then click Workspace > Version Control. Choose GitHub.com and click Next.
Follow the instructions within STEP 2, and in this tutorial, we will use a personal account instead of an organization account. The configuration is similar.
Go to your GitHub account. Click Settings on the dropdown menu.
Click Developer Settings at the bottom of the left side bar. Click OAuth Apps, and click New OAuth App.
Fill Application name and then copy the Homepage and Authorization callback URL in Bytebase and fill them. Click Register application.
After the OAuth application is created successfully. Click Generate a new client secret. Copy Client ID and this newly generated client secret and paste them back in Bytebase.
Click Next. You will be redirected to the confirmation page. Click Confirm and add, and the Git provider is successfully added.

Step 4 - Enable GitOps workflow with MariaDB

Go to project Demo Git, click GitOps, and choose GitOps Workflow. Click Configure GitOps.
Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose test-bb-mariadb-gitops.
Keep the default setting, and click Finish. The GitOps workflow is successfully enabled.

Step 5 - Change schema for MariaDB by pushing SQL schema change files to GitHub

In your GitHub repository test-bb-mariadb-gitops, create a folder bytebase, then create a subfolder prod, and create an sql file following the pattern {{ENV_ID}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql. It is the default configuration for file path template setting under project version control.

prod/demo_git_db##2023061614300000##ddl##create_t2.sql

prod corresponds to {{ENV_ID}}
demo_git_db corresponds to {{DB_NAME}}
2023061614300000 corresponds to {{VERSION}}
ddl corresponds to {{TYPE}}
create_t2 corresponds to {{DESCRIPTION}}

Paste the sql script in it.

   CREATE TABLE t2(
   id INT NOT NULL,
   PRIMARY KEY(id)
   )

Commit and push this file.
Go to Bytebase, and go into project Demo Git. You’ll find there is a new Push Event and a new issue created.
Click the issue link, and you’ll see:

The issue is created via GitHub.com.
The issue is waiting for your rollout because it’s on Prod environment where manual rollout is required by default.
The SQL is exactly the one we have committed to the GitHub repository.
The Creator is Owner, because the GitHub user you use to commit the change has the same email address found in the Bytebase member list.

Click Rollout, and the SQL will execute. The issue will be Done.
Click View change, you could view the schema diff.
You may try with your Test environment, the only difference is that you don’t need to rollout the issue manually.

If you have Enterprise Plan. Go to GitHub repository, you will see besides your committed sql, there is a .demo_git_db##LATEST.sql file. Because you have configured Schema path template before, Bytebase will write back the latest schema to that specified path after completing the schema change. Thus you have access to an update-to-date full schema at any time.

Summary and Next

Now you have tried out GitOps workflow, which will store your MariaDB schema in GitHub and trigger the change upon committing the change to the repository, to bring your MariaDB change workflow to the next level of Database DevOps - Database as Code.

In real world scenario, you might have separate features and main branches corresponding to your dev and production environment, you can check out GitOps with Feature Branch Workflow to learn the setup. Have a try and look forward to your feedback!

DevOps: Database Change Management with MariaDB

Adela — Sat, 17 Jun 2023 16:43:14 +0000

This is a series of articles about : Database Change Management with .

Database Change Management with MariaDB (this one)
Database Change Management with MariaDB and GitHub

is a community-developed, commercially supported fork of the management system (), intended to remain under the .

This tutorial is a step-by-step guide to setting up Database Change Management for in . With , a team can have a formalized review and rollout process to make change and data change.

provides a GUI for teams to perform Database Changes and retain full Change History. Free Plan is sufficient for this tutorial.

In the end, there is a bonus section about Schema Drift Detection for those advanced users.

Features included

Change Workflow
Change History
SQL Editor Admin Mode
Drift Detection

Prerequisites

Before you start, make sure you have

installed. You can follow the official guide to install it.
In this tutorial, we'll use StackBricks to run . You can also use other tools.

Step 1 - Deploy Bytebase via

Make sure your is running, and start the Bytebase with following command:

    run --init \
   --name  \
   --platform /amd64 \
   --restart always \
   --publish 5678:8080 \
   --health-cmd "curl --fail http://:5678/healthz || exit 1" \
   --health-interval 5m \
   --health-timeout 60s \
   --volume ~/./data:/var/opt/ \
   bytebase/bytebase:%%bb_version%% \
   --data /var/opt/bytebase \
   --port 8080

Run StackBricks, and create two instances:
- test,3307
- prod,3308

and are now running via .

Step 2 - Add in

In , an Instance could be your on-premises instance, an instance and etc. In this tutorial, it's instances.

Visit :5678 in your browser. Register the first which will be granted Workspace Owner.
Click Instances on the top bar and click Add instance, choose ``. Fill the form with the following information and click Create.
- Instance Name: test
- Environment: Test
- Host or Socket and Port: host.docker.internal and 3307
- Username and password: root and your password
Click Add instance again, choose ``. Fill the form with the following information and click Create.
- Instance Name: prod
- Environment: Prod
- Host or Socket and Port: host.docker.internal and 3307
- Username and password: root and your password

Step 3 - Create a Project

In , Project is the container to group logically related Databases, Issues and Users together, which is similar to the project concept in other dev tools such as , . So before you deal with the database, a project must be created.

Click Projects on the top bar.
Click New Project to create a new one with Demo UI as Project Name, DUI as Key and Standard as Mode.

Step 4 - Create a database in via

In , a Database is the one created by "CREATE DATABASE xxx". A database always belongs to a single Project. Issue represents a specific collaboration activity between Developer and DBA such as creating a database, altering a schema. It's similar to the issue concept in other issue management tools.

Go to the project Demo UI, and click New DB.
Fill the form with the following information and click Create.

Name: demo_db
Environment: Test
Instance: test

It will create an issue "CREATE DATABASE …" automatically. Because it’s for Test environment, the issue will automatically run then becomes ``. The database is created.

Click New DB again, fill the form with the following information and click Create.

Name: demo_db
Environment: Prod
Instance: prod

It will create an issue "CREATE DATABASE …" automatically. Because it’s for environment, the issue will require manual rollout. Click **Rollout** to run then becomes . The database is created.

Go back to project Demo UI, click Databases tab, and you can see there're two databases there.

Step 5 - Create a table in via

In Step 4, you actually created an issue in UI workflow and then executed it. Let’s make it more explicit.

Go to project Demo UI, and click Alter Schema. Here you can choose either one or both.
Check both demo_db and click Next. It will generate a pipeline.
Input the SQL as following, and click Apply to other tasks. Click Create.
`sql CREATE TABLE t1( Id INT AUTO_INCREMENT, (Id) ) `
will run SQL automatically on Test environment, but wait for manual rollout on . It's by default configuration, you can adjust it on Environments page.

Click Rollout and the SQL will execute and the issue will become Done.
On the issue page, click View change. You will see the difference.
You can also go to Change History under the project to view the full history. Or go into a specific database to view its history.

Bonus Section - Schema Drift Detection

This section requires you to have Enterprise Plan (you can start 14 days trial directly in the product without ). Click Start free trial on the bottom of left sidebar.

By adopting , we expect teams to use exclusively for all schema changes. Meanwhile, if someone has made schema change *outside of *, obviously won’t know it. And because has recorded its own copy of schema, when compares that with the live schema having that schema change, it will notice a discrepancy and surface a schema drift anomaly. If that change is intended, then you should use baseline the schema state again to reconcile.

In this section, you’ll be guided through this process.

You can use an external GUI or terminal to make a change to demo-db. In this tutorial, we use SQL Editor’s Admin mode which also counts when we say change outside of **. Go to **SQL Editor, and switch to Admin mode.

When you make a change in Admin mode, it will not record any history as in a normal process /docs/sql-editor/admin-mode

Paste the following and then press Enter:

`sql
ALTER TABLE t1
ADD COLUMN name (255);`




   ![bb-sql-editor-admin-alter-t1](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-sql-editor-admin-alter-t1.webp)
   Admin mode skips  system and works as an external input. The change will take effect in Bytebase GUI in the next sync.

1. Wait for 10 mins. Go to **Anomaly Center**, and you can find the **Schema Drift**.
   ![bb-anomaly-center-1-drift](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-anomaly-center-1-drift.webp)

2.  , and you’ll see the drift.
   ![bb-demo_db-drift-diff](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-demo_db-drift-diff.webp)

3. You may also find the drift by clicking **Databases** > `demo_db`.

4. Go to **Databases** > `demo_db` > **Change History** and click **Establish new baseline**.
   ![bb-demo_db-establish-new-baseline](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-demo_db-establish-new-baseline.webp)

5. It will create an issue and the issue will be `Done`.
   ![bb-issue-demo_db-baseline-done](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-issue-demo_db-baseline-done.webp)

6. Go back to **Databases** > `demo_db` or **Anomaly Center**, and you will find the drift is gone.

## Summary and Next

Now you have connected  with Bytebase, and tried out the UI workflow to do schema change. Bytebase will record the full change history for you. With **Enterprise Plan**, you can even have schema drift detection.

In the next article, you’ll try out GitOps workflow, which will store your  schema in  and trigger the change upon committing the change to the repository, to bring your  change workflow to the next level of Database  - [Database as Code](https://www.bytebase.com/blog/database-as-code).

Database Change Management with Amazon Aurora and GitHub

Adela — Mon, 13 Mar 2023 15:37:06 +0000

This is a series of articles about Database Change Management with Amazon Aurora

Database Change Management with Amazon Aurora
Database Change Management with Amazon Aurora and GitHub (this one)

In the last article Database Change Management with Amazon Aurora, you have tried UI workflow in Bytebase.

This tutorial will bring your Amazon Aurora schema change to the next level by introducing the GitOps workflow, where you commit the schema change script to the GitHub repository, which will in turn trigger the schema deployment pipeline in Bytebase.

You can use Bytebase free version to finish the tutorial.

Features included

GitOps Workflow
Change History

Prerequisites

Before you start this tutorial, make sure you have the following ready:

Followed our previous UI-based change tutorial Database Change Management with Amazon Aurora.
An Amazon Aurora MySQL instance.
A GitHub account.
A public GitHub repository, e.g aurora-test-bb-local.
Docker installed locally.
An ngrok account. (ngrok is a reverse proxy tunnel, and in our case, we need it for a public network address in order to receive webhooks from GitHub. We use ngrok here for demonstration purposes. For production use, we recommend using Caddy.)

Step 1 - Run Bytebase in Docker with URL generated by ngrok

To make local-running Bytebase visible to GitHub, we’ll pass the URL generated by ngrok to --external-url.

ngrok http 5678

and obtain the public URL:

Make sure your Docker daemon is running, if it’s running Bytebase container for the previous tutorial, stop and remove it. The data created in the last tutorial is stored under ~/.bytebase/data by default and will be restored if the system restarts.
Start the Bytebase Docker container by typing the following command in the terminal. Pay attention to the last parameter --external-url https://b725-103-197-71-76.ap.ngrok.io, which is generated by ngrok.

docker run --init \
--name bytebase \
--platform linux/amd64 \
--restart always \
--publish 5678:8080 \
--health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
--health-interval 5m \
--health-timeout 60s \
--volume ~/.bytebase/data:/var/opt/bytebase \
bytebase/bytebase:1.13.0 \
--data /var/opt/bytebase \
--port 8080 \
--external-url https://b725-103-197-71-76.ap.ngrok.io

Bytebase is running successfully in Docker, and you can visit it via https://b725-103-197-71-76.ap.ngrok.io.

Step 2 - Find your Amazon Aurora in Bytebase

Visit Bytebase Console through the browser via your ngrok URL https://b725-103-197-71-76.ap.ngrok.io. Log in using your account created from the previous tutorial.
If you followed the previous tutorial, you should see that the project and database created are still in your workspace.

Step 3 - Connect Bytebase with GitHub.com

Click Settings on the top bar, and then click Workspace > GitOps. Choose GitHub.com and click Next.

Follow the instructions within STEP 2, and in this tutorial, we will use a personal account instead of an organization account. The configuration is similar.
Go to your GitHub account. Click your avatar on the top right, and then click Settings on the dropdown menu.
Click Developer Settings at the bottom of the left sidebar. Click OAuth Apps, and add a New OAuth App.
Fill Application name and then copy the Homepage and Authorization callback URL in Bytebase and fill them. Click Register application.
After the OAuth application is created, click Generate a new client secret. Copy the Client ID and the newly generated Client Secret, paste them back into Bytebase's Application ID and Secret.
Click Next. You will be redirected to the confirmation page. Click Confirm and add, and the Git provider is successfully added.

Step 4 - Enable GitOps Workflow with Amazon Aurora

Go to your project TestAurora, click GitOps, and choose GitOps Workflow. Click Configure GitOps.
Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose aurora-mysql-test-bb-local.
Keep the default setting, and click Finish.

Step 5 - Change schema for Amazon Aurora by pushing SQL schema change files to GitHub

In your GitHub repository aurora-mysql-test-bb-local, create a folder bytebase, then create a subfolder Test, and create a SQL file using the naming convention

{{ENV_NAME}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql

It is the default configuration for the file path template setting under project TestAurora > GitOps.

Paste the sql script in it.

CREATE TABLE t2
(
   id BIGINT NOT NULL,
   course VARCHAR(255)
);

Commit and push this file.
Go to Bytebase, and go into project TestAurora. You’ll find there is a new Push Event and a new issue 107 created.
Click issue/107 and go the issue page. Click Resolve issue, and the issue will be Done. You’ll see:
- The issue is created via GitHub.com
- The issue is executed without approval because it’s on Test environment where manual approval is skipped by default. The Assignee is Bytebase, because the execution is automatic, and requires no manual approval.
- The SQL is exactly the one we have committed to the GitHub repository.
- The Creator is A, because the GitHub user you use to commit the change has the same email address found in the Bytebase member list.

Click View change, you can view the schema diff.
Go to GitHub repository, and you will see besides your committed sql, there is a .db_demo##LATEST.sql file. Because you have configured Schema path template before, Bytebase will write back the latest schema to that specified path after completing the schema change. Thus you have access to an update-to-date full schema at any time.

Summary and What's Next

Now that you have tried the GitOps workflow, which stores your Amazon Aurora schema in GitHub and triggers the change upon committing change to the repository, to bring your Amazon Aurora change workflow to the next level of Database DevOps - Database as Code.

You can check out GitOps docs to learn more configuration details.

In the real world, you might have separated feature and main branches corresponding to your development and production environment, you can check out GitOps with Feature Branch Workflow to learn the setup. Have a try and look forward to your feedback!

Database Change Management with Amazon Aurora

Adela — Mon, 13 Mar 2023 15:16:58 +0000

This is a series of articles about Database Change Management with Amazon Aurora.

Database Change Management with Amazon Aurora (this one)
Database Change Management with Amazon Aurora and GitHub

Amazon Aurora is a fully managed relational database engine that's compatible with MySQL and PostgreSQL.

This tutorial is a step-by-step guide to set up Database Change Management for Amazon Aurora MySQL in Bytebase. With Bytebase, a team can have a formalized review and rollout process to make Amazon Aurora database schema change and data change.

Bytebase provides a GUI for teams to perform database changes and retain full change history. You can use Bytebase free version to finish the tutorial.

At the end, there is a bonus section about Schema Drift Detection for those advanced users.

Features included

Change Workflow
Schema Editor
SQL Editor
Change History
DML Rollback
Drift Detection

Prerequisites

Before you start, make sure you have:

An Amazon Aurora MySQL instance.
Docker installed.

Step 1 - Deploy Bytebase via Docker

Make sure your Docker is running, and start the Bytebase Docker container with following command:

docker run --init \
  --name bytebase \
  --platform linux/amd64 \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:1.13.0 \
  --data /var/opt/bytebase \
  --port 8080

Bytebase is now running via Docker, and you can access it via localhost:5678.
Visit localhost:5678 in your browser. Register the first admin account which will be granted Workspace Owner.

Step 2 - Add an Amazon Aurora Instance to Bytebase

In Bytebase, an Instance could be your on-premises MySQL instance, an AWS RDS instance etc, in this tutorial, an Instance is your Amazon Aurora MySQL instance.

Visit localhost:5678 and log in as Workspace Owner.
Click Add Instance.
Fill in the fields and click Create. Pay attention to these fields:

Type: MySQL, if you use Aurora PostgreSQL, choose PostgreSQL instead.
Environment: choose Test, if you choose Prod, you'll need manual approval for all future change requests by default, let's keep it simple for this tutorial.

Step 3 - Create a Project

In Bytebase, Project groups logically-related Databases, Issues and Users together, which is similar to the project concept in other DevTools such as Jira and GitLab. So before you deal with the database, a Project must be created.

Click Projects on the top navigation bar.
Click New Project to create a new project TestAurora, key is TAR, mode is standard. Click Create.

Step 4 - Create an Amazon Aurora Database via Bytebase

In Bytebase, a Database is created by CREATE DATABASE xxx. A database always belongs to a single Project. An Issue represents a specific collaboration activity between Developer and DBA for when creating a database, altering a schema. It's similar to the issue concept in other issue management tools.

Click Projects > TestAurora on the left sidebar. Click New DB to create a new database. You can transfer your existing
Fill the form with Name - db_demo, Environment - Test, and Instance - Amazon Aurora MySQL. Click Create.
Bytebase will create an issue to create the database automatically. As it's the Test environment, the issue will run without waiting for your approval by default. Click Resolve, and the issue is Done.

Step 5 - Create a Table in Amazon Aurora MySQL

In Step 4, you created an issue to create a database using UI workflow and then executed it. Let’s continue to create a table.

Visit your project, and click on Alter Schema.
Choose db_demo and click Next.
This is where you get to try out the Schema Editor. It’s a visual editor for schema changes. Create a table called t1 with 2 columns: id and name.
Click Preview issue, and Bytebase will automatically preview an issue with the corresponding SQL statement. Verify it's right, and click Create.
The issue is automatically approved by default since it’s for the Test environment. Meanwhile, Bytebase has run several task checks before executing the SQL, and one such task check is called SQL Reivew. You may customize your own SQL Review policies.
Click Resolve issue. The issue will become Done .
From the issue page, click View change, and you can see schema diff.

Step 6 - Add Some Data and Query via SQL Editor

Go to the project TestAurora , and click Change Data.
Choose db_demo and click Next.
Fill in the SQL as follows and then click Create.

INSERT INTO
  t1
VALUES
  (1, 'Adela');

After its execution, Click Resolve.
Click SQL Editor on the left side bar. Input the query and click Run. You can see the new row is there.

Step 7 - Rollback the Data Change

Bytebase support Rollback for MySQL.

After a data change completes, Bytebase can parse MySQL binary logs and build rollback SQL statements from the logs. This allows you to revert that data change if needed.

Go back to the issue, turn the SQL Rollback on.
It may fail if the instance hasn't set binlog_format = ROW.
Go to Amazon RDS, click Parameter groups to create a new parameter group and set binlog_format to ROW.
Apply the parameter group to your database instance.
Go back to Bytebase SQL Editor, and switch to Admin Mode.
According to Amazon documentation. Type as following to set the binlog retention hours to 24.

call mysql.rds_set_configuration('binlog retention hours', 24);

Type as following to check it's set successfully.

CALL mysql.rds_show_configuration;

Reboot the Aurora MySQL instance.
Repeat the Step 6, but this time, we can see the Preview rollback issue. Click it and then click Create on the issue page.
Rollback always requires explicit approval.
Before approving rollback, let's go to SQL Editor and query. Bella is there.
Go back to the issue page and click Approve. The rollback SQL will execute.
Go to SQL Editor and query again. Bella is no longer there - The rollback is successful. You may rollback the rollback too, and yes, rollback the rollback for rollback... As long as the binlog is within the 24-hour retention period.

Bonus Section - Schema Drift Detection

To follow this section, you need to activate the Enterprise Plan (you can start a 14-day trial directly, no credit card required).

Now you can see the full change history of database db_demo. However, what is Establish new baseline? When to use it?

By adopting Bytebase, we expect teams to use Bytebase exclusively for all schema changes. Meanwhile, if someone has made Amazon Aurora schema change out side of Bytebase, obviously Bytebase won’t know it. And because Bytebase has recorded its own copy of schema, when Bytebase compares that with the live schema having that out-of-band schema change, it will notice a discrepancy and surface a schema drift anomaly. If that change is intended, then you should establish new baseline to reconcile the schema state again.

In this section, you’ll be guided through this process.

You can use an external GUI or terminal to make a change to db_demo . In this tutorial, we use Bytebase SQL Editor’s Admin mode which also counts when we say change outside of Bytebase. Go to SQL Editor, and switch to Admin mode.

When you make a change in Admin Mode, it will not record any history as in a normal process www.bytebase.com/docs/sql-editor/admin-mode

Paste the following and then press Enter:

ALTER TABLE t1
ADD COLUMN age integer;

Paste the following and then press Enter to verify it’s there:

SELECT 
  table_name,
  column_name,
  data_type
FROM
  information_schema.columns
WHERE
  table_name = 't1';

Wait for 10 mins (as Bytebase does the check roughly every 10 mins). Go to Anomaly Center, and you can find the Schema Drift.
Click on View diff, you will see the exact drift.
You may also find the drift by clicking Databases > db_demo.
Go to Databases > db_demo > Change History and click Establish new baseline, this step establishes a new baseline to reconcile the schema state from the live database schema.
Bytebase will create an issue to establish the new baseline, click Create, and then Resolve to mark it done.
Go back to Databases > db_demo or Anomaly Center, and you will find the drift is gone.

Summary and What's Next

Now you have connected Amazon Aurora with Bytebase, and used the UI workflow to accomplish schema change and data change. Bytebase will record the full change history for you. Furthermore, the Enterprise Plan is equipped with Schema Drift Detection to detect out-of-band schema changes performed outside of Bytebase.

In the next post, you’ll try out GitOps workflow: store your Amazon Aurora schema in GitHub and Bytebase will pick up the changes to the repo, bringing your Amazon Aurora change workflow to the next level, aka Database DevOps - Database as Code.

Forem: Adela

⚙️🔥 Automating Database Schema Change workflow Using GitHub Actions 🐙🚀

Prepare Bytebase

Prepare GitHubs Actions

Sample Workflow, Four Phases

Phase 1: Not passing SQL review on GitHub

Phase 2: Passing SQL review and waiting for TL's approval on GitHub

Phase 3: TL approves on GitHub and migration issue is created in Bytebase

Phase 4: Migration completed and PR is mergable on GitHub

Summary

🔄 How to Synchronize Database Schemas🔥🚀

Preparation

Case 1 - Sync Schema to a New Database

Case 2 - Sync Schema from One to Many

Case 3 - Rollback to Previous Schema Versions

Summary

🛡️4 Top Database Security Tools in 2024 🏆🔥

Tailscale

Infisical

Indent

Bytebase

Summary

🐣Your First Database Schema Change in 5 Minutes with Bytebase

Step 1 - Run via Docker

Step 2 - One Issue with Two Stages

Step 3 - Roll out on Test Stage

Step 4 - Roll out on Prod Stage

Next Step

🔥Top psql Commands with Examples🐘

1. Connect to a database - psql -U xxx -p -h xxx -d xxx

2. Run a single command - -c

4. Switch to a specified database - \c

5. List tables - \dt

6. Describe a specified table - \d

7. Listing users - \du

8. Show connections - pg_stat_activity

9. Kill connection - pg_terminate_backend(), kill

🚛 Deploy Database Schema Migrations with Bytebase

Features covered

Preparation

Level 1: Automatic rollout with SQL review (Community Plan)

Level 2: Manual rollout with dedicated roles and scheduled time (Pro Plan)

Level 3: Manual rollout with custom approval (Enterprise Plan)

Summary

🐣 Step-by-Step Guide to Data Masking 🎭

Prerequisites

Preparation

No Masking

Global Masking Rule

Column Masking Rule

Grant unmasked access to a user

Summary

How to Manage Data Access for Developers

Preparation

Step 1 - Run Bytebase via Docker

Step 2 - Register accounts Owner and Developer

Step 3 - Start Enterprise Plan trial and assign license to instances

Data Access Control - Query Data

Step 1 - Developer can skip approvals to query data

Step 2 - Developer can request to query data

Step 3 - Owner can grant access to query data directly

Data Masking

Audit Log

Watermark

Summary

DevOps: Database Change Management with MariaDB and GitHub

Features included

Prerequisites

Step 1 - Run Bytebase in Docker with URL generated by ngrok

Step 2 - Find your MariaDB instance in Bytebase

Step 3 - Connect Bytebase with GitHub.com

Step 4 - Enable GitOps workflow with MariaDB

Step 5 - Change schema for MariaDB by pushing SQL schema change files to GitHub

Summary and Next

DevOps: Database Change Management with MariaDB

Features included

Prerequisites

Step 1 - Deploy Bytebase via

Step 2 - Add in

Step 3 - Create a Project

1. Connect to a database - `psql -U xxx -p -h xxx -d xxx`

2. Run a single command - `-c`

4. Switch to a specified database - `\c`

5. List tables - `\dt`

6. Describe a specified table - `\d`

7. Listing users - `\du`

8. Show connections - `pg_stat_activity`

9. Kill connection - `pg_terminate_backend(), kill`