# Sqlite

- Page: https://shared.btbytes.com/p/sqlite
- Date: 2018-09-11
- Tags: database, sqlite

I wrote an article about SQLite for DeveloperIQ magazine published in Jan 2005 -- [Move Over MySQL, SQLite is here!!](https://www.btbytes.com/developeriq/diq-pradeep-sqlite.html)

---

[Query Parquet files in SQLite | Colin Dellow](https://cldellow.com/2018/06/22/sqlite-parquet-vtable.html)

Importing csv into sqlite: [see this page for extended usage](https://www.btbytes.com/amazon)

```sql
sqlite> .import myfile.csv mytable
```

[SQLite as a document database](https://dgl.cx/2020/06/sqlite-json-support) using [Generated Columns](https://www.sqlite.org/gencol.html) and [The JSON1 Extension](https://www.sqlite.org/json1.html)


## Tutorials

[SQLite Date & Time - How To Handle Date and Time in SQLite](http://www.sqlitetutorial.net/sqlite-date/) (The site sqlitetutorial.net seems to have handy reference for common tasks with sqlite.)

[SQLite as a Collaborative & Multiplayer Application File Format - YouTube](https://www.youtube.com/watch?v=T1ES9x8DKR4)

[Optimizing SQLite for servers](https://kerkour.com/sqlite-for-servers)

[60K RPS out of sqlite on a $5 VPS](https://x.com/meln1k/status/1813314113705062774)


## Django

- [Optimal SQLite settings for Django – Giovanni Collazo](https://gcollazo.com/optimal-sqlite-settings-for-django/); June 2024.

## Tools around SQLite

### [sqlpkg](https://antonz.org/install-sqlite-extension/)

The list of packages are here - <https://sqlpkg.org>.

#TODO SQLPkg is [written in go](https://github.com/nalgeon/sqlpkg-cli). install it manually.

### [Sqlite-utils](https://sqlite-utils.readthedocs.io/en/stable/)

can do things like:

* Running queries and returning JSON, CSV, markdown table
* Inserting JSON data: `echo <JSON> | sqlite-utils insert dogs.db dogs -
* Inserting CSV data: `sqlite-utils insert dogs.db dogs docs.csv --csv`
* Insert-replacing data.

### sqlite-http

[Introducing sqlite-http: A SQLite extension for making HTTP requests / Alex Garcia \| Observable](https://observablehq.com/@asg017/introducing-sqlite-http)

### Sqlean

[nalgeon/sqlean: The ultimate set of SQLite extensions](https://github.com/nalgeon/sqlean)

* crypto: hash functions
* define: user-defined functions and dynamic sql
* fileio: read and write files
* fuzzy: fuzzy string matching and phonetics
* ipaddr: IP address manipulation
* json1: JSON functions
* math: math functions
* regexp: regular expressions
* stats: math statistics
* text: string functions
* unicode: Unicode support
* uuid: Universally Unique IDentifiers
* vsv: CSV files as virtual tables


### Pocketbase

**Pocketbase** is an open source backend that can be used to build API applications.

* single binary download
* written in [Go](https://shared.btbytes.com/p/golang)
* embedded database ([SQLite](https://shared.btbytes.com/p/sqlite))
* built in user and files managment
* built in admin UI
* simple REST UI


### Sqlsync

[orbitinghail/sqlsync](https://github.com/orbitinghail/sqlsync) SQLSync is a collaborative offline-first wrapper around SQLite. It is designed to synchronize web application state between users, devices, and the edge.

Example use cases

- A web app with a structured file oriented data model like Figma. Each file could be a SQLSync database, enabling real-time local first collaboration and presence
- Running SQLSync on the edge with high tolerance for unreliable network conditions
- Enabling optimistic mutations on SQLite read replicas

### Soul

[soul | 🕉 A SQLite REST and realtime server](https://thevahidal.github.io/soul/), written in node.js;  Run `soul -d sqlite.db -p 8000` and it’ll start a REST API on [http://localhost:8000](http://localhost:8000/) and a Websocket server on [ws://localhost:8000](ws://localhost:8000/)


----

See also: [duckdb](https://shared.btbytes.com/p/duckdb), [til/sqlite-writable_schema](https://www.btbytes.com/til/sqlite-writable-schema), [code/sqlite-csv-loading-using-virtual-table-extension](https://www.btbytes.com/code/sqlite-csv-loading-using-virtual-table-extension)