######octothorp.es

#databases

  • Sqitch Update: The Plan

    https://justatheory.com/2012/05/sqitch-plan

    I gave my first presentation on Sqitch at PGCon last week. The slides are on Slideshare and the PGCon site. It came together at the last minute, naturally. I was not able to pay as close attention to PGCon sessions as I would have liked, as I was doing last minute hacking to get the deploy command working on PostgreSQL, and then writing the slides (which are based on the tutorial). I was pleased with the response, given that this is very much a project that is still under heavy development and available only as a very very early alpha. There was great discussion and feedback afterward, which I appreciate.

    Indexed 11/25/2025

  • Sqitch: Where to Define Dependencies?

    https://justatheory.com/2012/05/sqitch-dependencies

    Indexed 3/24/2026

  • Data Deployment with Sqitch

    https://justatheory.com/2013/08/data-deployment-with-sqitch

    Indexed 3/13/2026

  • Learn Mad Database Skillz at YAPC::NA 2009

    https://justatheory.com/2009/05/celko-at-yapc

    A few weeks ago, I twittered that, in my opinion, application developers should really learn how to use databases. And by that I mean SQL, really. I know that a lot of app developers like to use ORMs to access the database, so that you don’t have to really think about it, but most ORMs are written by people who don’t like databases, don’t like SQL, haven’t taken the time to learn it in any depth, and thus don’t write very good SQL. And even if they do like SQL, that usually means they provide a way for you to execute SQL queries directly. The reason? Because the ORM does not really understand how building more and more complex queries can have negative performance issues, and that there is more than one way to do it. It’s pretty common to have to go back to custom SQL to solve performance issues. So to solve those problems, you gotta understand SQL.

    Indexed 8/25/2025

  • SQL Change Management Sans Duplication

    https://justatheory.com/2012/01/sql-change-management-sans-redundancy

    Here’s how I propose to eliminate the duplication of code between deploy and revert SQL change management scripts.

    Indexed 3/12/2026

  • Sqitch: Back to the VCS

    https://justatheory.com/2012/06/sqitch-vcs-again

    Indexed 2/4/2026

  • The Ever Evolving Sqitch Plan

    https://justatheory.com/2012/05/evolving-sqitch-plan

    Indexed 4/1/2026

  • Some Thoughts on Database Change Management

    https://justatheory.com/2009/04/database-change-management

    Database migration frameworks and ad-hoc solutions leave things…wanting. Herein lie some preliminary thoughts on how to solve this problem.

    Indexed 3/15/2026

  • pgTAP Best Practices Slides Available

    https://justatheory.com/2009/11/pgtap-best-practices

    Last month I gave two presentations at the PostgreSQL Conference West. The first was a variation on Unit Test Your Database!, which I’ve now given about six times (and will at least two more times, including tomorrow night for Portland PLUG and in two weeks at the JPUG 10th Anniversary Conference). The second was a new talk, a 90-minute tutorial, called “pgTAP Best Practices.” And here it is (download and Slideshare). Enjoy!

    Indexed 10/7/2025

  • Introducing MyTAP

    https://justatheory.com/2010/07/introducing-mytap

    After some prodding from the MySQL Community Manager, some OSCON hacking yields tangible results.

    Indexed 1/30/2026

  • DBIx::Connector and Serializable Snapshot Isolation

    https://justatheory.com/2011/09/dbix-connector-and-ssi

    Indexed 4/8/2026

  • Sqitch — VCS-powered SQL Change Management

    https://justatheory.com/2012/04/sqitch-draft

    Back in January, I wrote three posts outlining some ideas I had about a straight-forward, sane way of managing SQL change management. The idea revolved around specifying scripts to deploy and revert in a plan file, and generating that plan file from VCS history. I still feel pretty good about the ideas there, and work has agreed to let me write it and open-source it. Here is the first step making it happen. I call it “Sqitch.”

    Indexed 3/12/2026

  • More on Database Testing

    https://justatheory.com/2009/05/more-on-database-testing

    More thoughts on they whys and hows of database testing, and wondering why one might think that it’s pointless or useless.

    Indexed 3/22/2026

  • Keep DBI's connect_cached From Horking Transactions

    https://justatheory.com/2009/09/dbi-connect-cached-hack

    I’ve been on a bit of a Perl hacking tear lately. In addition to knocking out Test::XPath last week, I’ve been experimenting with TAP::Harness sources, Template::Declare, Catalyst views, a new Module::Build subclass for building database-backed applications, and, last but not least, an IRC logging bot. Oh, and that application I’m working on for PGX with Quinn Weaver. So much is crowding my mind these days that I’m having trouble sleeping. Tonight I’m up late hacking to try to get some of this stuff out of my head.

    Indexed 2/25/2026

  • Sqitch v1.0.0

    https://justatheory.com/2019/06/sqitch-v1.0.0

    After seven years of development and production database deployments, I finally put in the effort to release v1.0.0.

    Indexed 6/16/2025

  • Simple SQL Change Management

    https://justatheory.com/2012/01/simple-sql-change-management

    I’ve been thinking a lot about SQL change management. I think I may finally have cracked this thing wide open.

    Indexed 4/5/2026

  • All RDMSes Suck

    https://justatheory.com/2003/05/all-rdmses-suck

    No, I’m not kidding.

    Indexed 9/27/2025

  • Sqitch: Now with SQLite Support

    https://justatheory.com/2013/04/sqitch-sqlite

    Indexed 4/8/2026

  • Sqitch Update: Almost Usable

    https://justatheory.com/2012/07/sqitch-almost-usable

    Indexed 4/9/2026

  • VCS-Enabled SQL Change Management

    https://justatheory.com/2012/01/vcs-sql-change-management

    Some thoughts on how to use VCS history to determine what changes need to be deployed or reverted without relying on a configuration file.

    Indexed 1/30/2026

  • Sqitch Update: All Your Rebase Are...Never Mind

    https://justatheory.com/2012/12/sqitch-rebase

    Indexed 4/3/2026

  • How to Generate Recurring Events in the Database

    https://justatheory.com/2008/01/postgres-recurring-events

    Going deep on using Postgres functions to create efficient recurring events.

    Indexed 4/15/2026

  • Sqitch v0.80: Now With More You

    https://justatheory.com/2012/08/sqitch-more-you

    Indexed 10/16/2025

  • Sqitch's Log

    https://justatheory.com/2012/07/sqitch-log

    Indexed 2/18/2026

  • Sqitch: Rename Step Objects and the SQL Directory?

    https://justatheory.com/2012/06/sqitch-steps-to-change

    After all of the thinking and rethinking about how to manage a Sqitch plan, I am just about done with all the changes to make it all work. One of the changes I’ve made is that tags are no longer objects that stand on their own between change steps, but are simply names the refer to specific change steps. Not only is this much more like how a VCS thinks of tags (basically another name for a single commit), but it also greatly simplifies the code for iterating over a plan and updating metadata in the database.

    Indexed 3/31/2026

  • Always Use TIMESTAMP WITH TIME ZONE

    https://justatheory.com/2012/04/postgres-use-timestamptz

    Indexed 4/15/2026

  • Why Test Databases?

    https://justatheory.com/2009/03/why-test-databases

    Indexed 8/28/2025

  • DBIx::Connector Updated

    https://justatheory.com/2009/10/dbix-connector-updated

    Indexed 4/2/2026

  • Notes on Upcoming Sqitch Improvements

    https://justatheory.com/2013/07/upcoming-sqitch-improvements

    Indexed 9/6/2025

  • My Catalyst Tutorial: Add Authors to the View

    https://justatheory.com/2009/11/sql-view-aggregate-magic

    Indexed 2/17/2026

  • What Advanced SQL Book Should I Buy?

    https://justatheory.com/2005/11/which-sql-book

    So, what advanced SQL book should I buy? I’ve learned a lot about SQL over the last year or so, but I’m sure that Josh Berkus is tired of being my own personal advanced SQL reference. So I’d like to really learn more about triggers, stored procedures, rules, views, and whatnot, what they’re best used for and when to use them. And other typical database features that I’m not familiar with, of course.

    Indexed 3/13/2026

  • Toward A Database URI Standard

    https://justatheory.com/2013/11/toward-a-database-uri-standard

    Indexed 4/8/2026

  • What Works and What Doesn't in Online Wine Applications

    https://justatheory.com/2006/06/on-wine-webapps

    Indexed 3/8/2026

  • Suggest Method Names for DBIx::Connector

    https://justatheory.com/2009/10/dbix-connector-methods

    Thanks to feedback from Tim Bunce and Peter Rabbitson in a DBIx::Class bug report, I’ve been reworking DBIx::Connector’s block-handling methods. Tim’s objection is that the the feature of do() and txn_do() that executes the code reference a second time in the event of a connection failure can be dangerous. That is, it can lead to action-at-a-distance bugs that are hard to find and fix. Tim suggested renaming the methods do_with_retry() and txn_do_with_retry() in order to make explicit what’s going on, and to have non-retry versions of the methods.

    Indexed 3/12/2026

  • DBIx::Connector Exception Handling Design

    https://justatheory.com/2011/05/dbix-connector-catch

    In response to a bug report, I removed the documentation suggesting that one use the catch function exported by Try::Tiny to specify an exception-handling function to the DBIx::Connector execution methods. When I wrote those docs, Try::Tiny’s catch method just returned the subroutine. It was later changed to return an object, and that didn’t work very well. It seemed a much better idea not to depend on an external function that could change its behavior when there is no direct dependency on Try::Tiny in DBIx::Connector. I removed that documentation in 0.43. So instead of this:

    Indexed 8/18/2025