Getting the type of an unsupported Postgres field in Laravel

Published by at 8th January 2018 2:00 pm

Today I've been working on a generic, reusable Laravel admin interface, loosely inspired by the Django admin, that dynamically picks up the field types and generates an appropriate input field accordingly.

One problem I've run into is that getting a representation of a database table's fields relies on doctrine/dbal, and its support for the more unusual PostgreSQL field types is spotty at best. I've been testing it out on a Laravel-based blogging engine, which has full-text search using the TSVECTOR field type, which isn't supported, and it threw a nasty Unknown database type tsvector requested error.

Fortunately, it's possible to register custom field type mappings easily enough. In this case we can safely treat a TSVECTOR field as a string` type anyway, so we can map it to the string type. We can do so in the boot method of a service provider:

3namespace App\Providers;
5use Illuminate\Support\ServiceProvider;
7class AppServiceProvider extends ServiceProvider
9 /**
10 * Bootstrap any application services.
11 *
12 * @return void
13 */
14 public function boot()
15 {
16 // Register the TSVECTOR column
17 $conn = $this->app->make('Illuminate\Database\ConnectionInterface');
18 $conn->getDoctrineSchemaManager()
19 ->getDatabasePlatform()
20 ->registerDoctrineTypeMapping('tsvector', 'string');
21 }
23 /**
24 * Register any application services.
25 *
26 * @return void
27 */
28 public function register()
29 {
30 //
31 }

We register a Doctrine type mapping that maps the tsvector type to a string. Now Doctrine will just treat it as a string.

We can then retrieve the field types as follows:

1 $table = $this->model->getTable();
2 $fields = array_values(Schema::getColumnListing($table));
3 $fielddata = [];
4 foreach ($fields as $field){
5 if ($field != 'id' && $field != 'created_at' && $field != 'updated_at' && $field != 'deleted_at') {
6 try {
7 $fielddata[$field] = Schema::getColumnType($table, $field);
8 } catch (\Exception $e) {
9 $fielddata[$field] = 'unknown';
10 }
11 }
12 }

Note that we specifically don't want to retrieve the ID or timestamps, so we exclude them - the user should never really have the need to update them manually. We fetch the table from the model and then call Schema::getColumnListing() to retrieve a list of fields for that table. Finally we call Schema::getColumnType() to actually get the type of each column.

Now, I suspect the performance of this admin interface is going to be inferior to a more specific one because it has to retrieve the fields all the time, but that's not the point here - with a non-user facing admin interface, performance isn't quite as much of an issue. For the same reason the admin doesn't do any caching at all. It's still useful under certain circumstances to be able to reverse-engineer the table structure and render an appropriate form dynamically.