While I’ve been tinkering around in PostgreSQL off late, one of the things I looked into a bit more thoroughly (other than index types) was data types. PostgreSQL offers a wide range of data types but it also allows creation of custom data types using one of its existing types – either built-in data type or a domain. It also allows us to create composite data types and set constraints on the newly created data types.
Types vs Domains
Something that one might come across is mention of user defined or custom types as well as domains. They are not exactly synonyms of each other, they are similar in some aspects but each serves a different purpose. So, what is the difference then?
Types are created using the CREATE TYPE SQL statement. A user-defined data type is a custom data type created to store data in a specific format or structure. User-defined types allow us to extend the existing data types in PostgreSQL to better match our application’s needs.
Domains are created using the CREATE DOMAIN SQL statement. A user-defined domain is a data type that is based on an existing data type, but with additional constraints or modifications. Domains allow us to create a new data type that is tailored to our specific needs, while still leveraging the functionality of the underlying data type.
There are some caveats here though. For example, we cannot create a domain directly based on enum. But we can create a custom data type using enum which specifies the acceptable values and then a domain can be created based on that custom data type.
Here’s what the excellent PostgreSQL documentation has to say about this:
A domain is a user-defined data type that is based on another underlying type. Optionally, it can have constraints that restrict its valid values to a subset of what the underlying type would allow. Otherwise it behaves like the underlying type — for example, any operator or function that can be applied to the underlying type will work on the domain type. The underlying type can be any built-in or user-defined base type, enum type, array type, composite type, range type, or another domain.
Why Bother with custom data types or domains?
One might wonder, what is the point? How would a custom data type or domain be of any use? Well, lets take a look:
- Data Integrity: Rules can be enforced at the database level itself.
- Performance: Complex operations can be faster when handled by the database.
- Expressiveness: Database schema becomes more self-explanatory.
- Reusability: Define once, use everywhere in your database.
Set up
Laravel has basic support for PostgreSQL but out of the box it has no support for the wider spectrum of features and abilities of PostgreSQL. What Laravel supports can work in this case as we can just use raw SQL to get by. Personally, I like to use tpetry/laravel-postgresql-enhanced package (created by Tobias Petry) for the projects where I use PostgreSQL. So the assumption now will be that we have this package installed in our Laravel app.
Creating types and domains
Let’s consider that our app is for a clothing store. And as such, there are 3 data points which have specific rules.
- Size: The sizes are always S, M, L, XL and 2XL. No other sizes are ever stocked.
- Availability: The product status can only be one of – “In Stock”, “Out of Stock”, “On Backorder” or “Discontinued”.
- Discount: Discounts are offered year round at different times on different products but the discount percentage can never be less than zero and can never be more than 55.
This means that size and availability are excellent candidates for custom data types while discount can be created as a domain as we need to place constraints on it.
Now that we have our criteria for the data types & domain, let’s define them.
We will create a new migration using the below command:
php artisan make:migration create_product_types_and_domains
This will create a new migration in our Laravel app and we will make it as below:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Query\Builder;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;
return new class extends Migration {
protected function getProductDiscountConstraint() : Closure
{
return function( Builder $query ) {
$query->where( [ [ 'VALUE', '>=', 0 ], [ 'VALUE', '<=', 55 ] ] );
};
}
/**
* Run the migrations.
*/
public function up() : void
{
$this->down();
DB::unprepared( "CREATE TYPE product_size AS ENUM( 'S', 'M', 'L', 'XL', '2XL' );" );
DB::unprepared( "CREATE TYPE product_status AS ENUM( 'in-stock', 'out-of-stock', 'backorder', 'discontinued' );" );
Schema::createDomain( 'product_discount', 'numeric(4, 2)', $this->getProductDiscountConstraint() );
}
/**
* Reverse the migrations.
*/
public function down() : void
{
DB::unprepared( 'DROP TYPE IF EXISTS product_size;' );
DB::unprepared( 'DROP TYPE IF EXISTS product_status;' );
Schema::dropDomainIfExists( 'product_discount' );
}
};
Here we are using Laravel’s query builder to directly run SQL to create our custom data types as at present the tpetry/laravel-postgresql-enhanced package does not support creation of custom data types. I’ve opened a Pull Request to add support for custom data type management. Hopefully we can get it merged soon and that will allow creation of custom data types using Schema::createType() and dropping them via Schema::dropType() and Schema::dropTypeIfExists().
However the package does support creation of domains and so we have used Schema::createDomain() to create our domain and specify its constraints.
We are calling the down() method in up() before anything else because Laravel does not always calls down() when doing a refresh. For example, if migrate:fresh command is run, then Laravel will drop all tables in the default database without actually calling down() on the migrations. If only tables are to be dropped then this will work but things like domains, functions, triggers, etc. will stick around. I wrote about this some time back.
Once added, we can use these data types & domain in any tables we create. Let’s say we are creating a table for t-shirts. We will run the below command to create our migration:
php artisan make:migration create_tshirts_table
And our migration would look like:
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;
return new class extends Migration {
/**
* Run the migrations.
*/
public function up() : void
{
Schema::create( 'tshirts', function( Blueprint $table ) {
$table->id();
$table->string( 'colour' )->default( 'white' );
$table->string( 'type' )->default( 'polo' );
$table->string( 'gender' )->default( 'unisex' );
$table->domain( 'size', 'product_size' )->default( 'L' );
$table->domain( 'status', 'product_status' )->default( 'in-stock' );
$table->domain( 'discount', 'product_discount' )->default( 0 );
$table->bigInteger( 'inventory' )->default( 100 );
$table->timestamps();
} );
}
/**
* Reverse the migrations.
*/
public function down() : void
{
Schema::dropIfExists( 'tshirts' );
}
};
Running this migration will create our tshirts table with three of its columns using our custom data types & domain that we created in previous migration.
Important: One thing to take note here in both these migrations is that we are using the Schema & Blueprint classes provided in the tpetry/laravel-postgresql-enhanced package and not the ones that come in Laravel core because we need the additional methods provided to us by the PostgreSQL package. While the tpetry/laravel-postgresql-enhanced package does not have a method to create custom data types yet, domain() method in its Blueprint class allows assigning a custom data type to a table column. The Pull Request I’ve submitted (see above) adds type() method to Blueprint class to allow differentiation between the kind of column added to a table.
Now that the migrations are created, we can run these migrations on our database to create the custom data types, domain and tshirts table, seed it with some test data if needed and then go about using in our app.
Using the custom data types & domain
Let’s create a model & see how to go about using these.
php artisan make:model Tshirt
One the model is created, it will look like:
namespace App\Models;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Casts\Attribute;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Tshirt extends Model {
use HasFactory;
protected $guarded = [];
protected $casts = [
'discount' => 'float',
'inventory' => 'integer',
];
public function scopeOfSize( Builder $query, string $size ) : void
{
$query->where( 'size', '=', strtoupper( $size ) );
}
protected function size() : Attribute
{
return Attribute::make(
set : fn( string $value ) => strtoupper( $value ),
);
}
}
We can now query our tshirts table like this:
// Get all in-stock tshirts of size `M`
// Our query scope will automatically convert lowercase `m` to uppercase `M`
$tshirts = Tshirt::ofSize( 'm' )
->where( 'size', '=', 'in-stock' )
->get();
// Add a new tshirt
$newTshirt = Tshirt::create([
'colour' => 'red',
'type' => 'round-collared',
'gender' => 'unisex',
'size' => '2xl', // our mutator will automatically convert this to `2XL`
'status' => 'in-stock',
'discount' => 5,
'inventory' => 500,
]);
Creating a composite data type
Let’s try our hand at creating a composite data type. In out tshirts table, let’s say we want to also store the location of our store where that particular t-shirt is available. So we will store X & Y coordinates for the store’s location with each record. This is not a good implementation; the idea here is to highlight how to create and use a custom composite data type in Laravel.
So we will add the following to our product_types_and_domains migration.
// add this to the up() method
DB::unprepared( 'CREATE TYPE store_coordinates AS (x FLOAT, y FLOAT);' );
// add this to down() method
DB::unprepared( 'DROP TYPE IF EXISTS store_coordinates;' );
And we will add the following to our tshirts migration:
$table->domain( 'store_location', 'store_coordinates' )->nullable();
We will also need to set an accessor and a mutator for our column here in the Tshirt model so that we don’t have to deal with parsing the data type our self.
Add this to the Tshirt model:
protected function storeLocation() : Attribute
{
return Attribute::make(
get : function( string $value ) : array {
$value = trim( rtrim( ltrim( $value, '(' ), ')' ) );
$values = explode( ',', $value );
return [
'x' => (float) $values[ 0 ],
'y' => (float) $values[ 1 ],
];
},
set : fn( array $value ) : string => sprintf( '(%1$s,%2$s)', $value[ 0 ], $value[ 1 ] )
);
}
This will make sure that we get our store’s location coordinates in an array with x and y keys while they are saved in the database as properly formatted string.
Now we can do this to create a record:
// The mutator for `size` will automatically convert the value to uppercase `2XL`.
// The mutator for `store_location` will automatically convert the array to appropriate string for DB.
$anotherTshirt = Tshirt::create([
'colour' => 'red',
'type' => 'round-collared',
'gender' => 'unisex',
'size' => '2xl',
'status' => 'in-stock',
'discount' => 5,
'inventory' => 500,
'store_location' => [ fake()->randomFloat( 4, 1, 10 ), fake()->randomFloat( 4, 20, 30 ) ],
]);
And if we fetch the record, then this is what we will get:
$newTshirt = Tshirt::find(1)->toArray();
/*
* The var $newTshirt will contain the following array
[
'id' => 1,
'colour' => 'red',
'type' => 'round-collared',
'gender' => 'unisex',
'size' => '2XL',
'status' => 'in-stock',
'discount' => 5,
'inventory' => 500,
'store_location' => [
'x' => 1.8029,
'y' => 27.8378,
],
'created_at' => '2024-07-26T12:07:24.000000Z',
'updated_at' => '2024-07-26T12:07:24.000000Z',
]
*/
Wrapping It Up
We just tried our hand at custom data types and domains in PostgreSQL and how to use them in Laravel. We’ve seen how to:
- Create simple
enumtypes for better data integrity. - Create a constrained domain of numeric type to allow for values only within a range.
- Use these types and domains in Laravel migrations and models.
- Create more complex composite types.
- Work with composite types in Laravel models.
Custom types/domains can be super powerful if they are used wisely. They’re great for enforcing data integrity and making our schema more expressive, but they can also make our database less portable.
Happy coding!