> For the complete documentation index, see [llms.txt](https://burkeholland.gitbook.io/vs-code-can-do-that/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://burkeholland.gitbook.io/vs-code-can-do-that/exercise-7-working-with-data/working-with-sqlite.md).

# 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

![](/files/-Lm77jyxsqT6tKrrJ2xK)

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

![](/files/-Lm77vlDQCA5PvnFAloB)

* Select "Choose database from file" in the prompt

![](/files/-Lm784og5vRPgrFZY5IW)

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

![](/files/-Lm78GZ_UtJQ8dbEtPU9)

* 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"

![](/files/-Lm78fLyz0qw4Gdx9xRk)

* 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"

![](/files/-Lm79aeTCTDCiuUKkBr_)

* View the query results in split pane mode

![](/files/-Lm79jSN1pHvblY3mHyg)
{% endtab %}
{% endtabs %}