SQL Studio, Visually Designing your Database
SQL Studio is a core component of Magic, allowing you to visually design your database, in addition to having lots of other features.
Join the DZone community and get the full member experience.Join For Free
SQL Studio is one of the core components of 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 improve your productivity. Below is how you create a new column in SQL Studio.
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
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 afterward.
It doesn't take a rocket scientist to understand the advantages here I presume...
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.
You can connect it to any of the following RDBMS systems.
- Microsoft SQL Server
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.
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 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 is one manifestation of that by eliminating repetitive 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
Yesterday, I was in a meeting with an automation project manager who 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 realized 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 a 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. The key to automation is to look for those places where you're doing repetitive 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...
Published at DZone with permission of Thomas Hansen. See the original article here.
Opinions expressed by DZone contributors are their own.