How to get an array into SQlite3 with Express + Knex + Node.js?

Question

Working on a recipe type app and I am working on the routes/endpoints/backend. Team wants the following JSON structure for when reaching the GET request.

const fakeDataRecipes = [
    {
        id:0,
        title:"PBJ",
        source:"Mother",
        ingredients:["bread", "peanut butter", "jam"],
        instructions: "1. Get bread. 2. Get peanut butter and jam. 3. Put together.",
        category:["snack", "dinner", "vegetarian", "sandwich"],
        user_id:1
    }, 
    {
        id:1,
        title:"Peanut Butter and Banana Sandwich",
        source:"Uncle Steve",
        instructions: "1. Get bread. 2. Get peanut butter. 3. Slice banana. 4. Put together",
        ingredients:["bread", "peanut butter", "banana", "chocolate"],
        category:["snack", "dinner", "vegetarian", "sandwich"],
        user_id:2
    }
];

I have searched but I seems SQLITE3 does not support arrays in columns. what is the best approach to this situation? I need ingredients & category to be an array. Some people say Create a new table for the ingredients & category. Others says to use a blob data type in SQLite3 which I am not familiar with. Or Just store it as a string and then then covert it to array which I am not sure would work or create problems for the front end. the following is the knex migration file



exports.up = function(knex) {

  return knex.schema.createTable('recipes', recipeColumn=>{

    recipeColumn.increments();
    recipeColumn.text('title').unique().notNullable();
    recipeColumn.text('source').unique().notNullable();


  })

};

Rich Churcher · Accepted Answer · 2019-07-14 09:14:58Z

0

Normalising into separate tables is the way to go here:

`recipes`: (`title`, `source`)
`ingredients`: (`name`)
`recipes_ingredients`: (`recipe_id`, `ingredient_id`, `quantity`)
`categories`: (`name`)
`categories_recipes`: (`recipe_id`, `category_id`)

As a brief example, to pull the ingredients array for each recipe:

knex.select('name')
  .from('ingredients')
  .join('recipes_ingredients')
  .on('recipes_ingredients.ingredient_id', '=', 'ingredients.id')
  .where('recipes_ingredients.recipe_id', recipeId)

(Not tested, but it'll look something like that). In other words, use the join table as glue to retrieve the results you're after.

Oh, and see also this answer.

answered Jul 14, 2019 at 9:14

Rich Churcher

7,6923 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get an array into SQlite3 with Express + Knex + Node.js?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related