How to automate database in a devops environment?
Tags: devops, database, serverless, discuss
Published: 2022-06-07 14:52:29

The success of any application depends on a lot of crucial factors. Having a seamless database deployment system is one of them. According to a study, [92%](https://www.liquibase.com/resources/reports/database-deployments-2019) of respondents reported that database deployments are the cause of their bottleneck. Devops ecosystem is based on stateless environments, but databases hold data in some kind of state. Therefore, it is obvious that [databases are most difficult to automate in a devops environment](https://www.simform.com/blog/database-devops/). But there are tricks around it too. 

##Installation and configuration management

Probably the simplest issue to address in the list. The issue relates to automating the database simpler than what we do with a middleware. 

For on-premises solutions, if you are using a windows SQL server, one can rely on using Chef for writing the configuration of my database server. The chef works seamlessly in a stateful environment.

For a cloud-based solution, one can write their own Terraform or CloudFormation for installation as soon as their RDS instance boots up with appropriate security and authentication details. 
For a local dev environment, one can rely on [Faker](https://github.com/joke2k/faker) to create mock database data for your database. 

```
const faker = require('faker');

let firstName = faker.name.firstName();
let lastName = faker.name.lastName();
let prefix = faker.name.prefix(); 
let suffix = faker.name.suffix();

console.log(`Employee: ${prefix} ${firstName} ${lastName} ${suffix}`);
```

##Data lifecycle management

One security concern about databases is always the leakage of confidential information. That’s why many organizations are against the practice of backups and restoration processes that runs with production data. However, it is extremely crucial to have similar data in the pre-production environment for proper load testing. 

This issue can be addressed by two approaches:

- Export production data into the QA database 
- Have automation that runs [diff] every 24 hours to look at how much new data has been generated. Then, leverage faker scripts to populate the QA database with the same amount of records. 
	
##Different environments, different sizes

This issue is somewhat trickier to solve. This problem can be solved by two approaches:

One method to go about this is to make sure your database is configured so that the real data is stored on a different filesystem than the actual database where installation needs to take place. This enables you to backup, copy or restore a filesystem to a different running database executable without having to touch the runtime database.

Another method is to use the faker tool for generating significant amounts of data for performing test runs. The only downside is that it might take a bit longer to set up the database. But, it will ensure good and clean data in your database.

##Wrapping up

Managing a database has tons of complexities. That's why organizations consider it as the bottleneck process in the entire devops operations. However, with certain tricks, it is possible to deal with the issues. Let us know which solution you found the most intriguing about dealing with the devops database.