A common operation of many applications is the simple task of inserting data into a database. If data needs to be persisted across multiple sessions or users then a central database server is the way to go. MySQL is a common choice and has a free version which makes it great for small projects and startups. In this article we are going to go through how to install MySQL Server, setup a simple database with a couple of tables, and then how to write stored procedures to insert data into the database in a controlled and secure fashion.
- A Terminal (I prefer Hyper as it works on MacOS, Linux, or Windows 1)0
- Homebrew (If on MacOS)
- Sequel Pro (Or other MySQL client)
For this article I am going to focus on how to install MySQL server on MacOS but it also runs on the major Linux distros and Windows 10
Assuming that you have Homebrew installed. The following command will install the MySQL server to a local environment.
brew install firstname.lastname@example.org # Note: the @5.7 part is optional as it selects and older version. Without this it defaults to the latest
Now to start the server run:
And there you go! Your MySQL server should be up and running now
Creating A Demo Database
While you could create the database through the CLI tool it is much easier for this kind of work to be done through a GUI. This is where Sequel Pro comes in. While its not the fanciest or most robust option out there it is free, lightweight and for most small projects gets the job done.
Go ahead and open up Sequel Pro and you should be greeted with the connection screen
Enter the following information (substituting as needed for any differences in local setup)
Host: localhost Username: root Password: Database: Port: 3306
Next click on
Choose Database and then
In the next dialog enter demo_db and click
Now that we have a database setup lets create a table for our stored procedure to insert data into. If its not open already open the Query tab which allows us to run SQL queries against the MySQL database. Enter the following query and then click
Run Previous to execute.
CREATE TABLE demo_table ( id int(11) unsigned NOT NULL AUTO_INCREMENT, value varchar(50) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This query will create a table called demo_table with an id column and a value column.
Creating the Stored Procedure
Now that we have all that setup out of the way we can focus on creating our stored procedure that inserts data into the demo_table table. Delete the contents of the Query window as we have already created our table so we don't need them anymore and replace it with the following query and once again click the
Run Previous button to execute.
use demo_db; DELIMITER $$ DROP PROCEDURE IF EXISTS insertDemoData$$ CREATE PROCEDURE insertDemoData ( IN p_id INT(11), IN p_value VARCHAR(50) ) BEGIN INSERT INTO demo_table(id, `value`) VALUES (p_id, p_value); END$$ DELIMITER ;
You should now have a stored procedure (aka a "sproc") which will insert data into the table with the entered data. Try the following two queries to call the newly created sproc and then view the data inserted.
CALL insertDemoData(1, "Hello World!"); SELECT * FROM demo_table;
And there you go the stored procedure can insert data into your MySQL database.
In this article we went over the basics of:
- Installing MySQL using Homebrew on MacOS
- Using Sequel Pro to create a new database
- Using Sequel Pro to create a table in that database
- Writing a stored procedure to insert data
- Executing that procedure with data parameters
For more information on how SQL queries work check out this awesome set of tutorials over at W3Schools.