Sql

It’s been a bit since I was typing SQL into a REPL. One interface that didn’t come back to me right away is the percent character in a like or ilike query: select full_name from users where full_name ilike 'jake%'; Returns: full_name ----------- Jake Gyllenhaal Jake Tapper Jake Worth (3 rows) This percent sign ensures we get all the Jakes. Here’s how I’m going to remember this next time. Like everything SQL, this an ISO/ANSI standard and there’s a long lost history. If you think like me, and you might wonder why this character is not an asterisk. Here’s my new mental model to reinforce the percent sign. ...

CRUD is an acronym that’s used a lot in web and mobile development. Frontend engineers in particular might wonder what it means and why we use it. In this post, I’ll explain CRUD using PostgreSQL, and talk about why it’s important. Unfamiliar with SQL or Postgres? No problem! These examples are pretty simple. What is CRUD? CRUD is an acronym that stands for ‘create, read, update, and delete.’ In persistence, these are four operations we perform on a record. According to Wikipedia: “[t]he term was likely first popularized by James Martin in his 1983 book Managing the Data-base environment.” Thanks, James! ...

I think a great database migration is: correct, atomic, reversible, consistent with the style of the application, and (mostly) uncoupled from your application and RDBMS. These ideas are framework-agnostic, but as an example, we’ll use this migration from the Ruby on Rails documentation: class CreateProducts < ActiveRecord::Migration def change create_table :products do |t| t.string :name t.text :description t.timestamps end end end Correct After running each migration, it’s important to inspect the database schema to ensure that the correct changes are present. I’ve seen many pull requests, and opened a few myself, where the migration and the schema tell competing stories. ...

Scenario: you need a database record that can belong to one record or another, but not both. Polymorphism and exclusivity. One approach is to create a polymorphic-style association at the data layer. By doing so, you’ll get data integrity built in, rather than trusting it will be enforced by each tenant at the application layer. Here’s how I’ve solved this problem in the past. The Database Migration This is a PostgreSQL example, but it could be applied to other databases. ...

RC files, thought to stand for “run command” files, are read by a program during startup. The file that configures the PostgreSQL REPL psql is called .psqlrc. In this post I’ll share my personal psql configuration. Note: many of these configurations came from my time at Hashrocket. We documented some these preferences via PG Casts. Here’s my .psqlrc. \set QUIET on \pset null 'ø' \x auto \timing \set HISTFILE ~/.psql_history-:DBNAME \set HISTCONTROL ignoredups \set QUIET off Let’s look at each setting. ...

Have you ever tried to read the documentation for a Postgres command? Although many consider Postgres’ docs best-in-class, they include conventions that might challenge newbies. In this post, I’ll paraphrase Postgres’ conventions page to help us understand what the documentation for a command is saying. To check the syntax of a command, I start with psql, the REPL, or ‘Read-Evaluate-Print-Loop’, of Postgres. I query using \help , and get output like this for update: ...

We introduced some breaking database changes to our Phoenix port of Today I Learned on Friday; today I deployed them to staging. Resetting an Ecto Heroku database, with new seed data, turned out to be a little tricky, and I wanted to document what I learned. This blog post assumes you already have the Heroku Toolbelt and an Ecto-supported application in production. First, make sure you deploy the breaking changes: $ git push <your_remote> master To drop a Heroku database, first we need the name of the database. DATABASE_URL is specific enough if there’s only one, but I think it’s a best practice to reference the specific database you’re attempting to destroy, every time. ...