Developers are SQLizer’s main customers and you’d be forgiven, as a developer, for thinking “why don’t I just write a DB converter script myself?” Yet it’s really not so simple. Getting a script to work some of the time is different from it working 98% of the time.
But if you’re intent on writing your own script, here are some pros and cons you might want to consider before you do.
Cons of writing your own database migration script
How do you handle big files?
Any file bigger than 100MB is going to be a PITA to convert via your own script, especially if you’re converting Excel to SQL. You need to think about server memory, how you upload the file, how you receive the SQL output.
You’ll probably spend more time thinking about these things than actually writing the script.
How do you detect different dates?
This isn’t so hard if your data files are from a single region, but often data are cross-regional. And detecting dates isn’t as simple as it might seem. In fact, figuring out proper date recognition processes has caused us many hours of dev time.
For starters, Americans use a different date format. How do you tell your script which date format to expect? And then how do you handle anomalies (pretty common in date data)? Then there’s the issue of turning rich-text CSV into a defined SQL date format.
Blah! We’re annoyed just writing about it. Have fun.
What time is it?
You’ll need to think about different time formats too. Specifically, AM/PM and 24 hour formats.
How do you distinguish between the two and do it consistently?
What do you do when there’re two different time formats in a file? (Especially common on human-made Excel to SQL database migrations.)
Numerous file-specific issues
If you convert different file types on a regular basis you’ll either need to write different scripts for each file type or one overarching one for each file type.
Here’s a question for you: what do you do with hierarchical XML or JSON data? SQL likes its data flat and you’ll need to flatten those bad boys down if you want to a fully automated DB converter. It’s not as easy as simply squashing data down.
How do you specify data type for
TABLE definitions? Data files don’t explicitly state what type of data they hold so your script will need to be able to figure it out and populate
Many online database migration tools will convert your file (e.g. Excel to SQL) but leave
TABLE definitions blank, leaving you to fill it out those yourself.
You don’t get access to an API
SQLizer has an API to help you automate your conversion processes even more. Build your own script and you get some of the benefits of an API but not all.
Tailor-made for you
This is a pro if you convert a specific file-type on a regular basis. You can spend some time optimizing your script for your most-used file type.
You control security
Despite robust security processes, it’s always nice to have peace of mind because you control the end-to-end process.
You have some fun coding and building something
Look, we know converting files and database migration isn’t sexy, but it needs to be done. If you want to write your own script so you feel you’re doing some real coding, that’s great.
Just use SQLizer
If you have a simple use case it’s easy to write your own script. Otherwise it’s honestly much easier to use SQLizer than mess around writing a script. The time you spend trying to get your script to work is better spent doing more useful coding tasks. At the end of the day, that’s why we built SQLizer and spent so much time on it: we hated messing around every time we wanted to convert a file. We just wanted something that works - which SQLizer does.