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”