ipsql

IPSQL

IPSQL is a decentralized database that can run in IPFS. It implements the SQL schema, data model, and query language.

This project is pre-alpha and under heavy active development. Do not use in production, breaking changes will land without notice.

The JS API is currently considered internal until the churn in the code base dies down.

• Features
  • Traditional SQL CREATE, UPDATE, SELECT, WHERE, etc.
  • Deltas and proofs for every database operation and query.
    • You can replicate the data for only a single query as hash linked blocks.
    • Every mutation creates deltas to prior states and even query deltas can be replicated.
  • Optional Encryption
  • DAG Tables (JSON-like unstructured objects as rows, with column indexing an SQL queries still available)

This project is built using a tree building technique implemented in the chunky-trees module.

CLI

The primary way to interact with IPSQL right now is via the command line. You can install it with npm or use npx to run it without installing locally.

$ ipsql help
ipsql [command]

Commands:
  ipsql query <uri> <sql>    Run IPSQL query
  ipsql repl <uri>           Run local REPL
  ipsql create <sql>         Create a new database
  ipsql write <uri> <sql>    Mutate an existing SQL database
  ipsql import <subcommand>  Import CSV files
  ipsql keygen <subcommand>  Generate keys for encryption

Options:
  --help     Show help                                                 [boolean]
  --version  Show version number                                       [boolean]

• CLI
  • Importing CSV files
  • Importing JSON files (into DAG tables)
  • CREATE and INSERT
  • Encrypting and Decrypting databases

SQL Feature Checklist

• CREATE
  • DATABASE (it isn't clear how we would want to implement and support this yet)
  • TABLE
    • INTEGER
    • VARCHAR(size)
    • FLOAT
    • DATE
    • TIME
    • DATETIME
    • BLOB (should use the FBL, values smaller than 32b will be inline binary, below 1MB should be a single raw block link, anything else is a full tree FBL as a stream)
    • TEXT (may never support, according to spec this is upt 2GB of string data so it's hard to figure out what the inline vs linking rules would be. Instead, using VARCHAR as the inlined string and BLOB the probably linked type)
• ALTER TABLE
• INSERT
  • ROWS INSERT INTO table_name VALUES ( 'test' )
  • COLUMNS INSERT INTO table_name ( column_name ) VALUES ( 'test' )
• UPDATE
  • UPDATE SET w/o WHERE
  • UPDATE SET WHERE
• DELETE
  • DELETE w/o WHERE (deletes table)
  • DELETE WHERE
• SELECT
  • * SELECT * FROM table_name
  • COLUMNS SELECT column1, column2 FROM table_name
  • ROWID meta column
  • TOP
  • MIN
  • MAX
  • COUNT
  • AVG
  • SUM
  • LIKE pattern
  • WHERE
    • AND
    • OR
    • ASC, DESC
    • basic comparison (=, >, <, >=, <=)
    • <>
    • GROUP BY
    • ORDER BY
    • BETWEEN
    • LIKE
    • IN
    • NOT
    • IS NOT NULL, IS NULL
  • JOIN
  • ORDER BY
  • GROUP BY
  • HAVING
  • UNION

IPLD Schema

type Column {
  schema &Map
  index nullable &DBIndex
}
type Columns { String: Column }

type Table struct {
  columns Columns
  rows nullable &SparseArray
}
type Tables { String: Table }

type Database struct {
  tables Tables
}