AnalystSQLTool README

As analysts, we often only have read access to most databases. However, the requests we received are too often so complicated that version control and cross reference are so important for better efficiency. That's why we start to pick up code editors like vscode.

Bad news is that most SQL tools here are for developers, requiring full access and all details of the database connection. But analysts also need autocompletions! That's why I develop this plugin.

Video introduction: https://youtu.be/eZAF6pFnuYY

Features

Prepare a version of offline schema json file and you will have the autocompletion when you are writing SQL queries in VScode.

You will have completion suggestions after from for table names!
You will have completion suggestions after select and where for column names!
You will have completion suggestions for enums in a categorical column!
You will be able to create, insert and delete frequently used code snippets via Command Palette!
You will have completion suggestions for your custom snippets!

Requirements

The name of your schema file doesn't matter. But keep in mind that it MUST BE a json file following the below format.

{
  "tables": [
    {
      "name": "users",
      "columns": [
        { "name": "id", "type": "integer" },
        { "name": "username", "type": "varchar" },
        { "name": "email", "type": "varchar" },
        { "name": "password_hash", "type": "varchar" },
        { "name": "created_at", "type": "timestamp" },
        { "name": "last_login", "type": "timestamp" }
      ]
    },
    {
      "name": "orders",
      "columns": [
        { "name": "id", "type": "integer" },
        { "name": "user_id", "type": "integer" },
        { "name": "total_amount", "type": "decimal" },
        { "name": "status", "type": "varchar" , "uniqueValues": [
            "active",
            "inactive",
            "suspended"
          ]},
        { "name": "order_date", "type": "date" }
      ]
    }
  ]
}

Add in more tables as you need!

Extension Settings

Designate your schema file by either using "Analyst SQL Tool: Set Schema Path" in the command palette or change "analystsqltool.schemaPath" in settings.json.

Feedback

If you have any feedback about your experience, any advice on the roadmap or encounter any issue, join Discord: https://discord.gg/tsR2cEgJNv

Roadmap

[ ] support multiple json schema file
[ ] connect and fetch back the json format schema for commonly used databases
[ ] add in sample data in the schema and provide local preview of execution results if sample data is ready

Release Notes

0.2.0 -- MVP Version

2024-11-05 Fixed bugs and refactor the entire codebase, finally finish the MVP version of this extension

0.1.0 -- At Least There Is An Extension

2024-08-25 Release the first version.

AnalystSQLTool

bearInTheRoad