This library is a zero-overhead, ergonamic wrapper over sqlite C api.
```rust // creates a database file 'dbfile.db' if it does not exists. let database = Database::open("dbfile.db")?;
// executes the query and creates a 'user' table database.execute(r#" create table if not exists user ( id integer primary key autoincrement not null, age int, name text, weight real );"#, ())?;
// inserts a new user record.
// binds the fields to '?' .
// note that only these types are allowed for bindings:
// int32, i64, f64, &str, &[u8]
// use &[u8]
to store blob data.
database.execute(
"insert into user(age, name, weight) values(?, ?, ?)",
(29, "amin", 69.5)
)?;
let name = String::from("negar");
database.execute(
"insert into user(age, name, weight) values(?, ?, ?)",
(26, name.as_str(), 61.0)
)?;
// slects from user table on a condition ( age > 27 ), // and executes the closure for each row returned. database.for_each( "select name, age, weight from user where age > ?", (27), |name: String, age: i32, weight: f64| { dbg!(name, age, weight); } )?;
// selects the count() from user table // you can extract a single culumn single row result to: // i32, i64, f64, String, Box<[u8]> let count: i32 = database.collect("select count() from user", ())?;
// you can also extract single row with multiple columns let amin: (i32, String, f64) = database.collect( "select age, name, weight from user where name = ?", ("amin") )?;
// this also works, the returned value will be automatically converted to String let str_count: String = database.collect("select count(*) from user", ())?;
```
You can pass additional open flags to SQLite:
toml
[dependencies]
sqlite3-sys = "*"
```rust
use rsqlite::{ffi, Database};
let flags = ffi::SQLITEREADONLY; let database = Database::openwith_flags("dbfile.db", flags)?;
// now you can only read from the database let n: i32 = database.collect( "select a from table where something >= ?", (1))?; ```
It is possible to retain and reuse statments, this will keep the query plan and might increase the performance significantly if the statement is reused. ```rust let mut statement = database.prepare("select age from user where age > ?")?; // Database methods are simply implemented in terms of statements. statement.for_each((27), |age: i32| { dbg!(age); })?;
let age: i32 = database.prepare("select count(*) from user where age > ? limit 1")? .collect((200))?; ```
If you have NULLABLE columes, you can use Option<T>
to pass and collect the values.
``rust
// use
None` to insert NULL values
database.execute("insert into user(name, age) values (?,?)", (None::<&str>, 20))?;
// use Option
// an empty result set, would also be treated as None
let name : Option
implsit type convertions in sqlite follow this table:
for example, if you collect a NULL
column as i32
, you'll get 0
.
|Internal Type|Requested Type|Conversion
|-------------|--------------|----------
|NULL |i32/i64 |Result is 0
|NULL |f64 |Result is 0.0
|NULL |String |Result is empty String::new()
|NULL |Box<[u8]> |Result is empty Box::new([])
|INTEGER |f64 |Convert from integer to f64
|INTEGER |String |ASCII rendering of the integer
|INTEGER |Box<[u8]> |Same as INTEGER->String
|FLOAT |i32/i64 |CAST to INTEGER
|FLOAT |String |ASCII rendering of the float
|FLOAT |Box<[u8]> |CAST to [u8]
|TEXT |i32/i64 |CAST to i32/i64
|TEXT |f64 |CAST to f64
|TEXT |Box<[u8]> |No change
|BLOB |i32/i64 |CAST to i32/i64
|BLOB |f64 |CAST to f64
|BLOB |String |No change
You can use transactions with begin
, commit
and rollback
commands.
```rust
database.execute("begin", ())?; // begin a transaction ... let mut statement = database.prepare("insert into user(name, age) values (?, ?)")?; // insert 10 users using a prepared statement for age in 0..10 { let name = format!("user-{}", age); statement.execute((name.as_str(), age))?; } database.execute("commit", ())?; // commit all the changes
database.execute("begin", ())?; // begin another transaction ... database.execute("delete from user where age > ?", (3))?; database.execute("rollback", ())?; // cancel this transaction
let sumage : i32 = database.collect("select sum(age) from user", ())?; assert!(sumage == 45); ```
Use &[u8]
to store and Box<[u8]>
to retrive blob data.
```rust database.execute("create table user (name TEXT, numbers BLOB)", ())?;
let numbers = vec![1, 1, 2, 3, 5]; database.execute("insert into user values (?, ?)", ("amin", numbers.asslice()))?; let storednumbers : Box<[u8]> = database.collect("select numbers from user where name = ?", ("amin"))?; assert!(numbers.asslice() == storednumbers.as_ref()); ```
MIT license - http://www.opensource.org/licenses/mit-license.php