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