How to create SQL

to test before developing it in Rails

Hash tags: Ruby on Rails| SQL| MySQLWorkbench|

Not a long time ago I got this new project to develop and it had really complicated DB structure I came up with. When I tried to implement it in Rails I bumped into a lot of different problems due to poor DB structure. So I thought it might worth to give a try to build a DB model in actual SQL server. But I didn't want to spend time and write everything manually and I opened google... Surprisingly the first result was MySQLWorkbench which I already had installed on my machine. It turns to be exactly what I was looking for:

Workbench Models

Here it is - Models section which helped me to accomplish that.
What is it good for? Well if you need quickly develop a SQL Model and run SQL server with it.
 
Let's create a quick example. I will use 2 tables only but of course, you can use as many as you need:
 
1. Installing MySQLWorkbench
Go to the official web site and follow the instruction
Quick install for ubuntu:
sudo apt install mysql-workbench

2. Creating a new model
Now you should have MySQLWorkbench installed let's open it up and click on tables diagram icon on the left menu.
Click on '+' symbol. You should see the following: 

Model Redactor

Now, the UI is pretty much self observatory. Add tables, views, diagrams you need based on your database logic. Here is what I have:
2 tables Users and Pictures. Relationship one to one

3. Creating tables
User Table


Picture Table


4. Making a connection

Foreign Key Settings


5. Checking Model Diagram
If everything was done correctly it should show your table connection automatically. Go head and move your tables to the diagram canvas. It should look this:

Diagram Connection


6. Exporting sql script
Let's assume that you finished your modeling and now is a time to export your DB model. You will need to Forward Engineer SQL:

Forward Engineer SQL

Follow the instruction and save your sql script somewhere on your machine.

7. Executing sql script in mySQL server.
Next step is to run your newly created script inside mySQL. Why mySQL?  Well because it has native script syntax and requires no changes made to the file. 
I assume you already have mySQL up and running. 
sudo mysql
Now we are in my sql terminal. Let's run following to execute our script:
source test_db.sql
You should see something like this:

Executing sql

If you made no mistakes during the model developing process it should create a new DB with the name you specified in Models tool. To check that you can always do some tests queries to see if it's there. Here is an example of DB I made the same way for one of my projects:

DB list


Next thing you can try is to see if tables are actually there. I will use aermotion db:
use aermotion

Show Tables Result

8. Connecting to DB
Now is time to test it. Open up mySQLWorkbench and use localhost connection and test your DB.

PROJECTS


MORE BLOGS