postgreSQL Archives

PostgreSQL

How to Partition Consecutive Rows with PostgreSQL

You can use PostgreSQL's window function capabilities to partition data over a specific number of consecutive rows preceding any given row.

Developer Tools

Migrate Your Heroku PostgreSQL Database to AWS RDS

Over time, Heroku add-ons may not fit your needs. I’ll share how I was able to successfully complete a migration from Heroku PostgreSQL to RDS.

PostgreSQL

A Simple Search Engine in Postgres

I had a lot of fun researching and experimenting with Postgres full-text search capabilities. Follow along as I build a simple keyword search with Postgres.

Developer Tools

Ingest Inconsistent Tables Quickly Using Postgres’s JSON Columns

Here’s how you can leverage JSON columns to help smooth out the distinctions between tables you are ingesting into a PostgreSQL database.

Prisma

A Simple REST API with Prisma, Part 1: The Setup

Let's create a simple REST API with Prisma. Part one of this two-part series will cover how to get a Prisma schema from your Postgresql database.

Platforms & Languages

Solving the Gaps and Islands Problem in Postgres

I hope this is a straightforward way for you to understand the Gaps and Islands problem so you can apply it in your next project.

Extracurricular Activities

Self-Hosted PostgreSQL with SSL in Docker

Here I share all the configuration details needed to self-host PostgreSQL running in a Docker container, with a valid SSL certificate from Let's Encrypt.

Development

PostgreSQL Isolation Levels: How much money is actually in Piper’s bank account?

PostgreSQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable Isolation) illustrated with banking transactions.

Web Apps

Do You Really Need Redis? How to Get Away with Just PostgreSQL

It's a common architecture to have PostgreSQL and Redis serve as the backbone of your app, but PostgreSQL may be capable of everything you use Redis for.

Developer Tools

Setting up GitHub Actions for a React/Node Project

How to set up GitHub Actions for a React, Node, Postgres, Yarn, and Typescript project running on Heroku. And why I won't be going back to CircleCI.

TypeScript

Helper Functions for Testing a PostgreSQL Database

Writing tests for a node.js server? You should test the database too. Here are a few helper functions for testing a postgres database, using knex.

TypeScript

BLOBs with Knex and PostgreSQL

An example of how to use the pg-large-object package with Knex to allow for streaming Large Objects from a PostgreSQL database in a Node.js program.