Hi Team,
we are looking for automating the deployment of creation table/index etc. in the Memsql.
Do we have any tools to suggest ?
if no tools , is there any suggestions for approach ?
Can you elaborate on your scenario? It’s not clear exactly what you are asking for.
Hello Hanson,
I am looking for MemSQL Continuous Deployment solutions as well.
To elaborate, MemSQL database objects (like tables, views, stored procs, etc.) are created, updated & deleted on a regular basis and “lookup” table data is also updated on a regular basis. Everything is checked into source control and we do have Jenkins doing deployments of our applications, kafka, etc. but for memsql, it is painfully manual.
Any suggestions?
Hi @geet!
The standard approaches to using Continuous Deployment (CD) will work with MemSQL, for the most part. However, the tools that are out there don’t have a MemSQL mode that I know of. So you’d typically have to start with the MySQL mode, because we’re close to MySQL behavior.
I asked our sales engineers and they said that there are a few customers of ours successfully using Liquibase with MemSQL for CD (see liquibase.org). I was not able to get details of any special changes they had to make.
The standard approach to putting all your database objects (SPs, UDFs, views) in version control, and having your tool redeploy them when needed, will work. You need to use the CREATE OR REPLACE… syntax for UDFs and SPs. For views, you can use CREATE VIEW and then ALTER VIEW. There’s no CREATE OR REPLACE VIEW.
For objects with true state, like tables, it’s not as simple, because, for example, if you add a column to a table, you have to use ALTER TABLE to do it, you can’t just recreate it. We support a pretty wide variety of ALTER TABLE capabilities (ALTER TABLE · SingleStore Documentation), for both row and columnstore tables, but there are probably differences from other products, which might require some customization.
Other readers, please post if you have found useful approaches for CD with MemSQL.
@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
Did anybody tried using , Below schema deploy project for Memsql, usually it works for Mysql and few other DB’s for automating schema deployment
Flyaway is sadly not currently compatible with MemSQL. It looks too deeply into the MySQL schema, and notes how MemSQL does things differently. There’s a very stale PR to add MemSQL support to Flyway, but I reached out to them, and it seems the Flyway team hasn’t prioritized merging in favor of other architecture moves and new features they’re building.
Thank you for the suggestions. I have also looked into others like (MySQL database connector (Reference)) and since memsql is based on MariaDB, that isn’t going to work either. I will post here if I find something that others can benefit from but in general looks like I may just go with your “migrate scripts” suggestion.
Is there anything new regarding this topic with newer version of the database? Still “migrate scripts” the recommended approach?