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) — user330315
– user330315, Commented Oct 19, 2022 at 9:17
why... are you not using the migration as god intended? switch the database driver, 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 if you want to know. — Bagus Tesa
– Bagus Tesa, Commented Oct 19, 2022 at 9:35
fyi, you can declare inet column in laravel migration using ipAddress. it will be translated to inet if you set it to run against postgresql. though, it will be nvarchar(45) for sql-server and varchar(45) for mysql. — Bagus Tesa
– Bagus Tesa, Commented Oct 19, 2022 at 9:44

Bagus Tesa · Accepted Answer · 2022-10-21 06:28:48Z

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.

Collectives™ on Stack Overflow

Translating php laravel sql code to postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related