sqlmodel_schema/ddl/
postgres.rs

1//! PostgreSQL DDL generator.
2//!
3//! PostgreSQL has excellent ALTER TABLE support with fine-grained control over schema changes.
4
5use super::{
6    DdlGenerator, format_fk_constraint, generate_add_column, generate_create_index,
7    generate_create_table, generate_drop_index, generate_drop_table, generate_rename_column,
8    generate_rename_table, quote_identifier,
9};
10use crate::diff::SchemaOperation;
11use crate::introspect::Dialect;
12
13/// DDL generator for PostgreSQL.
14pub struct PostgresDdlGenerator;
15
16impl DdlGenerator for PostgresDdlGenerator {
17    fn dialect(&self) -> &'static str {
18        "postgres"
19    }
20
21    fn generate(&self, op: &SchemaOperation) -> Vec<String> {
22        tracing::debug!(dialect = "postgres", op = ?op, "Generating DDL");
23
24        let statements = match op {
25            // Tables
26            SchemaOperation::CreateTable(table) => {
27                vec![generate_create_table(table, Dialect::Postgres)]
28            }
29            SchemaOperation::DropTable(name) => {
30                vec![generate_drop_table(name, Dialect::Postgres)]
31            }
32            SchemaOperation::RenameTable { from, to } => {
33                vec![generate_rename_table(from, to, Dialect::Postgres)]
34            }
35
36            // Columns
37            SchemaOperation::AddColumn { table, column } => {
38                vec![generate_add_column(table, column, Dialect::Postgres)]
39            }
40            SchemaOperation::DropColumn { table, column } => {
41                vec![format!(
42                    "ALTER TABLE {} DROP COLUMN {}",
43                    quote_identifier(table, Dialect::Postgres),
44                    quote_identifier(column, Dialect::Postgres)
45                )]
46            }
47            SchemaOperation::AlterColumnType {
48                table,
49                column,
50                to_type,
51                ..
52            } => {
53                // PostgreSQL uses ALTER COLUMN ... TYPE
54                // USING clause may be needed for type conversion
55                vec![format!(
56                    "ALTER TABLE {} ALTER COLUMN {} TYPE {}",
57                    quote_identifier(table, Dialect::Postgres),
58                    quote_identifier(column, Dialect::Postgres),
59                    to_type
60                )]
61            }
62            SchemaOperation::AlterColumnNullable {
63                table,
64                column,
65                to_nullable,
66                ..
67            } => {
68                // PostgreSQL uses SET NOT NULL / DROP NOT NULL
69                let action = if *to_nullable {
70                    "DROP NOT NULL"
71                } else {
72                    "SET NOT NULL"
73                };
74                vec![format!(
75                    "ALTER TABLE {} ALTER COLUMN {} {}",
76                    quote_identifier(table, Dialect::Postgres),
77                    quote_identifier(column, Dialect::Postgres),
78                    action
79                )]
80            }
81            SchemaOperation::AlterColumnDefault {
82                table,
83                column,
84                to_default,
85                ..
86            } => {
87                // PostgreSQL uses SET DEFAULT / DROP DEFAULT
88                if let Some(default) = to_default {
89                    vec![format!(
90                        "ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {}",
91                        quote_identifier(table, Dialect::Postgres),
92                        quote_identifier(column, Dialect::Postgres),
93                        default
94                    )]
95                } else {
96                    vec![format!(
97                        "ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT",
98                        quote_identifier(table, Dialect::Postgres),
99                        quote_identifier(column, Dialect::Postgres)
100                    )]
101                }
102            }
103            SchemaOperation::RenameColumn { table, from, to } => {
104                vec![generate_rename_column(table, from, to, Dialect::Postgres)]
105            }
106
107            // Primary Keys
108            SchemaOperation::AddPrimaryKey { table, columns } => {
109                let cols: Vec<String> = columns
110                    .iter()
111                    .map(|c| quote_identifier(c, Dialect::Postgres))
112                    .collect();
113                // PostgreSQL auto-generates constraint name as {table}_pkey
114                vec![format!(
115                    "ALTER TABLE {} ADD PRIMARY KEY ({})",
116                    quote_identifier(table, Dialect::Postgres),
117                    cols.join(", ")
118                )]
119            }
120            SchemaOperation::DropPrimaryKey { table } => {
121                // PostgreSQL requires the constraint name, which is typically {table}_pkey
122                let constraint_name = format!("{}_pkey", table);
123                vec![format!(
124                    "ALTER TABLE {} DROP CONSTRAINT {}",
125                    quote_identifier(table, Dialect::Postgres),
126                    quote_identifier(&constraint_name, Dialect::Postgres)
127                )]
128            }
129
130            // Foreign Keys
131            SchemaOperation::AddForeignKey { table, fk } => {
132                let constraint_name = fk
133                    .name
134                    .clone()
135                    .unwrap_or_else(|| format!("fk_{}_{}", table, fk.column));
136                vec![format!(
137                    "ALTER TABLE {} ADD CONSTRAINT {} {}",
138                    quote_identifier(table, Dialect::Postgres),
139                    quote_identifier(&constraint_name, Dialect::Postgres),
140                    format_fk_constraint(fk, Dialect::Postgres)
141                )]
142            }
143            SchemaOperation::DropForeignKey { table, name } => {
144                vec![format!(
145                    "ALTER TABLE {} DROP CONSTRAINT {}",
146                    quote_identifier(table, Dialect::Postgres),
147                    quote_identifier(name, Dialect::Postgres)
148                )]
149            }
150
151            // Unique Constraints
152            SchemaOperation::AddUnique { table, constraint } => {
153                let cols: Vec<String> = constraint
154                    .columns
155                    .iter()
156                    .map(|c| quote_identifier(c, Dialect::Postgres))
157                    .collect();
158                let name = constraint
159                    .name
160                    .clone()
161                    .unwrap_or_else(|| format!("uk_{}_{}", table, constraint.columns.join("_")));
162                vec![format!(
163                    "ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({})",
164                    quote_identifier(table, Dialect::Postgres),
165                    quote_identifier(&name, Dialect::Postgres),
166                    cols.join(", ")
167                )]
168            }
169            SchemaOperation::DropUnique { table, name } => {
170                vec![format!(
171                    "ALTER TABLE {} DROP CONSTRAINT {}",
172                    quote_identifier(table, Dialect::Postgres),
173                    quote_identifier(name, Dialect::Postgres)
174                )]
175            }
176
177            // Indexes
178            SchemaOperation::CreateIndex { table, index } => {
179                vec![generate_create_index(table, index, Dialect::Postgres)]
180            }
181            SchemaOperation::DropIndex { table, name } => {
182                vec![generate_drop_index(table, name, Dialect::Postgres)]
183            }
184        };
185
186        for stmt in &statements {
187            tracing::trace!(sql = %stmt, "Generated PostgreSQL DDL statement");
188        }
189
190        statements
191    }
192}
193
194// ============================================================================
195// Unit Tests
196// ============================================================================
197
198#[cfg(test)]
199mod tests {
200    use super::*;
201    use crate::diff::SchemaOperation;
202    use crate::introspect::{
203        ColumnInfo, ForeignKeyInfo, IndexInfo, ParsedSqlType, TableInfo, UniqueConstraintInfo,
204    };
205
206    fn make_column(name: &str, sql_type: &str, nullable: bool) -> ColumnInfo {
207        ColumnInfo {
208            name: name.to_string(),
209            sql_type: sql_type.to_string(),
210            parsed_type: ParsedSqlType::parse(sql_type),
211            nullable,
212            default: None,
213            primary_key: false,
214            auto_increment: false,
215            comment: None,
216        }
217    }
218
219    fn make_table(name: &str, columns: Vec<ColumnInfo>, pk: Vec<&str>) -> TableInfo {
220        TableInfo {
221            name: name.to_string(),
222            columns,
223            primary_key: pk.into_iter().map(String::from).collect(),
224            foreign_keys: Vec::new(),
225            unique_constraints: Vec::new(),
226            check_constraints: Vec::new(),
227            indexes: Vec::new(),
228            comment: None,
229        }
230    }
231
232    #[test]
233    fn test_create_table() {
234        let ddl = PostgresDdlGenerator;
235        let table = make_table(
236            "heroes",
237            vec![
238                make_column("id", "SERIAL", false),
239                make_column("name", "VARCHAR(100)", false),
240            ],
241            vec!["id"],
242        );
243        let op = SchemaOperation::CreateTable(table);
244        let stmts = ddl.generate(&op);
245
246        assert_eq!(stmts.len(), 1);
247        assert!(stmts[0].contains("CREATE TABLE IF NOT EXISTS"));
248        assert!(stmts[0].contains("\"heroes\""));
249    }
250
251    #[test]
252    fn test_drop_table() {
253        let ddl = PostgresDdlGenerator;
254        let op = SchemaOperation::DropTable("heroes".to_string());
255        let stmts = ddl.generate(&op);
256
257        assert_eq!(stmts.len(), 1);
258        assert_eq!(stmts[0], "DROP TABLE IF EXISTS \"heroes\"");
259    }
260
261    #[test]
262    fn test_rename_table() {
263        let ddl = PostgresDdlGenerator;
264        let op = SchemaOperation::RenameTable {
265            from: "old_heroes".to_string(),
266            to: "heroes".to_string(),
267        };
268        let stmts = ddl.generate(&op);
269
270        assert_eq!(stmts.len(), 1);
271        assert!(stmts[0].contains("ALTER TABLE"));
272        assert!(stmts[0].contains("RENAME TO"));
273    }
274
275    #[test]
276    fn test_add_column() {
277        let ddl = PostgresDdlGenerator;
278        let op = SchemaOperation::AddColumn {
279            table: "heroes".to_string(),
280            column: make_column("age", "INTEGER", true),
281        };
282        let stmts = ddl.generate(&op);
283
284        assert_eq!(stmts.len(), 1);
285        assert!(stmts[0].contains("ALTER TABLE"));
286        assert!(stmts[0].contains("ADD COLUMN"));
287    }
288
289    #[test]
290    fn test_drop_column() {
291        let ddl = PostgresDdlGenerator;
292        let op = SchemaOperation::DropColumn {
293            table: "heroes".to_string(),
294            column: "old_field".to_string(),
295        };
296        let stmts = ddl.generate(&op);
297
298        assert_eq!(stmts.len(), 1);
299        assert!(stmts[0].contains("ALTER TABLE"));
300        assert!(stmts[0].contains("DROP COLUMN"));
301    }
302
303    #[test]
304    fn test_alter_column_type() {
305        let ddl = PostgresDdlGenerator;
306        let op = SchemaOperation::AlterColumnType {
307            table: "heroes".to_string(),
308            column: "age".to_string(),
309            from_type: "INTEGER".to_string(),
310            to_type: "BIGINT".to_string(),
311        };
312        let stmts = ddl.generate(&op);
313
314        assert_eq!(stmts.len(), 1);
315        assert!(stmts[0].contains("ALTER COLUMN"));
316        assert!(stmts[0].contains("TYPE BIGINT"));
317    }
318
319    #[test]
320    fn test_alter_column_set_not_null() {
321        let ddl = PostgresDdlGenerator;
322        let op = SchemaOperation::AlterColumnNullable {
323            table: "heroes".to_string(),
324            column: "name".to_string(),
325            from_nullable: true,
326            to_nullable: false,
327        };
328        let stmts = ddl.generate(&op);
329
330        assert_eq!(stmts.len(), 1);
331        assert!(stmts[0].contains("SET NOT NULL"));
332    }
333
334    #[test]
335    fn test_alter_column_drop_not_null() {
336        let ddl = PostgresDdlGenerator;
337        let op = SchemaOperation::AlterColumnNullable {
338            table: "heroes".to_string(),
339            column: "name".to_string(),
340            from_nullable: false,
341            to_nullable: true,
342        };
343        let stmts = ddl.generate(&op);
344
345        assert_eq!(stmts.len(), 1);
346        assert!(stmts[0].contains("DROP NOT NULL"));
347    }
348
349    #[test]
350    fn test_alter_column_set_default() {
351        let ddl = PostgresDdlGenerator;
352        let op = SchemaOperation::AlterColumnDefault {
353            table: "heroes".to_string(),
354            column: "status".to_string(),
355            from_default: None,
356            to_default: Some("'active'".to_string()),
357        };
358        let stmts = ddl.generate(&op);
359
360        assert_eq!(stmts.len(), 1);
361        assert!(stmts[0].contains("SET DEFAULT"));
362        assert!(stmts[0].contains("'active'"));
363    }
364
365    #[test]
366    fn test_alter_column_drop_default() {
367        let ddl = PostgresDdlGenerator;
368        let op = SchemaOperation::AlterColumnDefault {
369            table: "heroes".to_string(),
370            column: "status".to_string(),
371            from_default: Some("'active'".to_string()),
372            to_default: None,
373        };
374        let stmts = ddl.generate(&op);
375
376        assert_eq!(stmts.len(), 1);
377        assert!(stmts[0].contains("DROP DEFAULT"));
378    }
379
380    #[test]
381    fn test_rename_column() {
382        let ddl = PostgresDdlGenerator;
383        let op = SchemaOperation::RenameColumn {
384            table: "heroes".to_string(),
385            from: "old_name".to_string(),
386            to: "name".to_string(),
387        };
388        let stmts = ddl.generate(&op);
389
390        assert_eq!(stmts.len(), 1);
391        assert!(stmts[0].contains("RENAME COLUMN"));
392    }
393
394    #[test]
395    fn test_add_primary_key() {
396        let ddl = PostgresDdlGenerator;
397        let op = SchemaOperation::AddPrimaryKey {
398            table: "heroes".to_string(),
399            columns: vec!["id".to_string()],
400        };
401        let stmts = ddl.generate(&op);
402
403        assert_eq!(stmts.len(), 1);
404        assert!(stmts[0].contains("ADD PRIMARY KEY"));
405    }
406
407    #[test]
408    fn test_drop_primary_key() {
409        let ddl = PostgresDdlGenerator;
410        let op = SchemaOperation::DropPrimaryKey {
411            table: "heroes".to_string(),
412        };
413        let stmts = ddl.generate(&op);
414
415        assert_eq!(stmts.len(), 1);
416        assert!(stmts[0].contains("DROP CONSTRAINT"));
417        assert!(stmts[0].contains("\"heroes_pkey\""));
418    }
419
420    #[test]
421    fn test_add_foreign_key() {
422        let ddl = PostgresDdlGenerator;
423        let op = SchemaOperation::AddForeignKey {
424            table: "heroes".to_string(),
425            fk: ForeignKeyInfo {
426                name: Some("fk_heroes_team".to_string()),
427                column: "team_id".to_string(),
428                foreign_table: "teams".to_string(),
429                foreign_column: "id".to_string(),
430                on_delete: Some("CASCADE".to_string()),
431                on_update: None,
432            },
433        };
434        let stmts = ddl.generate(&op);
435
436        assert_eq!(stmts.len(), 1);
437        assert!(stmts[0].contains("ADD CONSTRAINT"));
438        assert!(stmts[0].contains("FOREIGN KEY"));
439        assert!(stmts[0].contains("ON DELETE CASCADE"));
440    }
441
442    #[test]
443    fn test_drop_foreign_key() {
444        let ddl = PostgresDdlGenerator;
445        let op = SchemaOperation::DropForeignKey {
446            table: "heroes".to_string(),
447            name: "fk_heroes_team".to_string(),
448        };
449        let stmts = ddl.generate(&op);
450
451        assert_eq!(stmts.len(), 1);
452        assert!(stmts[0].contains("DROP CONSTRAINT"));
453        assert!(stmts[0].contains("\"fk_heroes_team\""));
454    }
455
456    #[test]
457    fn test_add_unique() {
458        let ddl = PostgresDdlGenerator;
459        let op = SchemaOperation::AddUnique {
460            table: "heroes".to_string(),
461            constraint: UniqueConstraintInfo {
462                name: Some("uk_heroes_name".to_string()),
463                columns: vec!["name".to_string()],
464            },
465        };
466        let stmts = ddl.generate(&op);
467
468        assert_eq!(stmts.len(), 1);
469        assert!(stmts[0].contains("ADD CONSTRAINT"));
470        assert!(stmts[0].contains("UNIQUE"));
471    }
472
473    #[test]
474    fn test_drop_unique() {
475        let ddl = PostgresDdlGenerator;
476        let op = SchemaOperation::DropUnique {
477            table: "heroes".to_string(),
478            name: "uk_heroes_name".to_string(),
479        };
480        let stmts = ddl.generate(&op);
481
482        assert_eq!(stmts.len(), 1);
483        assert!(stmts[0].contains("DROP CONSTRAINT"));
484    }
485
486    #[test]
487    fn test_create_index() {
488        let ddl = PostgresDdlGenerator;
489        let op = SchemaOperation::CreateIndex {
490            table: "heroes".to_string(),
491            index: IndexInfo {
492                name: "idx_heroes_name".to_string(),
493                columns: vec!["name".to_string()],
494                unique: false,
495                index_type: Some("btree".to_string()),
496                primary: false,
497            },
498        };
499        let stmts = ddl.generate(&op);
500
501        assert_eq!(stmts.len(), 1);
502        assert!(stmts[0].contains("CREATE INDEX"));
503        assert!(stmts[0].contains("USING btree"));
504    }
505
506    #[test]
507    fn test_create_gin_index() {
508        let ddl = PostgresDdlGenerator;
509        let op = SchemaOperation::CreateIndex {
510            table: "heroes".to_string(),
511            index: IndexInfo {
512                name: "idx_heroes_tags".to_string(),
513                columns: vec!["tags".to_string()],
514                unique: false,
515                index_type: Some("gin".to_string()),
516                primary: false,
517            },
518        };
519        let stmts = ddl.generate(&op);
520
521        assert_eq!(stmts.len(), 1);
522        assert!(stmts[0].contains("USING gin"));
523    }
524
525    #[test]
526    fn test_drop_index() {
527        let ddl = PostgresDdlGenerator;
528        let op = SchemaOperation::DropIndex {
529            table: "heroes".to_string(),
530            name: "idx_heroes_name".to_string(),
531        };
532        let stmts = ddl.generate(&op);
533
534        assert_eq!(stmts.len(), 1);
535        assert!(stmts[0].contains("DROP INDEX IF EXISTS"));
536    }
537
538    #[test]
539    fn test_dialect() {
540        let ddl = PostgresDdlGenerator;
541        assert_eq!(ddl.dialect(), "postgres");
542    }
543
544    #[test]
545    fn test_composite_primary_key() {
546        let ddl = PostgresDdlGenerator;
547        let op = SchemaOperation::AddPrimaryKey {
548            table: "hero_team".to_string(),
549            columns: vec!["hero_id".to_string(), "team_id".to_string()],
550        };
551        let stmts = ddl.generate(&op);
552
553        assert_eq!(stmts.len(), 1);
554        assert!(stmts[0].contains("ADD PRIMARY KEY"));
555        assert!(stmts[0].contains("\"hero_id\""));
556        assert!(stmts[0].contains("\"team_id\""));
557    }
558}
sqlmodel_schema/ddl/postgres.rs

sqlmodel_schema/ddl/
postgres.rs
