Search code examples
sqlitewebstormjetbrains-idedatagrip

With the JetBrains IDE WebStorm, how do I create a database using SQL DDL, (featuring an `Access is denied.` error)

The question:

My ultimate goal with this question is to figure out how to create and populate a database using an SQL script. My secondary goals are to do so in a way that is as compatible with the infrastructure as code process and to do so without the use of Node.js.

The SQL script I would like to run is as follows:

/* create_table.sql : Creates the basic table structure */
CREATE TABLE Quotes(quote TEXT, author TEXT, year INTEGER);
INSERT INTO Quotes VALUES('Life is short', 'Unknown', 1902);

I would like this to operate on a database file "quotes.db". Thus, my final question is as follows: Using my JetBrains IDE, how do I execute this .sql script?

What I've tried:

When I try to run this script on WebStorm, I run into the following errors:

1.) Error: Missing target data sources. If I don't define a target data source / schema, I get the missing target data sources error/warning. I can fix that error by creating a new SQLite data source on the Data Sources and Drivers UI wizard, pictured below.

Data source configuration

2.) This leads me to my second error: opening db: 'quotes.db': Access is denied.. This error remains the same whether I don't have any quotes.db file, or whether I have a stub file created by SQLite, or a quotes.db file that is populated with a blank table, and remains true even after renaming/replacing the file (in case of the presence of some Windows file-locking jank).

3.) The picture below just shows that I also tried creating a DDL Data Source from the Data Sources and Drivers UI wizard, and it seems to be able to parse out and understand my SQL script; however, when I go to run the script, I can't select the DDL Data Source and only have the option of picking the other one.

DDL Data Source shows promise

Why can't I use a DDL Data Source and run scripts with it? Why does my scripts.db data source say "access is denied"?

Software Versions:

Database Tools and SQL plugin version 202.6397.88

WebStorm 2020.2, Build #WS-202.6397.88, built on July 24, 2020

Runtime version: 11.0.7+10-b944.20 amd64, VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o., Windows 10 10.0

Solution

    1. DDL data source is not an option here because it can contain no data, it is just a data source based on your SQL files. There is no database, it cannot store data.

    2. SQLite should work here, but in your case it seems that you have no access to the working folder of WebStorm, please check it with your administrator. The ticket to upvote and follow: https://youtrack.jetbrains.com/issue/DBE-6729