In today’s fast-paced world of digital work, the demand for efficient and simplified applications has never been greater. We’re about to embark on a journey that will empower you to harness the full potential of your database for developing web, mobile, and web services applications, all while eliminating unnecessary complexities. You will be able to unlock the power of MySQL Stored Procedures with automatic REST API, while also streamlining data management, and heightening productivity.

Stored Procedures in MySQL

Stored procedures in MySQL (and other RDBMS) are a set of SQL statements that are precompiled and Stored in the database. They allow you to group multiple SQL statements into a single, named, and reusable unit. Stored procedures offer several benefits, including:

1. Modularity and Reusability: You can write complex SQL logic once and reuse it in multiple places in your application. This reduces code duplication and ensures consistent execution.
2. Improved Performance: Stored procedures are precompiled, which can result in improved performance compared to sending multiple individual SQL queries. This is particularly advantageous for complex operations.
3. Enhanced Security: Stored procedures can be executed with different user privileges, which can help you control who has access to specific operations and data.
4. Simplified Maintenance: Changes to the database logic can be made in the stored procedure, reducing the need to modify application code. This simplifies maintenance and updates.
5. Transaction Control: You can use transactions within stored procedures to ensure that a series of SQL statements either all succeed or all fail, maintaining data integrity.

Here’s an example of a stored procedure that returns the maximum salary of a certain user. The procedure takes ‘last name’ as a parameter.

DELIMITER //

CREATE PROCEDURE max_salary (IN ln CHAR(20))
BEGIN
  SELECT e.first_name, e.last_name, e.birth_date, s.salary, s.from_date, s.to_date 
  FROM employees e
  JOIN salaries s on s.emp_no = e.emp_no and e.last_name = ln
  ORDER BY s.salary DESC
  LIMIT 1
  ;
END//

DELIMITER ;

Once created, this procedure then can be executed as an SQL command. To execute this stored procedure, you can use the CALL statement:

call salary('Bamford');
call salary('Simmel');

To invoke stored procedures you need a direct access to the database (host, port, user, and password) and to expose your MySQL server to MySQL clients like MySQL Workbench or SQL Command Line. This means only people familiar with SQL would be able to use it.

Stored Procedure Availability with HTTP Service

In case you want to make a stored procedure more available to others you could write your own simple HTTP service. You could start small and extend functionality with your business needs.  A microframework like Flask with MySQL Connector Library would be a good base. A raw proof of concept is below.

import json
import mysql.connector
from flask import Flask, abort, Response

app = Flask('stored-procedures')

DB_CONN_SETTINGS = {
    'user': 'emp_dbo',
    'password': 'mysql',
    'host': '127.0.0.1',
    'database': 'employees'
}


@app.route("/max-salary/")
def max_salary_view(ln):
    try:
     cnx = mysql.connector.connect(**DB_CONN_SETTINGS)
     cursor = cnx.cursor()
     query = "CALL max_salary(%s)"
     args = (ln,)
     results = cursor.execute(query, args, multi=True)
    except mysql.connector.Error as err:
        abort(500, description=err)
    else:
        people = []
        for result in results:            
            for row in result:
                person = {
                    'first_name': row[0], 
                    'last_name': row[1],
                    'birth_date': row[2].isoformat(), 
                    'salary': row[3],
                    'from_date': row[4].isoformat(), 
                    'to_date': row[5].isoformat()
                }
                people.append(person)                
        if people:
            json_data = json.dumps(people)
            return Response(json_data, status=200, content_type='application/json; charset=utf-8')
        else:
            abort(404, "Not found maximum salary for this user.")
    finally:
        cursor.close()
        cnx.close()        

This is just a simple example and will get much more complicated when working with more general cases that require:

• all data types supported by MySQL
• authentication and authorization
• different output document formats.

The above also does not include logging, load balancing, or service monitoring tools either. This way is more time consuming to create, but you can simplify database operations with REST API and MySQL Procedures using SlashDB.

Using SQL Pass-Thru in SlashDB

SlashDB offers SQL Pass-Thru Queries, this feature allows you to execute a custom SQL stored procedures by making HTTP requests. We can use SQL Pass-Thru to make our procedure safely executable by others.

First, log in to SlashDB with sufficient administrative permissions to create users, create pass-thru queries, and grant permissions.

Create User and Grant user access to the database

• • Create new user with password for authentication and API Key for programmatic access.
  • Assign database mappings to the database with the stored procedure.
  • Lastly, set administrative privileges.

Create a SQL Pass-Thru query in SlashDB

• • In the top menu, select Configure > Queries, then on the new page click +New.
  • Set up the query just as below with “:last_name” being the parameter that will be passed to the stored procedure from the URL.
  • Grant the created user, in this case “philb”, the execute permission.
  • Set your HTTP Methods
  • Row limitations and offset should be handled inside the procedure and not passed from the call.

Output

• At this point, the user, “philb”, can log in or use the API Key to access the stored procedure. He can also choose suitable format of output document: JSON, CSV, XML (with XSD) or HTML. I have included a few samples of the output types below.

Conclusion

We’ve explored the transformative power of combining REST API and MySQL stored procedures to simplify database operations. This dynamic duo not only streamlines your data management but also enhances your application’s efficiency, security, and scalability.

By leveraging SlashDB, you open up a world of possibilities for accessing and manipulating your data seamlessly, and it works the same with any database including MS SQL Server, Oracle, IBM Db2, PostgreSQL and more. Whether you’re retrieving records, updating entries, or executing complex procedures, SlashDB provides a standardized and accessible interface for your applications across all database types.

Title image attribution: generated by the Microsoft Bing powered by DALL-E.

The post Unlock the Power of MySQL Stored Procedures with Automatic REST API appeared first on SlashDB.