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.

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

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

• Open the Command Prompt (Cmd/Ctrl + Shift + P)

• Select "SQLite: Open Database"

• Select "Choose database from file" in the prompt

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

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

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

• View the query results in split pane mode