# Working with SQLite

SQLite is a lightweight file based database that supports SQL queries. Create a new SQLite database in the container and execute some commands against it.

{% tabs %}
{% tab title="Exercise" %}

* Open the integrated terminal and browse to "/"
* Create a folder called "data"
* Move into that folder and create a new SQLite database with `sqlite3 lamp.db`
* Connect to the database with the SQLite Extension
* Create a new table called "colors"
* Insert a few records
* Select the records back out
  {% endtab %}

{% tab title="Answer" %}

* Open the integrated terminal with (**Cmd/Ctrl + \`**)
* Move to the root directly of the container

```
cd /
```

* Create a folder called "data"

```
mkdir data
```

* Move into that new folder

```
cd data
```

* Create a new SQLite database called "lamp"

```
sqlite3 lamp.db
```

* Display the database that was created with the ".databases" command

```
.databases
```

* Type `.quit` to exit the SQLite prompt

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm77jyxsqT6tKrrJ2xK%2Fimage.png?alt=media\&token=0facb26d-0c2e-4453-b62e-75a768e8e8ae)

* Open the Command Prompt (**Cmd/Ctrl + Shift + P**)
* Select "SQLite: Open Database"

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm77vlDQCA5PvnFAloB%2Fimage.png?alt=media\&token=eabcfd40-216e-4722-8f55-463559950cd6)

* Select "Choose database from file" in the prompt

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm784og5vRPgrFZY5IW%2Fimage.png?alt=media\&token=5d3c8213-74ed-4d10-ac00-d4c43891c455)

* Type "/data/lamp.db" in the prompt

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm78GZ_UtJQ8dbEtPU9%2Fimage.png?alt=media\&token=8d19fdfe-de55-4963-8216-f1dde00b5672)

* Open the Explorer view (**Cmd/Ctrl + Shift + E**)
* Notice there is now a "SQLite Explorer" view
* Right-click the lamp.db database and select "New Query"

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm78fLyz0qw4Gdx9xRk%2Fimage.png?alt=media\&token=1c921da3-b0ed-4cb4-9fd0-cd1e0314a721)

* Use the commands found in "sqlite.sql" in the project to...
  * Create a table
  * Insert a color
  * Select a record

```
-- SQLite
CREATE TABLE colors (
 id INTEGER PRIMARY KEY,
 color TEXT NOT NULL
);

INSERT INTO colors (id, color) VALUES (1, 'Blue')

SELECT * FROM colors
```

* Each block must be run by itself. Highlight the block to run.
* Open the Command Palette and select "SQLite: Run Selected Query"

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm79aeTCTDCiuUKkBr_%2Fimage.png?alt=media\&token=7d4ecfc2-3105-4451-afe8-372eb652c57b)

* View the query results in split pane mode

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm6wtOREb06-9RN3v91%2F-Lm79jSN1pHvblY3mHyg%2Fimage.png?alt=media\&token=b2458bc7-142a-48d2-af5a-c59dd2016861)
{% endtab %}
{% endtabs %}