SQL databases allow for robust storing of relational data. It is often used in JavaScript focused tech stack to store data.
SQL databases offer several benefits, including storing unstructured data. Although, creating tables, updating tables, and building SQL statements might be a labor-intensive task that takes away from their attractiveness.
However, we can write code to handle many of these basic functions and allow our SQL database to be more dynamic and reusable over multiple projects.
Interested? Let’s break down the coding process and analyze each step. I will use an example focused on JavaScript, using an SQL database on the client-side via a web / hybrid mobile application or server-side running Node.
Create Table
/*** Creates a SQL table from a JavaScript object* @parameter {string} tableName - name of table in uppercase snake case* @parameter {Object} object - data object used to create table* @parameter {string} primaryKeyString? - optional argument, allows the addition of a bespoke primary key */const createTable = async (tableName, object, primaryKeyString) => { const columns = getColumns(object, true); const primaryKey = primaryKeyString ? ", PRIMARY KEY (" + primaryKeyString + ")" : ""; await create(tableName, columns, primaryKey); await alterTable(tableName, object); // recursively create tables Object.keys(object).forEach(async key => { if (typeof object[key] == "object") { // add id to child object if it doesn't exist object[key].id = object[key].id ? object[key].id : ""; // create database table with nested object await createTable(toSqlName(key), object[key]); } });}Our method to create tables recursively calls itself to conceive individual tables for each nested object within the base object, passed as the initial argument to the created table method.
To build a new table, we build an SQL query from the object structure. To do this we first get the column names which will simply be the object keys, converted into the desired column naming conventions (uppercase snake case).
In JavaScript, object’s properties are written in camel case as a standard, whereas the SQL column name standard is to use uppercase snake case.
/*** Returns a string of the colomn names for a database table, adds the data type to this list when creating a table* @parameter {Object} object - data object used to interact with database* @parameter {boolean} isCreate - optional argument, adds column type to SQL statement (necessary when creating table)*/const getColumns = (object, isCreate) => { let columns = ""; Object.keys(object).forEach(key => { columns += toSqlName(key, object[key]); columns = create ? columns + " " + getColumnType(object[key]) + " , " : columns + " , "; }); return columns.slice(0, -3);}/*** Returns string of column type (determined from JavaScript object) * @parameter {any} property - camel case string*/const getColumnType = (property) => { const columnMapping = { string: "TEXT NULL", number: "NUMERIC", boolean: "BOOLEAN", object: "TEXT NULL" }; return columnMapping[typeof property];}When getting the column names for a table, we want to set up a table for each nested object in the base object.
To simplify the logic of knowing which tables are children of the parent tables, we add a prefix to the column name in the parent table.
/*** Converts a camel case string to an uppercase snake case string, adds CHILD_ prefix to string if property is an object* @parameter {string} key - camel case string* @parameter {any} property - object value associated to the key*/const toSqlName = (key, property) => { let sqlKey = key .replace(/(?:^|\.?)([A-Z,0-9])/g, y => { return "_" + y.toLowerCase(); }) .replace(/^_/, "") .toUpperCase(); return typeof property == "object" ? "CHILD_" + sqlKey : sqlKey;}We construct the SQL statement using the table name, the column names and data types and the bespoke primary key (if it was provided to the created table method).
/*** Builds SQL statement and performs query to create table if it does not exist * @parameter {string} tableName - string defining table name* @parameter {string} columns - string with all column names and datatypes* @parameter {string} primaryKey - bespoke primary key*/const create = async (tableName, columns, primaryKey) => { const query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + columns + primaryKey + ")"; // perform SQL query await this.db.executeSql(query, []); }Alter Table
If a table exists, but there are properties in the object which are not in the table that we want to insert – we need to add these columns to the table.
This allows us to adapt database tables to the data. Adding columns also lets us keep data currently in the table, and more easily adapt to changing requirements.
/*** Checks table to see if column names are the same as those in the object, if there are properties in the object and not in the table - these columns are added to the table * @parameter {string} tableName - string defining table name* @parameter {Object} object - data object used to interact with database */const alterTable = async (tableName, object) => { const columnNames = await getColumnsNames(tableName); if (columnNames.length > 0) { await addColumns(tableName, columnNames, object); }} /*** Returns an array of column names for a specific table * @parameter {string} tableName - string defining table name*/const getColumnsNames = async (tableName) => { const query = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name = " + "'" + tableName + "'"; const coloumnPartsDb = await this.db.executeSql(query, []); const columnParts = coloumnPartsDb.rows.item(0) .sql.replace(/^[^\(]+\(([^\)]+)\)/g, "$1") .split(","); // RegEx const columnNames = []; columnParts.forEach(columnPart => { if (typeof columnPart === "string") { const columnNameParts = columnPart.split(" "); columnNames.push( columnNameParts[0] ? columnNameParts[0] : columnNameParts[1] ); } }); // remove primary reference key from column names const index = columnNames.indexOf("PRIMARY"); return index > -1 ? columnNames.slice(0, index) : columnNames;}To add columns, we first query the existing table to see which columns exist if the object we wish to save has properties not contained in the table.
We want to know which columns we need to add and write functionality, to add these columns to the desired table.
/*** Adds columns to a specified table in the database * @parameter {string} tableName - string defining table name* @parameter {Array<string>} columns - array of column names to be added to table* @parameter {Object} object - data object used to interact with database */const addColumns = async (tableName, columns, object) => { const columnsToAdd = columnsToAdd(columns, object); columnsToAdd.forEach(async column => { const columnToAdd = new Object(); columnToAdd[key] = object[key]; const query = "ALTER TABLE " + tableName + " ADD COLUMN " + getColumns(columnToAdd, true); // perform SQL query await this.db.executeSql(query, []); });}/*** Returns array of column names - properties in object which do not exist in array of table columns * @parameter {Array<string>} columns - array of column names to be added to table* @parameter {Object} object - data object used to interact with database */const columnsToAdd = (columns, object) => { const tableColumns = columns.map(x => { return x.indexOf("CHILD_") ? toCamelCase(x.substring(5)) : toCamelCase(x); }); return Object.keys(object).filter(x => !tableColumns.some(y => y == x));}/*** Converts uppercase snake case string to camel case* @parameter {string} key - uppercase snake case string */const toCamelCase(key) { return key .toLowerCase() .replace(/([_][a-z,0-9])/g, group => group.toUpperCase().replace("_", "") );}Insert Data
After making it possible to create tables and update their column names based on the structure of a JavaScript object, we need to dynamically add data into the database tables.
/*** Inserts data into database* @parameter {string} tableName - string defining table name* @parameter {Object} data - data object used to interact with database */const insertData = async (tableName, data) => { await verifyTable(tableName, data); const columns = getColumns(data); const values = getValues(data); const query = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + values + ")"; // perform SQL query await this.db.executeSql(query, []); // recursively insert data Object.keys(data).forEach(async key => { if (typeof object[key] == "object") { // create database table with nested object await insertData(toSqlName(key), object[key]); } });}/*** Checks to see if database table exists and adds columns to table if they do not currently exist* @parameter {string} tableName - string defining table name* @parameter {Object} data - data object used to interact with database */const verifyTable = async (tableName, data) => { const columnNames = await getColumnsNames(tableName); if (!columnNames.length) { await createTable(tableName, data); } else { await addColumns(tableName, columnNames, data); }}When inserting data, we first verify that the database table exists, build it if it doesn’t, or add columns to the table if there are properties in the object that don’t exist in the table.
We then recursively embed the data into a specific table for each nested object in the base object (initially passed as an argument to the insert data method).
Next, we need to determine which values should be included in which table.
/*** Returns string of object values to be inserted into database, if value is an object an id is added to create table relationship* @parameter {Object} data - data object used to interact with database */const getValues = (object) => { let values = ""; Object.keys(object).forEach(key => { let value = ""; switch(typeof object[key]) { case "string": value = object[key].replace(/"/g, '""').replace(/\''/g, "'"); break; case "object": object[key].id = object[key].id ? object[key].id : guid() value = object[key].id; break; default: value = object[key]; break; } values += '"' + value + '", '; }); return values.slice(0, -2);}We need to perform some string formatting to ensure that the SQL query is formatted correctly.
If the property that we want to enter is an object, the data in this object is added to a different table with the same name as the property in the parent object.
To provide a relationship to the parent table, we add an id parameter to the object we wish to include. This id is embedded in the parent’s column (CHILD_ + database_table_name). The id is created using a GUID library.
Read Data
Once we are able to insert data into our database dynamically, we also want the ability to read data dynamically from our database tables.
/*** Returns an array of JavaScript objects from a parent table and it's children, an object (filterObject) of the structure {objectProperty1: value1, objectProperty2: value2, ...} can be passed as an argugment to filter the search* @parameter {string} tableName - string defining table name* @parameter {Object} filterObject? - optional, data object used filter query*/const readTable = async (tableName, filterObject) => { const query = "SELECT * FROM " + tableName + constraintQuery(filterObject); const data = await this.db.executeSql(query, []); if (data.length) { data.forEach(async dataElement => { Object.keys(dataElement).forEach(async key => { if (key.indexOf("CHILD_") > -1) { key = key.substring(5); dataElement[key] = (await readTable(key, {id: dataElement[key]}))[0]; } key = toCamelCase(key); }); }); return data; } else { return []; }}We iteratively read from the database tables. Columns with the CHILD_ prefix refer to child tables in the database.
These tables are queried by the id stored in the parent table’s associated column (CHILD_ + database_table_name).
We build the constraint part of the read query using the filter object passed as an argument to the read table method.
/*** Returns constraint query string used to query specific rows from a table* @parameter {Object} filterObject? - optional, data object used filter query*/const constraintQuery(filterObject) { let query = ""; if (filterObject) { Object.keys(filterObject).forEach(key => { const value = typeof filterObject[key] == "string" ? filterObject[key].replace(/"/g, '""').replace(/\''/g, "'") : filterObject[key]; query = query + toSqlName(key) + " = " + '"' + value + '" AND '; }); query = Object.keys(filterObject).length ? " WHERE " + query.slice(0, -5) : query; } return query;}The keys of the filter object specify the columns which require a constraint, and the values of the object specify the value that should be searched for in the query.
Delete Data
Now that it is possible to read dynamically to and from our database tables, we also need the ability to delete data dynamically from our tables.
/*** Deletes data from a table, an object (filterObject) of the structure {objectProperty1: value1, objectProperty2: value2, ...} can be passed as an argugment to determine which rows are deleted* @parameter {string} tableName - string defining table name* @parameter {Object} filterObject? - optional, data object used filter query*/const deleteData = async (tableName, filterObject) => { const columnNames = await getColumnsNames(tableName); const childrenTablesExist = columnNames.some(x => x.indexOf("CHILD_")); const query = "DELETE FROM " + tableName + constraintQuery(filterObject); await this.db.executeSql(query, []); //recursively delete data from tables if (childrenTablesExist) { const data = readTable(tableName, filterObject); data.forEach(async dataElement => { Object.keys(dataElement).forEach(async key => { if (typeof dataElement[key] == "object") { await deleteData(toSqlName(key), {id: dataElement[key].id}); } }); }); }}We repeatedly remove data from the database tables. We determine if data needs to be deleted from child tables by querying the columns of the parent table. Column names with the CHILD_ prefix specify which tables should have rows deleted from them.
If a table has child tables with data that should be eliminated, we read the tables to determine the id of the rows in the child table which should be deleted.
We construct an object with the ID of the row we wish to exclude and pass this to the deleted data method. This allows us to delete the data in the child table with the specified ID.
Concluding Remarks
We created an SQL library that interfaces with existing SQL database connection libraries – which allows us to dynamically construct and adapt tables based on the structure of the data we save.
Data gets saved in the form that is sent to the database and rebuilt in the same way. This might really simplify the need for building models and methods to handle the data being saved and read from our database.
