DbVisualizer
MySQL
OPTIMIZATION
SQL

Guidelines for a Proper SQL Script: ACID, CRUD, and Other Things You Need to Know

intro

In this blog, we’re walking you through a set of guidelines that will empower you to build a secure and performant SQL script for your use case. Dig in!

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
mariadb MARIADB
THE MARIADB DATABASE

If you’ve ever built an application that interacted with any database, you know first-hand just how important it is to write secure and performant SQL code. An SQL script that’s riddled with syntax errors won’t execute, a script that’s running on a database that doesn’t support the ACID principles won’t survive electricity going down, and a script that’s running on a non-properly optimized database will likely take ages to execute, too. What do you do to avoid these things? Right — adhere to guidelines that help you develop and work with SQL scripts!

Basic Guidelines for a Secure & Performant SQL Script

Everything has a beginning. The same can be said about an SQL script. Some would say that SQL scripts originate from the code that is written in an application, and that is partly correct — but we’ll dig deeper than that. We would argue that every script in SQL begins in your head: you think about the code you need to write, don’t you?

That’s why guidelines also begin with basic things and only then touch upon applications and/or databases connected to them: what do you need to accomplish? What’s your use case and why are you writing an SQL script in the first place?

After you’re well aware of the use case surrounding your script, it’s time to dig into its implementation. Only now do we come to things like OWASP for application security, GDPR and NIST standards, and so on. After all, you know what you’re trying to accomplish, so you know what data your application will be working with, how, when, and so on.

When writing a script in SQL, follow these basic steps:

  1. Be wary of SQL injection — don’t provide user input straight to the database. Don’t know why? Read our guide on preventing SQL injection.
  2. Don’t make your SQL script jump through hoops it isn’t supposed to — You’ve heard this before — SQL queries consist of tasks, and to make them execute faster, you optimize or remove those tasks, right? For example, consider using SELECT column instead of SELECT *, and so on. For more guidance, check out our article on how to write efficient queries.
  3. Be aware of GDPR and other compliance regulations — if you store data, that data has to be adequately protected, or else you can face huge fines. Perhaps there’s an option to email something to the user instead of storing data in the database?

These things will be a good starting point, but to make your script fast and secure even in the most demanding circumstances, you need to be aware of a couple more things — we’ll walk you through them now.

CRUD Queries and ACID Compliance

You have your use case. Now, what does your SQL script actually do? Every script does something. Something of value: and that something of value frequently manifests itself in the form of SQL queries known as CRUD — queries that either Create, Read, Update, or Delete data. So, how do your scripts act on data?

  1. Create — when working with an SQL script that deals with data creation (insertion), keep commits in mind (delay commits to the last second when inserting a lot of data to speed up the insertion process), and declutter the tables from indexes and partitions, too.
  2. Read — when reading data, remind yourself to scan through as little data as possible by using indexes and partitions, and look into related things like character sets and collations if the data you read isn’t in the desired format (i.e. if characters are suddenly getting replaced with question marks, etc.), and follow other performance guidelines outlined in the documentation of your DBMS of choice or this blog.
  3. **Update —** when updating data, beware of the aforementioned indexing and partitioning concepts (they will slow updates down), keep in mind that NULL values usually reside in the last possible partition when updating partitions, and consider locking your tables for reading/writing before performing an update for updates on bigger data sets to be faster. Also, when updating bigger data sets, you can take a backup and make use of the DEFAULT clause so that a column without any values has a default value pre-filled so you don’t have to execute UPDATE queries whatsoever.
  4. **Delete —** when deleting data, it’s wise to know that you can filter the number of rows that are deleted using the WHERE clause or delete all rows altogether using a TRUNCATE query instead.

Aside from the CRUD queries, ACID compliance would also be of paramount importance — ACID compliance would ensure that the queries you run won’t break your database in case electricity goes out or your network dies in the middle of a query being run.

To ensure your database is ACID compliant, identify the database management system in use, the parameter that adheres to ACID compliance (in MySQL and related DBMS, this parameter is called innodb_flush_log_at_trx_commit), and its options denoting ACID compliance or exchanging it for speed.

In MySQL, the default option — 1 — makes your database ACID compliant, and other options (0or 2) will make INSERT queries faster at the exchange of up to one second’s worth of data being lost if the database crashes.

Other Things You Should Know

Aside from ACID and query optimization, other things help your SQL script succeed, too! These are as follows:

  • SQL clients — a proper SQL client will format your scripts for you, and some will even provide you with drag-and-drop tools you can use where you will be able to drag-and-drop the tables you want to query into the SQL client for further actions. DbVisualizer is even able to ensure it commits (saves changes to the database) when you want it to if you right-click the query you want to be acted on in the SQL Editor:
DbVisualizer  Transaction Autocommit Feature
DbVisualizer — Transaction Autocommit Feature
  • Your server — a proper server is the backbone of any SQL script. If your scripts work with bigger sets of data, ensure that your server is set up properly and is capable of working with the load it’s facing.
  • Customer feedback — your script is built to complete a use case. In some cases, especially when developing software, customers know more about your software than you do yourself. Keep an eye out for customer feedback, use cases, feature suggestions, and so on.
  • Documentation — always keep an eye out for the documentation, too. The documentation of your specific database management system will always be of paramount importance and should be followed. Sometimes it doesn’t need to be followed to the tea though — also gain feedback from your colleagues to ensure you’re not implementing something the wrong way and that you’re not being left in the dark.

Before we conclude, grab a free trial of DbVisualizer and enjoy it for free for 21 days!

Summary

An SQL script is a file that consists of many SQL queries executed by your database. SQL queries, as such, are tasks contained of a bunch of subtasks that must each be executed for your scripts to be marked as a success by your database.

We hope that this blog has helped you better understand what makes scripts tick — explore more blogs in our TheTable blog section and until next time.

FAQ

What is an SQL Script?

An SQL script is a file that consists of SQL queries that are then executed by your database.

What does CRUD stand for?

CRUD stands for four database queries (Create, Read, Update, and Delete) that can be executed by your database. These SQL queries are often found within your SQL script.

What does ACID stand for?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four database properties ensure that the data within your database remains intact no matter what external factors may impact your server, your database, or anything in between.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Clustered vs Non-Clustered Index: Complete SQL Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-02-13
title

Queries and Subqueries in SQL: The Definitive Guide

author Lukas Vileikis tags DbVisualizer MySQL POSTGRESQL SQL SQL SERVER 7 min 2025-02-12
title

How to Optimize a Clustered Index Scan in SQL

author Antonello Zanini tags MySQL SQL SQL SERVER 7 min 2025-02-11
title

SQL Derived Table: Everything You Need to Know

author Antonello Zanini tags MySQL POSTGRESQL SQL SQL SERVER 9 min 2025-02-06
title

Understanding MVCC in MySQL

author Lukas Vileikis tags DbVisualizer MySQL SQL 7 min 2025-02-05
title

MySQL UPDATE JOIN Queries: A Complete Guide

author Antonello Zanini tags MySQL 8 min 2025-02-04
title

Postgres Create Array of Numbers: Complete Guide

author Lukas Vileikis tags POSTGRESQL SQL 7 min 2025-02-03
title

How to Protect MySQL With Fail2Ban

author Antonello Zanini tags MySQL 12 min 2025-01-30
title

SQL Injection Cheat Sheet: SQLi 101

author Lukas Vileikis tags MySQL SECURITY SQL 10 min 2025-01-29
title

SQL PARTITION BY in PostgreSQL: A Guide to Window Functions and Data Segmentation

author Leslie S. Gyamfi tags SQL 7 min 2025-01-28

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.