aster_forge_db_migration/
schema.rs

1use sea_orm_migration::prelude::*;
2use sea_orm_migration::sea_orm::DatabaseBackend;
3
4const MYSQL_UTC_DATETIME_TYPE: &str = "datetime(6)";
5
6/// Builds the conventional signed 64-bit auto-incrementing primary-key column.
7pub fn big_integer_primary_key<T>(column: T) -> ColumnDef
8where
9    T: IntoIden,
10{
11    let mut column = ColumnDef::new(column);
12    column
13        .big_integer()
14        .not_null()
15        .auto_increment()
16        .primary_key();
17    column
18}
19
20/// Builds a UTC datetime column for the schema manager's backend.
21pub fn utc_date_time_column<T>(manager: &SchemaManager<'_>, column: T) -> ColumnDef
22where
23    T: IntoIden,
24{
25    utc_date_time_column_for_backend(manager.get_database_backend(), column)
26}
27
28/// Builds a UTC datetime column for an explicit database backend.
29pub fn utc_date_time_column_for_backend<T>(backend: DatabaseBackend, column: T) -> ColumnDef
30where
31    T: IntoIden,
32{
33    let mut column = ColumnDef::new(column);
34
35    match backend {
36        DatabaseBackend::MySql => {
37            column.custom(Alias::new(MYSQL_UTC_DATETIME_TYPE));
38        }
39        _ => {
40            column.timestamp_with_time_zone();
41        }
42    }
43
44    column
45}
46
47/// Builds a non-null JSON-text column for a migration's final schema.
48///
49/// `MySQL` TEXT defaults are omitted for compatibility; `PostgreSQL` and `SQLite` receive `{}`.
50pub fn json_text_column_for_final_schema<T: IntoIden>(
51    manager: &SchemaManager<'_>,
52    name: T,
53) -> ColumnDef {
54    json_text_column_for_final_schema_backend(manager.get_database_backend(), name)
55}
56
57fn json_text_column_for_final_schema_backend<T: IntoIden>(
58    backend: DatabaseBackend,
59    name: T,
60) -> ColumnDef {
61    let mut column = ColumnDef::new(name);
62    column.text();
63
64    if backend == DatabaseBackend::MySql {
65        column.not_null();
66    } else {
67        column.not_null().default("{}");
68    }
69
70    column
71}
72
73/// Builds a nullable JSON-text column suitable for a later data backfill.
74pub fn nullable_json_text_column_for_backfill<T: IntoIden>(name: T) -> ColumnDef {
75    let mut column = ColumnDef::new(name);
76    column.text().null();
77    column
78}
79
80#[cfg(test)]
81mod tests {
82    use sea_orm_migration::sea_query::{
83        MysqlQueryBuilder, PostgresQueryBuilder, SqliteQueryBuilder,
84    };
85
86    use super::*;
87
88    #[derive(DeriveIden)]
89    enum ExampleTable {
90        Table,
91        Id,
92        ExpiresAt,
93        Options,
94    }
95
96    #[test]
97    fn big_integer_primary_key_has_the_same_contract_on_all_backends() {
98        for backend in [
99            DatabaseBackend::MySql,
100            DatabaseBackend::Postgres,
101            DatabaseBackend::Sqlite,
102        ] {
103            let sql = create_table_sql(backend, big_integer_primary_key(ExampleTable::Id));
104            let normalized = sql.to_ascii_lowercase();
105            assert!(normalized.contains("not null"), "{sql}");
106            assert!(normalized.contains("primary key"), "{sql}");
107            match backend {
108                DatabaseBackend::MySql => {
109                    assert!(normalized.contains("bigint"), "{sql}");
110                    assert!(normalized.contains("auto_increment"), "{sql}");
111                }
112                DatabaseBackend::Postgres => {
113                    assert!(normalized.contains("bigint"), "{sql}");
114                    assert!(
115                        normalized.contains("generated by default as identity"),
116                        "{sql}"
117                    );
118                }
119                DatabaseBackend::Sqlite => assert!(normalized.contains("integer"), "{sql}"),
120                _ => unreachable!("unsupported backend in migration schema helper test"),
121            }
122        }
123    }
124
125    fn create_table_sql(backend: DatabaseBackend, column: ColumnDef) -> String {
126        let table = Table::create()
127            .table(ExampleTable::Table)
128            .col(column)
129            .to_owned();
130
131        match backend {
132            DatabaseBackend::MySql => table.to_string(MysqlQueryBuilder),
133            DatabaseBackend::Postgres => table.to_string(PostgresQueryBuilder),
134            DatabaseBackend::Sqlite => table.to_string(SqliteQueryBuilder),
135            _ => unreachable!("unsupported backend in migration schema helper test"),
136        }
137    }
138
139    #[test]
140    fn utc_datetime_columns_respect_backend_types() {
141        let mysql = create_table_sql(
142            DatabaseBackend::MySql,
143            utc_date_time_column_for_backend(DatabaseBackend::MySql, ExampleTable::ExpiresAt)
144                .not_null()
145                .to_owned(),
146        );
147        assert!(mysql.contains("datetime(6) NOT NULL"));
148
149        let postgres = create_table_sql(
150            DatabaseBackend::Postgres,
151            utc_date_time_column_for_backend(DatabaseBackend::Postgres, ExampleTable::ExpiresAt)
152                .not_null()
153                .to_owned(),
154        );
155        assert!(postgres.contains("timestamp with time zone NOT NULL"));
156
157        let sqlite = create_table_sql(
158            DatabaseBackend::Sqlite,
159            utc_date_time_column_for_backend(DatabaseBackend::Sqlite, ExampleTable::ExpiresAt)
160                .not_null()
161                .to_owned(),
162        );
163        assert!(sqlite.contains("timestamp_with_timezone_text NOT NULL"));
164    }
165
166    #[test]
167    fn json_text_final_schema_uses_portable_defaults() {
168        let mysql = create_table_sql(
169            DatabaseBackend::MySql,
170            json_text_column_for_final_schema_backend(
171                DatabaseBackend::MySql,
172                ExampleTable::Options,
173            ),
174        );
175        assert!(mysql.contains("text NOT NULL"));
176        assert!(!mysql.contains("DEFAULT"));
177
178        for backend in [DatabaseBackend::Postgres, DatabaseBackend::Sqlite] {
179            let sql = create_table_sql(
180                backend,
181                json_text_column_for_final_schema_backend(backend, ExampleTable::Options),
182            );
183            assert!(sql.contains("NOT NULL"));
184            assert!(sql.contains("DEFAULT '{}'"));
185        }
186    }
187
188    #[test]
189    fn nullable_json_backfill_column_stays_nullable() {
190        let sql = create_table_sql(
191            DatabaseBackend::Postgres,
192            nullable_json_text_column_for_backfill(ExampleTable::Options),
193        );
194        assert!(!sql.contains("NOT NULL"));
195    }
196}
aster_forge_db_migration/schema.rs

aster_forge_db_migration/
schema.rs
