The LightFM handler functions as an interface for the LightFM Python recommendation library. The current implementation supports collaborative filtering for user-item and item-item recommendations. It allows users to make use of the powerful LightFM recommendation framework library for performing recommendation on interaction data sets.


Before proceeding, ensure the following prerequisites are met:

  1. Install MindsDB locally via Docker or Docker Desktop.
  2. To use LightFM within MindsDB, install the required dependencies following this instruction.

Please note that, if you are using Docker to run MindsDB, before installing the dependencies for this integration as per the instructions given above, it is currently necessary to install a couple of Linux development packages in the container. To do this, run the following commands:

Start an interactive shell in the container:

docker exec -it mindsdb_container sh

If you haven’t specified a name when spinning up the MindsDB container with docker run, you can find it by running docker ps.

Install the required Linux development packages:

apt-get -y update
apt-get install -y build-essential python3-dev libopenblas-dev

As the current implementation stands, the input data should be a table containing user-item interaction data:

| user_id | item_id | rating |
| 1       | 2       | 4      |
| 1       | 3       | 7      |

Please note that at the moment this integration does not support the FINETUNE feature.


Before creating a LightFM model, we need to create an ML engine.

FROM lightfm;

You can verify it by running SHOW ML_ENGINES.

Now let’s create a LightFM model specifying the necessary input parameters.

CREATE MODEL lightfm_demo
FROM mysql_demo_db (SELECT * FROM movie_lens_ratings)
  engine = 'lightfm',
  item_id = 'movieId',
  user_id = 'userId',
  threshold = 4,
  n_recommendations = 10,
  evaluation = true;

The required parameters include the following:

  • The item_id parameter that stores items to be recommended; here, these are movies.
  • The user_id parameter that stores users to whom items are recommended.
  • The threshold parameter is used when score of interaction is provided in the input data. It defines the threshold for the recommendation.
  • The n_recommendations parameter stores the number of recommendations to be returned.

Optionally, you can provide the evaluation parameter if you want to store the evaluation metrics. It is set to false by default.

Here is how to connect the mysql_demo_db used for training the model:

CREATE DATABASE mysql_demo_db
WITH ENGINE = 'mysql',
    "user": "user",
    "password": "MindsDBUser123!",
    "host": "",
    "port": "3306",
    "database": "public"

Let’s query for the following recommendations:

  • Get recommendations for all item_item pairs:

    SELECT b.*
    FROM lightfm_demo AS b
    WHERE recommender_type = 'item_item';
  • Get item-item recommendations for a specific item_id:

    SELECT b.*
    FROM lightfm_demo AS b
    WHERE movieId = 100
    USING recommender_type = 'item_item';
  • Get recommendations for all user-item pairs:

    SELECT b.*
    FROM lightfm_demo AS b
    where recommender_type = 'user_item';
  • Get user-item recommendations for a specific user_id:

    SELECT b.*
    FROM lightfm_demo AS b
    WHERE userId = 100
    USING recommender_type = 'user_item';
  • Get user-item recommendations for multiple user_ids:

    SELECT b.*
    FROM mysql_demo_db.movie_lens_ratings AS a
    JOIN lightfm_demo AS b
    WHERE a.userId in (215,216);