A Free and open source T-SQL via JavaScript CLI for the near-line Docker host.
Create a virtual, private & self-contained replica of any multi-data-sourced, SQL Server rich live systems useful for near-line modeling, coding, testing, analysis and other manipulations of the networked data environment. Made expressly for developers and other SQL power users.
Emulate, imitate, demonstrate or simulate clouds, clusters, farms, federations, LANs, WANs, graphs, distributed partitions, Availability Groups, shards, replicas and many other multi-store and/or heterogeneous network data environments, all on one Linux host.
- Design or model any Microsoft SQL Server rich TCP/IP network data environment
- Pull Docker Images from Docker Hub
- Manage a catalog of local MS SQL Server Containers
- Write, edit and run ad hoc and client stored T-SQL queries and a Library of T-SQL script files
- Run bash one-off commands from the CLI prompt without losing the T-SQL in the CLI's Batch cache
- Recall previously used T-SQL from client side data history and/or git source control system
- Enable a private SQLPad server to add a browser GUI and charts to the tool set (requires V8)
- Set-up TLS on the Docker API, any of the SQL Server Query Engines and/or SQLPad's Express server
- Simultaneously access the vNET of SQL Containers and admin them as local resources on the host
- Extend the environment, and optionally the CLI, to support other data stores from Docker Hub
- Architects can model with it
- Developers can code, debug and test on it
- Data Analysts can slice it & dice it
- Data Miners can obsess and regress over it
- Forcasters can discover trends on it
- Network Engineers won't have to route around it
- Testers can reset it to replay it, over and over and over...
- Trainers can teach with it, and then take it home and grade from it*
- Anyone can work remotely with it to get stuff done... with or without the Internet... *If uncertain your use case falls under Microsoft's "Developer" license model, please do seek licensing guidance from Microsoft.
- 64-bit Linux compute instance
- Docker CE - v. 19.3.06+
- Git - v. 2.17.1+
- Node.js - v. 8.10.0+
- OpenSSL - v.1.1.1+
- IDE (e.g., atom, bluefish, code, sublime, etc.) - file path as argument
- text editor (e.g., vi, Emacs, nano (aka pico), kwrite, etc.) - buffer text as argument
- browser - SQLPad, if used, needs a browser with the V8 JavaScript engine
Choosing a Linux distro for the host is out of our scope. Suffice it to say, the "official" SQL Server image is currently composed upon an "official" Ubuntu image. Linux.org offers "the 25 most popular" distro's - all I can guarantee is the over half the links on the linux.org page will work. One finds many differing opinions on the Internet about which distro is best to use with containers, but the proofs are thin and can be biased, often nothing more than advertisements. Regardless which host distro is chosen, prerequisites are best installed through the command-line package manager recommended by the distro's maker. If a prerequisite above did not come with the host linux instance, it will invariably be available from one of that package managers configured repositories.
Choosing an IDE and an editor are also beyond scope. It is anticipated that the IDE already in use will work just fine. All the CLI does with the IDE is asynchronously launch it with a file path argument. The editor can be used to do this as well, but won't be as pleasant of an experience as reviewing or working with JavaScript, JSON, HTML or T-SQL as it is a modern open source IDE that runs on Linux.
As to the editor, a Linux distro that ships without vi and does not also includes one or more other text editors is hard to find. I personally prefer Emacs over vi. Emacs will surely be in the package manager's repository, however it is usually not installed by default. The base requirement for full CLI functionality is that the editor accept text from stdin (buffered data) as the first positional argument and the IDE must accept a file path as the first positional arg. Thus, the preferred option is to specify both an editor and an IDE in the './config/config.json' file.
Matters not to the CLI which browser is used. However, SQLPad will only work with the V8 JavaScript engine. This limits the choices to browser based on the open source Chromium project (e.g., Blisk, Brave, Chrome, Opera, Vivaldi, etc.) if you plan to use the private SQLPad.
At the bash terminal prompt:
bill@HOST$ npm update bill@HOST$ mkdir anonym bill@HOST$ cd anonym bill@HOST:~/anonym$ npm install anonym
The user is prompted with the option to pull the latest SQL Server Image from the Docker hub if no local Images are found at CLI start-up. This is described in the Launch section below. The first time the CLI is started it is preferable to decline the pull and review/adjust the configuration as desired.
Most configurations used by the CLI are set in the './config/config.json' file. The file can be edited before launching the CLI or, from within the CLI, the file can be launched for edit in the configured IDE. The values most important to configure, once the CLI is installed, are the IDE and editor. The editor defaults to 'vi' when not configured. The IDE has no default, although it is most likely already be set in the config file to use Visual Studio code.
To open the config file and set the IDE and editor from the CLI use:
anonym > settings config
This will try to open the config file in the configured IDE. If the IDE is not present locally or not configured, the file will try to open in configured editor. If the editor is not present, the file will open in vi. If you don't know vi well enough to edit a text file or pull up the help documentation, have a peak at './docs/html/vimCheatSheet.html' in your browser. If the host Linux instance is configured to not open the GUI, consider one of the text only browsers that run in the terminal like Lynx. Much of the CLI documentation and source code can be viewed at the prompt or, especially to improve readability of larger documents, in a browser.
The defaults will get the CLI rolling and may be satisfactory as is as you evaluate the CLI, but the configuration is fully under the control of the user.
The config file includes default values for several secrets. However, on first use or on demand, the user is prompted for a different secret. Secrets are obfuscated and stored in the CLI's secret store for later recall. The values in config make for an interesting honeypot once other values are set for these secrets.
bill@HOST:~/anonym$ npm start anonym > anonym@0.1.4 start /home/bill/anonym anonym > node anonym.js π§ π§Ώ No Local SQL Images found ? Pull the latest SQL Image now? (Y/n)
This mini dashboard is output each time the catalog of SQL Server Docker artifacts map is buffered. The first line, prefixed with a network ucon (i.e., unicode icon), extends as a series of large dots. First in line are blue dots for each local image. These are followed by a set of red or green dots for each local SQL Container: red if idle, green if running. There are several examples of the dashboard shown below.
The second line, prefixed with the target like ucon (π§Ώ), provides a status message regarding the SQL Container currently selected as the target of CLI originated T-SQL queries. When the most recently targeted Container is found and is running, this line becomes a SQL connection status message and the ucon changes to the assigned to SQL Server messages (α).
A re-inventory and remapping of the catalog of SQL in Docker artifacts that underlies the dashboard occurs as the CLI is started. And again each time a container is stopped or started by the CLI, or an Image or Container is added or removed using the CLI, when a connection pool is opened or closed to by CLI, or when the CLI's Target SQL Server is changed. Remapping can also be invoked at any time by the user at the prompt:
anonym > catalog remap
The most vital of the CLI's innards is the Catalog of SQL Server on Linux Docker artifacts. This JavaScript Object of Maps filters out any non-SQL Server Images and Containers, including the private SQLPad server if used, then snapshots the local Docker API SQL Server Image and Container objects. It also keeps track of the SQL Server the CLI is currently targeting when submitting T-SQL and the CLI to SQL Server connection pools opened in the current CLI session.
Once one or more SQL Server Images have been pulled from https://hub.docker.com:
anonym > image pull
And the configuration has been adjusted as needed (described below), SQL Container Instances can be created from the user's choice among the local images:
anonym > image run ? Select from local SQL Images (Use arrow keys) β― 56655b462301 a8343d3ce21c ba266fae5320 d273eadd9675
Then any SQL Container can be set as the current Target for CLI originated T-SQL queries:
anonym > connection target ? Target for CLI originated SQL queries (Use arrow keys) 5004ba923fda 57f9ee49483d β― 9d51eb524061 c41e3ae7719b cf030328e0aa
At this point, the CLI will launch with a visual showing the state of the host's collection of SQL Containers or the Catalog, here seen as 4 blue Images, 1 green - or running - Container, 4 red - or idle - containers behind the catalog.js module's designated unicode icon (ucon) signaling the origin of the output line with the status of the CLI to Target SQL connection pool below, behind the ucon (α) that indicates the message originated in the sqldb.js module.
bill@HOST:~/anonym$ npm start Β > anonym@0.1.4 start /home/bill/anonym > node anonym.js Β π§ ββββ ββββββ α Pool open: db master in sql container 9d51eb524061 Β anonym >
If the host is booted or the Docker daemon restarted or the Target Container has been previously stopped by the user - and assuming the same Catalog artifacts as shown above - the launch changes slightly, by extending an offer to restart the Target Container if it is discovered to be idle at CLI start:
bill@HOST:~/anonym$ npm start Β > anonym@0.1.4 start /home/bill/anonym > node anonym.js Β π§ ββββ βββββ π§Ώ Target SQL Server not started: 9d51eb524061 ? Start Target Container now? (Y/n)
Reviewing the complete collection of Maps in entirety can be a daunting amount of JSON data, but is always possible. Each Image needs around 15 display lines and each container over 50. Give it a try, and take a moment to review the content details:
anonym > catalog all
More usable summary views are the is the default shown by the catalog command:
anonym > catalog Images Pulled 56655b462301 (v.15.0.4013.40) mcr.microsoft.com/mssql/server:latest a8343d3ce21c (v.14.0.3281.6) mcr.microsoft.com/mssql/server:2017-latest d273eadd9675 (v.15.0.4003.23) mcr.microsoft.com/mssql/server:2019-latest ba266fae5320 (v.15.0.2070.41) Containers Created f0eeeae7a2ab v.15.0.4013.40 /infallible_lederberg started 2020-03-05T14:09:57.895652979Z 9d51eb524061 v.15.0.4003.23 /edgy started 2020-03-05T12:16:40.51643927Z c41e3ae7719b v.15.0.2070.41 /suspicious_chaum stopped 2020-01-28T04:01:03.54556645Z cf030328e0aa v.15.0.2070.41 /infallible_yonath started 2020-03-05T14:25:16.348549127Z 57f9ee49483d v.15.0.2070.41 /SQL2 stopped 2020-01-30T04:59:02.289279034Z 5004ba923fda v.15.0.2070.41 /SQL1 started 2020-03-05T14:09:30.135737069Z Pools Opened cf030328e0aa using 'master' as 'sa' on port 38793 5004ba923fda using 'undefined' as 'sa' on port 46769 anonym >
Another view of the catalog, perhaps most interesting when defining database connections, shows both the virtual network IP address and the host port mapping of each running SQL Container. Data connections can be made to the IP address or Container Id over the VNET to the well-known 1433 port or as local instances on the host using the port shown. By default, the CLI connects through the docker assigned local port of the host. This port mapping is pseudo-randomly generated by the CLI's get-port dependency and assigned at the time a container is created.
anonym > catalog network Map { 'f0eeeae7a2ab /infallible_lederberg' => { bridge: '172.17.0.4:1433', port: '39021' }, '9d51eb524061 /edgy' => { bridge: '172.17.0.2:1433', port: '43527' }, 'cf030328e0aa /infallible_yonath' => { bridge: '172.17.0.5:1433', port: '38793' }, '5004ba923fda /SQL1' => { bridge: '172.17.0.3:1433', port: '46769' } } anonym >
Useful information when defining SQL Connections in SQLPad and other apps being run in the anonym or when connecting remotely to the anonym. Beware, if you do open a path to connect remotely that you have created a backdoor. This will not necessarily lead to cross-talk between the anonym Instance and another data network, but it is, like any backdoor, vulnerable to malice.
The Target is the SQL Server Container chosen by the user to be the recipient of T-SQL queries from the CLI. Any container can be set as the Target, however only one Target at a time is allowed. If a Target has been set previously, it is highlighted in the list of choices and has the focus. When no previous Target is detected by the code, the list appears with the first list item highlighted.
anonym > connection target ? Target for CLI originated SQL queries (Use arrow keys) 5004ba923fda 57f9ee49483d 9d51eb524061 c41e3ae7719b β― cf030328e0aa
Selecting a Target does not change the state of the newly Targeted Container, however the user is invited to start the Target if it is not running. The Target Container must be running before queries can submitted to that Target.
anonym > connection target ? Target for CLI originated SQL queries cf030328e0aa π§ ββββ βββββ π§Ώ Target SQL Server not started: cf030328e0aa ? Start Target Container now? (Y/n)
Termination describes the act of sending the query now in the Batch cache to a SQL Server. These
CLI commands terminate the Batch (all lower case) once entered:
go - mssql.Request.query() via tedious.js
run - mssql.Request.batch() via tedious.js
sqlcmd - mssql-tools (loosely speaking, this is ODBC)
stream - mssql.Request.batch() via tedious.js with the response handled through events
- Upon termination, the query in the Batch cache is submitted to the Target's Query Engine.
- Results returned through 'tedious.js' are displayed as JSON.
- Results returned from sqlcmd are displayed in tabular format.
- Only Terminators can/will execute a query from cache. There is also a pseudo-terminator, expose in batch issql (aka ? issql), that, from the console vantage, works just like the real terminators. The big difference is batch issql submits the query wrapped between SET NOEXEC ON and SET NOEXEC OFF , thus getting the same syntax checking as a query terminated for execution without actually risking a touch of the data. This pseudo-terminator can be enabled for all queries (recommended) by setting the value of config.cli.checkSyntax to true. When true, each terminated query is check for syntax errors and will return the first error with no risk of corrupting data by running a multi-statement batch with syntax errors that fails in the middle of a data manipulation.
T-SQL entered at the command prompt accumulates each line entered into a Batch Cache. Consider an example. (And notice the missing comma after the database_id column in the select list, we will fix that in a moment.):
anonym > SELECT name, anonym > database_id anonym > USER_NAME(owner_sid) anonym > FROM sys.databases anonym >
When the query is terminated - here we use the go terminating command - and mistakes like this missing comma bug are found, SQL Server returns the error message, the CLI handles it and keeps going:
anonym > go β (sqldb) SQL Server error Number: 102 Class: 15 State: 1 Line: 4 Message: Incorrect syntax near 'owner_sid'. Β β (sqldb) error Error: (query) SELECT name, database_id USER_NAME(owner_sid) FROM sys.databases at Promise (/home/bill/anonym/lib/sqldb.js:78:33) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7) Β anonym >
But the Batch cache is left as is when an error occurs. The user then determines whether to edit the Batch or to simply clear the cached query. During the edit, the CLI event loop will be blocked while the query is opened in the editor:
anonym > batch edit
Alternately, the batch command's alias (in this case, a shortcut) can be used:
anonym > ? edit
And, in either case, the CLI's TAB-key based auto-complete functionality may be invoked
anonym > b[TAB] e[TAB] - or - anonym > ? e[TAB]
When changes are 'saved' within the editor and the editor is exited, the Node.js event loop resumes. To review the changes any time the event loop is active:
anonym > ?
SELECT top 1 name,
database_id,
USER_NAME(owner_sid)
FROM sys.databases
anonym >
Or Terminated again to produce the sought result set at the CLI:
anonym > go { name: 'master', database_id: 1, '': 'dbo' } rowsAffected: 1 anonym >
Successful query execution logs the query and rowsAffected to history then truncates the Batch cache to prepare it for a new query:
anonym > ?
nothing in cache
anonym >
The './lib/queries.js' module contains only a JavaScript export object of named T-SQL queries. Review, add, remove, and modify the queries in this file, then import the queries into the CLI's query store document database.
anonym > query import
From this point, any query in the store can be quickly loaded into the CLI's Batch cache:
anonym > query ? query to load into the Batch (Use arrow keys) β― advancedOptions badSyntax_invalidObject badSyntax_misspelling badSyntax_noT-SQL badSyntax_null badSyntax_undefined badSyntax_whitespace (Move up and down to reveal more choices)
Loading a stored query into the Batch cache replaces anything that was previously held in the Batch cache.
To effect changes to one or more stored queries, the user can edit the './lib/queries.js' file, save the changes and then re-import into the store (it's an upsert).
anonym > query develop πΈ (spawnTask) Submitted bashCommand: code /home/bill/anonym/lib/queries.js anonym > query import query store is now overwritten with definitions from queries.js anonym >
Or edit a stored query and then sync the changes back to the './lib/queries.js' module
anonym > query edit ? Query to edit badSyntax_misspelling anonym > query sync query store has overwritten queries.js anonym >
Or load the query to the Batch cache first, then edit only the copy of the query in cache
anonym > query load ? query to load into the Batch badSyntax_invalidObject anonym > ? edit anonym > ? SELECT top 1 * FROM sys.configurations anonym >
Keeping the './lib/queries.js' module and the store synchronized helps to assure that the store is accurately documented within source control.
The user can copy and grow his or her collection of T-SQL Scripts into the './scripts' sub-folder. From here, scripts can me Developed, debugged, tested and run on any SQL Instance in the Catalog. While the query store is more convenient for brief queries, Multi-statement scripts are often easier to work with in files. Scripts with DDL, more than one query, deep join complexity, SQLCMD variables or multiple batches that may require changes between uses were meant to be in scripts or stored procedures. Scripts can be added, changed or removed from the folder, which is under source control, at any time and opened for edit on demand. Only Scripts having the '.sql' extension are recognized by the script command. Edit a script in the configured IDE (config.ide) asynchronously:
anonym > script develop
or edit a script in the configured editor (config.editor) - state-fully blocking the event loop:
anonym > script edit
or use your preferred tactics external to the CLI app. In all scenarios, changes are in effect once saved - and source control will pick-up the changes.
Like the stored queries described above, scripts can also be edited once loaded to the Batch cache without changing the file contents.
Load a script to the Batch Cache:
anonym > script ? Select script to load to Batch cache defaultTrace dumps Formatmessage β― linkedServerTest restoreAdventureWorks restoreWideWorldImporters ringBufferSummary (Move up and down to reveal more choices)
Edit only the copy of the script now in the Batch cache:
anonym > script ? Select script to load to Batch cache linkedServerTest anonym > batch edit