Hot on the heels of our post about rotating your API keys - this blog post will guide you through the process of automating your SQLizer API key rotation, and use cron to run that automation daily.

Guide: Automated Key Rotation

Rotating APIs keys regularly is crucial to maintaining the privacy of the data held in files that are processed by SQLizer. You can use the following approach to set up automatic rotation of your SQLizer API keys.

The https://sqlizer.io/api/keys endpoint can be used to fetch existing keys, create new keys and remove existing keys using the GET, POST and DELETE HTTP methods respectively. To call this endpoint you must pass an "Authorization" header containing "Bearer {a valid SQLizer API key}". So to start the process you will need to manually copy an existing API key from the SQLizer user interface and store it somewhere, such as in an environment variable. To ensure this variable is set when we log into our server, we can add it to the .bashrc file, or equivalent:

> echo "export SQLIZER_API_KEY={an existing API key}" >> ~/.bashrc
> source ~/.bashrc

We can check this is working by fetching our keys from the /api/keys endpoint using the cURL command line tool and our API key in the environment variable:

> curl https://sqlizer.io/api/keys -H "Authorization: Bearer $SQLIZER_API_KEY"

This should return a JSON array of one or more objects containing details of your existing keys. If you want to format the JSON response nicely, you could pipe the output to the jq command line tool, like this:

> curl https://sqlizer.io/api/keys -H "Authorization: Bearer $SQLIZER_API_KEY" | jq

This will return something like:

[
{
"name": "Default",
"token": "abc123",
"createdAt": "2024-05-03T15:02:25.627821"
},
{
"name": "another key",
"token": "xyz123",
"createdAt": "2024-05-03T15:28:50.531062"
}
]

To create a new key, send a POST request to the /api/keys endpoint, passing a JSON object with a name property containing the desired name:

> curl https://sqlizer.io/api/keys \
-H "Authorization: Bearer $SQLIZER_API_KEY" \
-H "Content-Type: application/json" \
-X POST \
-d '{"name":"my new key"}'

In response you will get some JSON data containing the newly generated key, this will look something like:

{
"name":"my new key",
"token":"def456",
"createdAt":"2024-05-03T15:32:28.7639212Z"
}

To delete a key we can call the DELETE method on /api/keys/{token}, like this:

> curl https://sqlizer.io/api/keys/abc123 \
-H "Authorization: Bearer $SQLIZER_API_KEY" \
-X DELETE

Assuming it worked, you should receive a HTTP 200 status code, and some JSON data containing a 'status' and 'message' property:

{
"status":"OK",
"message":null
}

So to automate the process of rotating your keys we could write a bash script that:

  1. Creates a new key, grabbing the "token" value from the returned JSON
  2. Updates the line in your bashrc file that sets the API key in an environment variable, with the newly created key value
  3. Deletes the key that was previously stored in the environment variable

Here's an example script that does that:

SQLIZER_NEW_KEY=$(curl -s https://sqlizer.io/api/keys \
-H "Authorization: Bearer $SQLIZER_API_KEY" \
-H "Content-Type: application/json" \
-X POST \
-d '{"name":"Auto generated key"}' | jq -r '.token')

sed "s,export SQLIZER_API_KEY=[^;]*,export SQLIZER_API_KEY=${SQLIZER_NEW_KEY}," -i ~/.bashrc

curl https://sqlizer.io/api/keys/$SQLIZER_API_KEY \
-H "Authorization: Bearer $SQLIZER_API_KEY" \
-X DELETE

If we saved that script as rotate-sqlizer-api-key.sh we could schedule it to run every day at midnight using cron:

> crontab -e
0 0 * * * . /home/my-user/.bashrc; /home/my-user/rotate-sqlizer-api-key.sh

The . /home/my-user/.bashrc; command loads the users environment variables, which include the current SQLIZER_API_KEY value.

There are many different ways you might be starting the process that actually uses the SQLIZER_API_KEY environment variable, so explaining how to ensure the new API key value is picked up by that process is beyond the scope of this article, but if that job was also started by cron, you'll need to tell that process to use the updated key from the .bashrc file. You could do that like this:

> crontab -e
0 0 * * * . /home/my-user/.bashrc; /home/my-user/rotate-sqlizer-api-key.sh
1 0 * * * . /home/my-user/.bashrc; /home/my-user/use-sqlizer-api-key.sh

It’s important to keep your API keys private, secure and rotate them periodically.

🖤 SQLizer team.

About: SQLizer converts for free - less than 5,000 rows of data for personal use - however if you have a >5000 row CSV file you need to convert, use code CONVERTME10 for 10% off your Pro Monthly plan for the first 12 months, or a one-off 10% discount on your Pro Annual plan.