Translating php laravel sql code to postgres

Question

I am working on rewriting a web app written in php and laravel to a JavaScript stack. At present I am working on reworking the db schema which seems to be mysql to postgres.

I am slightly confused with some of the syntax for the following create table command

    public function up()
    {
        Schema::create('sessions', function (Blueprint $table) {
            $table->string('id')->unique();
            $table->unsignedInteger('user_id')->nullable();
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->text('payload');
            $table->integer('last_activity');
        });
    }

From my understanding the postgres equivalent for the above would be

create table sessions (
    id text unique not null,
    user_id int references users,
    ip_address text,
    user_agent text,
    payload text,
    last_activity integer
    
);

However I am not sure that I have translated $table->string('ip_address', 45)->nullable(); correctly as I am not sure what exactly string('ip_address', 45) is doing.

Is my transformation to potgres correct or what do I need to change in order to have something equivalent in the postgres create command?

IP addresses shouldn't be stored as a string in Postgres, but with the data type `inet` (and last_activity sounds more like it should be a `timestamp`) — , Oct 19 '22 at 09:17
why... are you not using the migration as god intended? switch the [database driver](https://github.com/laravel/laravel/blob/9.x/.env.example#L11), put the creds and db address, then let the migration do it for you. those being said `->string()` can be translated into many things in laravel, you can dig [laravel's github](https://github.com/laravel/framework/blob/9.x/src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php) if you want to know. — Bagus Tesa, Oct 19 '22 at 09:35
fyi, you can declare `inet` column in laravel migration using [`ipAddress`](https://laravel.com/docs/9.x/migrations#column-method-ipAddress). it will be translated to `inet` if you set it to run against [tag:postgresql]. though, it will be `nvarchar(45)` for [tag:sql-server] and `varchar(45)` for [tag:mysql]. — Bagus Tesa, Oct 19 '22 at 09:44

score 0 · Accepted Answer · answered Oct 21 '22 at 06:28

You can, for one, leverage artisan command's migration as the developer intended. It has been explained in How to convert Laravel Migrations to Raw SQL Scripts that you can use:

php artisan migrate --pretend

However, it has one caveat, you need to have a working database server to actually make it work. It will create migrations table (if it doesn't exist) in the target database but it will not create any of the table in the migrations. It will also adhere migrations table so you might need to use new database or truncate migrations table before running pretend.

Alternatively, you can go digging into Laravel's SQL "Grammar" code and figure things out from there. Sadly, someone is yet to make an easy to read reference table for it.

In your case it translates roughly this way:

Laravel	Postgre
`$table->string('id')->unique();`	`id varchar`
`$table->unsignedInteger('user_id')->nullable();`	`user_id integer null`
`$table->string('ip_address', 45)->nullable();`	`ip_address varchar(45) null`
`$table->text('user_agent')->nullable();`	`user_agent varchar null`
`$table->text('payload');`	`payload text`
`$table->integer('last_activity');`	`last_activity integer`

Or rather, written in PostgreSQL:

create table sessions (
    id varchar,
    user_id integer null,
    ip_address varchar(45) null,
    user_agent varchar null,
    payload text,
    last_activity integer
);

Note:

There was no directive in the migration to add any foreign keys.
PostgreSQL does not support unsigned integer, see Why Unsigned Integer is Not Available in PostgreSQL.

Translating php laravel sql code to postgres

1 Answers1