apiplant_db/
migrate.rs

1//! Runtime migrations.
2//!
3//! apiplant has no hand-written migration files: the resource schemas *are* the
4//! desired state. On boot the migrator makes the database match them —
5//! idempotently. It creates missing tables and adds missing columns (an
6//! additive strategy that is safe to run on every start). Destructive changes
7//! (dropping/retyping columns) are intentionally left to the operator.
8
9use apiplant_core::schema::Field;
10use apiplant_core::{App, FieldType, Resource};
11use sea_orm::{ConnectionTrait, DatabaseBackend, Statement};
12use std::collections::HashSet;
13
14use crate::ident::quote_ident;
15use crate::Error;
16
17/// Bring the database in line with every resource in the app.
18///
19/// Three additive passes, all idempotent: create missing tables, add missing
20/// columns, then add missing foreign-key constraints for `reference` fields.
21/// FKs come last so every referenced table already exists.
22pub async fn migrate(conn: &impl ConnectionTrait, app: &App) -> Result<(), Error> {
23    for resource in app.resources_in_dependency_order() {
24        create_table_if_absent(conn, resource).await?;
25        add_missing_columns(conn, resource).await?;
26    }
27    for resource in app.resources_in_dependency_order() {
28        add_foreign_keys(conn, resource, app).await?;
29    }
30    Ok(())
31}
32
33/// Add an FK constraint for each `reference` field, if not already present.
34async fn add_foreign_keys(
35    conn: &impl ConnectionTrait,
36    r: &Resource,
37    app: &App,
38) -> Result<(), Error> {
39    let table = quote_ident(&r.table_name())?;
40    for reference in r.references() {
41        let Some(target) = app.resources.get(&reference.target) else {
42            tracing::warn!(
43                resource = %r.meta.name,
44                field = %reference.field,
45                target = %reference.target,
46                "reference points at an unknown resource; skipping FK"
47            );
48            continue;
49        };
50        // Deterministic name lets us skip re-adding it on the next boot.
51        let constraint = format!("fk_{}_{}", r.table_name(), reference.field);
52        if constraint_exists(conn, &constraint).await? {
53            continue;
54        }
55        let sql = format!(
56            "ALTER TABLE {table} ADD CONSTRAINT {con} \
57             FOREIGN KEY ({col}) REFERENCES {target_tbl}(\"id\") ON DELETE {action}",
58            con = quote_ident(&constraint)?,
59            col = quote_ident(&reference.field)?,
60            target_tbl = quote_ident(&target.table_name())?,
61            action = reference.on_delete.to_sql(),
62        );
63        conn.execute(Statement::from_string(DatabaseBackend::Postgres, sql))
64            .await?;
65        tracing::info!(
66            resource = %r.meta.name,
67            field = %reference.field,
68            target = %reference.target,
69            "migrated: added foreign key"
70        );
71    }
72    Ok(())
73}
74
75async fn constraint_exists(conn: &impl ConnectionTrait, name: &str) -> Result<bool, Error> {
76    let stmt = Statement::from_sql_and_values(
77        DatabaseBackend::Postgres,
78        "SELECT 1 FROM pg_constraint WHERE conname = $1 LIMIT 1",
79        [name.into()],
80    );
81    Ok(conn.query_one(stmt).await?.is_some())
82}
83
84/// SQL column type for a field, honouring `max_length` on strings.
85fn column_type(field: &Field) -> String {
86    match field.ty {
87        FieldType::String => match field.max_length {
88            Some(n) => format!("varchar({n})"),
89            None => "varchar".to_string(),
90        },
91        // A file field holds the URL it is served from — long enough for a
92        // signed CDN link, short enough to stay indexable.
93        FieldType::File => format!("varchar({})", field.max_length.unwrap_or(1024)),
94        FieldType::Text => "text".to_string(),
95        FieldType::Integer => "integer".to_string(),
96        FieldType::BigInt => "bigint".to_string(),
97        FieldType::Float => "double precision".to_string(),
98        FieldType::Boolean => "boolean".to_string(),
99        FieldType::Uuid | FieldType::Reference => "uuid".to_string(),
100        FieldType::Timestamp => "timestamptz".to_string(),
101        FieldType::Json => "jsonb".to_string(),
102    }
103}
104
105/// A `DEFAULT <literal>` clause for a field's declared default, or empty.
106fn default_clause(field: &Field) -> String {
107    let Some(v) = &field.default else {
108        return String::new();
109    };
110    match v {
111        serde_json::Value::Bool(b) => format!(" DEFAULT {b}"),
112        serde_json::Value::Number(n) => format!(" DEFAULT {n}"),
113        serde_json::Value::String(s) => format!(" DEFAULT '{}'", s.replace('\'', "''")),
114        _ => String::new(),
115    }
116}
117
118async fn create_table_if_absent(conn: &impl ConnectionTrait, r: &Resource) -> Result<(), Error> {
119    let table = quote_ident(&r.table_name())?;
120    let mut cols: Vec<String> = vec![format!(
121        "{} uuid PRIMARY KEY DEFAULT gen_random_uuid()",
122        quote_ident("id")?
123    )];
124
125    for (name, field) in &r.fields {
126        let mut col = format!("{} {}", quote_ident(name)?, column_type(field));
127        col.push_str(&default_clause(field));
128        if field.required {
129            col.push_str(" NOT NULL");
130        }
131        if field.unique {
132            col.push_str(" UNIQUE");
133        }
134        cols.push(col);
135    }
136
137    if r.meta.timestamps {
138        cols.push(format!(
139            "{} timestamptz NOT NULL DEFAULT now()",
140            quote_ident("created_at")?
141        ));
142        cols.push(format!(
143            "{} timestamptz NOT NULL DEFAULT now()",
144            quote_ident("updated_at")?
145        ));
146    }
147
148    let sql = format!("CREATE TABLE IF NOT EXISTS {table} ({})", cols.join(", "));
149    conn.execute(Statement::from_string(DatabaseBackend::Postgres, sql))
150        .await?;
151    tracing::debug!(table = %r.table_name(), "ensured table");
152    Ok(())
153}
154
155async fn add_missing_columns(conn: &impl ConnectionTrait, r: &Resource) -> Result<(), Error> {
156    let existing = existing_columns(conn, &r.table_name()).await?;
157    let table = quote_ident(&r.table_name())?;
158
159    for (name, field) in &r.fields {
160        if existing.contains(name.as_str()) {
161            continue;
162        }
163        // New column on an existing table: apply its default but never NOT NULL
164        // without one, or the ALTER would fail on populated tables.
165        let sql = format!(
166            "ALTER TABLE {table} ADD COLUMN IF NOT EXISTS {} {}{}",
167            quote_ident(name)?,
168            column_type(field),
169            default_clause(field),
170        );
171        conn.execute(Statement::from_string(DatabaseBackend::Postgres, sql))
172            .await?;
173        tracing::info!(table = %r.table_name(), column = %name, "migrated: added column");
174    }
175    Ok(())
176}
177
178async fn existing_columns(
179    conn: &impl ConnectionTrait,
180    table: &str,
181) -> Result<HashSet<String>, Error> {
182    let stmt = Statement::from_sql_and_values(
183        DatabaseBackend::Postgres,
184        "SELECT column_name FROM information_schema.columns \
185         WHERE table_schema = current_schema() AND table_name = $1",
186        [table.into()],
187    );
188    let rows = conn.query_all(stmt).await?;
189    let mut set = HashSet::new();
190    for row in rows {
191        set.insert(row.try_get::<String>("", "column_name")?);
192    }
193    Ok(set)
194}
195
196#[cfg(test)]
197mod tests {
198    use super::*;
199    use apiplant_core::schema::OnDelete;
200
201    fn field(ty: FieldType) -> Field {
202        Field {
203            ty,
204            references: None,
205            required: false,
206            unique: false,
207            hidden: false,
208            default: None,
209            max_length: None,
210            on_delete: Some(OnDelete::Restrict),
211            admin: Default::default(),
212        }
213    }
214
215    #[test]
216    fn column_type_honours_max_length_and_json_types() {
217        let mut string = field(FieldType::String);
218        string.max_length = Some(320);
219        assert_eq!(column_type(&string), "varchar(320)");
220
221        assert_eq!(column_type(&field(FieldType::Reference)), "uuid");
222        assert_eq!(column_type(&field(FieldType::Json)), "jsonb");
223        assert_eq!(column_type(&field(FieldType::Timestamp)), "timestamptz");
224    }
225
226    #[test]
227    fn default_clause_renders_scalars_and_escapes_strings() {
228        let mut text = field(FieldType::String);
229        text.default = Some(serde_json::json!("O'Hara"));
230        assert_eq!(default_clause(&text), " DEFAULT 'O''Hara'");
231
232        let mut number = field(FieldType::Integer);
233        number.default = Some(serde_json::json!(42));
234        assert_eq!(default_clause(&number), " DEFAULT 42");
235
236        let mut structured = field(FieldType::Json);
237        structured.default = Some(serde_json::json!({ "nested": true }));
238        assert_eq!(default_clause(&structured), "");
239    }
240}
apiplant_db/migrate.rs

apiplant_db/
migrate.rs
