Skip to content

Learning MySQL With ExpressJS (Part 0)

Introduction

At work I spend a fair amount of time developing wordpress applications: plugins, themes, multisite installations, etc... One of the great things about wordpress is that it takes a lot of the guesswork out of interacting with mysql and making queries. For instance if you eed a title for a blog post, you can use the the_title() function.

The consequence of this is that you can end up learning very little about how SQL queries work. It's pretty easy to not know what's going on in the wordpress database at all! So I started a project that I hoped would teach me a few things.

My goals included to learn more about:

  • SQL
  • wordpress database interactions
  • javascript especially async/await

What I ended up choosing to do was build an express application that would model for me the kinds of interactions I saw when I performed actions in wordpress.

In general the way I've been doing this is to have this application and a basic wordpress application running side-by-side. Then, I'd try something like creating a new post. By observing the changes to the wordpress database, I could then try to mimic them in my own application.

Setup

This is meant to be a broad overview of the project. I'll get into details in later posts. For now, here's the file structure of the project as it currently stands.

/crud-experiment
├── adminRoutes
│   ├── postsRoute.js
│   └── routes.js
├── apiRoutes
│   ├── imagesRoute.js
│   ├── postsRoute.js
│   ├── routes.js
│   └── usersRoute.js
├── controllers
│   ├── imagesController.js
│   ├── postsController.js
│   └── usersController.js
├── entrypoint
│   ├── entrypoint.sh
│   └── wait-for-it.sh
├── public
│   ├── css
│   │   ├── admin.css
│   │   └── style.css
│   └── js
│       ├── admin.js
│       └── index.js
├── queries
│   ├── README.md
│   ├── check-auto-increment.md
│   ├── count-distinct-users.md
│   ├── delete-from-row.md
│   ├── get-thumbnail-from-post-id.md
│   ├── get-user-capabilities-by-ID.md
│   ├── get-user-info-by-metavalue.md
│   ├── inner-join-example.md
│   ├── insert-into-row.md
│   ├── timezone-offset.md
│   ├── update-post.md
│   └── update-row.md
├── utils
│   ├── helpers.js
│   ├── pool.js
│   └── upload.js
├── views
│   ├── admin
│   │   ├── posts
│   │   │   └── single.hbs
│   │   ├── home.hbs
│   │   └── posts.hbs
│   ├── layouts
│   │   └── main.hbs
│   ├── partials
│   │   ├── footer.hbs
│   │   ├── head.hbs
│   │   ├── header.hbs
│   │   └── scripts.hbs
│   ├── home.hbs
│   ├── posts.hbs
│   └── single.hbs
├── docker-compose.yml
├── package-lock.json
├── package.json
└── server.js

Docker

This application runs from a docker-compose.yml file. It describes two containers (a server and a database) which looks like this:

version: '3'
services:
  server:
    image: uconn/express:1.1.0
    ports:
      - "3000:3000"
    volumes:
      - ./entrypoint/entrypoint.sh:/entrypoint.sh
      - ./:/project
    entrypoint: ["/entrypoint.sh"]
  mysqldb:
    image: mysql:5.7
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: mysql
      MYSQL_PASSWORD: mysql
      MYSQL_USER: mysql
      MYSQL_DATABASE: mysql

The uconn/express image is a custom docker image I wrote to allow for quick local development of express applications. For now, the important thing to know is that it includes wait-for-it.sh which is critical for getting the interaction between server and database right.

In order not to begin with a blank database, I exported a model wordpress database from a different project. Again the goal here is to learn about SQL queries and not to conceive the entire database model from scratch.

Express

The server application in server.js is handled by express. It handles things like

  • environment variables
  • routing
  • templating
  • and a little sanitization for any inputs (although this will probably be broken out later)

Because this is a largely experimental project, I've started modeling routes for

  • the public facing site
  • the admin area
  • an API

I did this because I was curious and wanted to explore the different perspectives of each.

Routes and Controllers

Routes pass requests off to controller files. So a request to /admin will make a request to getPosts function exported by the posts controller. Similarly /admin/:id will get a single post by its ID in the database. Keeping with how wordpress handles things, a request to /admin/new will create a new post automatically.

Queries

The queries directory is a repository of notes with queries that I've tried. Each file explains what I was trying to do based on what I observed from the wordpress database. Then it shows the query I used to mimic that effect. The goal for now isn't exact duplication. Rather it's trying to understand how things might be done so I can understand them better for the future.

Conclusion

My hope is that by working on this project, I'll gain a better appreciation for the technologies and techniques involved in running a wordpress site. I've already learned quite a lot about SQL and it's been a fun experiment to work on.