Rewrite the title as: Convert php laravel's sql code to postgres format

Question

I'm rewriting a web application written in php and laravel into a JavaScript stack. Currently I'm redesigning my database schema, which seems to be mysql to postgres.

I am a little confused about some of the syntax of 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 of the above is

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

But I'm not sure if I translated $table->string('ip_address', 45)->nullable(); because I'm not sure string('ip_address', 45 ) is doing.

Is my conversion to potgres correct, or what do I need to change to get the equivalent in the postgres create command?

Answer 1

For example, you can leverage migrations of the artisan command as intended by developers. It's explained in How to Convert Laravel Migrations to Raw SQL Scripts You can use:

php artisan migrate --pretend

However, it comes with a caveat, you need to have a database server available for this to actually work. It will create the migrations table in the target database if it does not exist, but will not create any tables in the migration. It will also follow the migrations table, so you may need to use a new database or truncate the migrations table before running pretend.

Alternatively, you can dig into Laravel's SQL "syntax" code and figure it out. Unfortunately, no one has made an easy-to-read reference table for it yet.

In your case it roughly translates as follows:

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