API

SQLAutocomplete

This is the main entrypoint to this library. First, you instantiate an instance of this class with the SQL dialect you require. Then, you can use the autocomplete method to get a list of viable options.

constructor(dialect: SQLDialect, tableNames?: string[], columnNames?: string[])

Requires the SQL dialect you will be parsing for autocompletion. See SQLDialect. Optionally, you can specify table names and/or column names that will be used for making suggestions. If names are not provided, you will still be notified if a table or column could be suggested at the location, but the value will be null. See autocomplete for details. tableNames and columnNames can also be updated after instantiation. It is recommended to use the setTableNames and setColumnNames functions.

Usage Example

import { SQLAutocomplete, SQLDialect } from 'sql-autocomplete';

const sqlAutocomplete = new SQLAutocomplete(SQLDialect.MYSQL);
// Or
const sqlAutocomplete2 = new SQLAutocomplete(SQLDialect.MYSQL, ['table1', 'table2'], ['columnA', 'columnB']);

autocomplete(sqlScript: string, atIndex?: number): AutocompleteOption[]

This method returns a list of possible AutocompleteOptions, at the index specified. If atIndex is not specified, the autocomplete suggestions will be for the end of the sqlScript.

If tableNames and/or columnNames have not been set on the SQLAutocomplete instance, and the autocomplete engine determines that a table or column could be suggested at the specified index, then an AutocompleteOption with value null (and type TABLE or COLUMN) will be included in the array of AutocompleteOptions. An AutocompleteOption with value null (and type TABLE or COLUMN) will also be included in the array or AutocompleteOptions if the table or column name is partially completed and it does not match any of the provided table/column names.

sqlScript: The string containing the SQL to be autocompleted. Although autocomplete works for strings containing multiple queries, you will have better performance if you only provide the SQL statement being autocompleted.

atIndex (Optional): The string index where autocompletion should occur. If this value is not provided, then autocompletion occurs at the end of the string (i.e. at index sqlScript.length)

Usage Example

import { SQLAutocomplete, SQLDialect } from 'sql-autocomplete';

const sqlAutocomplete = new SQLAutocomplete(SQLDialect.MYSQL);
const sql = 'SELECT * FR';
const options = sqlAutocomplete.autocomplete(sql);
console.log(options);

//  [ AutocompleteOption { value: 'FROM', optionType: 'KEYWORD' } ]

setColumnNames(columnNames: string[]): void

Sets the column names that will be used for making autocomplete column suggestions. Overwrites any previously set column names.

Usage Example

import { SQLAutocomplete, SQLDialect } from 'sql-autocomplete';

const sqlAutocomplete = new SQLAutocomplete(SQLDialect.MYSQL);
const sql = 'SELECT col';
sqlAutocomplete.setColumnNames(['columnA', 'anotherColumn']);
const options = sqlAutocomplete.autocomplete(sql);
console.log(options);

// [
//   AutocompleteOption { value: 'columnA', optionType: 'COLUMN' },
//   AutocompleteOption { value: null, optionType: 'TABLE' },
//   AutocompleteOption { value: 'COLLATION', optionType: 'KEYWORD' },
//   AutocompleteOption { value: 'COLUMN_NAME', optionType: 'KEYWORD' },
//   AutocompleteOption { value: 'COLUMN_FORMAT', optionType: 'KEYWORD' },
//   AutocompleteOption { value: 'COLUMNS', optionType: 'KEYWORD' }
// ]

setTableNames(tableNames: string[]): void

Sets the table names that will be used for making autocomplete table suggestions. Overwrites any previously set table names.

Usage Example

import { SQLAutocomplete, SQLDialect } from 'sql-autocomplete';

const sqlAutocomplete = new SQLAutocomplete(SQLDialect.MYSQL);
const sql = 'SELECT col1 FROM ano';
sqlAutocomplete.setTableNames(['table1', 'anotherTable']);
const options = sqlAutocomplete.autocomplete(sql);
console.log(options);

// [ AutocompleteOption { value: 'anotherTable', optionType: 'TABLE' } ]

SQLDialect

This class enumerates the SQL dialects that are supported. Possible values include:

MYSQL
TSQL      // For SQL Server
PLSQL     // For Oracle
PLpgSQL   // For PostgreSQL

Usage Example

import { SQLAutocomplete, SQLDialect } from 'sql-autocomplete';

const sqlAutocomplete = new SQLAutocomplete(SQLDialect.MYSQL);

AutocompleteOption

An autocomplete option is returned by autocomplete.

If the option type is a KEYWORD type, the value will be populated with the keyword that is suggested. If the option type is COLUMN and TABLE, then the value will be null.

constructor(value: string, optionType: AutocompleteOptionType)

AutocompleteOptionType

This class enumerates the type of autocomplete options. Possible values include:

KEYWORD
COLUMN
TABLE

SimpleSQLTokenizer

A very simple tokenizer for splitting a string into tokens based on whitespace. Handles SQL quote characters (i.e. '). Also splits ; and . as separate tokens.

This should not be used for most ANTLR4 tasks.

It is designed to be used to find the correct token index at any string location, regardless of the validity of the SQL string. See the getTokenIndexAt for usage example.

This class is used to assist in finding the correct token to make autocompletion suggestions for. We cannot relay on the built-in ANTLR4 lexers/parsers as they do not always return all tokens when the SQL is incomplete.

constructor(value: string, tokenizeWhitespace: boolean)

value: The string that will be tokenized.

tokenizeWhitespace: If true, whitespace characters will be included in the set of tokens. If false, whitespace characters will not be included. How this is set depends on how whitespace characters are handled by the SQL dialect grammar being used. For grammars that skip whitespace tokens, this value should be set to false.

Usage Example

import { SimpleSQLTokenizer } from 'sql-autocomplete';

const sqlString = 'SELECT * FROM table';
const tokenizer = new SimpleSQLTokenizer(sqlString, false);

nextToken(): Token

Return the next token in the value string.

Usage Example

import { SimpleSQLTokenizer } from 'sql-autocomplete';

const sqlString = 'SELECT * FROM table';
const tokenizer = new SimpleSQLTokenizer(sqlString, false);
let token = tokenizer.nextToken();
console.log(token.text);

// 'SELECT'