ICSQLite

ICSQLite is a cloud SQLite database on Internet Computer and provides SDK for developers to use.
Our goal is to help developers quickly migrate web2 applications to Internet Computer.

Usage

In your Cargo.toml:

[dependencies]
ic-sqlite = "0.1.0"

Limitations & Suggestions

Limited by the total number of cycles of a call, if the number of rows retrieved by a single SQL query exceeds a certain amount, the call will crash.

SQL statement suggestions

• Strictly follow the rules of database optimization
• Index building must be an empty table
• Where query must be filtered for primary key or index field
• Less use NOT,!=,<>,!<,!> NOT EXISTS, NOT IN, NOT LIKE, OR, they will ignore the index and cause a full table scan

Performance benchmarks for SQL commands

SQL commands	performance counter 1w single table data	performance counter 10w single table data	performance counter 50w single table data	performance counter 100w single table data
create table	1194347	1433766	2565609	4066020
create index (empty table)	884588	1122419	2241730	3601724
count	209847	2995943	15183853	30392494
insert	350256	349635	351731	355381
select (where primary key)	265363	265960	265345	268112
select (where index field)	312389	314594	314666	319276
select (where like field)	178263088	1784671532	limit for single message execution	limit for single message execution
update (where primary key)	385492	389192	391599	394111
update (where index filed)	239384	237908	237993	240998
delete (where primary key)	429190	259541	419615	423064

IC Canister Simple example usage

Data migration suggestions & Debugging

• Provide an interface for executing sql statements with super-management authority
• Export standard sql statements for offline data and upload in batches
• Debugging Get the data by running the sql statement through the super-management interface