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

sqlmodel_schema/ddl/
postgres.rs
