Allow SQL DATABASE Interaction

[krstp]

Check for existing issues

• Completed

Describe the feature

Many of us use cloud (or local) database (DB) queries quite a bit. Would be great to have an option (probably under CMD+SHIFT+P?) to run the in-editor tab selected (with SHIFT+ARROWS or via MOUSE-SELECT) SQL entry through any of the DB provides (GCP-BigQuery/AWS-Redshift/MS-Azure, etc.).

If applicable, add mockups / screenshots to help present your vision of the feature

For example:

after CMD+SHIFT+P one would select RUN QUERY via BigQuery and this could open a default (or perfectly user specified) web-browser running the query, in an example of GCP, presenting the result in the BigQuery Preview within the web-browser window.

[jansol]

Why a web browser? Wouldn't a dock in Zed be more convenient? Like the terminal pane that you can pop out from the button on the bottom right.

[krstp]

Well, web browser, because by principle data warehouse such as BigQuery stores millions or billions of entries, so if my query is about to return heaps of row and column data, probably I would want to have some kind of fail mechanism before Zed crashes. For this reason I would say reference/opening to web browser is desirable solution.

If Zed developers can provide view within Zed that would be great, but I think probably hard to accomplish.

On the above two notes... if we really want to run wild ideas loose... Zed could have embedded Chromium engine (lightweight like Brave) that would allow web UI to be viewed in Zed tab or pane, but I think this is a bit too much labour for the current dev stage.

Either way, link or redirection to web browser would do the job just fine!

[hovsater]

This seem like it would be a great candidate for an extension. Would you mind upvoting zed-industries/zed#5269? 🙂

[krstp]

Indeed. Done. Thanks.

[Angelk90]

It would be nice if, as for example with markdown, the .sql files would be able to see a preview of the relationships of the various tables like dbdiagram does for example which allows you to see the relationships from the converted code.

Link: https://dbdiagram.io/d/66646fb19713410b05165782

Code example db.sql :

-- Creazione del database
CREATE DATABASE my_database;
USE my_database;

-- Creazione della tabella user
CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Creazione della tabella organization
CREATE TABLE organization (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Creazione della tabella permission
CREATE TABLE permission (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Creazione della tabella role
CREATE TABLE role (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Creazione della tabella user_session
CREATE TABLE user_session (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- Creazione della tabella role_permission
CREATE TABLE role_permission (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES role(id),
    FOREIGN KEY (permission_id) REFERENCES permission(id)
);

-- Creazione della tabella user_org_role
CREATE TABLE user_org_role (
    user_id INT NOT NULL,
    organization_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, organization_id, role_id),
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (organization_id) REFERENCES organization(id),
    FOREIGN KEY (role_id) REFERENCES role(id)
);