Writing a schema

Where to write a schema

Write a file schema.sql at the top level of your projects/db-project directory.

This should contain all your CREATE TABLE statements.

You can also load pre-existing data here with .import commands. Or you can put those elsewhere, either in a separate .sql file or in your Java code.

How to write your schema

drop table if exists;

create table students (
  student_id integer,
  name text,
  grade integer
);

-- etc.

This resets your database when you run it.

For all the details see: https://www.sqlite.org/lang_createtable.html

Alternatively

create table if not exists students (
  student_id integer,
  name text,
  grade integer
);

-- etc.

This makes your schema.sql idempotent: running it twice is the same as running it once.

Loading your schema

sqlite3 db.db < schema.sql

db.db is the filename of your database. sqlite3 is the database program.

It can take SQL on the command line: sqlite3 db.db 'select * from students'

Or it can take SQL on its standard input as shown above.

Loading data

.mode tabs
.import data.tsv tablename

.mode csv
.import data.csv tablename

Use the --skip 1 option to .import to skip a header row if the table is already created and the first row of the file is headers not data.

More details: https://www.sqlite.org/cli.html#csv

Interacting with your database

Remember you can also run sqlite3 db.db with no arguments to get an interactive promt where you can run sql.

$ sqlite3 db.db
-- Loading resources from /Users/peter/.sqliterc
SQLite version 3.49.0 2025-02-06 11:55:18
Enter ".help" for usage hints.
sqlite>