SQL Studio, Visually Designing your Database

SQL Studio, Visually Designing your Database

SQL Studio is one of the core components in Magic. One of its features is that it allows you to visually design your database. This significantly simplifies software development, since it allows you to focus on "the what" instead of "the how".

To illustrate the importance of the above point, try to write down the SQL statement required to create a database table in SQLite with an auto increment primary key and two text columns. Chances are you cannot do it without searching. Software development as an art form contains too many constructs to be able to fit all of them into your head, which is a large part of the reason why Low-Code, No-Code, and AI, so drastically improves your productivity. Below is how you create a new column in SQL Studio.

Use SQL Studio to Visually Design your Database

In the following video I walk you through SQL Studio's most important features, covering among other things.

  • Creating tables
  • Creating columns
  • Creating indexed columns
  • Creating foreign keys
  • Creating primary keys
  • Automatically create migration scripts
  • Using AI to create SQL DDL scripts
  • Saving SQL snippets for later
  • Exporting data from your database to CSV files
  • Etc ...

If you're to teach yourself the stuff I go through using a "drag'n'drop" type of user interface in the above video, you could easily spend years reading books and studying at the university level. I run through everything in 20 minutes, at which point you'll have a basic understanding of most concepts required to create and manage your own database afterwards.

It doesn't take a rocket scientist to understand the advantages here I presume ...

SQL editor

In addition to the visual designer, SQL Studio also allows you to execute any SQL you wish. Below is a screenshot of the autocomplete features, giving you suggestions for tables and columns as you write your SQL.

SQL Studio and its autocomplete feature

You can connect it to any of the following RDBMS systems.

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • MariaDB
  • SQLite

The designer will automatically adjust its SQL dialect, while the SQL editor transparently gives you autocomplete and syntax highlighting. Combined with the ability to connect to a cloud database, this gives you an intuitive and pleasent environment to manage your database, such as those hosted with Amazon or Azure. Below is a screenshot of how you connect to an existing database.

Connect SQL Studio to an existing database

Storing SQL snippets

Magic also allows you to save frequently used SQL snippets. This can be a handy feature for frequently used SQL statements you need to execute periodically, to create reports for instance. Combined with the ability to export your result-sets as CSV files, this allows you to rapidly create for instance reports that you export to Excel once every month, etc.

DRY

DRY is one of those acronyms we software developers seem to be obsessed with. It means "Don't Repeat Yourself". Some would argue that every time you repeat yourself, you're losing money, because you're doing something that could be automated.

Magic Cloud is all about becoming DRY - SQL Studio being one manifestation of that, by eliminating repetetive tasks, while reducing the cognitive complexity required to perform said tasks. I once heard somebody explain automation as follows.

When you work with automation, nothing happens for a very long time. Then everything happens almost instantly

Automation

Yesterday I was in a meeting with an automation project manager that was responsible for 3,600 retail stores. He told me they had a project where they were able to automate 10 minutes of manual work for each store manager per day. I wasn't particularly impressed initially, before I realised that 10 minutes multiplied by 3,600 stores becomes 36,000 minutes every single day.

36,000 minutes becomes 600 hours of saved time, every single day. Multiplied by one year, we're looking at 219,000 hours. The average employee works 1,760 hours per year. This implies the automation expert was saving 124 full time positions every single year. Multiplying that by an average salary cost of $30,000 annually becomes 3,7 million dollars annually.

10 minutes per day became 3.7 million dollars in savings

Doing basic ROI cost/effect analysis on the above, implies the automation expert could in theory have hired 124 people, working full time on the problem for 12 months, and the ROI of the finished project would be worth it. Of course, he didn't have 124 people, he probably only had a handful of people, working on it for a month or something - However, this equation illustrates the importance of automation. And the key to automation is to look for those places where you're doing repetetive work, as in "wet work", implying places where you are not DRY ...

Saving yourself 10 minutes of work per day, might save your employer 3.7 million dollars per year! And I'm willing to be a kidney on that if you're to write down the SQL required to create a table with an auto increment primary key and two text fields for SQLite, you will easily spend 10 minutes Googling for it before you've found the answer ...

Thomas Hansen

Thomas Hansen I am the CTO of AINIRO.IO AS. I am a software developer with more than 25 years of experience. I write about Machine Learning, AI, and how to help organizations adopt said technologies. You can follow me on LinkedIn if you want to read more of what I write.

Published 17. Jan 2024