Using Sublime Text as your SQL Query Tool

Using Sublime Text's "build system" see how you can use your favorite text editor as the front end to a database for interactive querying.

---

Closed Caption:

hi my name is joe romeiro and today i
want to show you how i'm using sublime
text to be my sequel interactive editor
I really like sublime text and frankly I
really don't like the microsoft query
analyzer that I have to use during the
day and my day job so i decided that i
would like to try and use the sublime
text editor as a platform to build an
interactive query analyzer and
fortunately sublime text build system
makes it pretty easy to do
i'm going to show you what i did and
ideally if you can improve upon it
because it can certainly be improved
upon
I'd love to hear your ideas so here we
go
first thing i do when i'm on my home
computer which is a mac i don't use
windows at home in my home directory i
just created a sequel directory and I
have a couple files in there that i'll
show you momentarily
now flipping over the line text
what I do is I have two panes and I open
the editor with a group in the top and a
group in the bottom the top group is
going to be the place where I write my
sequel queries and the bottom group is
where i'm going to display the results
the first thing we need to do is we need
to create a build system from sublime
text
I've already done that but i'm going to
go ahead and show you what I did
if you go into tools you can click on
build system and create a new build
system and if you do that you simply get
this
untitled sublime - build and the syntax
right here and there is a variety of
things you can do there
I'm not going to save that but rather
i'm going to show you the one that i've
already created and we'll go ahead and
go into preferences and browse packages
and come over here to the user packages
and in here i have this
sequel . sublime build and you can see
that in my case I'm using postgresql and
so I've created a command
what sublime text notes as a command in
the build system and essentially I've
created a command to run a query in
postgresql reading it from a file the
query and writing the output to another
file
so the first part of this is and you can
read more about the anatomy of a command
sublime comand online i'll just give you
a high level overview and it should be
pretty apparent what's going on here
I'm creating a command and as you can
see down here
this is a json text that the text type
is Jason and so each portion of the
command line instruction
you have to put in its own set of
quotation marks inside the square
brackets and then delimited by a comma
so just to run through here really
quickly this is P sequel which is the
postgres command line utility
I'm seeing run as user postgres and - D
is my database name - OH is where I want
my results to go to and you can see I've
put them in users
Joe sequel results that text - a is
something I was trying that doesn't seem
to have any effect so i'm going to skip
over that
so is this - E - f and then dollar file
what this basically says is take the
content of the current file and use that
as the input so this dollar file is a
shorthand within the sublime text build
system to refer to the current file and
we're passing that in interactively to
the minus F parameter and the pc command
line which means it is the input for the
sequel text so that was a lot but we got
through it also
just in case i'm running from somewhere
else
I'm telling that sublime text my working
directory is users Joe that sequel
I probably don't need to do that because
i'm giving an absolute path to my
results txt file here but i'm not sure
perhaps it might help with the dollar
file
I don't really plan ever any queries
anywhere other than the sequel directory
but with that let's go on and give this
thing has been so i can basically just
come up here and begin to type a command
and i'm going to go ahead and just issue
a command from the against the
information schema tables table and if i
hit command be on a Mac or control be on
windows
it will run the build system and
essentially it's going to execute that
command we just looked at
and so if I do this you can see down
here
i get a message in the sublime text
console finished in 0.6 seconds that
didn't refresh automatically and that's
one thing I'd like to figure out how to
do a little bit differently but if i
simply click on here
I get my results and you can see that I
have the results of my query and we very
much have an interactive query editor
there was no need to save that file
before I ran just running the build by
hitting command B will save and then run
the file one of the main reasons I
wanted to use sublime text as my
interactive query tool is because I just
like the editor so much better than the
sequel server management studio studio
editor that i use at work and I do use
sublime text at work quite a bit and so
is looking to replace the sequel server
query utility and one of the things I
really like about sublime text is the
ability to make pretty powerful snippets
and we'll go ahead and do that now if we
take a look at this output here you can
see that these are a bunch of system
catalogs in postgres and if we get down
here we can see that these tables
labeled as public where the scheme is
public are actually my application table
so I don't really care that much about
this particular are all these other
particular tables just the public tables
now maybe another time I why I might
want to query against a different schema
so one thing i can do is i can go create
a step it and so that I don't have to
type this query over and over again and
so i'm going to go ahead and go into
tools in fact first i'm going to copy
this until the clipboard and i'm going
to go down to snippets new snippet and i
will go ahead and paste this text in but
before I do let's take a look at the
structure of a snippet
it's basically xml with this character
data piece of text here in this example
and this is a simple snippet that simply
displays hello and a a tab stop if you
will and then some more text in another
tab snap and so these these tab stops
are these placeholders are really
powerful features a lot of other text
editors have them such as textmate but i
think it's going to be really helpful in
our interactive query builder that we're
building here so i'm going to go ahead
and type in my statement and i'm going
to say where table schema equals and
we'll put one thing i like i love about
sublime text is the ability to highlight
a bit of character string of characters
and hit one key in this case the single
quote and put single closed on both
sides of that and i'll go ahead and turn
into that statement put my semicolon
there too
terminate my sequel command down here
there are some comment about areas
including one called tab trigger
I'm going to high-level all of that text
and using the sublime shortcut g of
command forward slash will uncomment
that will toggle the the comment
character and here I'm going to simply
type the word schema and that will that
followed by the tab will prompt us to
see if we want to indeed use that step
it so i'll go ahead and save this and
we'll save it under it defaults to the
packages user directory and so there's a
certain name format that this has to
follow and so we'll type in schema .
sublime - sniff it and when we save this
that step it is now available for a file
and in fact if i get rid of this text
here and I type in
I just begin to type in the word schema
and you can see that i have my scheme
available here and i can go ahead and
hit enter and now it has not only paste
it in that text but it is highlighting
on the tab stop that we put my left it
the text this I could have put schema
name here or something to that effect
but we'll go ahead and type in public
and now when i hit command be
we've run our query again you see that
we only have tables in the public schema
printing there now one thing I really
like about using this text area
this file is output for text
is that if i want to go back and see the
results that I did for a query a few
queries ago because it's simply a text
editor i can simply hit command Z and go
back and control or command Y rather to
go forward and I can go see all those
results without running those queries
again
and so that's kind of nice now one thing
I would really love to do to improve
this is I'd really like it if there was
a way to somehow write a program for
example perhaps in in objective-c or in
ruby or Java and have a program that is
nothing more than a bunch of grid cells
and I could then get rid of the text
output and when i run my command it
would write it to a file that would be
immediately read and parsed by this grid
program running in the background and
put my mom really not in the background
in the foreground but behind the scenes
that would read this file and display
the text results in this table
this grid table and that populate all of
the cells with the column headers and
all of that good stuff
so this has just been a brief
demonstration of how you can use sublime
text 2 as an interactive query tool and
i would love to hear ideas from you on
additions that you make to this idea to
improve it further still
thanks for listening