sqlite

- get list of databases : `.database` or `.databases`
- get list of tables : `.tables`
- get schemas : `.schema` , for particular table : `schema <TABLE_NAME>`
- to show field name when using select query, make sure to switch headers on by `.headers on`


ddl commands

**alter tables**
- `ALTER TABLE old_table_name RENAME TO new_table_name;`
-  `ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;`
-  `ALTER TABLE table_name ADD COLUMN column_name datatype;`
-  `ALTER TABLE table_name DROP COLUMN column_name;`
- `ALTER TABLE songdata add COLUMN is_bookmarked INTEGER DEFAULT 0`
- `ALTER TABLE Employees ALTER COLUMN Status SET DEFAULT 'Active'` (requires new version)
- `ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL` (requires new version)
**create tables**
- foreign key constraint
 `FOREIGN KEY (lyrics_url) REFERENCES lyricsUrl(lyrics_url)`
&nbsp;

dml ones

  **timestamp**
  https://www.sqlite.org/lang_datefunc.html
  ```sql
  CREATE TABLE event_log (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    
    -- Option A: Stores as human-readable TEXT (UTC time)
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    
    -- Option B: Stores as a Unix INTEGER timestamp (Seconds since 1970)
    unix_timestamp INTEGER DEFAULT (strftime('%s', 'now'))
);
  ```

```sql
insert into event_log (id,event_name, unix_timestamp)
values(1,'event1',unixepoch());
```

&nbsp;

upsert
```sql
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (conflict_column)
DO UPDATE SET column1 = excluded.column1, column2 = excluded.column2;   
```

&nbsp;

auto increment
only possible for integer columns declared as primary key
```sql
id INTEGER PRIMARY KEY AUTOINCREMENT
```