Visualize, modify, and build your database with dbSpy!

dbSpy is an open-source data modeling tool to facilitate relational database development.

Key Features

Database Connection: Connect to a PostgreSQL database
Database File Upload: Upload a database's SQL file
ER Diagrams: Visualize the entity relationship diagram of a database
Schema Modification: Modify a database's schema
Database Building: Create a new database from scratch using entity relationship diagrams
SQL Query Generator: Generate an executable SQL query with every modification to your schema
Screenshot: Take a screenshot of the canvas with all your tables (BETA)
Time Travel: Provides a history of edits for easier backtracking

Use Cases

Connecting to an existing database

Renders an ER diagram of the existing database and provides an interface for users to both modify existing tables and create new tables. A log of changes is stored, and at any point, the user can execute a transaction containing the changes, such that they are reflected in the existing database.

Uploading your database's SQL file

Renders an ER diagram for the provided SQL file (db dump) and provides an interface for a user to both modify existing tables and create new tables. Changes are converted into the corresponding queries, which the user can view and execute on their own database outside of dbSpy.

Starting an ER diagram from scratch

Provides a canvas for users to create a database by using ER diagrams, thus creating a blueprint for engineering database structures.

How to Use

Connecting to an existing database

Locate and 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.
Input your Postgres database URI information and click on "Connect". It may take a couple of seconds to connect to your database.
Once the connection to your database is established, the page will populate with the tables in your database and the relationships between your tables.
Each table is editable. Editing the tables does not automatically change the schema of your database.
Every change you make will generate an executable SQL query. After you are done making all your changes, click on the "Execute changes" button to make those changes to your database.

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 window for you to select the SQL file you wish to upload. It may take a couple of seconds to upload the file.
Once the file uploads, the page will populate with the tables in your database and the relationships between your tables.
Each table is editable. Editing the tables does not automatically change the schema of your database.
You'll notice that after making a change to a table, a SQL query will be generated in the SQL Queries section of the page. These queries are generated for you to be able to execute the changes on your own database.

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 open a window for you to create and name a table.
The table will then be rendered on the page. You will then be able to edit the table and add the columns that you wish to this table.
You'll notice that after making a change to the table, a SQL query will be generated in the SQL Queries section of the page. These queries are generated for you to be able to create the database that you just designed.

How to contribute

Below is a list of features and improvements to which you can contribute. If you have any additional ideas, please raise the issue or implement them as well!

Screenshot feature - The screenshot feature does presently does not capture the tables with the arrows. It would be best if the features captures everything as it is rendered on the canvas.
SQL File Export feature (for new databases) - After a user creates a database from scratch on dbSpy, this feature would create a SQL file with a list of executable queries for them to create that new database on their own.
SQL File Export feature (for existing databases) - After a user makes changes to their database's schema, this feature would create a SQL file with a list of executable queries for them to execute those changes on their existing database.
Refactoring - We need help refactoring the codebase according to the Airbnb style guide.

Getting started

Fork and clone this repo
Add a .env file to the root directory
Go to the Google Cloud Platform Console
Set up OAuth 2.0 credentials as laid out here
Populate the .env file with the newly created:

CLIENT_ID = "client-id-goes-here"
CLIENT_SECRET = "client-secret-goes-here"
CALLBACK= http://localhost:8080/google/callback
TEAM_SECRET = "team-secret-goes-here"

Install the dependencies:

$ npm install

Run the project in development mode:

$ npm run dev

Make changes with comments
Add appropriate tests and ensure they pass
Commit your changes and make a pull request

Request a feature

Submit an appropriately tagged GitHub issue
Submit your request here

Credits/Contributors

Angel Giron • LinkedIn • Github
John Paul Adigwu • LinkedIn • Github
Kevin Park-Lee • LinkedIn • Github
Tarik Mokhtech • LinkedIn • Github

License

dbSpy is developed under the MIT license.

lherna05/dbSpy