Node.js MySQL create Table dynamically

Question

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.

is this the only possibility? I'd rather have a column for each value. — David
– David, Commented Feb 21, 2019 at 9:17
Are you sure all objects have the exact same keys. If it is you can generate that portion of the create table with something than Object.keys(data).map(k=>k+" int,\n"). — bitifet
– bitifet, Commented Feb 21, 2019 at 9:19
This can't be really answered without knowing the exact use case, and all edge cases of how you work with the data. In general, if you don't know the number of data_ entries, and you need to be able to query the data stored for data_ then you need to have a table in the form other_table(id), key, value and each data_ is an own row in the table. If you have a maximum number of keys per entry, then you can think about columns than can be NULL. — t.niese
– t.niese, Commented Feb 21, 2019 at 9:25
@bitifet this works perfectly. thanks! didn't actually knew that you can "loop" inside an sql statement using map. if you could post it as an actual answer i will mark is as accepted. my statement (which is working) looks like this now: "CREATE TABLE new_table (ID int, FOREIGN KEY (ID) REFERENCES other_table (ID), data_0 int, "+ Object.keys(obj).map(k=>k+" double")+")" — David
– David, Commented Feb 21, 2019 at 9:52

bitifet · Accepted Answer · 2019-02-22 08:44:34Z

4

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");

answered Feb 22, 2019 at 8:44

bitifet

3,74919 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

Node.js MySQL create Table dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related