simple schema to demonstrate a stored procedure in mysql

places.sql

CREATE DATABASE  IF NOT EXISTS `places` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `places`;
-- MySQL dump 10.13  Distrib 5.7.17, for macos10.12 (x86_64)
--
-- Host: localhost    Database: places
-- ------------------------------------------------------
-- Server version	5.7.18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `Continent`
--

DROP TABLE IF EXISTS `Continent`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Continent` (
  `idContinent` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`idContinent`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Continent`
--

LOCK TABLES `Continent` WRITE;
/*!40000 ALTER TABLE `Continent` DISABLE KEYS */;
INSERT INTO `Continent` (`idContinent`, `name`) VALUES (3,'Africa'),(2,'Europe'),(1,'South America');
/*!40000 ALTER TABLE `Continent` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `CountriesInContinent`
--

DROP TABLE IF EXISTS `CountriesInContinent`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `CountriesInContinent` (
  `idContinent` tinyint(4) NOT NULL,
  `idCountry` mediumint(9) NOT NULL,
  PRIMARY KEY (`idContinent`,`idCountry`),
  KEY `fk_idCountry_idx` (`idCountry`),
  CONSTRAINT `fk_idContinent` FOREIGN KEY (`idContinent`) REFERENCES `Continent` (`idContinent`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_idCountry` FOREIGN KEY (`idCountry`) REFERENCES `Country` (`idCountry`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `CountriesInContinent`
--

LOCK TABLES `CountriesInContinent` WRITE;
/*!40000 ALTER TABLE `CountriesInContinent` DISABLE KEYS */;
INSERT INTO `CountriesInContinent` (`idContinent`, `idCountry`) VALUES (2,7),(2,8),(1,36);
/*!40000 ALTER TABLE `CountriesInContinent` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Country`
--

DROP TABLE IF EXISTS `Country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Country` (
  `idCountry` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`idCountry`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Country`
--

LOCK TABLES `Country` WRITE;
/*!40000 ALTER TABLE `Country` DISABLE KEYS */;
INSERT INTO `Country` (`idCountry`, `name`) VALUES (36,'spain');
/*!40000 ALTER TABLE `Country` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'places'
--
/*!50003 DROP PROCEDURE IF EXISTS `addCountry` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE PROCEDURE `addCountry`(
  IN p_idContinent SMALLINT, 
  IN p_countryName VARCHAR(100)
)
BEGIN
  DECLARE v_lastInsertId MEDIUMINT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
 
  START TRANSACTION;
    INSERT INTO Country SET name = p_countryName;
    SET v_lastInsertId = LAST_INSERT_ID();
    INSERT INTO 
      CountriesInContinent 
    SET 
      idContinent = p_idContinent, 
      idCountry = v_lastInsertId;
  COMMIT;
  SELECT v_lastInsertId;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-04-20 21:30:46

do we have PROCEDURE in knex migrations