CSV Dump SQL Generator

MySQL / MariaDB are very common systems that developers encounter. At some point a client or colleague is going to ask you to extract data (something we are specialists at hint, hint :-)

No doubt they want to "play with it in Excel" or have an external reporting capacity that you need. There are great tools that allow you to do this like PHPMyAdmin but at some point you might need to use actual SQL in your application or by hand to regularly perform extractions. This tool helps you build your SQL queries and dump them to CSV with headings of your choice.

Understandably the tool has limitations but it is designed as a short cut for the busy or educational tool for the emerging Developers out there. If you have any questions or ideas please hit up our Facebook page and send us a message. If there is interest we will think about creating an SQL library to store and automate your extractions and more complex queries.

Using the tool:

In this example we want to dump the users table but give it a more friendly names. There are buttons to add or remove rows depending on what you need to do

The SQL the tool outputs:

SELECT 
	'ID',
	'Username',
	'Email',
	'First Name',
	'Last Name'
UNION ALL
SELECT
	`user_id`,
	`user`,
	`email_address`,
	`fname`,
	`lname`
FROM 
	`users`
INTO OUTFILE '/home/dump/users_1627001407630.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Traps for new players!

The MySQL process runs as the user mysql on most Linux systems. This means that you need to write to a folder that the mysql process has permissions on.

Create a folder for your CSV exports and give MySQL access

mkdir /home/dump
chown -R mysql:mysql /home/dump
        

The dump file has a timestamp

Deleting files isn't fun because you need to re-run the export. MySQL will not overwrite an existing file. This is why we have put a timestamp in the code prefixed with the table name. Be warned that if you are doing many exports and have large datasets that you will have to make sure you don't use up all your disk space.