Postgres Arrays and PHP -Part 1

The other day I found myself designing a database schema for a collection of musicians each of which plays one or more instruments. The result looked something like this.

CREATE TABLE musicians
    id bigserial NOT NULL,
    name text NOT NULL,
    PRIMARY KEY (id)

CREATE TYPE musical_instrument AS ENUM
    ('guitar', 'piano', 'bass', 'trumpet', 'drums');

CREATE TABLE musician_instruments
    musician_id bigint NOT NULL,
    instrument musical_instrument NOT NULL,
    "position" integer NOT NULL,
    PRIMARY KEY (musician_id, instrument)

As you can see the table musician_instruments is used to associate musical instruments to a musician. It has an additional column called position because the order of instruments is relevant for the application.

When I squinted real hard I saw that I had in front of me a simple array disguised table. So I thought, why not use the array type that Postgres provides and see where it leads me. A research project if you might.

ALTER TABLE public.musicians
    ADD COLUMN instruments musical_instrument[] NOT NULL;
Continue reading “Postgres Arrays and PHP -Part 1”

Add columns with custom types in Laravel Migrations

I was certainly surprised when I found out that there is no built-in method to create table columns with custom types in Laravel migrations. The default list of column types available in Laravel may be more than enough for most applications, but, I think there are some valid use cases for opening the hood and directly working on the engine.

The scenario

Many DBMS offer non-standard column types that you might want to use to unlock some untapped potential. For me this was adding support for PostgreSQL’s built in enumerated types (enums) and arrays. Now, I am not claiming that using these particular types is generally a good idea (especially arrays). As always, you need to evaluate the upsides and downsides for your specific application.

Continue reading “Add columns with custom types in Laravel Migrations”