Socrates – web based MySQL schema diagram generator (now version 1.0)

WHAT IS SOCRATES

Socrates is a web based application which generates a database schema diagram. It is written in PHP and uses JQuery in the front end.

All it requires as an input is your Mysql dump sql script. Just paste the sql and it generates a schema diagram. Simple and fast. Please note that this is a work in progress and I am in the process of adding in more features.

WHY SOCRATES WAS MADE

As part of our documentation of projects, we are often needed to create database schema diagrams of our existing MySQL databases. Now the only way to do that is :

  • Use a commercial application/tool which automatically creates a schema diagram
  • Create a schema diagram by hand by entering everything manually

I did a lot of searching over the web to look for a free online tool which could immediately show me a schema diagram of my MySQL database, but there is nothing like that available. As a result I decided I would make an online application for this purpose.

FEATURES IN VERSION 1.0 (Feb 19th, 2015)

  1. Ability to handle “CREATE IF NOT” table clause
  2. Display foreign keys
  3. Show relations between tables

1.0 Screenshot:

2015-02-19 15-24-59

 

 

FEATURES IN VERSION 0.5 (Aug 2nd, 2012)

  1. Takes in a maximum of 750k characters for the sql dump input.
  2. Parses the sql file to generate information about tables,fields and primary keys
  3. Ability to extract table name, field info, default values and primary key info.
  4. Shows the parsed information as a diagram.
  5. The boxes in the diagram are draggable so that you can achieve your desired layout

A sample screenshot of a Joomla CMS database schema is shown below:

EXPLANATION OF THE SCHEMA DIAGRAM

Each table is shown as a block. Within each table it shows

  • The table name
  • The field list. For each field it shows the name, field type and default value if any
  • If a field is part of a primary key it is marked as (P)
  • If a field is allowed to have a null value is marked with an N
  • If a field is defined as a foreign key from another table , it is marked with (F)

SAVE THE DIAGRAM AS A SCREENSHOT

Since its not possible for any server-side language to save a web page as an image, without using external server hosted applications, I would recommend that you install any of the several free browser-addons which lets you save the current page in the browser as an image. Any of these add-ons would save the entire diagram as an image.

WHAT NEXT

As mentioned before, this is a work in progress, so I will be continually adding more features. I am particularly aiming to automatically show foreign key linkages using connectors between the blocks as thats something which is very useful.

I would be very interested in your feedback and comments on any bugs or ideas that you want to convey.

Click Here To Use Socrates

10 Comments

    • Thanks for pointing out wwwsqldesigner. I didnt know about it. Is there a demo page where I can see it in action. Currently it requires installing the code somewhere before one can see it.

      • Also I believe wwwsqldesigner is the reverse of what Socrates is. wwwsqldesigner helps in creating a schema diagram and then exporting it as an sql. Please point out if I am mistaken.

  1. Hi,
    Good job, did you plan to add tables relation in the future ?

    did you try
    phpmyadmin recent version have nice reverse engineering capabilities so you can export your db schema in pdf. But you must get it installed in your dev environnement where your database is installed.

    But I definitively use dbvis it’s not open source, but you can have a free version wich is verfy powerfull, I mean the reverse engineering capabilities are truely amazing, and you have a very fine desktop IDE for managing database dev. Since it’s a desktop software you must have an open connection to your developpement MySQL database (port 3306).

    Diludead

    • Thanks Diludead. I do plan op adding in new features over the days and weeks. In fact the main reason was creating Socrates was the ability to automatically show the relations among tables in a graphical way, as long as the sql script contained the information about it.

  2. Hi, nice start !
    I’ve got all my tables named “IF” in the diagram… any idea ?
    Thanks, and yeah, showing the foreign keys (not automatically especially, just the ones mentioned as FOREIGN KEY (foreign_id) REFERENCES table(id) in the script would be great.

    I’m afraid you will have to use the html5 canvas to achieve this, have a look at kinetic.js to manipulate graphical objects on the canvas…

    Keep in touch !

    • Greg, thanks for the feedback. You are getting the ‘IF’ name for all the tables because I havent got around to parsing sql dumps which have a ‘IF NOT CREATED ‘ clause for the tables. If you generate the sql dump without that option then the table names come fine. I do plan to make that fix very soon.

      The foreign key connections are the main reason why I created Socrates and I already have the UI components in place to show dynamic connectors which move when you drag the tables. I am getting around to implementing the logic.

      This will be done within the next few days. All I want from all of you who appreciated this effort is to spread the word. You know, this is a great tool to have and I dont intend making money out of it, but I definitely want more people to start using. I guess once I can show the foreign key connections it will bring in more people.

Leave a Reply

Your email address will not be published.


*