🔢 How to convert JSON to an SQL Insert Statement

#json-to-sql-insert.md

🔢 How to convert JSON to an SQL Insert Statement

I found some issues with this article, so I decided to write my own article about it.

This page will explain the solution to convert this:

{
  "firstName": "James",
  "lastName": "Smith",
  "strength": 70,
  "stamina": 40
}

Into this:

INSERT INTO characters (
  firstName, lastName, strength, stamina
)
VALUES (
  'James', 'Smith', 70, 40
);

This solution uses the object attributes to identify the column names and then aligns them with their values.

Getting the column names from the object keys

Let's start with getting the column names:

const generateSqlInsert = (tableName, row) => {
  const columns = Object.keys(row);
  return `INSERT INTO ${tableName} (${columns.join(", ")})`;
};

Being able to pass in your tableName will allow you to use this for any table.

Sanitizing inputs

Minimally, the single quote is the only value you ened to santize.

If there are othere characters or values you want to prevent from getting into your data like HTML tags, add them in this method:

const sanitizeSql = s => s.replace("'", "''");

Getting the values

Using the columns we extracted from the object, we can align them with the INSERT values.

There is a check for numeric values to avoid adding the single-quotes around it.

If you are expecting other types of values like Date you can apply the appropriate date formatting for SQL here.

columns.map(col => {
  const val = row[col];
  return isNaN(val) ? `'${sanitizeSql(val)}'` : val;
}).join(", ")

Expected output: 'James', 'Smith', 70, 40

The solution

This is a base solution that can be expanded to support the specific needs for your code.

const generateSqlInsert = (tableName, row) => {
  const columns = Object.keys(row);
  return `INSERT INTO ${tableName} (${columns.join(", ")})
VALUES (
  ${columns.map(col => {
    const val = row[col];
    return isNaN(val) ? `'${sanitizeSql(val)}'` : val;
  }).join(", ")}
);`;
};

See json-to-sql-insert.html to see the code in action.

json-to-sql-insert.html

<pre id=o></pre>
<script>
const sanitizeSql = s => s.replace("'", "''");

const generateSqlInsert = (tableName, row) => {
  const columns = Object.keys(row);
  return `INSERT INTO ${tableName} (${columns.join(", ")})
VALUES (
  ${columns.map(col => {
    const val = row[col];
    return isNaN(val) ? `'${sanitizeSql(val)}'` : val;
  }).join(", ")}
);`;
};

const TABLE_NAME = "characters";

const obj = {
  "firstName": "James",
  "lastName": "Smith",
  "strength": 70,
  "stamina": 40
};

o.innerHTML = generateSqlInsert(TABLE_NAME, obj);
</script>