DataGrip: Exporting Data as SQL Batch Inserts (and more)

Preamble

This isn't so much a "great new feature" as it is a missing feature from DataGrip. While I could go on with my love/hate relationship with DG's features, (it's mostly love) it is fortunately easy enough to manually add some functionality.

I will also take a moment to say this is my first post and would love any feedback on improving it.

On to the main attraction... (or just skip to the script)

Why Batches?

In short, better performance and limited table/row locks when inserting thousands, millions, or even billions of rows.

Batches vs Individual Inserts

Batches perform orders of magnitude faster than individual inserts. In SQL, each query is parsed and executed separately; which causes significant overhead between each query. This makes the individual inserts very slow, while batches avoid almost all of this overhead.

Batches vs 1 Large Insert

One large insert is technically faster than batched inserts; however, this often comes at the cost of locking tables/rows for extended periods of time. Table & row locks block all other queries that modify data (causing a desync) and even some queries that read data (causing request lag). This is particularly harmful in production environments, especially when you need to comply with an SLA.

Inserting in batches gives you a significant performance boost, while still preventing tables or rows from getting locked for too long. Allowing normal queries to run at the same time as the set of batched queries. Additionally, in most cases, the difference in performance is very minor between batched inserts and 1 large insert.

Installation

1. Go to the "Data Extractors" dropdown. (in results bar)
2. Select "Go to Scripts Directory"
3. Create a new file called "SQL Batch Multi-Line Insert.sql.groovy" (The double extension is important for syntax highlighting)
4. Paste & save the this script

That's it! You now have access to exporting rows as batches.

You can also customize the output using the configuration vars at the top of the file, but the defaults should work well for most people.

Key Features

• Custom batch sizes
• Ability to minify
• Custom formatting options
• Config Preview
• Well formatted & commented code

Cheers! 🍻