Flipping keys/values in SQL JSON

Bryce — Thu, 13 Jul 2023 03:41:46 +0000

Have you ever come across SQL JSON that was injected in the improper order? Something along the lines of: {"0": "numDogs", "3": "numCats"} when you wanted {"numDogs": "0", "numCats": "3"}? Not only is it hard to make sense of your data (How would you sum up all the total numDogs across all the rows?) it is critically dangerous for the collision of keys. If you have a couple of rows you could manually UPDATE/replace the individual fields but this is still tricky if you have more then a couple rows.

I was throwing around this problem in my head and, utilizing some temp tables and the JSON cmds introduced in 5.7.8, came up with a series of SQL queries that could be a solution. They can help make the JSON's key:value => value:key (or in other words your new key:value). Note: this only works if your values are scalar (not array/object/etc because those arent valid keys).

#If you want to run multiple times you have to uncomment these to drop tables. Otherwise the tables are dropped when session ends
#DROP TEMPORARY TABLE tab;
#DROP TEMPORARY TABLE tmp1;

#Create a table to separate the keys and values
#REPLACE "mytable" and "json_data" and "id" with your table/column/primary key names
CREATE TEMPORARY TABLE tab
SELECT id, mykeys, myvalues
FROM (
SELECT *,
CASE WHEN JSON_VALID(json_data) THEN JSON_KEYS(json_data) ELSE null END AS mykeys,
CASE WHEN JSON_VALID(json_data) THEN JSON_EXTRACT(JSON_UNQUOTE(json_data), "$.*") ELSE null END AS myvalues
FROM mytable
WHERE json_data!="" and json_data!="null" and json_data is not null
) mytable;

#Create and fill a table that we are just going to use as index values
#Might be a clever way to add a sequence of values to a table but this is the way I did it...
#Just verify you insert more then the number of keys in the json you are "fixing". IE my example had 2 for numDogs and numCats
create temporary table tmp1 (id tinyint unsigned not null primary key);  
insert into tmp1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30);  

#REPLACE "mytable" and "id" with your table/primary key designations
UPDATE mytable jTable,
  (
select myresultstable.id, JSON_OBJECTAGG(col2,col1) as JSONresult from
(
select
tab.id,
JSON_UNQUOTE(json_extract(tab.mykeys, concat('$[', tmp1.id, ']'))) as col1,
JSON_UNQUOTE(json_extract(tab.myvalues, concat('$[', tmp1.id, ']'))) as col2
FROM tmp1
join tab
) myresultstable
WHERE myresultstable.col1 is not null and myresultstable.col2 is not null
group by myresultstable.id
  ) t2
SET jTable.json_data=JSONresult
WHERE t2.id = jTable.id;

I only ask you test and verify with your own environment before running these test queries because I am not a DBA (clearly) and this will overwrite your data 🖖

*ERN Full Stack Creation with Bash

Bryce — Sat, 22 Feb 2020 23:42:59 +0000

Project structure is something every web developer has thought about at some point. Should I separate my front-end and back-end code into different repositories? If I dont, what is the perfect folder hierarchy that will benefit the way I develop and keep the two sides completely modular?

This was certainly a question that I thought about after my umpteenth web application project. Being primarily a front-end developer, I have always heard that separating the back-end into its own repository was the best thing to do. However, after spending just a minute or two on google, it appeared that this architecture is not always foolproof. Like everything regarding development, there are pros and cons to both approaches.

If you have a team of developers devoted to each side of the stack, then keeping the code apart obviously makes a lot of sense. The teams will be building code at different rates and debugging problem gets much simpler. If the back-end is getting used for multiple projects, that is another good reason to have it completely isolated.

However, this is not the case I have found most of the time. There are a lot of independent developers out there: student, hobbyist, or even freelancers. I think a lot of people in the community fall into this category. Besides, in all of my technical internships, production level code isn't even written with this clear level of division. A lot of developers help with both sides and have both code bases installed on their machines. This is a lot of unnecessary overhead managing multiple repositories for testing and deploying small projects.

So it got me thinking. How could I organize my own personal projects that could start as a single repo but also provide the best possible folder structure if I needed to separate it in the future? Then for more fun...could I simplify the project set up enough to automate the process?

Just a few points up front: This article covers creating a bare-ish bones full stack project in the *ERN stack (<pick a DB>, Express, React, and Node). It also covers managing the front-end/back-end in the same repository. The process uses the most popular OS shell, bash, to help automate the process (As a Windows developer, I highly recommend using Git Bash). A lot of the tools/techniques might be applicable to other web project management regardless of the tech stack, but if you just want to see the finished bash script I will add it at the bottom of the page.

Here were my two main objectives:

Keep the front-end and the back-end in the same repo, but have their dependency management completely isolated. I wanted the folder structure to look something like this:

<project name>
│   .git
│   package.json  
│   ...    
└───server
│   │   package.json
│   │   .gitignore
│   │   ...
│   └───client
│       │   package.json
│       │   .gitignore
│       │   ...

Automate the full stack project creation using a single command via bash

#1 Install the necessary CLI tools

Note: Like a lot of these steps, there is some personal preference. Feel free to pick and choose how you want the script to run. If you dont want to install a cli tool, you will just have to remove those lines from the script we will cover in the later steps.

Part of project will set up git version control. If you dont have it, you can install it here

We will use node and npm, so if you do not yet have these tools you can download them here

The only third-party cli tool I was forced pollute my global npm dependencies with was "json". Found on npm here. It helps edit us the .bashrc file quickly and easily without constantly using insane regex. Mac/Unix users potentially have native command alternatives but I was on a Windows and opted for this for simplicity. I would love to discuss a more supported alternative for this in the future.

#2 Create a bash function

You know those commands that you typically use in your terminal like: rm, cd, cp, tail, etc? Did you know you can actually create your own commands? The most common way of doing this is by putting aliases in your .bashrc file. This file checks for updates when you run your terminal. For example a simple alias like:

alias ll="ls -al"

creates an ll command that you can run from any directory and executes the commands in the parentheses. Be aware every time you edit the .bashrc you need to reboot the terminal or run:

source ~/.bashrc

to update the current terminal session settings.

But a single command doesn't help us very much for setting up a full stack project template. We need a batch of commands. Almost like a bash script file but more convenient. That's where bash functions stored in our .bashrc come in. Similar to most programming languages, you can create a single bash function that will run a series of statements when it is called.

We will create a bash function for automating our project creation and review how it works in chunks. First find your .bashrc file (usually in your home directory) and open it in your favorite text editor.

#3 Creating the boiler plate folders

Put this into your .bashrc:

#Params: <proj name>
newreact() {
   #Create front-end & back-end boilerplate
   mkdir "$1"
   cd "$1"
   npx create-react-app client
   npx express-generator server --no-view --git
}

Because we want this function to be versatile it only expects one command, the name of the project. The root folder is made with this name. We then use the incredibly helpful front-end and back-end tools offered by react and express for filling in the majority of the project files. npx is a nifty tool that will execute and fetch the latest versions of their code online. I will not get to far into dependency management here but I definitely recommend looking at the benefits of global, local, and npx package management (hint: mostly use npx for tools that offer a service).

Note: I make some assumptions with the text in these files later on. npx might be problematic for changes later on with these files. You can update/remove the sed statements from the script later or use the tool versions I currently have: create-react-app 3.4.0 and express-generator 4.16.1.

A helpful review of the included flags in this section are:

"--no-view": Removes view engine templates for express-generator(we want to use react for views)
"--git": express-generator includes a .gitignore file for the back-end

Front-end and back-end is implemented. Done right? Well it could be, but we can take this several steps further.

#4 Set up the server

Go ahead and include this code next. It customizes the details for the back-end folder.

#Params: <proj name>
newreact() {
   ...
   #Set up Server
   cd server && npm install
   json -I -f package.json -e "this.name=\"${1}-backend\""
   json -I -f package.json -e "this.version=\"0.1.0\""
   rm -rf public
   npm install -D nodemon
   echo -e "\npublic" >> .gitignore
   sed -i -E "s/(app\.use\(express\.static\(path\.join\(__dirname, 'public'\)\)\);)/\nif (process.env.NODE_ENV === 'production') {\n  app.use(express.static(path.join(__dirname, 'public')));\n\n  app.get('*', (req, res) => {\n    res.sendFile(path.join(__dirname\+'\/public\/index.html'));\n  });\n}/g" app.js
   rm routes/index.js
   sed -i -E "s/(var indexRouter = require\('\.\/routes\/index'\);)//g" app.js
   sed -i -E "s/(app\.use\('\/', indexRouter\);)//g" app.js
   sed -i -E "s/(app\.use\('\/users', usersRouter\);)/\/\/app.use('\/users', usersRouter);/g" app.js
   json -I -f package.json -e 'this.scripts.dev="npx nodemon"'
   sed -i 's/3000/5000/g' ./bin/www 
}

There is a lot going on here so I will try to walk through the worst parts.

It starts by going into the folder and installing the dependencies (since express-generator doesn't do this immediately).
Then it uses the global json dependency tool to help us modify our package.json file. It sets some properties like the name and the semver version.
Next, we remove the example public folder (this is injected from the front-end later).
The only module I chose to install every time is nodemon. This updates the project when node files change.
Ignore the public folder when it gets injected into the back-end folder
Then I modified the routes. I removed the index route and its associated file but left the user one as a quick reference. Personal preference.
I had to do a large text replacement with sed to change where the front-end files are served. I replaced:

app.use(express.static(path.join(__dirname, 'public')));

with this:

if (process.env.NODE_ENV === 'production') {
  app.use(express.static(path.join(__dirname, 'public')));

  app.get('*', (req, res) => {
    res.sendFile(path.join(__dirname+'/public/index.html'));
  });
}

A run script is added for development. We installed nodemon but npx will check your local project before finding the latest version. It will run slightly faster when it is installed and npx instead of npm future proofs execution for when it might be missing.
The last step we do is change the port express uses for deploying. Since react also uses 3000, we want to change one of these default ports.

A helpful review of the included flags in this section are:

"-I": In place editing for json. The file is saved with new changes
"-f": Path to file for json
"-e": Indicates JS expression in context of the object for json
"-e": Enable back escape characters for echo
"-E": Extended regular expressions for sed
"-i": In place editing for sed. The file is saved with new changes

#5 Set up the client

Next is the client customization.

#Params: <proj name>
newreact() {
   ...
   cd "../client"
   json -I -f package.json -e "this.name=\"${1}-frontend\""
   json -I -f package.json -e "this.version=\"0.1.0\""
   json -I -f package.json -e 'this.proxy="http://localhost:5000"'
   rm -rf ".git"
   cd ".."
}

This does a a couple of things we did in the back-end but has two differences. The first one is adding a proxy to the package.json. This points our API calls to the back-end only during our development. This helps remove development CORS issues. Deploying in a production setting will connect differently as it will be located in the same place. We will also remove the .git folder created by create-react-app since we want our version control at our root level.

#6 Set up files at project root

Here is a strong benefit for having all your code in a single place. The root level is where you can manage both sides of your application. You can create execution commands that are wrappers for the commands in the front and back-end. Only include dev-dependencies here and package.json scripts. The root is not supposed to be its own project, only a wrapper for the other two.

#Params: <proj name>
newreact() {
   ...
   #Add root level package.json for dev work/deployment
   npm init -y
   echo "node_modules" > .gitignore
   json -I -f package.json -e 'this.author="<add your name>"'
   json -I -f package.json -e "delete this.version"
   json -I -f package.json -e "delete this.main"
   json -I -f package.json -e "this.name=\"${1}\""
   json -I -f package.json -e 'this.main="./server/app.js"'
   #json tool has an issue with -, so for now I am sed-ing after this tool
   json -I -f package.json -e 'this.scripts.herokupostbuild="npm install --only=prod --prefix server && npm install --only-prod --prefix client && npm run build --prefix client && rm -rf server/public && cp -r client/build server/public"'
   json -I -f package.json -e 'this.scripts.clientinstall="npm run build --prefix client && rm -rf server/public && cp -r client/build server/public"'
   sed -i 's/herokupostbuild/heroku-postbuild/g' package.json
   json -I -f package.json -e 'this.scripts.client="npm start --prefix client"'
   json -I -f package.json -e 'this.scripts.server="npm start --prefix server"'
   json -I -f package.json -e 'this.scripts.start="npm start --prefix server"'
   npm install -D concurrently
   json -I -f package.json -e 'this.scripts.dev="concurrently --kill-others-on-fail \"cd server && npm run dev\" \"cd client && npm start\""'
   mv package.json temp
   echo "{\"engines\": {\"node\": \"$(node.exe -v | cut -c 2-)\"}}" >> temp
   cat temp | json --merge > package.json
   rm temp
}

First thing this does is create a root package.json for handling execution commands at the development stage. It adds a name, version, and removes the main file.
Wrapper scripts for both sides of the stack
- (opt) The first one is a heroku hook that runs before deployment. Installs all the dependencies and builds the react client and puts the files into the server
- The "clientinstall" script puts the client files into the server
- Several other scripts are implemented for starting up the front-end/back-end
- Then it installs concurrently as a dev-dependency. This is helpful for running the front-end and the back-end at the same time with the same command. The bash function also adds a command for using this tool
- Some deployment services need to have a node version, so the rest of the bash lines are just for adding that to the package.json. Windows users, make sure to include that "exe" suffix.

#7 Add Git for version control

#Params: <proj name>
newreact() {
   ...
   #Git init
   git init
   echo '* text=auto' > .gitattributes
   git add .
   git commit -q -m 'Full stack React.js template built'

   echo -e "\n\n=== Full stack application ${1} created ==="
}

This is your basic git set up. I change the new line conversions on check-in (again, Windows user) to help with cross platform development.

Now at the end of the script, I chose to output a nice little message to the terminal indicating completion.

A helpful review of the included flags in this section are:

"-q": Suppress feedback messages for git
"-m": Add commit message for git
"-e": Enable back escape characters for echo

#8 Heroku deployment (opt)

This section I have commented out since I dont include heroku with all my projects and besides, I think there is a maximum deployments limit. So using this part might effect your currently active project on the platform. However, if you have a deployment service like Heroku or Netlify this is a nice place to put the set up. You could always introduce another bash function parameter to indicate whether or not you want to push to a cloud platform.

Note: Heroku can not deploy part of a repository. A cleaner method would be to figure out how to deploy only the server with the client prepackaged into the public folder. That is why most heroku project layouts put the client folder inside the server folder and call it a day. This might be a detriment for using this pattern, although this will still run on heroku. Experiments with git submodules could possibly solve this issue.

#Params: <proj name>
newreact() {
   ...
   #Heroku
   #heroku login
   #heroku create "$1"
   #git push heroku master
}

Conclusion

You can now create and run a full stack project after you reboot your terminal with these commands:

newreact <project name>
npm run dev

So there you have it. A tool that can be utilized for quick full stack work. Is it the silver bullet for the perfect web application? Nah. You will find that this pattern is not even one found commonly in practice; either the stack is in another repository or has the front-end folder inside the back-end folder. I personally think this is poor future proofing and with a little bit of work, we can create our projects that can easily adapt. Who knows? With a little bit of git mastery, since the commits are in different folders, maybe even the git history can be maintained if the folders are put into separate places. Are you starting to see the vision? 😃

As a web developer, this is an ongoing project of mine, and I would love to hear the community's thoughts! What are some common layouts and patterns you use for your web applications?

Here is the complete bash function (make sure to edit it to personalize):

#Params: <proj name>
newreact() {
   #Create front-end & back-end boilerplate
   mkdir "$1"
   cd "$1"
   npx create-react-app client
   npx express-generator server --no-view --git

   #Set up Server
   cd server && npm install
   json -I -f package.json -e "this.name=\"${1}-backend\""
   json -I -f package.json -e "this.version=\"0.1.0\""
   rm -rf public
   npm install -D nodemon
   echo -e "\npublic" >> .gitignore
   sed -i -E "s/(app\.use\(express\.static\(path\.join\(__dirname, 'public'\)\)\);)/\nif (process.env.NODE_ENV === 'production') {\n  app.use(express.static(path.join(__dirname, 'public')));\n\n  app.get('*', (req, res) => {\n    res.sendFile(path.join(__dirname\+'\/public\/index.html'));\n  });\n}/g" app.js
   rm routes/index.js
   sed -i -E "s/(var indexRouter = require\('\.\/routes\/index'\);)//g" app.js
   sed -i -E "s/(app\.use\('\/', indexRouter\);)//g" app.js
   sed -i -E "s/(app\.use\('\/users', usersRouter\);)/\/\/app.use('\/users', usersRouter);/g" app.js
   json -I -f package.json -e 'this.scripts.dev="npx nodemon"'
   sed -i 's/3000/5000/g' ./bin/www 

   #Set up Client
   cd "../client"
   json -I -f package.json -e "this.name=\"${1}-frontend\""
   json -I -f package.json -e "this.version=\"0.1.0\""
   json -I -f package.json -e 'this.proxy="http://localhost:5000"'
   rm -rf ".git"
   cd ".."

   #Add root level package.json for dev work/deployment
   npm init -y
   echo "node_modules" > .gitignore
   json -I -f package.json -e 'this.author="Bryce Vonilten"'
   json -I -f package.json -e "delete this.version"
   json -I -f package.json -e "delete this.main"
   json -I -f package.json -e "this.name=\"${1}\""
   json -I -f package.json -e 'this.main="./server/app.js"'
   #json tool has an issue with -, so for now I am sed-ing after this tool
   json -I -f package.json -e 'this.scripts.herokupostbuild="npm install --only=prod --prefix server && npm install --only-prod --prefix client && npm run build --prefix client && rm -rf server/public && cp -r client/build server/public"'
   json -I -f package.json -e 'this.scripts.clientinstall="npm run build --prefix client && rm -rf server/public && cp -r client/build server/public"'
   sed -i 's/herokupostbuild/heroku-postbuild/g' package.json
   json -I -f package.json -e 'this.scripts.client="npm start --prefix client"'
   json -I -f package.json -e 'this.scripts.server="npm start --prefix server"'
   json -I -f package.json -e 'this.scripts.start="npm start --prefix server"'
   npm install -D concurrently
   json -I -f package.json -e 'this.scripts.dev="concurrently --kill-others-on-fail \"cd server && npm run dev\" \"cd client && npm start\""'
   mv package.json temp
   echo "{\"engines\": {\"node\": \"$(node.exe -v | cut -c 2-)\"}}" >> temp
   cat temp | json --merge > package.json
   rm temp

   #Git init
   git init
   echo '* text=auto' > .gitattributes
   git add .
   git commit -q -m 'Full stack React.js template built'

   #Heroku
   #heroku login
   #heroku create "$1"
   #git push heroku master

   echo -e "\n\n=== Full stack application ${1} created ==="
}

DEV Community: Bryce

Flipping keys/values in SQL JSON

*ERN Full Stack Creation with Bash

#1 Install the necessary CLI tools

#2 Create a bash function

#3 Creating the boiler plate folders

#4 Set up the server

#5 Set up the client

#6 Set up files at project root

#7 Add Git for version control

#8 Heroku deployment (opt)

Conclusion