An intro to SQL databases for building web apps.
Start by following the workshop notes in workshop/README.md. It'll guide you through relational database concepts (creating tables, inserting data, querying, one-to-many and many-to-many relationships, JOINs) and then explain how to integrate a SQL database into your Node app (migrations, query builders, and API routes).
This repo also contains a Node/Express app with PostgreSQL set up. Here's what the files do:
router.js - Where API endpoints are defined. Most of the interesting logic is here.
app.js - Standard Express app setup
docker-compose.yml - Docker file which specifies how to run Node and Postgres
package.json - Contains scripts to build/run app and manage DB migrations
db/knexfile.js - Knex database configuration
db/migrations/ - Folder containing database migrations (e.g. CREATE TABLE statements)
db/seeds/ - Folder containing seed data
Install docker: https://www.docker.com/products/docker-desktop
yarn install
yarn run build
Start the docker app in a terminal window. This will run the Postgres database and the Node app at localhost:3001.
yarn start
In a separate terminal window, set up the database:
yarn run db:setup
To get a psql shell, first make sure yarn start is running in a separate terminal. Then:
yarn run db:shell
To generate a new migration with Knex, run:
yarn run db:migrate:make YOUR_MIGRATION_NAME
To execute your migrations, first make sure yarn start is running in a separate terminal. Then:
yarn run db:migrate