Embed SQL Editor in Your Internal Web Portal
Bytebase is an open-source database DevSecOps solution for Developer, Security, DBA, and Platform Engineering teams. The GitLab for database DevSecOps.
Bytebase has a web-based SQL Editor, offering powerful features such as centralized access control, data masking, audit logs, AI assistance, and more.
You can also embed the Bytebase SQL Editor into your application. This tutorial will guide you through the process and show how to configure the settings using the Bytebase API.
By the end of this tutorial, you will have achieved the following:
Prerequisites
- A Google Cloud account
- Docker installed
- Download the api-example repository, you'll only need
sql-editor
folder for this tutorial
Overview
The demo app simulates the process of receiving your email from your internal portal. It then uses this email to authenticate via SSO with Bytebase and opens the Bytebase SQL Editor within an iframe.
Imagine you are a SaaS provider and you provision a separate database to store the data for each customer. Sometimes, you need your support team to query the customer database for troubleshooting. You want to embed SQL Editor into your internal support portal and grant query permission to the support team on demand.
Workflow
- Setup Google OAuth
- Run a Bytebase instance and setup Google SSO
- Configure the environment variables and run the
sql-editor
demo app
Setup Google OAuth
-
Go to Google Cloud Console, create a new project
GoogleAuth
, and then click APIs & Services. -
On the left bar, click Credentials, then click +CREATE CREDENTIALS, and select OAuth client ID.
-
Choose
Web application
as the Application type, give it a Name. -
For Authorized redirect URIs, add
http://localhost:8080/oauth/callback
and click CREATE. -
Save the Client ID and Client Secret for later use.
Run Bytebase, setup SSO and an admin user
-
Start Bytebase via Docker and register an account which will be granted
Workspace Admin
role. -
Log in to Bytebase, go to IAM&Admin > SSO, click Create SSO.
-
Select
OAuth 2.0
as the Type, andGoogle
used as the template. -
Fill in the Client ID and Client Secret, which you can save from Google Cloud Console.
-
Click Test Connection, if it's successful, click Create.
You'll need an API service account user too:
- Go to IAM&Admin > Users&Groups, click +Add User.
- Choose
Service Account
as the Type, fill in the Email withsuper-api@service.bytebase.com
, chooseWorkspace Admin
as Roles, and click Confirm. - Copy the Service Key for later use.
Configure the environment variables and run the sql-editor
demo app
-
Go to the
sql-editor
folder of theapi-example
repository, and copyenv-template.local
file as.env.local
. Replace the placeholders with yours. -
Run
pnpm i
andpnpm run dev
, you can run the demo app locally withlocalhost:3000
.
Code explanation
Check user
-
The demo app receives your email from your internal portal.
-
It then uses that email to check if there is a Bytebase user with that email using the API
/v1/users/${email}
. If no user is found, it creates a new user with the email. The username is derived by removing the@
and.
symbols from the email.
Create project
It then use the username to check if there is a project with that name with the API /v1/projects/${username}
, if there is no project, it'll create a new project with the username.
Create database
Within the project, it'll create a database with the username. The database will be created on Bytebase's embedded PostgreSQL instance test-sample-instance
. First check if there is a database with that name using the API /v1/instances/test-sample-instance/databases/${username}
, if no, it'll create a new database by creating an issue.
Typically, the issue creation process in Bytebase involves four steps: sheet -> plan -> issue -> rollout. However, for a database creation issue, it only includes three steps: plan -> issue -> rollout.
Grant permission
Once the database is created, you must assign the necessary permissions to the user for database access. In this case, the user is granted the Project Owner
role.
First, fetch the project IAM using the API /v1/projects/${project}:getIamPolicy
. Then, add the new role to the array and update the IAM using the API /v1/projects/${project}:setIamPolicy
.
Configure database change mode
To show the SQL Editor and allow editing directly instead of the issue system. You'll need to switch the workspace mode from Issue to SQL Editor:
After all this is done, the app will open the Bytebase instance in an iframe with your SSO logged in credentials.
Summary
Building a SQL Client is a no easy task. By embedding the Bytebase SQL Editor in your own internal portal, your team doesn't need to reinvent the wheel and can enjoy all the powerful SQL Editor features Bytebase offers.