Golang MySQL queries made easier
Short story behind ‘strikeMySQLQueries’
When we started developing Strike, we knew that a brand new cybersecurity company should be developed on top of one of the newest and most efficients programming languages available. That’s why Go was our choice… and we loved it!
We learnt a lot, but nonetheless, many solutions that were available on other languages were not implemented.
Therefore, we also developed material that helped us increase our productivity. One clear example is the InMemory Mongo DB we designed for making unit testing considerably faster.
When we needed to use a relational database, we chose MySQL as our main database management system. We also chose not to use an ORM in order to maintain great performance and also keep an eye on the implementation of the code we were using.
In this case, we are sharing an open source library, where we would love contributors to participate at. This simple library, allows basic MySQL queries to be auto generated as code.
The main advantages this library offers are:
- New attributes or changes on existing attributes are updated automatically. This means a huge impact on mantainability.
- Typing errors on queries do not exist.
- Developers do not need to know MySQL queries syntax.
Hands On
To start with, we will first present you some auxiliar structures we will be mentioning; they help us generating the queries.
Now that we know the structures we will be using, we will study our functions. The functions are pretty simple and self-explanatory. However, for better understanding, we are going to do a proof of concept, a simple CRUD.
Create or INSERT example
Before reaching the create example, let’s set up our tutorial scenario: We are going to insert “kobe”, who is a BasketPlayer into the basket_legends table.
A BasketPlayer is composed of the following attributes:
Now, we create “kobe”:
For generating the insert query, we just call the MakeInsertQueryWithStruct(…) function with the table name and the struct as params.
MakeInsertQueryWithStruct("basket_legends", kobe)It will return the following string:
As we see, string attributes are surrounded by quotation marks, while booleans, ints or float do not. Also, the BirthDate attribute is taken to UTC time and then parsed into a MySQL datetime format .
Slices are left over, as another table is required to insert them together with a foreign key refering to kobe. This second table must be done manually.
It can be made with a for loop and the same method MakeInsertQueryWithStruct(…) or with the MakeInsertQuery(…) method (MakeInsertQuery(…) method receives the table name and a slice of FieldWithValues). Both options could be implemented as shown below:
For any of the implementations, now the slice insertNicknamesQuery is filled with the suitable strings for easily inserting the attributes onto the basket_players_nicknames table.
This works, but queries the database multiple times. However, it can perform better if we call the function MergeManyInsertsIntoOneInsert(…) which reveives the insert queries we previously generated.
MergeManyInsertsIntoOneInsert(insertNicknamesQueries...)The function finally returns the insert query we wanted for inserting Kobe’s nicknames into the basket_players_nicknames while accessing the database only once.
Read or SELECT example
The read or select query might be the most challenging to call, this is because the query has many alternatives: making a projection, filtering, joining, and ordering.
In the select example, we would like to get the ‘name’, ‘surname’ and ‘golden boot award year’ of the football players, with 1.70m height, with name ascending sorting. The select query will be called on the players table, joining with the golden_boots table.
We will be calling the MakeSelectQuery(…) function, which receives:
- The selectionfields —A slice of FieldName, which are the fields we want to get. If nil, then every field will be returned (acts as a ‘*’).
- The tableName — A string.
And optional fields that can be disabled by being set as nil:
- The filterFields — A slice of FieldWithValue for filtering the query.
- The sortingFields — A slice of FieldWithSorting for sorting the query.
- The joinFields — A slice of JoinFields for joining with other tables.
A quick example of this might be:
The funcion will return the following query:
Update example
In this example, we will be updating the all_time_scorers table due to a recent Cristiano Ronaldo goals that made him the all time scorer.
For generating an update query, we will call the MakeUpdateQuery(…) function, which receives:
- The tableName — A string.
- The newValues — A slice of FieldWithValue which represent the new values.
- The filter — A slice of FieldWithValue which acts as a filter for knowing which field to update.
An example of a soft delete would be just another implementation of the update query. For example, deleting Juventus from the champions_league table can be done as follows:
Delete example
Last, but not least, the delete query is pretty simple. For example, we will now be hard deleting Suarez from Barcelona. The soft delete mechanism was covered on the update section as it is just an adaptation of the Update query.
For generating a delete query, we just call the MakeDeleteQuery(…) function, which receives the table name and optional FieldWithValues, that serve as filter fields for selecting which row to delete.
The function will return the following string:
Conclusion
We have just overviewed a simple solution for autogenerating MySQL queries.
If you want to use it in your own projects, you just need to install it by running ‘go get -u github.com/strikesecurity/strikemysqlqueries’ and add it to your go module.
We would love for your contribuitions and of course will be happy to help you if any doubt arises, just reach out here or in LinkedIn, thanks for reading!
Cheers from Strike ;)
