MySQL Oracle PostgreSQL Tool

How to execute SQL in Sublime Text 3 (SQLTools)


How to execute SQL in Sublime Text 3

What is SQLTools?

If we are able to execute SQL in Sublime Text 3, we can improve our development environment and can save our time. "SQLTools" package can make your dream come true!

https://github.com/mtxr/SQLTools

SQLTools supports PostgreSQL, MySQL, Oracle, MSSQL, Vertica, Firebird, (SQLite) databases.

Also, SQLTools provides the following features, the tool is very useful.

SQLTools features

  • Smart auto completions (for PostgreSQL, MySQL, Oracle, MSSQL, Vertica, Firebird)
  • Run SQL Queries (CTRL+e, CTRL+e)
  • View table schemas (CTRL+e, CTRL+d)
  • View Queries history (CTRL+e, CTRL+h)
  • Show table records (CTRL+e, CTRL+s)
  • Show explain plan for queries (PostgreSQL, MySQL, Oracle, Vertica, SQLite) (CTRL+e, CTRL+x)
  • Formatting SQL Queries (CTRL+e, CTRL+b)
  • Threading Support (prevent ST lockups)
  • Query timeout (Kill thread if query takes too long)
  • Unescape chars for languages (PHP " is replaced by ")
  • Save queries (CTRL+e, CTRL+q)
  • List and Run saved queries (CTRL+e, CTRL+a)
  • Remove saved queries (CTRL+e, CTRL+r)
Those are very useful features and can improve our database development environment. Let's install the SQLTools package into Sublime Text 3.




Install SQLTools package into Sublime Text 3

First off, we need to install SQLTools package in Sublime Text 3 as follows.

1. Press CTRL+SHIFT+p.
2. Type "Install Package" and select "Package Control: Install Package".
3. Type "sqltools", then select "SQLTools" as the following image.



Install database client software

SQLTools requires database client software. If we haven't installed yet, we need to install.

In order to connect to Oracle database, please refer to  Installing Oracle Database Instant Client on Windows .

In order to connect to MySQL database, please refer to  Download MySQL Installer .

In order to connect to PostgreSQL database, please refer to  Windows installers .

In order to connect to Microsoft SQL Server database, please refer to  SQL Server 2017 Express Installation .


Change settings of SQLTools

In order to use SQLTools effectively, we need to make some changes in SQLTools setting.

Navigate Preferences => Package Settings => SQLTools => Settings.


The following SQLTools setting files are opened.

Copy everything in left window and paste it to right window. Left window is default setting file and right window is user setting file. Normally, we just need to modify user setting, we copy all default settings to user setting file.

In the user setting file (right window), we need to move to "cli" section as the following image. We need to define basic database setting in the section.

We only set up MySQL, PostgreSQL and Oracle database settings, but we can do the same thing for other databases. We need to define database command location in the "cli" section. In this article, we defined database command location as follows. The location is vary.

[MySQL]
"mysql"   : "C:/xampp/mysql/bin/mysql.exe",

[PostgreSQL]
"pgsql"   : "C:/Program Files/PostgreSQL/9.3/bin/psql.exe",

[Oracle]
"oracle"  : "C:/APPL/instantclient-windows.x64-12.2.0.1.0/instantclient_12_2/sqlplus.exe",

Save setting after we defined.


Add database connections

Then, we need to define database connections which we would like to connect to in Sublime Text 3.

Open Preferences => Package Settings => SQLTools => Connections as following image.

"Connections" setting is to define database connection one by one. If you use SQLTools at the first time, copy everything in left window and paste it to right window.

From now, we only need to make changes in right window (user setting file). There are some sample database connections, we just need to copy and past it, then modify each database connection parameter.

Save database connection setting after you defined.


Connect to database

In order to connect to a database in Sublime Text 3, we need to type CTRL+SHIFT+p command, then type "st:". Then, select "ST: Select Connection" (blue circle).

So that we should be able to see a list of database connections as the following image. In the list of database connections, we need to select one of the database connections, so that Sublime Text 3 can connect to database. If we don't get a list of databases, please restart Sublime Text 3. Or, please double-check the SQLTools setting and database connection setting.


Execute SQL in Sublime Text 3

We are going to connect to PostgreSQL and execute a SQL. Highlight a SQL, then type CTRL+e、CTRL+e (Keep pressing CTRL key and type e twice), so that the highlighted SQL will be executed.

When we executed a SQL, window is automatically split into two windows (one is for SQL and the other is for results). If we have any SQL errors, it will be displayed in the result window.

When we execute a SQL in Sublime Text 3, we can also get its results in Sublime Text 3. It accelerates our development. We are also able to copy and past SQL results very easily.


More SQLTools customization

If we adjust more SQLTools settings, it's going to be more useful. Let's open SQLTools setting and find "cli_options" section in the user setting. The section is to customize database behavior per database.

For example, in PostgreSQL database, we can add psql command's options in the "args" parameter in the SQLTools user setting. As a result of that, SQL results will be displayed more beautifully.

"args": "-h {host} -p {port} -U {username} -d {database}",

"args": "-h {host} -p {port} -U {username} -d {database} -P border=2 -P linestyle=unicode ",

Once we added the parameters in the "args", let's execute a SQL. We we can see, border line is very beautiful as follows.

For other databases, we can do the same thing in the "cli_options" setting.