Introduction

This library is the foundation of upcoming projects on Document ORM (Sea-ORM) and Database Synchor (Sea-Horse).

Install

```toml

Cargo.toml

[dependencies] sea-query = "*" ```

Usage

Construct a SQL statement with the library then execute the statement with a database connector such as SQLx.

Later we will release Document ORM (Sea-ORM) which you can load and save document on any supported relational database, without the need of managing database connection on your own.

Iden

A trait for identifiers used in any query statement.

Commonly implemented by Enum where each Enum represent a table found in a database, and its variants include table name and column name.

[Iden::unquoted()] must be implemented to provide a mapping between Enum variant and its corresponding string value.

```rust use seaquery::{*, testscfg::*};

// For example Character table with column id, character, font_size... pub enum Character { Table, Id, Character, FontSize, SizeW, SizeH, FontId, }

// Mapping between Enum variant and its corresponding string value impl Iden for Character { fn unquoted(&self, s: &mut dyn FmtWrite) { write!(s, "{}", match self { Self::Table => "character", Self::Id => "id", Self::Character => "character", Self::FontSize => "fontsize", Self::SizeW => "sizew", Self::SizeH => "sizeh", Self::FontId => "fontid", }).unwrap(); } } ```

Expression

Use [Expr] to construct select, join, where and having expression in query.

```rust use seaquery::{*, testscfg::*};

asserteq!( Query::select() .column(Char::Character) .from(Char::Table) .leftjoin(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id)) .andwhere( Expr::expr(Expr::col(Char::SizeW).add(1)).mul(2) .equals(Expr::expr(Expr::col(Char::SizeH).div(2)).sub(1)) ) .andwhere(Expr::col(Char::SizeW).insubquery( Query::select() .expr(Expr::cust("3 + 2 * 2")) .take() )) .orwhere(Expr::col(Char::Character).like("D").and(Expr::col(Char::Character).like("E"))) .to_string(MysqlQueryBuilder::new()), vec![ "SELECT character FROM character", "LEFT JOIN font ON character.font_id = font.id", "WHERE ((size_w + 1) * 2 = (size_h / 2) - 1)", "AND size_w IN (SELECT 3 + 2 * 2)", "OR ((character LIKE 'D') AND (character LIKE 'E'))", ].join(" ") ); ```

Query Select

```rust use seaquery::{*, testscfg::*};

let query = Query::select() .column(Char::Character) .tablecolumn(Font::Table, Font::Name) .from(Char::Table) .leftjoin(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id)) .andwhere(Expr::col(Char::SizeW).isin(vec![3, 4])) .andwhere(Expr::col(Char::Character).like("A%")) .toowned();

asserteq!( query.tostring(MysqlQueryBuilder), r#"SELECT character, font.name FROM character LEFT JOIN font ON character.font_id = font.id WHERE size_w IN (3, 4) AND character LIKE 'A%'"# ); asserteq!( query.tostring(PostgresQueryBuilder), r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."fontid" = "font"."id" WHERE "sizew" IN (3, 4) AND "character" LIKE 'A%'"# ); asserteq!( query.tostring(SqliteQueryBuilder), r#"SELECT character, font.name FROM character LEFT JOIN font ON character.font_id = font.id WHERE size_w IN (3, 4) AND character LIKE 'A%'"# ); ```

Query Insert

```rust use seaquery::{*, testscfg::*};

let query = Query::insert() .intotable(Glyph::Table) .columns(vec![ Glyph::Aspect, Glyph::Image, ]) .valuespanic(vec![ 5.15.into(), "12A".into(), ]) .json(json!({ "aspect": 4.21, "image": "123", })) .to_owned();

asserteq!( query.tostring(MysqlQueryBuilder), r#"INSERT INTO glyph (aspect, image) VALUES (5.15, '12A'), (4.21, '123')"# ); asserteq!( query.tostring(PostgresQueryBuilder), r#"INSERT INTO "glyph" ("aspect", "image") VALUES (5.15, '12A'), (4.21, '123')"# ); asserteq!( query.tostring(SqliteQueryBuilder), r#"INSERT INTO glyph (aspect, image) VALUES (5.15, '12A'), (4.21, '123')"# ); ```

Query Update

```rust use seaquery::{*, testscfg::*};

let query = Query::update() .intotable(Glyph::Table) .values(vec![ (Glyph::Aspect, 1.23.into()), (Glyph::Image, "123".into()), ]) .andwhere(Expr::col(Glyph::Id).eq(1)) .to_owned();

asserteq!( query.tostring(MysqlQueryBuilder), r#"UPDATE glyph SET aspect = 1.23, image = '123' WHERE id = 1"# ); asserteq!( query.tostring(PostgresQueryBuilder), r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"# ); asserteq!( query.tostring(SqliteQueryBuilder), r#"UPDATE glyph SET aspect = 1.23, image = '123' WHERE id = 1"# ); ```

Query Delete

```rust use seaquery::{*, testscfg::*};

let query = Query::delete() .fromtable(Glyph::Table) .orwhere(Expr::col(Glyph::Id).lt(1)) .orwhere(Expr::col(Glyph::Id).gt(10)) .toowned();

asserteq!( query.tostring(MysqlQueryBuilder), r#"DELETE FROM glyph WHERE (id < 1) OR (id > 10)"# ); asserteq!( query.tostring(PostgresQueryBuilder), r#"DELETE FROM "glyph" WHERE ("id" < 1) OR ("id" > 10)"# ); asserteq!( query.tostring(SqliteQueryBuilder), r#"DELETE FROM glyph WHERE (id < 1) OR (id > 10)"# ); ```

Table Create

```rust use seaquery::{*, testscfg::*};

let table = Table::create() .table(Char::Table) .createifnotexists() .col(ColumnDef::new(Char::Id).integer().notnull().autoincrement().primarykey()) .col(ColumnDef::new(Char::FontSize).integer().notnull()) .col(ColumnDef::new(Char::Character).string().notnull()) .col(ColumnDef::new(Char::SizeW).integer().notnull()) .col(ColumnDef::new(Char::SizeH).integer().notnull()) .col(ColumnDef::new(Char::FontId).integer().default(Value::NULL)) .foreignkey( ForeignKey::create() .name("FK2e303c3a712662f1fc2a4d0aad6") .table(Char::Table, Font::Table) .col(Char::FontId, Font::Id) .ondelete(ForeignKeyAction::Cascade) .onupdate(ForeignKeyAction::Cascade) ) .to_owned();

asserteq!( table.tostring(MysqlQueryBuilder), vec![ r#"CREATE TABLE IF NOT EXISTS character ("#, r#"id int NOT NULL AUTOINCREMENT PRIMARY KEY,"#, r#"font_size int NOT NULL,"#, r#"character varchar NOT NULL,"#, r#"size_w int NOT NULL,"#, r#"size_h int NOT NULL,"#, r#"font_id int DEFAULT NULL,"#, r#"CONSTRAINT FK_2e303c3a712662f1fc2a4d0aad6"#, r#"FOREIGN KEY FK_2e303c3a712662f1fc2a4d0aad6 (font_id) REFERENCES font (id)"#, r#"ON DELETE CASCADE ON UPDATE CASCADE"#, r#")"#, ].join(" ") ); asserteq!( table.tostring(PostgresQueryBuilder), vec![ r#"CREATE TABLE IF NOT EXISTS "character" ("#, r#""id" serial NOT NULL PRIMARY KEY,"#, r#""fontsize" integer NOT NULL,"#, r#""character" varchar NOT NULL,"#, r#""sizew" integer NOT NULL,"#, r#""sizeh" integer NOT NULL,"#, r#""fontid" integer DEFAULT NULL,"#, r#"CONSTRAINT "FK2e303c3a712662f1fc2a4d0aad6""#, r#"FOREIGN KEY ("fontid") REFERENCES "font" ("id")"#, r#"ON DELETE CASCADE ON UPDATE CASCADE"#, r#")"#, ].join(" ") ); asserteq!( table.to_string(SqliteQueryBuilder), vec![ r#"CREATE TABLE IF NOT EXISTS character ("#, r#"id integer NOT NULL PRIMARY KEY AUTOINCREMENT,"#, r#"font_size integer NOT NULL,"#, r#"character text NOT NULL,"#, r#"size_w integer NOT NULL,"#, r#"size_h integer NOT NULL,"#, r#"font_id integer DEFAULT NULL,"#, r#"FOREIGN KEY (font_id) REFERENCES font (id) ON DELETE CASCADE ON UPDATE CASCADE"#, r#")"#, ].join(" ") ); ```

Table Alter

```rust use seaquery::{*, testscfg::*};

let table = Table::alter() .table(Font::Table) .addcolumn(ColumnDef::new(Alias::new("newcol")).integer().notnull().default(100)) .toowned();

asserteq!( table.tostring(MysqlQueryBuilder), r#"ALTER TABLE font ADD COLUMN new_col int NOT NULL DEFAULT 100"# ); asserteq!( table.tostring(PostgresQueryBuilder), r#"ALTER TABLE "font" ADD COLUMN "newcol" integer NOT NULL DEFAULT 100"# ); asserteq!( table.to_string(SqliteQueryBuilder), r#"ALTER TABLE font ADD COLUMN new_col integer NOT NULL DEFAULT 100"#, ); ```

Table Drop

```rust use seaquery::{*, testscfg::*};

let table = Table::drop() .table(Glyph::Table) .table(Char::Table) .to_owned();

asserteq!( table.tostring(MysqlQueryBuilder), r#"DROP TABLE glyph, character"# ); asserteq!( table.tostring(PostgresQueryBuilder), r#"DROP TABLE "glyph", "character""# ); asserteq!( table.tostring(SqliteQueryBuilder), r#"DROP TABLE glyph, character"# ); ```

Table Rename

```rust use seaquery::{*, testscfg::*};

let table = Table::rename() .table(Font::Table, Alias::new("fontnew")) .toowned();

asserteq!( table.tostring(MysqlQueryBuilder), r#"RENAME TABLE font TO font_new"# ); asserteq!( table.tostring(PostgresQueryBuilder), r#"ALTER TABLE "font" RENAME TO "fontnew""# ); asserteq!( table.to_string(SqliteQueryBuilder), r#"ALTER TABLE font RENAME TO font_new"# ); ```

Table Truncate

```rust use seaquery::{*, testscfg::*};

let table = Table::truncate() .table(Font::Table) .to_owned();

asserteq!( table.tostring(MysqlQueryBuilder), r#"TRUNCATE TABLE font"# ); asserteq!( table.tostring(PostgresQueryBuilder), r#"TRUNCATE TABLE "font""# ); asserteq!( table.tostring(SqliteQueryBuilder), r#"TRUNCATE TABLE font"# ); ```

Foreign Key Create

```rust use seaquery::{*, testscfg::*};

let foreignkey = ForeignKey::create() .name("FKcharacterfont") .table(Char::Table, Font::Table) .col(Char::FontId, Font::Id) .ondelete(ForeignKeyAction::Cascade) .onupdate(ForeignKeyAction::Cascade) .toowned();

asserteq!( foreignkey.tostring(MysqlQueryBuilder), vec![ r#"ALTER TABLE character"#, r#"ADD CONSTRAINT FK_character_font"#, r#"FOREIGN KEY FK_character_font (font_id) REFERENCES font (id)"#, r#"ON DELETE CASCADE ON UPDATE CASCADE"#, ].join(" ") ); asserteq!( foreignkey.tostring(PostgresQueryBuilder), vec![ r#"ALTER TABLE "character" ADD CONSTRAINT "FKcharacterfont""#, r#"FOREIGN KEY ("font_id") REFERENCES "font" ("id")"#, r#"ON DELETE CASCADE ON UPDATE CASCADE"#, ].join(" ") ); // Sqlite does not support modification of foreign key constraints to existing tables ```

Foreign Key Drop

```rust use seaquery::{*, testscfg::*};

let foreignkey = ForeignKey::drop() .name("FKcharacterfont") .table(Char::Table) .toowned();

asserteq!( foreignkey.tostring(MysqlQueryBuilder), r#"ALTER TABLE character DROP FOREIGN KEY FK_character_font"# ); asserteq!( foreignkey.tostring(PostgresQueryBuilder), r#"ALTER TABLE "character" DROP CONSTRAINT "FKcharacterfont""# ); // Sqlite does not support modification of foreign key constraints to existing tables ```

Index Create

```rust use seaquery::{*, testscfg::*};

let index = Index::create() .name("idx-glyph-aspect") .table(Glyph::Table) .col(Glyph::Aspect) .to_owned();

asserteq!( index.tostring(MysqlQueryBuilder), r#"CREATE INDEX idx-glyph-aspect ON glyph (aspect)"# ); asserteq!( index.tostring(PostgresQueryBuilder), r#"CREATE INDEX "idx-glyph-aspect" ON "glyph" ("aspect")"# ); asserteq!( index.tostring(SqliteQueryBuilder), r#"CREATE INDEX idx-glyph-aspect ON glyph (aspect)"# ); ```

Index Drop

```rust use seaquery::{*, testscfg::*};

let index = Index::drop() .name("idx-glyph-aspect") .table(Glyph::Table) .to_owned();

asserteq!( index.tostring(MysqlQueryBuilder), r#"DROP INDEX idx-glyph-aspect ON glyph"# ); asserteq!( index.tostring(PostgresQueryBuilder), r#"DROP INDEX "idx-glyph-aspect""# ); asserteq!( index.tostring(SqliteQueryBuilder), r#"DROP INDEX idx-glyph-aspect ON glyph"# ); ```