CODEWITHSUNDEEP
mysqlsqlnode.js
David
David

Reputation: 1174

Node.js MySQL create Table dynamically

I'm having trouble creating a table with dynamically amount of columns.

I have an Object, where i don't know the amount of keys exactly:

var obj = {
  data_1: 123,
  data_2: 456,
  data_3: 789,
  (...)
};

Each key of this Object should become a column in the new table. Fortunately, the column datatype is always a double.

I need something like this:

"CREATE TABLE 'Data' (  
   FOREIGN KEY (id) REFERENCES other_table(id), // this is fixed
   data_0 int, // this is fixed
   Object.keys(obj) double
)"

Is this even possible? I could find nothing that looks similar to this.

I'd be really glad, if someone could help me out with this one.

Upvotes: 1

Views: 2224

Answers (1)

bitifet
bitifet

Reputation: 3669

Actually solved in comments.

But for the sake of completion, here it is more detailed:

If all objects to be inserted have the same keys, we can pick the keys from the first one and use that data to generate the whole CREATE TABLE statement.

Example:

const data = require("path/to/some/data");
const sql = [
  "CREATE TABLE new_table (",
  "id int REFERENCES other_table (id),",
  "data_0 int,",
  Object.keys(data[0])
    .map(k=>k+" int")
    .join("\n")
  ,
  ")",
].join("\n");

Upvotes: 4

Related Questions