As a lone freelance full-stack developer it’s very easy to keep all your local developments backed up, especially if using a Mac and time-machine.

However, now and again, it’s a good idea to save stuff into git, even if only to copy all that hard work into the cloud. And that’s not to mention versioning …

I recently needed to update a large number of views and decided to automate the export from my development database into a path covered by my favourite IDE (currently Visual Studio – yea a Microsoft product running on Mac beats the best of the rest).

So I wrote a bash script to export all views from a database, each view into it’s own file.

I decided to export using info from the table schema rather than mysqldump as I really only wanted the raw SQL for each view.

I used an npm package called sql-formatter-cli to parse the output from mysql and format into reasonable looking SQL

So here it is:

</p> <p>#!/bin/bash COMMAND="SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME=" DATABASE=your_database_name USERNAME=your_user_name</p> <p> ALLVIEWS=`mysql --defaults-extra-file=~/mysql-chris.cnf --skip-column-names $DATABASE -h 192.168.1.11 -u $USERNAME -B -e "show full tables where table_type = 'VIEW'" | awk '{print $1}' ` echo $ALLVIEWS for ONEVIEW in $ALLVIEWS do SQL=`mysql --defaults-extra-file=~/mysql-chris.cnf --skip-column-names $DATABASE -h 192.168.1.11 -u $USERNAME -B -s -e "$COMMAND '$ONEVIEW'"` echo "CREATE OR REPLACE VIEW $ONEVIEW as $SQL" >$ONEVIEW.txt sql-formatter-cli -i $ONEVIEW.txt -o $ONEVIEW.sql rm $ONEVIEW.txtql done echo "Finished !"

You will need to install the npm sql-formatter-cli using the npm command:-

npm i -g sql-formatter-cli

I store my local mysql password in a .cnf file (called mysql-chris.cnf above) with the format:-

[client] password=Your_password_here