Crate sea_query[−][src]
A database agnostic runtime query builder for Rust.
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).
Usage
Table of Content
-
Background
-
Query Statement
-
Table Statement
Construct a SQL statement with the library then execute the statement with a database connector, see SQLx example here.
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.
use sea_query::{*, tests_cfg::*}; // 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 => "font_size", Self::SizeW => "size_w", Self::SizeH => "size_h", Self::FontId => "font_id", }).unwrap(); } }
If you’re okay with running another procedural macro, you can activate
the derive
feature on the crate to save you some boilerplate:
use sea_query::Iden; // This will implement Iden exactly as shown above #[derive(Iden)] pub enum Character { Table, Id, Character, FontSize, SizeW, SizeH, FontId, }
You can also override the generated column names by specifying an #[iden = ""]
attribute on the enum or any of its variants; for more information, look at
the derive example.
Expression
Use Expr
to construct select, join, where and having expression in query.
use sea_query::{*, tests_cfg::*}; assert_eq!( Query::select() .column(Char::Character) .from(Char::Table) .left_join(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id)) .and_where( Expr::expr(Expr::col(Char::SizeW).add(1)).mul(2) .equals(Expr::expr(Expr::col(Char::SizeH).div(2)).sub(1)) ) .and_where(Expr::col(Char::SizeW).in_subquery( Query::select() .expr(Expr::cust("3 + 2 * 2")) .take() )) .or_where(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:
-
build(&self, query_builder: T) -> (String, Vec<Value>)
Build a SQL statement in string and collect parameters into a vector, see here for example. -
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. -
to_string(&self, query_builder: T) -> String
Build a SQL statement in string with parameters in it, see here for example.
Query Select
See SelectStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let query = Query::select() .column(Char::Character) .table_column(Font::Table, Font::Name) .from(Char::Table) .left_join(Font::Table, Expr::tbl(Char::Table, Char::FontId).equals(Font::Table, Font::Id)) .and_where(Expr::col(Char::SizeW).is_in(vec![3, 4])) .and_where(Expr::col(Char::Character).like("A%")) .to_owned(); assert_eq!( query.to_string(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%'"# ); assert_eq!( query.to_string(PostgresQueryBuilder), 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%'"# ); assert_eq!( query.to_string(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
See InsertStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let query = Query::insert() .into_table(Glyph::Table) .columns(vec![ Glyph::Aspect, Glyph::Image, ]) .values_panic(vec![ 5.15.into(), "12A".into(), ]) .json(json!({ "aspect": 4.21, "image": "123", })) .to_owned(); assert_eq!( query.to_string(MysqlQueryBuilder), r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (5.15, '12A'), (4.21, '123')"# ); assert_eq!( query.to_string(PostgresQueryBuilder), r#"INSERT INTO "glyph" ("aspect", "image") VALUES (5.15, '12A'), (4.21, '123')"# ); assert_eq!( query.to_string(SqliteQueryBuilder), r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (5.15, '12A'), (4.21, '123')"# );
Query Update
See UpdateStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let query = Query::update() .table(Glyph::Table) .values(vec![ (Glyph::Aspect, 1.23.into()), (Glyph::Image, "123".into()), ]) .and_where(Expr::col(Glyph::Id).eq(1)) .to_owned(); assert_eq!( query.to_string(MysqlQueryBuilder), r#"UPDATE `glyph` SET `aspect` = 1.23, `image` = '123' WHERE `id` = 1"# ); assert_eq!( query.to_string(PostgresQueryBuilder), r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"# ); assert_eq!( query.to_string(SqliteQueryBuilder), r#"UPDATE `glyph` SET `aspect` = 1.23, `image` = '123' WHERE `id` = 1"# );
Query Delete
See DeleteStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let query = Query::delete() .from_table(Glyph::Table) .or_where(Expr::col(Glyph::Id).lt(1)) .or_where(Expr::col(Glyph::Id).gt(10)) .to_owned(); assert_eq!( query.to_string(MysqlQueryBuilder), r#"DELETE FROM `glyph` WHERE (`id` < 1) OR (`id` > 10)"# ); assert_eq!( query.to_string(PostgresQueryBuilder), r#"DELETE FROM "glyph" WHERE ("id" < 1) OR ("id" > 10)"# ); assert_eq!( query.to_string(SqliteQueryBuilder), r#"DELETE FROM `glyph` WHERE (`id` < 1) OR (`id` > 10)"# );
Table Create
See TableCreateStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let table = Table::create() .table(Char::Table) .create_if_not_exists() .col(ColumnDef::new(Char::Id).integer().not_null().auto_increment().primary_key()) .col(ColumnDef::new(Char::FontSize).integer().not_null()) .col(ColumnDef::new(Char::Character).string().not_null()) .col(ColumnDef::new(Char::SizeW).integer().not_null()) .col(ColumnDef::new(Char::SizeH).integer().not_null()) .col(ColumnDef::new(Char::FontId).integer().default(Value::Null)) .foreign_key( ForeignKey::create() .name("FK_2e303c3a712662f1fc2a4d0aad6") .table(Char::Table, Font::Table) .col(Char::FontId, Font::Id) .on_delete(ForeignKeyAction::Cascade) .on_update(ForeignKeyAction::Cascade) ) .to_owned(); assert_eq!( table.to_string(MysqlQueryBuilder), vec![ r#"CREATE TABLE IF NOT EXISTS `character` ("#, r#"`id` int NOT NULL AUTO_INCREMENT 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(" ") ); assert_eq!( table.to_string(PostgresQueryBuilder), vec![ r#"CREATE TABLE IF NOT EXISTS "character" ("#, r#""id" serial NOT NULL PRIMARY KEY,"#, r#""font_size" integer NOT NULL,"#, r#""character" varchar NOT NULL,"#, r#""size_w" integer NOT NULL,"#, r#""size_h" integer NOT NULL,"#, r#""font_id" integer DEFAULT NULL,"#, r#"CONSTRAINT "FK_2e303c3a712662f1fc2a4d0aad6""#, r#"FOREIGN KEY ("font_id") REFERENCES "font" ("id")"#, r#"ON DELETE CASCADE ON UPDATE CASCADE"#, r#")"#, ].join(" ") ); assert_eq!( 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
See TableAlterStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let table = Table::alter() .table(Font::Table) .add_column(ColumnDef::new(Alias::new("new_col")).integer().not_null().default(100)) .to_owned(); assert_eq!( table.to_string(MysqlQueryBuilder), r#"ALTER TABLE `font` ADD COLUMN `new_col` int NOT NULL DEFAULT 100"# ); assert_eq!( table.to_string(PostgresQueryBuilder), r#"ALTER TABLE "font" ADD COLUMN "new_col" integer NOT NULL DEFAULT 100"# ); assert_eq!( table.to_string(SqliteQueryBuilder), r#"ALTER TABLE `font` ADD COLUMN `new_col` integer NOT NULL DEFAULT 100"#, );
Table Drop
See TableDropStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let table = Table::drop() .table(Glyph::Table) .table(Char::Table) .to_owned(); assert_eq!( table.to_string(MysqlQueryBuilder), r#"DROP TABLE `glyph`, `character`"# ); assert_eq!( table.to_string(PostgresQueryBuilder), r#"DROP TABLE "glyph", "character""# ); assert_eq!( table.to_string(SqliteQueryBuilder), r#"DROP TABLE `glyph`, `character`"# );
Table Rename
See TableRenameStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let table = Table::rename() .table(Font::Table, Alias::new("font_new")) .to_owned(); assert_eq!( table.to_string(MysqlQueryBuilder), r#"RENAME TABLE `font` TO `font_new`"# ); assert_eq!( table.to_string(PostgresQueryBuilder), r#"ALTER TABLE "font" RENAME TO "font_new""# ); assert_eq!( table.to_string(SqliteQueryBuilder), r#"ALTER TABLE `font` RENAME TO `font_new`"# );
Table Truncate
See TableTruncateStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let table = Table::truncate() .table(Font::Table) .to_owned(); assert_eq!( table.to_string(MysqlQueryBuilder), r#"TRUNCATE TABLE `font`"# ); assert_eq!( table.to_string(PostgresQueryBuilder), r#"TRUNCATE TABLE "font""# ); assert_eq!( table.to_string(SqliteQueryBuilder), r#"TRUNCATE TABLE `font`"# );
Foreign Key Create
See ForeignKeyCreateStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let foreign_key = ForeignKey::create() .name("FK_character_font") .table(Char::Table, Font::Table) .col(Char::FontId, Font::Id) .on_delete(ForeignKeyAction::Cascade) .on_update(ForeignKeyAction::Cascade) .to_owned(); assert_eq!( foreign_key.to_string(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(" ") ); assert_eq!( foreign_key.to_string(PostgresQueryBuilder), vec![ r#"ALTER TABLE "character" ADD CONSTRAINT "FK_character_font""#, 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
See ForeignKeyDropStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let foreign_key = ForeignKey::drop() .name("FK_character_font") .table(Char::Table) .to_owned(); assert_eq!( foreign_key.to_string(MysqlQueryBuilder), r#"ALTER TABLE `character` DROP FOREIGN KEY `FK_character_font`"# ); assert_eq!( foreign_key.to_string(PostgresQueryBuilder), r#"ALTER TABLE "character" DROP CONSTRAINT "FK_character_font""# ); // Sqlite does not support modification of foreign key constraints to existing tables
Index Create
See IndexCreateStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let index = Index::create() .name("idx-glyph-aspect") .table(Glyph::Table) .col(Glyph::Aspect) .to_owned(); assert_eq!( index.to_string(MysqlQueryBuilder), r#"CREATE INDEX `idx-glyph-aspect` ON `glyph` (`aspect`)"# ); assert_eq!( index.to_string(PostgresQueryBuilder), r#"CREATE INDEX "idx-glyph-aspect" ON "glyph" ("aspect")"# ); assert_eq!( index.to_string(SqliteQueryBuilder), r#"CREATE INDEX `idx-glyph-aspect` ON `glyph` (`aspect`)"# );
Index Drop
See IndexDropStatement
for the full documentation.
use sea_query::{*, tests_cfg::*}; let index = Index::drop() .name("idx-glyph-aspect") .table(Glyph::Table) .to_owned(); assert_eq!( index.to_string(MysqlQueryBuilder), r#"DROP INDEX `idx-glyph-aspect` ON `glyph`"# ); assert_eq!( index.to_string(PostgresQueryBuilder), r#"DROP INDEX "idx-glyph-aspect""# ); assert_eq!( index.to_string(SqliteQueryBuilder), r#"DROP INDEX `idx-glyph-aspect` ON `glyph`"# );
Work in Progress
- Sqlite
Re-exports
pub use backend::*; |
pub use driver::*; |
pub use foreign_key::*; |
pub use index::*; |
pub use query::*; |
pub use table::*; |
pub use expr::*; |
pub use func::*; |
pub use prepare::*; |
pub use types::*; |
pub use token::*; |
pub use value::*; |
Modules
backend | Translating unified SQL representation into database specific SQL statement. |
driver | |
expr | Building blocks for constructing select, join, where and having expression in query. |
extension | |
foreign_key | Table foreign key (create and drop). |
func | |
index | Table index (create and drop). |
prepare | |
query | Table query (select, insert, update & delete). |
table | Database table (create, alter, drop, rename & truncate). |
tests_cfg | Helper module setting up rustdoc and test environment. |
token | |
types | Common types used in the library. |
value | Universal value variants used in the library. |
Derive Macros
Iden |