PostgreSQL support

Hi,

Does Invoice-Ninja support PostgreSQL?

Hi,

@david can you please advise?

@Solairen

I haven’t tested it myself with PostgreSQL, but the app is built on Laravel which does support postgres.

@david

I’ve checked and yes it supports PostgreSQL but there are errors.
First in repository: invoiceninja/dockerfiles two php extensions should be added:

  • pdo_pgsql
  • pgsql

In repository: invoiceninja/invoiceninja there should be a change in database.php :

'pgsql' => [
            'driver'         => 'pgsql',
            'host'           => env('DB_HOST', '127.0.0.1'),
            'port'           => env('DB_PORT', '5432'),
            'database'       => env('DB_DATABASE', 'forge'),
            'username'       => env('DB_USERNAME', 'forge'),
            'password'       => env('DB_PASSWORD', ''),
            'charset'        => 'utf8',
            'prefix'         => '',
            'prefix_indexes' => true,
            'search_path'         => 'public',
            'sslmode'        => env('DB_SSLMODE', 'disable'), <= this change
        ],

but after that I have an error:

2023-01-21T21:58:49Z [INFO] [Entrypoint]: Initialising Invoice Ninja...

   INFO  Configuration cached successfully.


   INFO  Caching the framework bootstrap files.

  config ........................................................... 28ms DONE
  routes ........................................................... 62ms DONE


   INFO  Preparing database.

  Creating migration table ......................................... 15ms DONE

   INFO  Running migrations.

  2014_10_12_100000_create_password_resets_table ................... 14ms DONE
  2014_10_13_000000_create_users_table .......................... 1,459ms DONE
  2019_11_10_115926_create_failed_jobs_table ....................... 12ms DONE
  2020_03_05_123315_create_jobs_table .............................. 14ms DONE
  2020_04_08_234530_add_is_deleted_column_to_company_tokens_table .. 72ms DONE
  2020_05_13_035355_add_google_refresh_token_to_users_table ......... 1ms FAIL

In Connection.php line 760:

  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "modify"
  LINE 1: alter table users modify column oauth_user_token text
                            ^ (SQL: alter table users modify column oauth_use
  r_token text)


In Connection.php line 545:

  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "modify"
  LINE 1: alter table users modify column oauth_user_token text

I’ve opened two issues:
PostgreSQL support · Issue #8191
Add two php extensions for PostgreSQL support · Issue #491

Best regards
Michał

1 Like

@Solairen

Thanks for this. I have found the issue regards that particular migration error. I’ll tag a new release, and if we can retry the migration please. If all the migrations pass ok, we’ll then add in the missing extensions/libs for the docker container :slight_smile:

Great! :slight_smile:

Can’t wait for the new release :slight_smile:

If you want I can create a PR for missing extensions/libs for the docker container.

@Solairen

I’ve just tagged 5.5.59

@david

I’ve tested it on 5.5.60 and:

postgresql:

2023-01-22T12:00:31Z [INFO] [Entrypoint]: Initialising Invoice Ninja...

   INFO  Configuration cached successfully.


   INFO  Caching the framework bootstrap files.

  config ........................................................... 28ms DONE
  routes ........................................................... 60ms DONE


   INFO  Running migrations.

  2020_05_13_035355_add_google_refresh_token_to_users_table ........ 84ms DONE
  2020_07_05_084934_company_too_large_attribute ..................... 5ms DONE
  2020_07_08_065301_add_token_id_to_activity_table ................. 10ms DONE
  2020_07_21_112424_update_enabled_modules_value .................... 9ms DONE
  2020_07_28_104218_shop_token ...................................... 2ms DONE
  2020_08_04_080851_add_is_deleted_to_group_settings ................ 3ms DONE
  2020_08_11_221627_add_is_deleted_flag_to_client_gateway_token_table  2ms DONE
  2020_08_13_095946_remove_photo_design ............................. 5ms DONE
  2020_08_13_212702_add_reminder_sent_fields_to_entity_tables ...... 15ms DONE
  2020_08_18_140557_add_is_public_to_documents_table .............. 145ms DONE
  2020_09_22_205113_id_number_fields_for_missing_entities .......... 17ms DONE
  2020_09_27_215800_update_gateway_table_visible_column ........... 152ms DONE
  2020_10_11_211122_vendor_schema_update ........................... 28ms DONE
  2020_10_12_204517_project_number_column ......................... 103ms DONE
  2020_10_14_201320_project_ids_to_entities ........................ 23ms DONE
  2020_10_19_101823_project_name_unique_removal ................... 183ms DONE
  2020_10_21_222738_expenses_nullable_assigned_user ................ 43ms DONE
  2020_10_22_204900_company_table_fields ............................ 5ms DONE
  2020_10_27_021751_tasks_invoice_documents ......................... 5ms DONE
  2020_10_28_224711_status_sort_order .............................. 84ms DONE
  2020_10_28_225022_assigned_user_tasks_table ...................... 31ms DONE
  2020_10_29_001541_vendors_phone_column ........................... 85ms DONE
  2020_10_29_093836_change_start_time_column_type .................. 30ms DONE
  2020_10_29_204434_tasks_table_project_nullable ................... 27ms DONE
  2020_10_29_210402_change_default_show_tasks_table ................ 48ms DONE
  2020_10_30_084139_change_expense_currency_id_column .............. 86ms DONE
  2020_11_01_031750_drop_migrating_column ........................... 2ms DONE
  2020_11_03_200345_company_gateway_fields_refactor ............... 104ms DONE
  2020_11_08_212050_custom_fields_for_payments_table ................ 2ms DONE
  2020_11_12_104413_company_gateway_rename_column .................. 81ms DONE
  2020_11_15_203755_soft_delete_paymentables ........................ 2ms DONE
  2020_12_14_114722_task_fields ..................................... 3ms DONE
  2020_12_17_104033_add_enable_product_discount_field_to_companies_table  2ms DONE
  2020_12_20_005609_change_products_table_cost_resolution ......... 117ms DONE
  2020_12_23_220648_remove_null_values_in_countries_table ........... 2ms DONE
  2021_01_03_215053_update_canadian_dollar_symbol .................. 26ms DONE
  2021_01_05_013203_improve_decimal_resolution .................. 2,583ms DONE
  2021_01_07_023350_update_singapore_dollar_symbol ................. 12ms DONE
  2021_01_08_093324_expenses_table_additional_fields ................ 4ms DONE
  2021_01_11_092056_fix_company_settings_url ........................ 6ms DONE
  2021_01_17_040331_change_custom_surcharge_column_type ............ 89ms FAIL

In Connection.php line 760:

  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "custom_surcharge1" ca
  nnot be cast automatically to type numeric
  HINT:  You might need to specify "USING custom_surcharge1::numeric(20,6)".
  (SQL: ALTER TABLE invoices ALTER custom_surcharge1 TYPE NUMERIC(20, 6))


In Connection.php line 545:

  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "custom_surcharge1" ca
  nnot be cast automatically to type numeric
  HINT:  You might need to specify "USING custom_surcharge1::numeric(20,6)".

This is a bit of a showstopper

Laravel isn’t able to know the correct syntax when converting a column type in postgres.

I’ll try and create a postgres compatible schema dump.

1 Like