kodus/sql-splitter

A simple facility to split SQL files into individual queries - supports MySQL, PostgreSQL and Microsoft SQL Server

TSParser / SQL Statement Parser

A simple parser to split multiple statement SQL queries to separated statements for MySQL, PostgreSQL and Microsoft SQL Server, _(*1)

What does it do and which database engines are supported?

It supports MySQL, PostgreSQL and Microsoft SQL Server. Our parser splits multi-statement SQL queries into single statements., _(*2)

Install via npm

npm install --save tsparser, _(*3)

Import TSParser to your project

import {TSParser} from 'TSParser', _(*4)

TSParser.parse() function description

static parse(query: string, dbType: string, delimiter: string): Array<string> {
        ...
    }

It expects 3 parameters ;, _(*5)

query : SQL query, _(*6)

dbType : mysql, pg or mssql, _(*7)

delimiter: semicolon (;) for MySQL and PostgreSQL, 'GO' for Microsoft SQL Server, _(*8)

TSParser can parse;, _(*9)

SQL Queries, _(*10)

Stored procedures, functions, views, etc.., _(*11)

PostgreSQL's tags (like $mytag$ ), _(*12)

MySQL's 'DELIMITER’, _(*13)

MySQL Example

In MySQL, semicolon (;) is default delimiter., _(*14)

    var mysqlQueriesBasic : string = 'SELECT * FROM users;SELECT * FROM user_details;'
    var mysqlStatements = TSParser.parse(mysqlQueriesBasic, 'mysql', ';');
    mysqlStatements.forEach(statement => {
            console.log(statement + '\n-----------');
        });

It will return an array with 2 items, items are;, _(*15)

SELECT * FROM users, _(*16)

SELECT * FROM user_details, _(*17)

MySQL Stored Procedure and Regular Queries

    DELIMITER //
    CREATE PROCEDURE country_hos(IN con CHAR(20))
    BEGIN
        SELECT Name, HeadOfState FROM Country
        WHERE Continent = con;
    END //
    DELIMITER ;
    SELECT * FROM users;
    SELECT * FROM user_details;

It will return an array with 3 items, items are;, _(*18)

  CREATE PROCEDURE country_hos(IN con CHAR(20))
  BEGIN
      SELECT Name, HeadOfState FROM Country
      WHERE Continent = con;
  END

SELECT * FROM users, _(*19)

SELECT * FROM user_details, _(*20)

PostgreSQL Example

In PostgreSQL, semicolon (;) is default delimiter., _(*21)

    var postgreSQLQueriesBasic : string = 'SELECT * FROM users;SELECT * FROM user_details;'
    var pgStatements = TSParser.parse(postgreSQLQueriesBasic, 'pg', ';');
    pgStatements.forEach(statement => {
            console.log(statement + '\n-----------');
    });

This will return an array with 2 items, items are;, _(*22)

SELECT * FROM users, _(*23)

SELECT * FROM user_details, _(*24)

PostgreSQL Function and Regular Queries

   CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;
SELECT * FROM users;
SELECT * FROM user_details;

It will return an array with 3 items, items are;, _(*25)

  CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql

SELECT * FROM users, _(*26)

SELECT * FROM user_details, _(*27)

Microsoft SQL Server Example

In Microsoft SQL Server, GO keyword is default delimiter., _(*28)

Unlike MySQL and PostgreSQL, if you don’t use a delimiter ( which is ‘GO’ ), MSSQL will execute it as a multi-statement query. MySQL and PostgreSQL will throw syntax exception., _(*29)

    var postgreSQLQueriesBasic : string = ‘SELECT * FROM users GO SELECT * FROM user_details;’
    var pgStatements = TSParser.parse(postgreSQLQueriesBasic, ‘pg’, ‘;’);
    pgStatements.forEach(statement => {
            console.log(statement + ‘\n—————‘);
    });

This will return an array with 2 items, items are;, _(*30)

SELECT * FROM users, _(*31)

SELECT * FROM user_details, _(*32)

Microsoft SQL Server Stored Procedure and Regular Queries

  CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City;
GO
SELECT * FROM users;
GO
SELECT * FROM user_details;

It will return an array with 3 items, items are;, _(*33)

CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City;

SELECT * FROM users, _(*34)

SELECT * FROM user_details, _(*35)