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 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:
- wordpress database interactions
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.
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
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
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.
The server application in
server.js is handled by express. It handles things like
- environment variables
- 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.
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.
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.