Repository: sql-formatter-org/sql-formatter (ts)

Issue:
Feature Request: Place `ON CONFLICT` on new line
**Describe the Feature**
Place `ON CONFLICT` on new line.

Current behaviour:
```sql
INSERT INTO
    tests (val)
VALUES
    (1),
    (2) ON CONFLICT (val)
DO NOTHING;
```

Wanted behaviour:
```sql
INSERT INTO
    tests (val)
VALUES
    (1),
    (2) 
ON CONFLICT (val)
DO NOTHING;
```

Pull request:
Fix formatting of ON CONFLICT clause
In PostgreSQL and SQLite.

Also eliminate the `DO` keyword from onelineClauses list in every dialect.

Fixes #535 

Patch:
diff --git a/src/languages/mariadb/mariadb.formatter.ts b/src/languages/mariadb/mariadb.formatter.ts
index 687e86d3..c0b2a57d 100644
--- a/src/languages/mariadb/mariadb.formatter.ts
+++ b/src/languages/mariadb/mariadb.formatter.ts
@@ -92,7 +92,6 @@ const onelineClauses = expandPhrases([
   'CREATE USER',
   'DEALLOCATE PREPARE',
   'DESCRIBE',
-  'DO',
   'DROP DATABASE',
   'DROP EVENT',
   'DROP FUNCTION',
diff --git a/src/languages/mysql/mysql.formatter.ts b/src/languages/mysql/mysql.formatter.ts
index d448dd0b..7f58d5ee 100644
--- a/src/languages/mysql/mysql.formatter.ts
+++ b/src/languages/mysql/mysql.formatter.ts
@@ -87,7 +87,6 @@ const onelineClauses = expandPhrases([
   'CREATE USER',
   'DEALLOCATE PREPARE',
   'DESCRIBE',
-  'DO',
   'DROP DATABASE',
   'DROP EVENT',
   'DROP FUNCTION',
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index b057b38a..55ac8ae1 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -36,6 +36,8 @@ const onelineClauses = expandPhrases([
   // - update:
   'UPDATE [ONLY]',
   'WHERE CURRENT OF',
+  // - insert:
+  'ON CONFLICT',
   // - delete:
   'DELETE FROM [ONLY]',
   // - drop table:
@@ -150,7 +152,6 @@ const onelineClauses = expandPhrases([
   'DEALLOCATE',
   'DECLARE',
   'DISCARD',
-  'DO',
   'DROP ACCESS METHOD',
   'DROP AGGREGATE',
   'DROP CAST',
diff --git a/src/languages/singlestoredb/singlestoredb.formatter.ts b/src/languages/singlestoredb/singlestoredb.formatter.ts
index 1188f96e..36987f8b 100644
--- a/src/languages/singlestoredb/singlestoredb.formatter.ts
+++ b/src/languages/singlestoredb/singlestoredb.formatter.ts
@@ -89,7 +89,6 @@ const onelineClauses = expandPhrases([
   'DESCRIBE',
   'DETACH DATABASE',
   'DETACH PIPELINE',
-  'DO',
   'DROP DATABASE',
   'DROP FUNCTION',
   'DROP INDEX',
diff --git a/src/languages/sqlite/sqlite.formatter.ts b/src/languages/sqlite/sqlite.formatter.ts
index ca7ba517..e282d46c 100644
--- a/src/languages/sqlite/sqlite.formatter.ts
+++ b/src/languages/sqlite/sqlite.formatter.ts
@@ -32,6 +32,8 @@ const reservedClauses = expandPhrases([
 const onelineClauses = expandPhrases([
   // - update:
   'UPDATE [OR ABORT | OR FAIL | OR IGNORE | OR REPLACE | OR ROLLBACK]',
+  // - insert:
+  'ON CONFLICT',
   // - delete:
   'DELETE FROM',
   // - drop table:


Test patch:
diff --git a/test/features/onConflict.ts b/test/features/onConflict.ts
new file mode 100644
index 00000000..bd73441c
--- /dev/null
+++ b/test/features/onConflict.ts
@@ -0,0 +1,16 @@
+import dedent from 'dedent-js';
+
+import { FormatFn } from '../../src/sqlFormatter.js';
+
+export default function supportsOnConflict(format: FormatFn) {
+  // Regression test for issue #535
+  it('supports INSERT .. ON CONFLICT syntax', () => {
+    expect(format(`INSERT INTO tbl VALUES (1,'Blah') ON CONFLICT DO NOTHING;`)).toBe(dedent`
+      INSERT INTO
+        tbl
+      VALUES
+        (1, 'Blah')
+      ON CONFLICT DO NOTHING;
+    `);
+  });
+}
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index 8ba232ca..d6c998fa 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -25,6 +25,7 @@ import supportsInsertInto from './features/insertInto.js';
 import supportsUpdate from './features/update.js';
 import supportsTruncateTable from './features/truncateTable.js';
 import supportsCreateView from './features/createView.js';
+import supportsOnConflict from './features/onConflict.js';
 
 describe('PostgreSqlFormatter', () => {
   const language = 'postgresql';
@@ -45,6 +46,7 @@ describe('PostgreSqlFormatter', () => {
   });
   supportsDeleteFrom(format);
   supportsInsertInto(format);
+  supportsOnConflict(format);
   supportsUpdate(format, { whereCurrentOf: true });
   supportsTruncateTable(format, { withoutTable: true });
   supportsStrings(format, ["''-qq", "U&''", "X''", "B''"]);
diff --git a/test/sqlite.test.ts b/test/sqlite.test.ts
index 5106717f..78ec8e74 100644
--- a/test/sqlite.test.ts
+++ b/test/sqlite.test.ts
@@ -21,6 +21,7 @@ import supportsLimiting from './features/limiting.js';
 import supportsInsertInto from './features/insertInto.js';
 import supportsUpdate from './features/update.js';
 import supportsCreateView from './features/createView.js';
+import supportsOnConflict from './features/onConflict.js';
 
 describe('SqliteFormatter', () => {
   const language = 'sqlite';
@@ -40,6 +41,7 @@ describe('SqliteFormatter', () => {
   });
   supportsDeleteFrom(format);
   supportsInsertInto(format);
+  supportsOnConflict(format);
   supportsUpdate(format);
   supportsStrings(format, ["''-qq", "X''"]);
   supportsIdentifiers(format, [`""-qq`, '``', '[]']);