How to dynamically add columns to an existing database in ruby on rails

Question

i use ruby on rails on my backend and mysql2 as database, and as you can guess from my nickname i'm newbie in rails.

{
  "last_viewed_categorie" : "shirt",
  "last_name" : "herose",
  "login_screen_apperaed" : 1,
  "item_added_to_cart"  : 5,
  "first_name" : "george"
}

this is the json format that i'm currently receiving to my database. What i want is this something like that

jsonColumns //array that contains keys of json

jsonColumns.each do |jsonColumn|
unless @existingColumnNames.include?(jsonColumn)

"Alter table mutable add_column #{jsonColumn}"

I can't seem to find any way to make something like this to run,

Is it even possible?

if it's not possible can you offer a similar one?

Thank you

mysql2 would be your database driver, and MySQL is your database. Also worth noting, variable names in Ruby are, by tradition, expressed like json_columns. — tadman
– tadman, Commented Sep 11, 2014 at 17:17
TL:DR DON'T. What will stop people from creating their own JSON requests with random column names? You will have a completely messed up DB that Rails cannot use. — Reuben Mallaby
– Reuben Mallaby, Commented Aug 24, 2017 at 8:40

tadman · Accepted Answer · 2014-09-11 17:19:35Z

4

Dynamically altering the table is not something you want to do. A better plan is to use a serialized column, something where you create a LONGBLOB column that can store arbitrary data, and declare that with serialize in your model.

The ActiveRecord layer in Rails actually requires your schema to be static, unchanging, apart from scheduled migrations. Introducing arbitrary columns at run-time makes the column cache inconsistent and can lead to problems.

If you're handling a lot of JSON data, you may want to consider using PostgreSQL which has a native JSON column type that is supported by Rails. This makes storing, querying, and manipulating those results a lot easier.

answered Sep 11, 2014 at 17:19

tadman

212k23 gold badges237 silver badges266 bronze badges

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

2 Comments

railsnewbie Over a year ago

i tested between sqlite postgresql and mysql the best performance came from mysql, i can't seem to understand your solution offer.So i create columns from start and than give them names?That makes sense but can you show me a little bit on how can i do that?

tadman Over a year ago

What did "best performance" mean? I'm not sure how you tested these or if/how they were tuned. Generally models are created with a migration script when you run rails generate model model_name.

user3252359 · Accepted Answer · 2014-09-11 17:18:13Z

0

Run this code inside your loop.

 add_column :mutable, :jsonColumn, :text

answered Sep 11, 2014 at 17:18

user3252359

Comments

Ryo · Accepted Answer · 2017-08-24 08:40:47Z

0

Here's a gem for that. Check this out

https://github.com/Liooo/dynabute

Also, this question is a duplication of

Rails: dynamic columns/attributes on models?

answered Aug 24, 2017 at 8:40

Ryo

2,1334 gold badges27 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to dynamically add columns to an existing database in ruby on rails

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related