Building An Express App - Creating A Database In Express

November 25 2024
2 min. read

One of the aspects I seldom consider when building demos and applications is how to build the database that will power the CRUD application.

For this post we'll use Postgresql 17 installed via Homebrew. Just like with Node, we append the version specifying it using @ and the version that we want to install. This is important because the Homebrew default version is not the latest.

brew install postgresql@17

When starting the database we have two options. We can start it manually every time we want to use it with this command:

The LC_ALL variable control collation settings for the database.

You specify the location of the database cluster using the -D option.

LC_ALL="C" /usr/local/opt/postgresql@17/bin/postgres \
	-D /usr/local/var/postgresql@17

If you'd like to start the database on system startup use Homebrew services command.

brew services start postgresql@17

Once you start the database using either method, we need to log in to the database using the psql command to connect to the postgres database.

psql postgres

Once we login with the default superuser account, we need to create a new account to ensure security. We don't want a superuser account for everyday use.

Instead of user or account we use create role with a login password expressed as a string in single quotation marks.

CREATE ROLE me WITH LOGIN PASSWORD 'password';

Then we use alter role statement to enable our new account to create databases and tables.

ALTER ROLE me CREATEDB;

We then quit Postgresql.

\q

Connecting table to Postgresql with another account is slightly different. We specify the database with the -d parameter and the user with -U.

psql -d postgres -U me

Once we are logged in as me, we create the database.

CREATE DATABASE api;

Next step, we connect to the database we just created

\c api

We can then build the database structure by creating tables with the appropriate structure using the create table statement.

CREATE TABLE users (
  ID SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(30)
);

We can insert data into the tables using the insert statement.

INSERT INTO users (name, email)
  VALUES ('Jerry', 'jerry@example.com'), ('George', 'george@example.com');

We can check if the results match our expectations using the select statement. In this case we retrieve all rows from the users table.

SELECT * FROM users;

The post just glosses over table related commands and tasks. That's on purpose. A future post will address this in more detail when discussing a proposed application using Express and Postgresql together.

Edit on Github