The Problem With MySQL
Recently, I was assigned a 2 week sprint as part of a dev team. We were to build an app from scratch (our first), from blank repository to fully deployed MVP++. After deliberation, we decided on an app called HeirBloom. HeirBloom exists to celebrate the Locavore/Slow Food movements. Once you have registered for the app, it parses out your data and returns a gallery of produce that are all seasonally available in your area, along with suggested recipes for that produce, and local farmers markets nearby where you could do your shopping. Simple enough, right? Nice and clean and elegant. The only problem was that when it came to picking our assignments, I volunteered to be in charge of the static databases that would hold most of our App's contents. Which means I had to catalogue not ONLY every fruit/vegetable I could think of, as well as some information on each fruit/vegetable, I also had to make sure that every fruit/vegetable had it's entire seasonality represented across 5 different subregions of the continental United States.
Needless to say, this was going to be a lot of data. What wasn't clear was just how much data a lot of data is. When we did our first round of deadline estimations, I was confident that the spreadsheet could be completed in a manner of hours. The spreadsheet ended up being 20 columns wide, and close to 700 rows long. It took me around 4 days to get all of the information entered correctly.
For our database, we had chosen to go with MySQL over a document based database. We felt that there was significant relational connections among the seasons, regions, produce, recipes, and users in order to require a relational database. So MySQL it is!
A problem with MySQL, and any other SQL databases, is that they are incredibly rigid for good reason, but this makes them an absolute chore for inputting data into. To input data, there are special secret commands, yelled at in all-caps into the abyss of the terminal command line, most of which are mercilessly thrown back in our faces by the cruel and insatiable Lord of Darkness The Syntax Error.
Tables must be commanded to be built, and data must be inserted with commands like corkscrews. The whole thing reeked of torture and toil, and that's not for me, not Ol' Geoff, no sir!
Inputting Data in Excel
I had previously spent an incredible amount of time and energy getting a B.S. in Business Administration, which had really only taught me one thing- all things can be done in Excel. I decided that I was pretty proficient with the spreadsheeting software (On top of the degree, I'd had numerous professional positions where I was in charge of inventories and payrolls, and so Excel was a welcome GUI for me after months of terminals and debuggers), and so I was going to input all of this data into an Excel spreadsheet, and then hopefully find a way to import the file from Excel into our database.
Introducing Sequel PRO
After our information had been collected in Excel, I lucked upon Sequel PRO after some diligent googling. Sequel PRO was recommended specifically for the task I required of it, but it also provided a lot more functionality that continued to benefit us right up to the moment of deployment. Sequel PRO is a graphical interface for managing relational databases. Installation was painless (oh thank god! I had been working on a WSL machine until recently and every installation was a NIGHTMARE. Installing new software without issue was an emotional experience for me.) and I quickly was able to link up to my AWS deployed database with ease (Seriously, I didn't get one single error. When does that ever happen?).
Benefits of Sequel PRO
Exporting my Excel database was as easy as cake. I should mention that I had moved the spreadsheet over to Google Sheets at some point so that I could share it live with my dev team- the functionality is practically the same, although some of the options might be slightly different. Regardless, you simply need to export your spreadsheet as a .csv file. Sequel PRO readily imports .csv files, and after a couple of formatting questions, my entire produce table was imported. A few more imported tables later, and our whole database was built!
Sequel PRO continued to be incredibly useful, even after the schemas were initialized. Sequel PRO made it really simple to view the values in each table, which was incredibly valuable when we started working with the tables in our database that held user created data. Being able to see these values being input in nearly real-time gave us immediate feedback on whether or not our API tests were firing correctly. I could also use Sequel PRO to add or delete data, which became helpful when we realized that the produce table was missing a vital column. Relationships could also be changed readily, as well as the types of values that would be accepted as inputs. At every step in the database process, Sequel PRO was there to make the often tedious process of interacting with the MySQL prompts much simpler, quicker, and most importantly- error-free.
So here's to you, Sequel PRO! Thank you for teaching me how to stop worrying and love MySQL.
Top comments (2)
Sequel Pro has a few problems:
At my place, we switched to using Table Plus to get round the bugs in Sequel Pro, but that's not a wonderful solution either because it's non-free.
MySQL workbench is probably the best solution because it's cross-platform and free, but it has its own bugs (sometimes it even crashes the MySQL server, but that's more an indicator that the server needs work...)
All fair points! To my credit- 1) I'm on a mac 2) It was refreshing to use a GUI after so much terminal spelunking 3) It does not support the newest versions of SQL PRO as far as I know, but we're using 5.7 so that wasn't an issue 4) I'm a greedy boy, and since SQL PRO fit my very specific needs, I love it.
I was looking at using MySQL workbench, actually...but the Sequel PRO docs were speaking to my immediate needs so I tried that out instead. I'll check out workbench next!