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        FieldType::Text => "text".to_string(),
92        FieldType::Integer => "integer".to_string(),
93        FieldType::BigInt => "bigint".to_string(),
94        FieldType::Float => "double precision".to_string(),
95        FieldType::Boolean => "boolean".to_string(),
96        FieldType::Uuid | FieldType::Reference => "uuid".to_string(),
97        FieldType::Timestamp => "timestamptz".to_string(),
98        FieldType::Json => "jsonb".to_string(),
99    }
100}
101
102/// A `DEFAULT <literal>` clause for a field's declared default, or empty.
103fn default_clause(field: &Field) -> String {
104    let Some(v) = &field.default else {
105        return String::new();
106    };
107    match v {
108        serde_json::Value::Bool(b) => format!(" DEFAULT {b}"),
109        serde_json::Value::Number(n) => format!(" DEFAULT {n}"),
110        serde_json::Value::String(s) => format!(" DEFAULT '{}'", s.replace('\'', "''")),
111        _ => String::new(),
112    }
113}
114
115async fn create_table_if_absent(conn: &impl ConnectionTrait, r: &Resource) -> Result<(), Error> {
116    let table = quote_ident(&r.table_name())?;
117    let mut cols: Vec<String> = vec![format!(
118        "{} uuid PRIMARY KEY DEFAULT gen_random_uuid()",
119        quote_ident("id")?
120    )];
121
122    for (name, field) in &r.fields {
123        let mut col = format!("{} {}", quote_ident(name)?, column_type(field));
124        col.push_str(&default_clause(field));
125        if field.required {
126            col.push_str(" NOT NULL");
127        }
128        if field.unique {
129            col.push_str(" UNIQUE");
130        }
131        cols.push(col);
132    }
133
134    if r.meta.timestamps {
135        cols.push(format!(
136            "{} timestamptz NOT NULL DEFAULT now()",
137            quote_ident("created_at")?
138        ));
139        cols.push(format!(
140            "{} timestamptz NOT NULL DEFAULT now()",
141            quote_ident("updated_at")?
142        ));
143    }
144
145    let sql = format!("CREATE TABLE IF NOT EXISTS {table} ({})", cols.join(", "));
146    conn.execute(Statement::from_string(DatabaseBackend::Postgres, sql))
147        .await?;
148    tracing::debug!(table = %r.table_name(), "ensured table");
149    Ok(())
150}
151
152async fn add_missing_columns(conn: &impl ConnectionTrait, r: &Resource) -> Result<(), Error> {
153    let existing = existing_columns(conn, &r.table_name()).await?;
154    let table = quote_ident(&r.table_name())?;
155
156    for (name, field) in &r.fields {
157        if existing.contains(name.as_str()) {
158            continue;
159        }
160        // New column on an existing table: apply its default but never NOT NULL
161        // without one, or the ALTER would fail on populated tables.
162        let sql = format!(
163            "ALTER TABLE {table} ADD COLUMN IF NOT EXISTS {} {}{}",
164            quote_ident(name)?,
165            column_type(field),
166            default_clause(field),
167        );
168        conn.execute(Statement::from_string(DatabaseBackend::Postgres, sql))
169            .await?;
170        tracing::info!(table = %r.table_name(), column = %name, "migrated: added column");
171    }
172    Ok(())
173}
174
175async fn existing_columns(
176    conn: &impl ConnectionTrait,
177    table: &str,
178) -> Result<HashSet<String>, Error> {
179    let stmt = Statement::from_sql_and_values(
180        DatabaseBackend::Postgres,
181        "SELECT column_name FROM information_schema.columns \
182         WHERE table_schema = current_schema() AND table_name = $1",
183        [table.into()],
184    );
185    let rows = conn.query_all(stmt).await?;
186    let mut set = HashSet::new();
187    for row in rows {
188        set.insert(row.try_get::<String>("", "column_name")?);
189    }
190    Ok(set)
191}
192
193#[cfg(test)]
194mod tests {
195    use super::*;
196    use apiplant_core::schema::OnDelete;
197
198    fn field(ty: FieldType) -> Field {
199        Field {
200            ty,
201            references: None,
202            required: false,
203            unique: false,
204            hidden: false,
205            default: None,
206            max_length: None,
207            on_delete: Some(OnDelete::Restrict),
208            admin: Default::default(),
209        }
210    }
211
212    #[test]
213    fn column_type_honours_max_length_and_json_types() {
214        let mut string = field(FieldType::String);
215        string.max_length = Some(320);
216        assert_eq!(column_type(&string), "varchar(320)");
217
218        assert_eq!(column_type(&field(FieldType::Reference)), "uuid");
219        assert_eq!(column_type(&field(FieldType::Json)), "jsonb");
220        assert_eq!(column_type(&field(FieldType::Timestamp)), "timestamptz");
221    }
222
223    #[test]
224    fn default_clause_renders_scalars_and_escapes_strings() {
225        let mut text = field(FieldType::String);
226        text.default = Some(serde_json::json!("O'Hara"));
227        assert_eq!(default_clause(&text), " DEFAULT 'O''Hara'");
228
229        let mut number = field(FieldType::Integer);
230        number.default = Some(serde_json::json!(42));
231        assert_eq!(default_clause(&number), " DEFAULT 42");
232
233        let mut structured = field(FieldType::Json);
234        structured.default = Some(serde_json::json!({ "nested": true }));
235        assert_eq!(default_clause(&structured), "");
236    }
237}
apiplant_db/migrate.rs

apiplant_db/
migrate.rs
