Hi,
Does Invoice-Ninja support PostgreSQL?
Hi,
@david can you please advise?
I haven’t tested it myself with PostgreSQL, but the app is built on Laravel which does support postgres.
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
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 
Great!
Can’t wait for the new release
If you want I can create a PR for missing extensions/libs for the docker container.
I’ve just tagged 5.5.59
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
Heyo - it looks like the first issue may have been fixed – I went to stand up a pgsql instance and ran into a similar (but newer) error:
2022_07_29_091235_correction_for_companies_table_types ........... 28ms FAIL
In Connection.php line 760:
. (SQL: ALTER TABLE companies ALTER enabled_expense_tax_rates TYPE INT)
In Connection.php line 545:
SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "enabled_expense_tax_r
ates" cannot be cast automatically to type integer
HINT: You might need to specify "USING enabled_expense_tax_rates::integer"
Got through that one and another, but am stumped on 2022_10_05_205645_add_indexes_to_client_hash as this appears to use MySQL-only syntax for creating this index. In Pgsql that would probably look like:
CONSTRAINT clients_client_hash_index UNIQUE (substring(client_hash from 1 for 20))
Pushed the other two fixes to my fork; compare here: Comparing invoiceninja:v5-stable...NeilHanlon:v5-stable · invoiceninja/invoiceninja · GitHub
We shorten the index length to improve performance, on small datasets you can just get away with
$table->index('email');
In that particular file if you can switch on the loaded database driver you can use
public function up()
{
Schema::table('clients', function (Blueprint $table) {
$table->index('client_hash');
});
Schema::table('client_contacts', function (Blueprint $table) {
$table->index('contact_key');
$table->index('email');
});
Schema::table('vendor_contacts', function (Blueprint $table) {
$table->index('contact_key');
$table->index('email');
});
}
Thanks! I managed to get all the migrations to apply, and have at least got to a point where it tries to run the database seeds. Unfortunately, these fail for a couple reasons.
Regarding the fix for the shortened indices, this is what I came up with: Comparing invoiceninja:v5-stable...NeilHanlon:v5-stable · invoiceninja/invoiceninja · GitHub ($driver can probably be refactored out)
In order to create a user using the script, I had to make the following changes to the database after the migrations were applied (for testing purposes):
alter table company_user alter column slack_webhook_url drop not null;
alter table companies alter column calculate_expense_tax_by_amount drop not null;
alter table companies alter column slack_webhook_url drop not null;
alter table companies alter column google_analytics_key drop not null;
Regarding seeding; the following is encountered:
Database\Seeders\DateFormatsSeeder ............................................................................ RUNNING
In Connection.php line 760:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`"
LINE 1: select * from "date_formats" where BINARY `format`= $1 limit...
^ (SQL: select * from "date_formats" where BINARY `format`= d/M/Y limit 1)
In DateFormatsSeeder you’ll want to change the queries, it looks like Postgres doesn’t play nice with binary searches:
// $record = DateFormat::whereRaw('BINARY `format`= ?', [$format['format']])->first();
$record = DateFormat::find($format['id']);
and
// $record = DatetimeFormat::whereRaw('BINARY `format`= ?', [$format['format']])->first();
$record = DatetimeFormat::find($format['id']);
thank you for that. i’ll keep plugging away at this while i have time and keep my repo up to date, as well as this thread.
For now, I’ve installed on mysql and that’s working quite well!
Hey @david where does this stand?
Given some push over the last couple years and having apps like Mattermost working to fully move away from MySQL/MariaDB, I’m wanting to also standardize on PostgreSQL since presently I have lot of mixed environments. The following is fairly compelling from a developmental and deployment point of view and I’m also attempting to follow this path in my Rails apps:
I think this project is too far gone for us to invest the time in backward compatibility. But happy to help if you hit any roadblocks.