Skip to main content

appstruct_migrate/
sql.rs

1use crate::{
2    ColumnSchema, DatabaseSchema, DatabaseType, ForeignKeySchema, IndexSchema, MigrationPlan,
3    SchemaChange, SeedSchema, TableSchema, UniqueConstraintSchema,
4};
5use appstruct_ir::{GeneratedValueIr, OnDeleteIr};
6
7#[must_use]
8pub fn initial_migration(schema: &DatabaseSchema) -> String {
9    let mut statements = vec![generated_header()];
10    statements.extend(schema.tables.iter().map(create_table));
11    statements.extend(schema.unique_constraints.iter().map(add_unique_constraint));
12    statements.extend(schema.indexes.iter().map(add_index));
13    statements.extend(schema.seeds.iter().map(add_seed));
14    statements.extend(schema.foreign_keys.iter().map(add_foreign_key));
15    format!("{}\n", statements.join("\n"))
16}
17
18/// Render SQL for an automatically applicable migration plan.
19///
20/// # Errors
21///
22/// Returns an error when the plan contains a blocked or unsupported change.
23pub fn migration_sql(plan: &MigrationPlan) -> Result<String, String> {
24    if plan.is_blocked() {
25        return Err("migration contains changes that require review or input".to_owned());
26    }
27    let mut statements = vec![generated_header()];
28    for planned in &plan.changes {
29        statements.push(match &planned.change {
30            SchemaChange::AddTable { table } => create_table(table),
31            SchemaChange::AddColumn { table, column } => format!(
32                "ALTER TABLE {} ADD COLUMN {};\n",
33                quote_ident(table),
34                column_definition(column, true)
35            ),
36            SchemaChange::AlterColumn {
37                table,
38                before,
39                after,
40            } => alter_column(table, before, after)?,
41            SchemaChange::AddUniqueConstraint { constraint } => add_unique_constraint(constraint),
42            SchemaChange::AddIndex { index } => add_index(index),
43            SchemaChange::AddSeed { seed } => add_seed(seed),
44            SchemaChange::AddForeignKey { foreign_key } => add_foreign_key(foreign_key),
45            SchemaChange::RemoveTable { .. }
46            | SchemaChange::RenameTable { .. }
47            | SchemaChange::RemoveColumn { .. }
48            | SchemaChange::RemoveUniqueConstraint { .. }
49            | SchemaChange::RemoveIndex { .. }
50            | SchemaChange::RemoveSeed { .. }
51            | SchemaChange::RemoveForeignKey { .. } => {
52                return Err("destructive migration cannot be rendered automatically".to_owned());
53            }
54        });
55    }
56    Ok(format!("{}\n", statements.join("\n")))
57}
58
59fn create_table(table: &TableSchema) -> String {
60    let primary_keys = table
61        .columns
62        .iter()
63        .filter(|column| column.primary_key)
64        .collect::<Vec<_>>();
65    let inline_primary_key = primary_keys.len() <= 1;
66    let mut definitions = table
67        .columns
68        .iter()
69        .map(|column| column_definition(column, inline_primary_key))
70        .collect::<Vec<_>>();
71    if !inline_primary_key {
72        let columns = primary_keys
73            .iter()
74            .map(|column| quote_ident(&column.name))
75            .collect::<Vec<_>>()
76            .join(", ");
77        definitions.push(format!("PRIMARY KEY ({columns})"));
78    }
79    let columns = definitions.join(",\n    ");
80    format!(
81        "CREATE TABLE {} (\n    {columns}\n);\n",
82        quote_ident(&table.name)
83    )
84}
85
86fn column_definition(column: &ColumnSchema, inline_primary_key: bool) -> String {
87    let mut fragments = vec![
88        quote_ident(&column.name),
89        sql_type(&column.data_type).to_owned(),
90    ];
91    if matches!(column.generated, Some(GeneratedValueIr::AutoIncrement)) {
92        fragments.push("GENERATED BY DEFAULT AS IDENTITY".to_owned());
93    }
94    if !column.nullable {
95        fragments.push("NOT NULL".to_owned());
96    }
97    if column.primary_key && inline_primary_key {
98        fragments.push("PRIMARY KEY".to_owned());
99    } else if column.unique {
100        fragments.push("UNIQUE".to_owned());
101    }
102    if let Some(default) = sql_default(column) {
103        fragments.push(format!("DEFAULT {default}"));
104    }
105    if let DatabaseType::Enum { values } = &column.data_type {
106        let choices = values
107            .iter()
108            .map(|value| quote_literal(value))
109            .collect::<Vec<_>>()
110            .join(", ");
111        fragments.push(format!(
112            "CHECK ({} IN ({choices}))",
113            quote_ident(&column.name)
114        ));
115    }
116    fragments.join(" ")
117}
118
119const fn sql_type(data_type: &DatabaseType) -> &'static str {
120    match data_type {
121        DatabaseType::Uuid => "UUID",
122        DatabaseType::Text | DatabaseType::Enum { .. } => "TEXT",
123        DatabaseType::Integer => "INTEGER",
124        DatabaseType::Bigint => "BIGINT",
125        DatabaseType::Decimal => "NUMERIC",
126        DatabaseType::Boolean => "BOOLEAN",
127        DatabaseType::Date => "DATE",
128        DatabaseType::Datetime => "TIMESTAMPTZ",
129        DatabaseType::Json => "JSONB",
130    }
131}
132
133fn sql_default(column: &ColumnSchema) -> Option<String> {
134    if matches!(column.generated, Some(GeneratedValueIr::Now)) {
135        return Some("CURRENT_TIMESTAMP".to_owned());
136    }
137    if matches!(column.generated, Some(GeneratedValueIr::Revision)) {
138        return Some("1".to_owned());
139    }
140    let value = column.default.as_ref()?;
141    Some(match column.data_type {
142        DatabaseType::Boolean
143        | DatabaseType::Integer
144        | DatabaseType::Bigint
145        | DatabaseType::Decimal => value.clone(),
146        _ => quote_literal(value),
147    })
148}
149
150fn add_foreign_key(foreign_key: &ForeignKeySchema) -> String {
151    let action = match foreign_key.on_delete {
152        OnDeleteIr::Restrict => "RESTRICT".to_owned(),
153        OnDeleteIr::Cascade => "CASCADE".to_owned(),
154        OnDeleteIr::SetNull if foreign_key.source_columns.len() > 1 => {
155            let nullable_columns = foreign_key.source_columns[1..]
156                .iter()
157                .map(|column| quote_ident(column))
158                .collect::<Vec<_>>()
159                .join(", ");
160            format!("SET NULL ({nullable_columns})")
161        }
162        OnDeleteIr::SetNull => "SET NULL".to_owned(),
163    };
164    let source_columns = quote_columns(&foreign_key.source_columns);
165    let target_columns = quote_columns(&foreign_key.target_columns);
166    let constraint = format!(
167        "fk_{}_{}",
168        foreign_key.source_table,
169        foreign_key.source_columns.join("_")
170    );
171    format!(
172        "ALTER TABLE {} ADD CONSTRAINT {} FOREIGN KEY ({source_columns}) REFERENCES {} ({target_columns}) ON DELETE {action};\n",
173        quote_ident(&foreign_key.source_table),
174        quote_ident(&constraint),
175        quote_ident(&foreign_key.target_table),
176    )
177}
178
179fn add_unique_constraint(constraint: &UniqueConstraintSchema) -> String {
180    let name = format!("uq_{}_{}", constraint.table, constraint.columns.join("_"));
181    let columns = quote_columns(&constraint.columns);
182    format!(
183        "ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({columns});\n",
184        quote_ident(&constraint.table),
185        quote_ident(&name)
186    )
187}
188
189fn add_index(index: &IndexSchema) -> String {
190    let unique = if index.unique { " UNIQUE" } else { "" };
191    let predicate = index
192        .predicate
193        .as_ref()
194        .map_or_else(String::new, |value| format!(" WHERE ({value})"));
195    format!(
196        "CREATE{unique} INDEX {} ON {} ({}){predicate};\n",
197        quote_ident(&index_name(index)),
198        quote_ident(&index.table),
199        quote_columns(&index.columns),
200    )
201}
202
203fn index_name(index: &IndexSchema) -> String {
204    let suffix = index.id.rsplit("::").next().unwrap_or(index.id.as_str());
205    format!("idx_{}", suffix.replace('.', "_"))
206}
207
208fn add_seed(seed: &SeedSchema) -> String {
209    let columns = seed
210        .values
211        .iter()
212        .map(|value| quote_ident(&value.column))
213        .collect::<Vec<_>>()
214        .join(", ");
215    let values = seed
216        .values
217        .iter()
218        .map(|value| seed_value(&value.value, &value.data_type))
219        .collect::<Vec<_>>()
220        .join(", ");
221    format!(
222        "INSERT INTO {} ({columns}) VALUES ({values}) ON CONFLICT DO NOTHING;\n",
223        quote_ident(&seed.table)
224    )
225}
226
227fn seed_value(value: &str, data_type: &DatabaseType) -> String {
228    match data_type {
229        DatabaseType::Boolean if matches!(value, "true" | "false") => value.to_owned(),
230        DatabaseType::Integer if value.parse::<i32>().is_ok() => value.to_owned(),
231        DatabaseType::Bigint if value.parse::<i64>().is_ok() => value.to_owned(),
232        DatabaseType::Decimal if value.parse::<f64>().is_ok_and(f64::is_finite) => value.to_owned(),
233        _ => quote_literal(value),
234    }
235}
236
237fn quote_columns(columns: &[String]) -> String {
238    columns
239        .iter()
240        .map(|column| quote_ident(column))
241        .collect::<Vec<_>>()
242        .join(", ")
243}
244
245fn alter_column(
246    table: &str,
247    before: &ColumnSchema,
248    after: &ColumnSchema,
249) -> Result<String, String> {
250    if before.name != after.name
251        || before.data_type != after.data_type
252        || before.primary_key != after.primary_key
253        || before.unique != after.unique
254        || before.generated != after.generated
255    {
256        return Err("column shape change requires manual migration".to_owned());
257    }
258    if before.nullable && !after.nullable {
259        return Err("adding a not-null constraint requires manual migration".to_owned());
260    }
261    let prefix = format!(
262        "ALTER TABLE {} ALTER COLUMN {}",
263        quote_ident(table),
264        quote_ident(&after.name)
265    );
266    let mut statements = Vec::new();
267    if before.nullable != after.nullable {
268        statements.push(format!("{prefix} DROP NOT NULL;\n"));
269    }
270    if before.default != after.default {
271        statements.push(sql_default(after).map_or_else(
272            || format!("{prefix} DROP DEFAULT;\n"),
273            |default| format!("{prefix} SET DEFAULT {default};\n"),
274        ));
275    }
276    Ok(statements.concat())
277}
278
279fn generated_header() -> String {
280    "-- @generated by AppStruct. Review before applying.\n".to_owned()
281}
282
283fn quote_ident(value: &str) -> String {
284    format!("\"{}\"", value.replace('"', "\"\""))
285}
286
287fn quote_literal(value: &str) -> String {
288    format!("'{}'", value.replace('\'', "''"))
289}
290
291#[cfg(test)]
292mod tests {
293    use super::*;
294
295    #[test]
296    fn quotes_sql_values() {
297        assert_eq!(quote_ident("project\"name"), "\"project\"\"name\"");
298        assert_eq!(quote_literal("it's"), "'it''s'");
299    }
300}