quick-ex-db-monorepo

quick-ex-db

A CLI tool to quickly scaffold an Express.js app with PostgreSQL and Jest.

Installation

To install this package globally, run:

npm install -g quick-ex-db

Usage

After installation, you can create a new Express app by running:

npx quick-ex-db <project-name>

Configure ENV files for development and testing:

• .env.test

PGDATABASE=your_test_db_name

• .env.development

PGDATABASE=your_db_name

Write Your First Test

Create the first endpoint test in app.test.js:

const request = require("supertest");
const app = require("../app");

describe("GET /endpoint", () => {
  it("should respond with 200 status", async () => {
    const response = await request(app).get("/endpoint");
    expect(response.statusCode).toBe(200);
  });
});

Run the test:

npm test

Creating Routes, Controllers, and Models

• Routes: Define your endpoints in the routes folder, for example:

const express = require("express");
const router = express.Router();
const { getExample } = require("../controllers/example-controller.js");

router.get("/example", getExample);

module.exports = router;

• Controllers: Write the logic needed for the endpoint in the controllers folder, for example:

const { selectExample } = require("../models/example-model.js");

exports.getExample = (req, res, next) => {
  selectExample()
    .then((example) => {
      res.status(200).send({ example });
    })
    .catch((err) => {
      next(err);
    });
};

• Models: Now, add the logic needed for the controller to work in the models folder, for example:

const db = require("../../db/connection.js");

exports.selectExample = () => {
  return db.query("SELECT * FROM example_table").then(({ rows }) => {
    return rows;
  });
};

Setting up your database

• Ensure PostgreSQL is installed and setup on your system.
• Create a new database:

createdb <database-name>

Creating a Supabase Account

• Create a Supabase account (either by email or connecting to GitHub), after signing in create a new project from your dashboard, if prompted to create a new organisation, give it a name of your liking but make sure you choose a personal organisation and select the free pricing option (unless you want to upgrade).
• Give your project a name and create a password (using alphanumeric characters only, e.g. a-z, 0-9) for your database, save this somewhere safe for the next steps as it can't be retrieved again, but it can be reset.
• Select any region you like, though, the closer to you the better.
• Create your project. If you need to change any settings you can navigate to your project settings in the project dashboard.
• Return home using the navigation menu and wait a moment for your project to finish initialising.
• Click connect and copy the URI connection string - "postgres://...", or keep that tab open as you complete the next step.

Setting Up Production ENV File

Carrying on from before, with the database password and URI both handy:

• In the connection URI string, replace [YOUR-PASSWORD] with your alphanumeric database password you created earlier, removing the square brackets also.
• Add this URI to env.production to the DATABASE_URL variable

DATABASE_URL=postgresql://postgres:[YOUR-PASSWORD]@<host>:<port>/postgres

Hosting your API on Render

• Sign up to Render, once signed in create a new Web Service using the New + button at the top right.
• Either allow Render to access your GitHub repositories or paste in the URL for it, making sure it's set as public.
• Give the app a name, leaving most of the settings as default.
• Change the default build command to yarn and the default start command to yarn start.
• Scroll to near the bottom, where you can find the Environment Variables section:

• Create a Key called DATABASE_URL using the URI for Value from your .env.production file
• Create another Key called NODE_ENV, set the value to production

• Create the service and then wait a few moments while it completes, you can view the Logs for it by going to the Events on the dashboard

Check your API

When it's deployed, you can view it via the generated link, upon navigating there you will be greeted with an error, make sure you are pointing to an existing endpoint, such as /api and confirm your data is being fetched correctly.

• Downloading a JSON formatter can be useful for viewing the data, you can use the JSON Formatter from the Chrome store

Contribution

I welcome contributions to improve this tool! Here’s how you can help:

Reporting Issues

If you find a bug or have a feature request, please create an issue on the GitHub repository.

Making Changes

• Fork this repository.
• Clone your forked repository:

git clone https://github.com/<your-username>/quick-ex-db.git

• Create a new branch for your feature or bugfix:

git checkout -b my-new-feature

• Make your changes and commit them:

git add .
git commit -m "Add my new feature"

• Push your branch to your forked repository:

git push origin my-new-feature

Submitting Pull Requests

• Go to the original repository and click "New Pull Request."
• Describe your changes clearly and submit your PR.