seaql / sea-orm Goto Github PK
View Code? Open in Web Editor NEW๐ An async & dynamic ORM for Rust
Home Page: https://www.sea-ql.org/SeaORM/
License: Apache License 2.0
๐ An async & dynamic ORM for Rust
Home Page: https://www.sea-ql.org/SeaORM/
License: Apache License 2.0
Tested with sea-orm-codegen 0.1.2, the generated mod.rs
includes all entities modules but not prelude.rs
.
While the SelectTwo we have now works well for one-to-one relation i.e. Cake <> Fruit,
find cakes and fruits: SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`, `fruit`.`id` AS `B_id`, `fruit`.`name` AS `B_name`, `fruit`.`cake_id` AS `B_cake_id` FROM `cake` LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id`
(Model { id: 1, name: "New York Cheese" }, Model { id: 2, name: "Rasberry", cake_id: Some(1) })
(Model { id: 1, name: "New York Cheese" }, Model { id: 1, name: "Blueberry", cake_id: Some(1) })
(Model { id: 2, name: "Chocolate Forest" }, Model { id: 3, name: "Strawberry", cake_id: Some(2) })
but for one-to-many relations, it would duplicate the left hand side.
So it'd be perfect if there is a selector that can consolidate the rows, returning:
(Model { id: 1, name: "New York Cheese" },
vec![ Model { id: 2, name: "Rasberry", cake_id: Some(1) }, Model { id: 1, name: "Blueberry", cake_id: Some(1) }])
(Model { id: 2, name: "Chocolate Forest" }, vec![ Model { id: 3, name: "Strawberry", cake_id: Some(2) }])
This is trivial to implement for SelectTwo, but to generalize to SelectThree, it will be more complex, requiring us to obtain the graph structure of the join path beforehand.
https://github.com/SergioBenitez/Rocket/blob/v0.5-rc/examples/databases/src/sqlx.rs
Tasks:
Currently, the last_insert_id
from InsertResult simply uses the ExecResult's last_insert_id.
However, at least for Postgres with returning
, we should be able to retrieve the primary key column for the Entity and return the value in its native type. For example, a uuid
instead of an i64
.
I switched over to the mysql adaptor for one of the tests and I started getting the following error:
thread 'main' panicked at 'range end index 8 out of range for slice of length 4', /Users/sam/.cargo/registry/src/github.com-1ecc6299db9ec823/byteorder-1.4.3/src/lib.rs:2199:28
I tracked it down to the profit_margin
in the bakery model which was using f64
. Changing it to f32
fixed the panic.
Here is an extract of tests/common/bakery_chain/bakery.rs
on the origin/ss/mysql_try
branch:
impl EntityName for Entity {
fn table_name(&self) -> &str {
"bakery"
}
}
#[derive(Clone, Debug, PartialEq, DeriveModel, DeriveActiveModel)]
pub struct Model {
pub id: i32,
pub name: String,
pub profit_margin: f64,
}
Right now we have a tuple based selector for select one and two. Need to think about how to generalise and support up to arity 9 perhaps?
src/query/combine.rs
Hi guys!
Bumped in to a tiny error just now when trying to generate entities for my Postgres database. I've used sqlx-cli for the migrations.
โฏ sea-orm-cli generate entity
Error writing files: failed to resolve mod `sqlx_migrations`: /Users/nilskanevad/projects/kekou-api does not exist
The default table name for sqlx migrations is _sqlx_migrations
- Notice the underscore.
The codegen creates a _sqlx_migrations.rs
file but the table_name is getting set to sqlx_migrations
//! src/entity/_sqlx_migrations.rs
impl EntityName for Entity {
fn table_name(&self) -> &str {
"sqlx_migrations"
}
}
After changing the table name in my DB to sqlx_migrations
instead of _sqlx_migrations
and generating the entities again it works without a issue.
So, it seems that somewhere, somehow the cli/codegen gets confused by the "_" in the tablename. Can't find where in the code though
Right now, filter
takes a simpleExpr as argument, which can be combined into more complex boolean statements with .and
and .or
, but I think it would be a better interface (and clearer, and more consistent) if it took a sea_query::query::condition::ConditionWhere
(or rather an Into<ConditionWhere>
).
Current development is MySQL only, and we need to catch up support for Postgres and ensure it's correctness.
Re-export EnumIter
such that users don't have to depend on strum directly
https://github.com/SeaQL/sea-orm/blob/master/examples/sqlx-mysql/Cargo.toml#L11
Right now, removing that line would make compilation fail.
We should not ignore any errors when decoding db result.
match row.try_get(column.as_str()) {
Ok(v) => Ok(Some(v)),
// Error ignored
Err(_) => Ok(None),
}
For example:
Lines 86 to 121 in e144216
Related issue: SeaQL/sea-query#105
With the following function in a tokio async runtime, I get an error saying:
future created by async block is not `Send`
help: the trait `Sync` is not implemented for `(dyn Iden + 'static)`
async fn update(
db: &DatabaseConnection,
active_model: model::ActiveModel,
) -> Result<bool, Box<dyn std::error::Error>> {
Entity::update(active_model)
.exec(db)
.await
.map_err(Box::new)?;
Ok(true)
}
SeaORM does not seem to be thread safe. Perhaps this is due to sea-query's Iden type.
SelectTwo
, which becomes Vec<Vec>.from
and .to
in RelationBuilder
should take IntoIdentity
instead of E::Column
impl RelationTrait for Relation {
fn def(&self) -> RelationDef {
match self {
Self::Fruit => Entity::has_many(super::fruit::Entity)
.from(Column::Id)
.to(super::fruit::Column::CakeId)
.into(),
}
}
}
impl<E, R> RelationBuilder<E, R>
where
E: EntityTrait,
R: EntityTrait,
{
pub(crate) fn new(rel_type: RelationType, from: E, to: R) -> Self {
Self {
entities: PhantomData,
rel_type,
from_tbl: from.into_iden(),
to_tbl: to.into_iden(),
from_col: None,
to_col: None,
}
}
pub fn from(mut self, identifier: E::Column) -> Self {
self.from_col = Some(identifier.into_identity());
self
}
pub fn to(mut self, identifier: R::Column) -> Self {
self.to_col = Some(identifier.into_identity());
self
}
}
I have a column of the following type:
"id" uuid DEFAULT uuid_generate_v4()
And I cannot find a row by it's ID.
I tried both of the following:
find_by_id("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11")
// SELECT "users"."name" FROM "users" WHERE "users"."id" = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' LIMIT 1
// Error: Query Error: error returned from database: operator does not exist: uuid = text
find_by_id(uuid::Uuid::from_str("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11").unwrap())
// ^^^^^^ the trait `From<uuid::Uuid>` is not implemented for `sea_orm::Value`
Is there any way to currently filter a row by UUID?
Currently SeaORM uses a feature flag called "debug-print" which simple executes a println
with the query.
It might be nice to use the "log"
/ "env-logger"
crates instead.
For example, replace
debug_print!("{}", stmt);
with
info!(target: "sea-orm-query", "{}", stmt);
The benefit of this is that apps using SeaORM can change logging through env vars rather than recompiling with a new feature flag.
For example:
$ RUST_LOG=sea-orm-query=info ./main
As far as I'm aware, there seems to be a lack of support for querying from schemas. For example, switching from public schema to a different schema.
A possible solution is that EntityName
should support a new function called schema_name
perhaps:
pub trait EntityName {
fn schema_name(&self) -> Option<&str> {
None
}
}
Where None
would not include a schema selector in the queries.. and Some(&str)
would append the schema before the table name in queries.
This would change queries from:
SELECT "users"."id", "users"."email" FROM "users" ...
to:
SELECT "user"."users"."id", "user"."users"."email" FROM "user"."users" ...
-- or
SELECT "public"."users"."id", "public"."users"."email" FROM "public"."users" ...
Hello, it's me again! xD
I just noticed there is no support for row locking (or at least I haven't find out).
Is it something you have excluded for a reason or simply you still haven't covered this feature?
Let's say I have a field in my table, something like
status: CHAR(1) NOT NULL
status can have those values: A (active), D (deleted), Q (queued), E (executed).
I want to map the field to a Rust enum, I can easily do:
pub enum Status {
Active,
Deleted,
Queued,
Executed,
}
impl From<Status> for Value {
fn from(s: Status) -> Value {
Value::from(match s {
Status::Active => "A",
Status::Deleted => "D",
Status::Queued => "Q",
Status::Executed => "E",
})
}
}
and everything works fine.
But if my status is, instead
status: CHAR(1) DEFAULT NULL
In my Model I would have to use Option< Status>, and I cant impl From< Option< Status>> for Value, because neither Option or Value comes from my crate.
Maybe you could solve removing every impl From< Option< Something>> for Value and replacing it with a simple impl< T: Into< Value>> From< Option< T>> for Value, I don't know if there would be conflicts, but this way you unlock a lot of custom types potentials
Now the error types are opaque.
Create a DbErr
type to generalize QueryErr
and ExecErr
.
So that one ?
can catch them all.
async fn find_all(db: &DbConn) -> Result<(), DbErr>
Such that we can do:
cake::Entity::find().from_raw_sql().into_model()
I was trying your crate, seems promising, but the way you choose to represent models is too verbose.
For example I've a DB table with 53 fields, using your system I've written like 210 LOC, repeating the same informations like 3 times.
I think everything could be done with a single proc_macro, that takes a standard Rust struct and replaces it with a module with the same name of the struct, with Model struct with more or less the same contents of the original struct, Column enum with structs fields as variants, automatic impl ColumnTrait for Column with a type mapping, where the mapping fails you can use a field notation to override it (comes handy for custom types), table name and primary key can be specified with a struct notation, what's missing?
Relations, well, this have to be specified externally, but you can point the enum with another notation, and if the notation isn't present you generate a void enum.
After that, if I can add something more, I often use Cow on my entities, because this way I'm not forced to clone Strings when updating/creating. With your system I can't, because Model doesn't support a lifetime. It's not a big problem, but it blocks also other custom types with a lifetime
Connection
Database
: abstract interface of a databaseConnection
: a live connection able to execute SQL statementsExecutionResult
: execution result of non select queryORM Core
Entity
: encapsulates all the necessary specification of the corresponding database table
Column
: column namePrimaryKey
: primary key nameRelation
: definition of relation between tablesModel
: struct to store the data of a database rowEntity
file as Rust code (#11)Select from Table
SelectStatment
and return one or many modelsimpl Stream<Item = Result<_, _>>
Insert into Table
Update from Table
ActiveModel
: wrap Model
and indicates which fields are modified, and only update respective columnssave
semantics: insert if model is new, otherwise updateDelete from Table
Utility (#24)
Discovery
: generate
command to generate entities from existing databaseTest suite
When generating models with the sea-orm-cli, if you have a column with a name that conflicts with a Rust keyword, the cli crashes and is not handled correctly.
For example, an SQL table:
| column_name | data_type |
|-------------------------|
| id | serial |
| type | varchar |
I get an error:
11 | ... { pub id : i64 , pub type : Option < String > }
| ^^^^ expected identifier, found keyword
This is caused because the column type
conflicts with the Rust keyword type
.
Any rust columns conflicting should be renamed to resolve this issue.
https://doc.rust-lang.org/reference/keywords.html
To avoid manually exec
the insert ActiveModel
. In below, we cannot simply save
it because this is a junction table and the composite primary key should be set during insert.
sea-orm/tests/relational_tests.rs
Lines 562 to 569 in bfaa7d4
Perhaps impl
it here
sea-orm/sea-orm-macros/src/derives/active_model.rs
Lines 39 to 47 in 8cfa142
https://gist.github.com/roberto-butti/d490a8eb02bf4a540f7fd1715df18970
It should be able to execute the test suite with SQLite directly inside GitHub Action?
To design a schema which will be used in our test suite, tutorial and demo web app (REST API).
Covering all datatypes (numeric, strings, currency, datetime etc) and relations (1-1, 1-N, M-N).
Some 'static data' that don't change so often (like products) and operations (like sales).
In total around 10~20 entities (excluding junction table) ?
I have a rough idea:
to annotate the attributes of Entity's Model, where each attribute has three possible 'requirements': auto_identity
(auto generated primary key), required
(implied and thus can be omitted) and optional
from there, we can derive two more structs, InsertModel
and UpdateModel
For InsertModel
, auto_identity
will be omitted, and required fields will be wrapped with RequiredValue
, which must be Set
. Optional fields will be wrapped with ActiveValue
, which can be Set
or Unset
.
For UpdateModel
, auto_identity
will be wrapped with RequiredValue
, while all other fields will be wrapped with ActiveValue
(thus are optional)
change the Insert
and Update
API such that they accept IntoInsertModel
and IntoUpdateModel
respectively. ActiveModel
can still be converted automatically into InsertModel
and UpdateModel
, but will panic if the 'requirements' mismatch
Branch: ss/test_suite_refactor
Commit: 3a0e7f3
To run the test:
DATABASE_URL="postgres://root:root@localhost" cargo test --features sqlx-postgres --test bakery_chain_tests
Issue is that the test passes for mysql but fails for postgres, feature flagging allows this test to pass but then one of the unit tests fails and I'm not sure how to fix it.
Tried changing executer/paginator.rs:66 like this:
let num_items = if cfg!(feature = "sqlx-postgres") {
result.try_get::<i64>("", "num_items")? as usize
} else {
result.try_get::<i32>("", "num_items")? as usize
};
but then the unit tests fail.
This is blocking further work with the test suite.
Running CI tests for all the combinations of below...
Generating entity file for each db table.
Entity
Model
find_*
helper functionColumn
ColumnTrait
column defPrimaryKey
Relation
RelationTrait
relation defRelated
Work plan in progress...
use crate as sea_orm;
use crate::entity::prelude::*;
#[derive(Copy, Clone, Default, Debug, DeriveEntity)]
#[table = "cake"]
pub struct Entity;
#[derive(Clone, Debug, PartialEq, DeriveModel, DeriveActiveModel)]
pub struct Model {
pub id: i32,
pub name: String,
}
#[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
pub enum Column {
Id,
Name,
}
#[derive(Copy, Clone, Debug, EnumIter, DerivePrimaryKey)]
pub enum PrimaryKey {
Id,
}
#[derive(Copy, Clone, Debug, EnumIter)]
pub enum Relation {
Fruit,
}
impl ColumnTrait for Column {
type EntityName = Entity;
fn def(&self) -> ColumnType {
match self {
Self::Id => ColumnType::Integer(None),
Self::Name => ColumnType::String(None),
}
}
}
impl RelationTrait for Relation {
fn def(&self) -> RelationDef {
match self {
Self::Fruit => Entity::has_many(super::fruit::Entity)
.from(Column::Id)
.to(super::fruit::Column::CakeId)
.into(),
}
}
}
impl Related<super::fruit::Entity> for Entity {
fn to() -> RelationDef {
Relation::Fruit.def()
}
}
impl Related<super::filling::Entity> for Entity {
fn to() -> RelationDef {
super::cake_filling::Relation::Filling.def()
}
fn via() -> Option<RelationDef> {
Some(super::cake_filling::Relation::Cake.def().rev())
}
}
impl Model {
pub fn find_fruit(&self) -> Select<super::fruit::Entity> {
Entity::find_related().belongs_to::<Entity>(self)
}
pub fn find_filling(&self) -> Select<super::filling::Entity> {
Entity::find_related().belongs_to::<Entity>(self)
}
}
We assume all columns in database table are in snack-case.
#[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
pub enum Column {
Id,
Name,
}
We need to support an optional derive attribute to override this assumption.
#[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
#[DeriveColumn(case = "camel")]
pub enum Column {
Id,
Name,
}
Or, customize column name of each variants
#[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
pub enum Column {
#[DeriveColumn(name = "ID")]
Id,
Name,
}
Example problematic cases:
COUPLE.member1 -> USER
and COUPLE.member2 -> USER
)In these cases there are several issues:
Relation
variants, so we obtain duplicate variants. I guess it could use the join table or foreign key entity nameRelated
trait is typed on A/B entities, so same issue. So I believe fixing this requires a breaking conception change...let cake = cake::ActiveModel { ..Default::default() };
Cake::insert(cake).exec(db).await?;
This returns a database error:
Query Error: error returned from database: syntax error at or near ")"
Because the generated query is:
INSERT INTO "cakes" () VALUES () RETURNING "id" AS "last_insert_id"
@acidic9 thanks for reporting this!
What should be command line interface?
First, we should read config from .env
.
migrate
command for schema migration# to run all migrations
sea-orm-cli migrate
# to run one migration
sea-orm-cli migrate up
# to revert a migration
sea-orm-cli migrate down
generate
command for code generation# to generate entity files
sea-orm-cli generate entity
Ideally the test suite should:
A new struct Paginator
that wraps a Select
, that would:
COUNT
the total number of records and number of pagesLIMIT
and OFFSET
A helper method to convert a select into a paginator: (something like)
fn paginate(page_size: i32) -> Paginator<T>
The Paginator should impl the Stream
trait.
Ref:
https://docs.rs/futures-core/0.3.15/futures_core/stream/trait.Stream.html
https://tokio.rs/tokio/tutorial/streams
https://docs.rs/async-std/1.9.0/async_std/stream/trait.Stream.html
When a primary key is not included in an active model, sea-orm panics. Could this be handled in a better way?
https://github.com/SeaQL/sea-orm/blob/master/src/query/update.rs#L95
let active_model = cake::ActiveModel {
// Don't include ID ...
..Default::default()
};
let products = Cake::update(active_model).exec(&db).await?;
@acidic9 thanks for raising this issue
Just thinking where we need subquery support in sea-orm
SELECT (SELECT ...) AS subquery FROM ...
... WHERE id IN (SELECT ...) ...
... FROM (SELECT ...) ...
... LEFT JOIN (SELECT ...) ON ...
How many kinds of subquery we want to support at this stage? I think supporting "select expression" and "conditional expression" are enough for now.
The enum DbErr
should implement std::error::Error
.
Add a new trait AggregatorTrait
to be impl by Selector*
count()
and sum
avg
max
min
by specifying a column to aggregate on
sea-orm/tests/common/setup/schema.rs
Lines 29 to 51 in 08acc2a
To avoid duplication of information, we can actually generate a SeaQuery SchemaStatement entirely from the Entity definition.
It would be useful in 2 scenarios:
writing test cases i.e. original model is MySQL but we want to test in SQLite. this is particularly helpful if the migration files are written in raw SQL. we effectively automatically translated from MySQL to SQLite!
writing SQLite applications. in this case, apps don't need a separate migration tool. everything should be done by the app itself. so it's actually helpful to automatically bootstrap a SQLite database from an Entity file
let baked_for_customers = Baker::find()
.find_also_linked(baker::BakedForCustomer)
.group_by(baker::Column::Id)
.all(&ctx.db)
.await?;
With SQL statement
SELECT `baker`.`id` AS `A_id`,
`baker`.`name` AS `A_name`,
`baker`.`contact_details` AS `A_contact_details`,
`baker`.`bakery_id` AS `A_bakery_id`,
`customer`.`id` AS `B_id`,
`customer`.`name` AS `B_name`,
`customer`.`notes` AS `B_notes`
FROM `baker`
LEFT JOIN `cakes_bakers` ON `baker`.`id` = `cakes_bakers`.`baker_id`
LEFT JOIN `cake` ON `cakes_bakers`.`cake_id` = `cake`.`id`
LEFT JOIN `lineitem` ON `cake`.`id` = `lineitem`.`cake_id`
LEFT JOIN `order` ON `lineitem`.`order_id` = `order`.`id`
LEFT JOIN `customer` ON `order`.`customer_id` = `customer`.`id`
GROUP BY `baker`.`id`
Seems that there is no proper way to query with raw SQL.
Maybe we can add some helper method to EntityTrait
pub trait EntityTrait {
fn find_raw( ... ) -> ... {
todo!()
}
fn insert_raw( ... ) -> ... {
todo!()
}
fn update_raw( ... ) -> ... {
todo!()
}
fn delete_raw( ... ) -> ... {
todo!()
}
}
To support usage like
let stmt = Statement::from_string(
DatabaseBackend::Postgres,
r#"SELECT "cake"."id", "cake"."name" FROM "cake" LIMIT 1"#
);
let _ = fruit::Entity::find_raw(stmt).one(db).await?;
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.