Exclude tables when restoring a PostgreSQL Database dump

Exclude tables when restoring a PostgreSQL Database dump
When time is critical, exclude large files from your Postgres restore and save the world at the same time. Imagine you’re a super secret spy. You’ve infiltrated the enemy’s datacenter and you need to dump their database to save the world from being destroyed by the evil villain’s satellite death rays. While fighting off ninjas, robot assassins, and a very fat Scottish overlord, you manage to dump the entire database to a secure thumbdrive.

The Wonderful and Dangerous to_json from Postgres

PostgreSQL is an amazing RDBMS. Not only is has it been ACID compliant since 2001, it supports many data types and functions, putting it on par with Document Store systems like MongoDB. Postgres supports a wide range of JSON formats and methods to store, search, and transform JSON object data. For instance, the to_json and related function json_agg allow you to collect a row into a JSON object. Handy for when you have nested data and want PG to return an object with nested values.

Cool Tips When Using Knex with Postgres

Cool Tips When Using Knex with Postgres
I’ve been using Knex for a long time, closing in on two years. Just to clarify, Knex is NOT an ORM (Object Relation Model). ORM’s try to model your database into neat and logic classes, but I agree with Laurie Voss, and ORM is an anti-pattern. I’ve used Sequelize, Doctrine ORM, and others. Every. Single. Time. ORM’s will let you down and keep you from leveraging your database to its fullest potential.

Gitlab Docker in Docker with Postgres

Gitlab Docker in Docker with Postgres

I’m embarrassed this took me longer than expected, but here is a .gitlab-ci.yml that will fire up Docker in Docker (so you can can build and run containers) and run Postgres to use in your contract tests.

Apps I use and may be useful to you too

Apps I use and may be useful to you too
A run down on the apps I use regularly and why. Caution, Mac specific drivel ahead. Vim Do I even need to explain? It’s pretty common place for proud Vim users to show off their .vimrc, but I’ve come to find copy/paste from someone else’s .vimrc is not really a good idea. A developer’s .vimrc is a personal matter, crafted over time with love and affection, it suits them and their needs.

Stupid Postgres Tricks

I was curious, how many day’s old am I? I suppose I could solve this in many different programming languages, but thought “PG has really good date managment features, let me try that”. So here it is.

Delete All Tables in a Postgres database

This doesn’t happen every day, but once in a while you may find yourself needing to remove all the tables/functions/views from a PostgreSQL database. The key is you don’t want to delete the database itself. Maybe you are on a hosted solution where you don’t really have access to drop/restore a database. For example, I’m using Heroku’s Postgres service and dropping the database is not an option. Here is a simple command that will remove all the tables and restore to new.

Database Field Naming Notes

Let’s look at a few ways we can name our database fields. This is mostly subjective, with some personal opinion mixed in for added velocity. Naming tables To me, this is straight forward. All table names should be lower snake_case e.g. account_names or account_email_options. Table names should be plural e.g. accounts, people, emails, settings, etc. This slightly breaks down for words where the plural is the singular, for example series.

PostgreSQL Performance Notes

Databases are magic from my perspective. They take in queries, find all the information you tasked it to find, and POOF. In the blink of an eye, there it is, all packaged up nice and neat. Or it blows up in your face, but that never happens, right? Let’s take a look at some steps we can take when things are not not returned in the blink of and eye.

Postgres Database Design Notes

I love projects where I’m learning something new, don’t you? When I get in a project, I want to be learning new things on a regular basis, otherwise I grow restless. Lately, I’ve been on a pretty big project for a client designing a backend system (GraphQL + Node.js + Postgres). I can’t go into too much detail, but we’ve leveraged Postgres as our main data store and in the process we’ve had to level up on our Postgres skills.