Introduction

This library aims to provide an ergonomic API to construct Abstract Syntax Trees for SQL. The AST is generic by design and can be serialized to different SQL variants. We align the behaviour between different engines where appropriate, while offering vendor specific features via extensions.

This library is the foundation of upcoming projects: Document ORM (SeaORM) and Database Synchor (SeaHorse).

Install

```toml

Cargo.toml

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

Usage

Table of Content

1. Background

   1. Iden
   2. Expression
   3. Statement Building

2. Query Statement

   1. Query Select
   2. Query Insert
   3. Query Update
   4. Query Delete

3. Table Statement

   1. Table Create
   2. Table Alter
   3. Table Drop
   4. Table Rename
   5. Table Truncate
   6. Foreign Key Create
   7. Foreign Key Drop
   8. Index Create
   9. Index Drop

Drivers

We provide integration for SQLx and postgres. See examples for usage.

Iden

Iden is 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(); } } ```

If you're okay with running another procedural macro, you can activate the derive feature on the crate to save you some boilerplate. For more information, look at the derive example.

```rust use sea_query::Iden;

// This will implement Iden exactly as shown above

[derive(Iden)]

pub enum Character { Table, Id, Character, FontSize, SizeW, SizeH, FontId, } ```

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), 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(" ") ); ```

Statement Building

All the query statements and table statements support the following ways to build database specific SQL statement:

1. build(&self, query_builder: T) -> (String, Vec<Value>)
   Build a SQL statement in string and collect parameters into a vector, see here for example.

2. build_collect(&self, query_builder: T, collector: &mut dyn FnMut(Value)) -> String
   Build a SQL statement in string and collect parameters into a user defined collector, see here for example.

3. to_string(&self, query_builder: T) -> String
   Build a SQL statement in string with parameters in it, see here for example.

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() .table(Glyph::Table) .values(vec![ (Glyph::Aspect, 1.23.into()), (Glyph::Image, "123".into()), ]) .andwhere(Expr::col(Glyph::Id).eq(1)) .toowned();

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(255) 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"# ); ```

Work in Progress

• Sqlite