DbSpy
Visualize, modify, and build your database with dbSpy! An open-source data modeling tool to facilitate relational database development.
Install / Use
/learn @open-source-labs/DbSpyREADME
<a href="https://makeapullrequest.com">
</a>
<a href="https://img.shields.io/badge/version-1.0.1-blue">
🚀 dbSpy v8.0 ✨ New Features
🔍 Test New Query Page - a modern querying interface
- Run SQL queries against PostgreSQL or MySQL DBs
- Live results + metadata: execution time, query name, timestamp
- Zustand-powered state management
- Input validation and error handling
- Component-level Jest + ReactTestLibrary tests
💾 View Saved Queries Page
- Access previously saved queries per user
- Sortable, styled tables with TypeScript typing
- Track query performance across data models
🎨 UI / UX Enhancements
- TailwindCSS redesign: colors, layout, interactions
- Responsive and fully supported dark/light mode
- Logo animation integrated in navbar
- Sidebar and tab layout fixes
🛠 Functional Fixes
- Resolved layout shift bugs
- Optimized backend response handling
- OAuth (GitHub + local login)
📈 Opportunities for Continued Development
- Extend Test Query feature to additional DB types (e.g. Oracle)
- Persist database sessions across routes
- Integrate LLMs to suggest efficient queries and schema improvements
- Add OAuth support for Microsoft accounts
- Add support for NoSQL (MongoDB) and graph databases
- Expand UI testing coverage
dbSpy v7.0 updates
- Full widescreen UI with a dropdown menu for more workspace
- Enhanced Save, Load, and Delete features that support filenames for saving schema and data
- Proper deletion of dependent elements across tables
- Algorithm to highlight edges connected to the selected table
- Fixed rendering and deletion issues with elements
- Support for connecting to remote databases (PostgreSQL, MySQL, Microsoft SQL, Oracle SQL)
- Ability to upload SQL files and render ER diagrams for the provided SQL dump
- Interface for modifying tables and creating new ones, with changes reflected in the database via generated queries
- Canvas to create databases and ER diagrams from scratch.
🔑 Key Features
-
Database Uploads: Connect to remote SQL database or upload local SQL files
-
ER Diagram Visuals: Visualize the entity relationship diagram of a database with dynamic handle placement
-
Table Relationship Visuals Relationships of individual tables are easily identified when clicking on a table
-
Schema Modification: Toggle between Schema and Data easily with a click of a button
-
Data/Schema Modification: Easily modify a database's schema or data through a simple UI
-
Guided Database Building: Create a new database from scratch using entity relationship diagrams to ensure the integrity of the database
-
Database Undo/Redo: Provides a history of edits for easier backtracking
-
Query Generator: Query generator generates executable SQL queries
-
User Sessions: Sign up/Log in securely with either Google/Github OAuth or JWTs/Bcrypt
-
Save/Load: Store and reload past database sessions through your user accoun
-
Dark Mode: Visual settings to provide a more comfortable viewing experience in low-light environements
-
Live Querying and Save Queries Test SQL queries and also save them to track and analyze query performance across data models (<span style="color:green"><strong>dbSpy V8.0</strong></span>)
<img src="images/dbspy8homepage.png">
<img src="images/dpspy8testquery.png">
<img src="images/dpspy8savequery.png">
🛠 Getting started
You will need your own MySQL database for backend functions.
- Fork and clone this repo
- Add a db_schemas folder in server directory
- Add a .env file to the root directory with the information below:
# production environment variables
USER_DB_URL = <MySQL url for storing user data>
USER_DB_USER = <user string from USER_DB_URL>
USER_DB_PW = <password string from USER_DB_URL>
TOKEN_KEY = <any string>
# testing environment variables
## encoded SSL data required for GitHub Actions
SSL_KEY = <base64 encoded SSL key (see SSL Configuration)>
SSL_CERT = <base64 encoded SSL cert>
## MySQL and Postgres databases to test remote connection functionality
MYSQL_TEST_URL = <MySQL url for a test database>
MYSQL_TEST_USERNAME = <user string from MYSQL_TEST_URL>
MYSQL_TEST_PW = <password string from MYSQL_TEST_URL>
PG_TEST_URL = <PostgreSQL url for a test database>
PG_TEST_USERNAME = <user string from PG_TEST_URL>
PG_TEST_PW = <password string from PG_TEST_URL>
## test user with saved schema to test save/load functionality
TEST_USER_EMAIL = <email string>
TEST_USER_PW = <password string>
GOOGLE_OAUTH_CLIENT_ID = <Google Oauth client id>
GOOGLE_OAUTH_CLIENT_SECRET= <Google Oauth client id>
GOOGLE_OAUTH_REDIRECT_URI = 'http://localhost:8080/display'
GITHUB_OAUTH_CLIENT_ID = <Github Oauth client id>
GITHUB_OAUTH_CLIENT_SECRET= <Github Oauth client id>
GITHUB_OAUTH_REDIRECT_URI = 'http://localhost:8080/display'
- Run the following below:
$ npm install
- Execute the following command to populate your mySql database with a users table:
$ npm run seed
- Run the project in development mode:
$ npm run dev
SSL Configuration
To connect with the SQL database, we must create and configure SSL certificates.
macOS
- Install mkcert; you can learn more about mkcert here
npm install -g mkcert
- Run the following script
npm run cert:mac
Linux
- Check mkcert is up to date (v1.5.1 as of publishing)
mkcert --version
- Run the following script
npm run cert:linux
How to Use
Connecting to an existing database
- Click on the "Connect Database" button under the Action section on the left side of the page. This will open a sidebar on the right side of the page.
- Select the database type from the dropdown.
- Input your database URI information OR database connection credentials and click on "Connect".
- Once the connection to your database is established, the canvas will render and generate the tables and their relationships.
- OracleSQL requires the download of the OCI - here
Uploading your database's SQL file
- Locate and click on the "Upload SQL File" button under the Action section on the left side of the page. This will open a dialog box for you to select the SQL file you wish to upload.
- Once the file uploads, the canvas will render and generate the tables and their relationships.
Starting an ER diagram from scratch
- Locate and click on the "Build Database" button under the Action section on the left side of the page. This will render the canvas for you to create your database schema.
- Click on "Add Table" to name the first table and start building your database.
Adding column(s) to a SCHEMA table or row(s) to a DATA table
-
To add a new column/row in a table, click on the add/plus icon on the top right of the table node.
-
This will render a new column/row in the table in edit mode.
-
Add in the data you would like in each column/row.
-
Click on the confirm icon to save the column/row.
-
The changes made in DATA table will be updated in your database.
-
Click on the cancel icon to cancel the addition.
<img src="images/zuckOverWaz.png"> <img src="images/zuckTables.png">
Editing an existing row of a DATA table
- Cl
