this is a rust tool for generate sql and table struct
based on sqlx and sql_builder
it support mysql and postgres
for more see examples/pg_examples.rs
use sql_wrapper::generator::Generator;
use sql_wrapper::pg_generator;
#[tokio::main]
async fn main() {
// it will generate a file table_name.rs whit table struct, get 4 insert functions
let gen = pg_generator::PgGenerator{};
let conn_url = "postgres://postgres:123456@localhost/jixin_message?&stringtype=unspecified";
let table_name = "test_table";
let result = gen.gen_file(conn_url, table_name).await;
println!("result:{:?}", result)
}after run the code. it will generate a file test_table.rs
content:
#[derive(sqlx::FromRow, Debug, PartialEq)]
pub struct TestTable {
id: i64,
b1: bool,
b2: Option<bool>,
c1: String,
c2: Option<String>,
i4: i32,
i41: Option<i32>,
r1: f32,
r2: Option<f64>,
d1: f64,
d2: Option<f64>,
t1: String,
t2: String,
t3: Option<String>,
t4: Option<String>,
byte1: Option<Vec<u8>>,
interval1: Option<sqlx::postgres::types::PgInterval>,
big1: Option<sqlx::types::Decimal>,
big2: Option<sqlx::types::Decimal>,
ts1: chrono::NaiveDateTime,
ts2: Option<chrono::DateTime<chrono::Utc>>,
date1: Option<chrono::NaiveDate>,
date2: Option<chrono::NaiveDate>,
time1: chrono::NaiveTime,
time2: Option<sqlx::postgres::types::PgTimeTz>,
uid1: uuid::Uuid,
json1: Option<serde_json::Value>,
json2: Option<serde_json::Value>,
i5: Option<i16>,
}
pub async fn insert_returning_id(conn: &mut PgConnection, obj: TestTable) -> i64 {
let mut sql = sql_builder::SqlBuilder::insert_into("test_table");
sql.field("b1");
sql.field("b2");
sql.field("c1");
sql.field("c2");
sql.field("i4");
sql.field("i41");
sql.field("r1");
sql.field("r2");
sql.field("d1");
sql.field("d2");
sql.field("t1");
sql.field("t2");
sql.field("t3");
sql.field("t4");
sql.field("byte1");
sql.field("interval1");
sql.field("big1");
sql.field("big2");
sql.field("ts1");
sql.field("ts2");
sql.field("date1");
sql.field("date2");
sql.field("time1");
sql.field("time2");
sql.field("uid1");
sql.field("json1");
sql.field("json2");
sql.field("i5");
sql.values(&[
sql_builder::quote(obj.b1.field_to_string()),
sql_builder::quote(obj.b2.unwrap().field_to_string()),
sql_builder::quote(obj.c1.field_to_string()),
sql_builder::quote(obj.c2.unwrap().field_to_string()),
sql_builder::quote(obj.i4.field_to_string()),
sql_builder::quote(obj.i41.unwrap().field_to_string()),
sql_builder::quote(obj.r1.field_to_string()),
sql_builder::quote(obj.r2.unwrap().field_to_string()),
sql_builder::quote(obj.d1.field_to_string()),
sql_builder::quote(obj.d2.unwrap().field_to_string()),
sql_builder::quote(obj.t1.field_to_string()),
sql_builder::quote(obj.t2.field_to_string()),
sql_builder::quote(obj.t3.unwrap().field_to_string()),
sql_builder::quote(obj.t4.unwrap().field_to_string()),
sql_builder::quote(obj.byte1.unwrap().field_to_string()),
sql_builder::quote(obj.interval1.unwrap().field_to_string()),
sql_builder::quote(obj.big1.unwrap().field_to_string()),
sql_builder::quote(obj.big2.unwrap().field_to_string()),
sql_builder::quote(obj.ts1.field_to_string()),
sql_builder::quote(obj.ts2.unwrap().field_to_string()),
sql_builder::quote(obj.date1.unwrap().field_to_string()),
sql_builder::quote(obj.date2.unwrap().field_to_string()),
sql_builder::quote(obj.time1.field_to_string()),
sql_builder::quote(obj.time2.unwrap().field_to_string()),
sql_builder::quote(obj.uid1.field_to_string()),
sql_builder::quote(obj.json1.unwrap().field_to_string()),
sql_builder::quote(obj.json2.unwrap().field_to_string()),
sql_builder::quote(obj.i5.unwrap().field_to_string())
]);
sql.returning_id();
let sql = sql.sql().unwrap();
let columns:(i64,) = sqlx::query_as(sql.as_str()).fetch_one(conn).await.unwrap();
return columns.0
}
pub async fn insert(conn: &mut PgConnection, obj: TestTable) -> Result<sqlx::postgres::PgQueryResult, sqlx::Error> {
let mut sql = sql_builder::SqlBuilder::insert_into("test_table");
sql.field("id");
sql.field("b1");
sql.field("b2");
sql.field("c1");
sql.field("c2");
sql.field("i4");
sql.field("i41");
sql.field("r1");
sql.field("r2");
sql.field("d1");
sql.field("d2");
sql.field("t1");
sql.field("t2");
sql.field("t3");
sql.field("t4");
sql.field("byte1");
sql.field("interval1");
sql.field("big1");
sql.field("big2");
sql.field("ts1");
sql.field("ts2");
sql.field("date1");
sql.field("date2");
sql.field("time1");
sql.field("time2");
sql.field("uid1");
sql.field("json1");
sql.field("json2");
sql.field("i5");
sql.values(&[
sql_builder::quote(obj.id.field_to_string()),
sql_builder::quote(obj.b1.field_to_string()),
sql_builder::quote(obj.b2.unwrap().field_to_string()),
sql_builder::quote(obj.c1.field_to_string()),
sql_builder::quote(obj.c2.unwrap().field_to_string()),
sql_builder::quote(obj.i4.field_to_string()),
sql_builder::quote(obj.i41.unwrap().field_to_string()),
sql_builder::quote(obj.r1.field_to_string()),
sql_builder::quote(obj.r2.unwrap().field_to_string()),
sql_builder::quote(obj.d1.field_to_string()),
sql_builder::quote(obj.d2.unwrap().field_to_string()),
sql_builder::quote(obj.t1.field_to_string()),
sql_builder::quote(obj.t2.field_to_string()),
sql_builder::quote(obj.t3.unwrap().field_to_string()),
sql_builder::quote(obj.t4.unwrap().field_to_string()),
sql_builder::quote(obj.byte1.unwrap().field_to_string()),
sql_builder::quote(obj.interval1.unwrap().field_to_string()),
sql_builder::quote(obj.big1.unwrap().field_to_string()),
sql_builder::quote(obj.big2.unwrap().field_to_string()),
sql_builder::quote(obj.ts1.field_to_string()),
sql_builder::quote(obj.ts2.unwrap().field_to_string()),
sql_builder::quote(obj.date1.unwrap().field_to_string()),
sql_builder::quote(obj.date2.unwrap().field_to_string()),
sql_builder::quote(obj.time1.field_to_string()),
sql_builder::quote(obj.time2.unwrap().field_to_string()),
sql_builder::quote(obj.uid1.field_to_string()),
sql_builder::quote(obj.json1.unwrap().field_to_string()),
sql_builder::quote(obj.json2.unwrap().field_to_string()),
sql_builder::quote(obj.i5.unwrap().field_to_string())
]);
let sql = sql.sql().unwrap();
sqlx::query(sql.as_str()).execute(conn).await
}
pub async fn batch_insert_returning_id(conn: &mut PgConnection, objs: Vec<TestTable>) -> Vec<i64> {
let mut sql = sql_builder::SqlBuilder::insert_into("test_table");
sql.field("b1");
sql.field("b2");
sql.field("c1");
sql.field("c2");
sql.field("i4");
sql.field("i41");
sql.field("r1");
sql.field("r2");
sql.field("d1");
sql.field("d2");
sql.field("t1");
sql.field("t2");
sql.field("t3");
sql.field("t4");
sql.field("byte1");
sql.field("interval1");
sql.field("big1");
sql.field("big2");
sql.field("ts1");
sql.field("ts2");
sql.field("date1");
sql.field("date2");
sql.field("time1");
sql.field("time2");
sql.field("uid1");
sql.field("json1");
sql.field("json2");
sql.field("i5");
for obj in objs {
sql.values(&[
sql_builder::quote(obj.b1.field_to_string()),
sql_builder::quote(obj.b2.unwrap().field_to_string()),
sql_builder::quote(obj.c1.field_to_string()),
sql_builder::quote(obj.c2.unwrap().field_to_string()),
sql_builder::quote(obj.i4.field_to_string()),
sql_builder::quote(obj.i41.unwrap().field_to_string()),
sql_builder::quote(obj.r1.field_to_string()),
sql_builder::quote(obj.r2.unwrap().field_to_string()),
sql_builder::quote(obj.d1.field_to_string()),
sql_builder::quote(obj.d2.unwrap().field_to_string()),
sql_builder::quote(obj.t1.field_to_string()),
sql_builder::quote(obj.t2.field_to_string()),
sql_builder::quote(obj.t3.unwrap().field_to_string()),
sql_builder::quote(obj.t4.unwrap().field_to_string()),
sql_builder::quote(obj.byte1.unwrap().field_to_string()),
sql_builder::quote(obj.interval1.unwrap().field_to_string()),
sql_builder::quote(obj.big1.unwrap().field_to_string()),
sql_builder::quote(obj.big2.unwrap().field_to_string()),
sql_builder::quote(obj.ts1.field_to_string()),
sql_builder::quote(obj.ts2.unwrap().field_to_string()),
sql_builder::quote(obj.date1.unwrap().field_to_string()),
sql_builder::quote(obj.date2.unwrap().field_to_string()),
sql_builder::quote(obj.time1.field_to_string()),
sql_builder::quote(obj.time2.unwrap().field_to_string()),
sql_builder::quote(obj.uid1.field_to_string()),
sql_builder::quote(obj.json1.unwrap().field_to_string()),
sql_builder::quote(obj.json2.unwrap().field_to_string()),
sql_builder::quote(obj.i5.unwrap().field_to_string())
]);
}
sql.returning_id();
let sql = sql.sql().unwrap();
let columns:Vec<(i64,)> = sqlx::query_as(sql.as_str()).fetch_all(conn).await.unwrap();
let mut ret = vec![];
for v in columns {
ret.push(v.0)
}
println!("insert id:{:?}", ret);
return ret;
}
pub async fn batch_insert(conn: &mut PgConnection, objs: Vec<TestTable>) -> Result<sqlx::postgres::PgQueryResult, sqlx::Error> {
let mut sql = sql_builder::SqlBuilder::insert_into("test_table");
sql.field("id");
sql.field("b1");
sql.field("b2");
sql.field("c1");
sql.field("c2");
sql.field("i4");
sql.field("i41");
sql.field("r1");
sql.field("r2");
sql.field("d1");
sql.field("d2");
sql.field("t1");
sql.field("t2");
sql.field("t3");
sql.field("t4");
sql.field("byte1");
sql.field("interval1");
sql.field("big1");
sql.field("big2");
sql.field("ts1");
sql.field("ts2");
sql.field("date1");
sql.field("date2");
sql.field("time1");
sql.field("time2");
sql.field("uid1");
sql.field("json1");
sql.field("json2");
sql.field("i5");
for obj in objs {
sql.values(&[
sql_builder::quote(obj.id.field_to_string()),
sql_builder::quote(obj.b1.field_to_string()),
sql_builder::quote(obj.b2.unwrap().field_to_string()),
sql_builder::quote(obj.c1.field_to_string()),
sql_builder::quote(obj.c2.unwrap().field_to_string()),
sql_builder::quote(obj.i4.field_to_string()),
sql_builder::quote(obj.i41.unwrap().field_to_string()),
sql_builder::quote(obj.r1.field_to_string()),
sql_builder::quote(obj.r2.unwrap().field_to_string()),
sql_builder::quote(obj.d1.field_to_string()),
sql_builder::quote(obj.d2.unwrap().field_to_string()),
sql_builder::quote(obj.t1.field_to_string()),
sql_builder::quote(obj.t2.field_to_string()),
sql_builder::quote(obj.t3.unwrap().field_to_string()),
sql_builder::quote(obj.t4.unwrap().field_to_string()),
sql_builder::quote(obj.byte1.unwrap().field_to_string()),
sql_builder::quote(obj.interval1.unwrap().field_to_string()),
sql_builder::quote(obj.big1.unwrap().field_to_string()),
sql_builder::quote(obj.big2.unwrap().field_to_string()),
sql_builder::quote(obj.ts1.field_to_string()),
sql_builder::quote(obj.ts2.unwrap().field_to_string()),
sql_builder::quote(obj.date1.unwrap().field_to_string()),
sql_builder::quote(obj.date2.unwrap().field_to_string()),
sql_builder::quote(obj.time1.field_to_string()),
sql_builder::quote(obj.time2.unwrap().field_to_string()),
sql_builder::quote(obj.uid1.field_to_string()),
sql_builder::quote(obj.json1.unwrap().field_to_string()),
sql_builder::quote(obj.json2.unwrap().field_to_string()),
sql_builder::quote(obj.i5.unwrap().field_to_string())
]);
}
let sql = sql.sql().unwrap();
sqlx::query(sql.as_str()).execute(conn).await
}
pub fn select_sql() -> String {
"select id, b1, b2, c1, c2, i4, i41, r1, r2, d1, d2, t1, t2, t3, t4, byte1, interval1, big1, big2, ts1, ts2, date1, date2, time1, time2, uid1, json1, json2, i5 from test_table".to_string()
}
pub async fn select_by_id(conn: &mut PgConnection,id: i64) -> Result<TestTable, sqlx::Error> {
let sql = format!("select id, b1, b2, c1, c2, i4, i41, r1, r2, d1, d2, t1, t2, t3, t4, byte1, interval1, big1, big2, ts1, ts2, date1, date2, time1, time2, uid1, json1, json2, i5 from test_table where id='{}'", id);
let result = sqlx::query_as(sql.as_str()).fetch_one(conn).await;
result
}
pub async fn delete_by_id(conn: &mut PgConnection,id: i64) -> Result<sqlx::postgres::PgQueryResult, sqlx::Error> {
let sql = format!("delete from test_table where id= '{}'", id);
sqlx::query(sql.as_str()).execute(conn).await
}
email: [email protected]
wechat: zyy20101289