@geet, great to have you in the community. Are you looking specifically for a recommendation on a tool? Or are you looking more for the methodology of CD with Databases?
There’s generally 2 approaches when doing Database DevOps. Both have up-sides and down-sides, so it’s your choice which you prefer.
On one hand, you’ll dump all schema objects into scripts. These scripts create each item in the database. It’s not uncommon to have 1 object per file. As the objects change, these changes overwrite the SQL in the file. An engine then compares the SQL files with the schema objects in the target database, and depending on the option used, either updates the SQL files or runs ALTER scripts on the database. This migrations engine usually understands the deep corners of the database, and can take the shortest path between where it is and where you want to be.
On the other hand, you can create migration scripts for each change. Every time a column is added, a table deleted, or a pipeline generated, a new script is created. Usually these scripts are named with the date & time so their sequence is easily inferred alphabetically. During CD, the engine compares the list of scripts in the repository with the list of scripts in a migrations table in the database, and runs any scripts not run previously. On the up-hand, each deployment to every non-prod environment is a practice production deployment – the exact scripts are run each time. On the down-side, we must meander through all the experiments and adjustments the developers made. Did they split the table then back it out? Did they keep adjusting the column length? We’ll make each of these changes in every environment. The other major down side is if a script fails, it usually requires manual intervention to get back on the rails because we’re now in an unknown state with a rather naïve engine.
In general, I’ve found this second methodology more compatible with MemSQL because the migration scripts are simply SQL files authored with whatever syntax makes sense. The engine then merely needs to connect to the database, enumerate a table, and run a SQL script. Any migrations engine that can connect to MySQL or MariaDB can do this. Then add the db-migrate up command to the deploy script and you’re golden. We’re using a tool like this in the @MemSQL developer Live Stream on Twitch. Check out the code in the sql folder at GitHub - robrich/product-catalog: Reference architecture microservice API in TypeScript, Node.js, Vue.js and MemSQL