At some point in your programming career, you are going to have to work with a database. You might need to modify a schema, or to add a record, or to delete a record. But early on, things can go wrong. Deleting entire databases isn't something that you normally would want to have on your list of 'top 3 weaknesses' during an interview, but it happens.
I'll start off by prefacing the fact that deleting a big company's database is much different than deleting your test sales database at home. For one, there's just alot more data involved and the stakes are a bit higher.
So if you are new to working with databases, here are a few tips to consider to avoid large scale issues.
6. Don't run updates on live databases
The biggest mistake that any developer can make, is to pretty much delete a database when not intending to. Lucky for me, I made that mistake very early on in my career. Which is great as I got that out of the way right from the start.
And the cause of this, is something that pretty much every developer does from time to time. And that is running queries on a production database. But not just any queries. UPDATE queries.
This is not an uncommon scenario at the workplace. Sometimes you will get a request to update a single record in the database, either due to a typo or an unforeseen circumstance. Those database tables won't always have their very own CRUD form with validation (see below). They should, but they won't always.
Your typical update query would more than likely resemble something like the following:
UPDATE product SET name = 'Surface Pro 7' WHERE productID = 4
The biggest flaw here is with the WHERE clause. Or to be more specific, it is with the lack of the WHERE clause. Because the following scenario isn't too far away from the SQL query above.
UPDATE product SET name = 'Surface Pro 7'
That UPDATE statement has no fallback plan. It will run in milliseconds and will update the name of each row into the exact same thing. If you had 1 million records in your database, you now have 1 million records with the product name of 'Surface Pro 7'.
While my advice could definitely be something along the lines of "double check your update queries on a live database!", I can do better. My actual active is to avoid running update statements on a production database. There are plenty of safer alternatives that, while more work, are safer and can be used by multiple people.
One possible alternative is to embed all of your update queries into SQL Transactions. After your update, you can run a query to perform some form of validation on your data, and if anything is off in any way, you can easily Rollback all of your changes.
Whichever solution you implement though will almost always include more work up front.
5. Keep daily backups
When I ran the update query similar to the one above on a production table, I pretty much wiped out tens of thousands of records in the blink of an eye. As I was still new to my company I was not completely aware where the backup files were, or if we even had backup files to begin with.
The lesson here? Know where your backups are and how to get to them if needed. After emailing my then lead developer with the news that a moderately important database table was now gone, he quickly started the process of rolling back and updating the database with the most recent set of backup data.
It took a bit of time and there was very slight data loss as the backup was several hours old, but most of the data was able to be recovered without a problem.
Note that this isn't always a simple case though. Depending on the size of your database, restoring a backup could be a time consuming process and it could result in substantial data loss.
Backups are vital, but not having to use them is the goal.
4. Always make a CRUD
Updating data directly in a database isn't forbidden, nor is it wrong. It serves a purpose and many times it is the fastest way to make updates. But, it removes most kinds of data validation that you can perform to ensure that things like deleting a database table don't happen.
A CRUD (Create, Read, Update, Delete) is a form or API that takes care of these 4 primary actions for you. You can think of any data entry form in a CMS as an example of a CRUD.
While time-consuming (usually), the benefits are clear. A web form allows you to control the type of input coming in and it can also validate it before it even heads to the database.
You can also include confirmation modal boxes when performing operations like data deletion.
Essentially, it's a carefully guarded doorway into the database.
3. Save your queries
The more familiar you become with the database and the overall schema, the more queries that you will end up writing for various tasks.
A complex aggregate query that you wrote 6 months ago could be the thing that you need in order to get a task completed today.
I personally use note taking apps in order to save and catalog my queries. My current note taking app of choice is Notion, which you can read more about here.
2. Use default values
It's good practice in general to avoid null values in your database. For one, it's one less check that you have to perform in your actual code.
if (data != null && data != 0){}
Most database engines let you specify default values to given columns depending on their data types.
For the most part, null is much too vague of a value to make any determinations from. Is the value null due to error or because the column was added after the row? And in either case, what's the solution?
Default values at a minimum can prevent null-check errors from occurring more frequently.
1. Meet your DBA
If you work for a large scale company, then odds are you probably have a DBA (Database Administrator) somewhere in the vicinity.
DBA's are to databases, what sysadmins are to web servers. They essentially have the run of the land and can do things to a database that you probably never will.
Personally, for me, DBA's have been life savers in the past. Everything from changing backup schedules to analyzing performance to debugging slow performing database servers.
At most companies that I've worked at though, there is a clear separation between developers and administrators, and rarely do the 2 sides meet. Both sides are busy with their own work and if a developer needs help in some capacity from a DBA, then they can write a ticket and wait for a response.
And that's why I make it a point to know my DBA's personally. Because sometimes, you just need a quick 2 second response to a question and when deadlines are looming, it's nice to be able to walk over to someone's cubicle for a quick chat.
In closing
There's alot that goes into working with databases as a developer. And that means there's alot that can go wrong as well.
So hopefully these tips help you out in your future development, so that you can avoid the feeling of having deleted a database.