How to put JSON object in MySQL database

Question

I am getting following JSONObject from client.

{
  "registerationId": 57502,
  "firstName": "Taha",
  "lastName": "Kirmani",
  "dob": "10-08-1988",
  "cnic": 4210137718361,
  "gender": "Male"
}

This is how i am parsing it for now.

app.post('/addUser', function (req, res) {

  var registrationId= req.body.registerationId; 
  var firstName= req.body.firstName; 
  var lastName= req.body.lastName; 
  var dob= req.body.dob; 


  //var randomQuery = connection.query('INSERT INTO students `COLUMN NAME` VALUES `VALUES NAME` //);
  // 


})

With the above approach i can put data in MySQL database, but i am looking for more efficient approach.

What i want to do is to pass the jsonObject to query, and it inserts the data in DB, so i won't be doing the req.body.id step for each route.

I am looking for something like this;

   (insert into table (?) VALUES (?) , req.keys, req.values)

Russell Santos · Accepted Answer · 2018-04-17 18:08:24Z

There are multiple ways of approaching this problem.

Use an ORM library. An Object Relational Mapper or ORM is a library that can help you store and retrieve objects from a relational database (like MySQL). It helps you create the proper SQL string for your use case(whether creating, reading ,updating or deleting). There is quite a number of available ORMs for Node.js. Here's a few: Bookshelf, Sequelize,Objection. Some notes about an ORM
- They make it easy for really simple operations (CRUD)
- There is usually support for more complex queries (joins, group by, etc)
- Really complicated queries are usually hard to do, but they usually support using raw SQL, so you can craft them yourself if needed.
- Generally, they still require you to define columns and types for a particular table. So there is still a bit of work to be done.
Store the information as JSON. If you really want to store the JSON data as is, you can opt to write the JSON object directly do the database. Some notes about this approach:
- Storing would be really easy - you don't need to do a complicated query.
- retrieval would be a bit hard, unless your database supports the JSON data type, and allows indexing for that type (MySQL 5.7 has some support for this)
- schema isn't enforced, which may be problematic for you when future versions require changes to the schema - you'll have to deal with some values possibly not having new fields.

In general, I would recommend that you use an ORM over storing the information as JSON. Just from experience, it becomes really difficult to maintain if this is done, and support for indexing in JSON is relatively new in MySQL. If you think this is really better, I would recommend using a non-relational DB instead of MySQL.

However for most use cases, option 1 - an ORM - is probably the safe bet.

Bill Karwin · Accepted Answer · 2018-04-17 17:52:03Z

You can't do that simply with SQL. In SQL syntax, the names of the columns and the number of values in the VALUES clause must be fixed at the time the query is parsed.

And of course the columns must exist in the table definition. If your JS object has extra properties that don't match real columns, it wouldn't work regardless.

To do what you're intending, you'd need some JS class that knows about the columns that exist in the table. It would pick out the subset of properties in your JS object whose names match known columns of the table, and I assume ignore other properties. Or would you want it to raise an error if there are illegitimate properties?

That exemplifies a problem — what you want the mapping to do isn't necessarily what anyone else would want it to do. Or not even what you want it to do in some other app of yours.

Collectives™ on Stack Overflow

How to put JSON object in MySQL database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related