oxide_sql_core/migrations/dialect/
duckdb.rs

1//! DuckDB dialect for migrations.
2
3use super::MigrationDialect;
4use crate::ast::DataType;
5use crate::migrations::column_builder::{ColumnDefinition, DefaultValue};
6use crate::migrations::operation::{
7    AlterColumnChange, AlterColumnOp, CreateTableOp, DropIndexOp, RenameColumnOp, RenameTableOp,
8};
9use crate::schema::RustTypeMapping;
10
11/// DuckDB dialect for migration SQL generation.
12///
13/// DuckDB does not support `AUTOINCREMENT` or `SERIAL`/`BIGSERIAL`.
14/// Instead, auto-increment is implemented via `CREATE SEQUENCE` +
15/// `DEFAULT nextval('seq_<table>_<column>')`.  The [`create_table`]
16/// override emits the sequence DDL automatically for every column
17/// marked with `autoincrement`.
18#[derive(Debug, Clone, Copy, Default)]
19pub struct DuckDbDialect;
20
21impl DuckDbDialect {
22    /// Creates a new DuckDB dialect.
23    #[must_use]
24    pub const fn new() -> Self {
25        Self
26    }
27
28    /// Generates a column definition with sequence-backed default for
29    /// autoincrement columns, using the given table name to build the
30    /// sequence name.
31    fn column_def_with_table(&self, col: &ColumnDefinition, table: &str) -> String {
32        let data_type = self.map_data_type(&col.data_type);
33        let mut sql = format!("{} {}", self.quote_identifier(&col.name), data_type);
34
35        if col.primary_key {
36            sql.push_str(" PRIMARY KEY");
37        } else {
38            if !col.nullable {
39                sql.push_str(" NOT NULL");
40            }
41            if col.unique {
42                sql.push_str(" UNIQUE");
43            }
44        }
45
46        if col.autoincrement && col.default.is_none() {
47            sql.push_str(&format!(" DEFAULT nextval('seq_{}_{}')", table, col.name,));
48        } else if let Some(ref default) = col.default {
49            sql.push_str(" DEFAULT ");
50            sql.push_str(&self.render_default(default));
51        }
52
53        if let Some(ref fk) = col.references {
54            sql.push_str(" REFERENCES ");
55            sql.push_str(&self.quote_identifier(&fk.table));
56            sql.push_str(" (");
57            sql.push_str(&self.quote_identifier(&fk.column));
58            sql.push(')');
59            if let Some(action) = fk.on_delete {
60                sql.push_str(" ON DELETE ");
61                sql.push_str(action.as_sql());
62            }
63            if let Some(action) = fk.on_update {
64                sql.push_str(" ON UPDATE ");
65                sql.push_str(action.as_sql());
66            }
67        }
68
69        if let Some(ref check) = col.check {
70            sql.push_str(&format!(" CHECK ({})", check));
71        }
72
73        if let Some(ref collation) = col.collation {
74            sql.push_str(&format!(" COLLATE \"{}\"", collation));
75        }
76
77        sql
78    }
79}
80
81impl MigrationDialect for DuckDbDialect {
82    fn name(&self) -> &'static str {
83        "duckdb"
84    }
85
86    fn map_data_type(&self, dt: &DataType) -> String {
87        match dt {
88            DataType::Smallint => "SMALLINT".to_string(),
89            DataType::Integer => "INTEGER".to_string(),
90            DataType::Bigint => "BIGINT".to_string(),
91            DataType::Real => "REAL".to_string(),
92            DataType::Double => "DOUBLE".to_string(),
93            DataType::Decimal { precision, scale } => match (precision, scale) {
94                (Some(p), Some(s)) => format!("DECIMAL({p}, {s})"),
95                (Some(p), None) => format!("DECIMAL({p})"),
96                _ => "DECIMAL".to_string(),
97            },
98            DataType::Numeric { precision, scale } => match (precision, scale) {
99                (Some(p), Some(s)) => format!("NUMERIC({p}, {s})"),
100                (Some(p), None) => format!("NUMERIC({p})"),
101                _ => "NUMERIC".to_string(),
102            },
103            DataType::Char(len) => match len {
104                Some(n) => format!("CHAR({n})"),
105                None => "CHAR".to_string(),
106            },
107            DataType::Varchar(len) => match len {
108                Some(n) => format!("VARCHAR({n})"),
109                None => "VARCHAR".to_string(),
110            },
111            DataType::Text => "TEXT".to_string(),
112            DataType::Blob => "BLOB".to_string(),
113            DataType::Binary(len) => match len {
114                Some(n) => format!("BLOB({n})"),
115                None => "BLOB".to_string(),
116            },
117            DataType::Varbinary(len) => match len {
118                Some(n) => format!("BLOB({n})"),
119                None => "BLOB".to_string(),
120            },
121            DataType::Date => "DATE".to_string(),
122            DataType::Time => "TIME".to_string(),
123            DataType::Timestamp => "TIMESTAMP".to_string(),
124            DataType::Datetime => "TIMESTAMP".to_string(),
125            DataType::Boolean => "BOOLEAN".to_string(),
126            DataType::Custom(name) => name.clone(),
127        }
128    }
129
130    fn autoincrement_keyword(&self) -> String {
131        // DuckDB uses CREATE SEQUENCE + DEFAULT nextval() instead.
132        String::new()
133    }
134
135    fn create_table(&self, op: &CreateTableOp) -> String {
136        // Emit CREATE SEQUENCE for every autoincrement column.
137        let mut sql = String::new();
138        for col in &op.columns {
139            if col.autoincrement {
140                sql.push_str(&format!(
141                    "CREATE SEQUENCE IF NOT EXISTS \
142                     \"seq_{table}_{col}\" START 1;\n",
143                    table = op.name,
144                    col = col.name,
145                ));
146            }
147        }
148
149        sql.push_str("CREATE TABLE ");
150        if op.if_not_exists {
151            sql.push_str("IF NOT EXISTS ");
152        }
153        sql.push_str(&self.quote_identifier(&op.name));
154        sql.push_str(" (\n");
155
156        let column_defs: Vec<String> = op
157            .columns
158            .iter()
159            .map(|c| format!("    {}", self.column_def_with_table(c, &op.name)))
160            .collect();
161        sql.push_str(&column_defs.join(",\n"));
162
163        if !op.constraints.is_empty() {
164            sql.push_str(",\n");
165            let constraint_defs: Vec<String> = op
166                .constraints
167                .iter()
168                .map(|c| format!("    {}", self.table_constraint(c)))
169                .collect();
170            sql.push_str(&constraint_defs.join(",\n"));
171        }
172
173        sql.push_str("\n)");
174        sql
175    }
176
177    fn render_default(&self, default: &DefaultValue) -> String {
178        match default {
179            DefaultValue::Boolean(b) => {
180                if *b {
181                    "TRUE".to_string()
182                } else {
183                    "FALSE".to_string()
184                }
185            }
186            _ => default.to_sql(),
187        }
188    }
189
190    fn rename_table(&self, op: &RenameTableOp) -> String {
191        format!(
192            "ALTER TABLE {} RENAME TO {}",
193            self.quote_identifier(&op.old_name),
194            self.quote_identifier(&op.new_name)
195        )
196    }
197
198    fn rename_column(&self, op: &RenameColumnOp) -> String {
199        format!(
200            "ALTER TABLE {} RENAME COLUMN {} TO {}",
201            self.quote_identifier(&op.table),
202            self.quote_identifier(&op.old_name),
203            self.quote_identifier(&op.new_name)
204        )
205    }
206
207    fn alter_column(&self, op: &AlterColumnOp) -> String {
208        let table = self.quote_identifier(&op.table);
209        let column = self.quote_identifier(&op.column);
210
211        match &op.change {
212            AlterColumnChange::SetDataType(dt) => {
213                format!(
214                    "ALTER TABLE {} ALTER COLUMN {} SET DATA TYPE {}",
215                    table,
216                    column,
217                    self.map_data_type(dt)
218                )
219            }
220            AlterColumnChange::SetNullable(nullable) => {
221                if *nullable {
222                    format!(
223                        "ALTER TABLE {} ALTER COLUMN {} DROP NOT NULL",
224                        table, column
225                    )
226                } else {
227                    format!("ALTER TABLE {} ALTER COLUMN {} SET NOT NULL", table, column)
228                }
229            }
230            AlterColumnChange::SetDefault(default) => {
231                format!(
232                    "ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {}",
233                    table,
234                    column,
235                    self.render_default(default)
236                )
237            }
238            AlterColumnChange::DropDefault => {
239                format!("ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT", table, column)
240            }
241        }
242    }
243
244    fn drop_index(&self, op: &DropIndexOp) -> String {
245        let mut sql = String::from("DROP INDEX ");
246        if op.if_exists {
247            sql.push_str("IF EXISTS ");
248        }
249        sql.push_str(&self.quote_identifier(&op.name));
250        sql
251    }
252
253    fn drop_foreign_key(&self, op: &super::super::operation::DropForeignKeyOp) -> String {
254        format!(
255            "ALTER TABLE {} DROP CONSTRAINT {}",
256            self.quote_identifier(&op.table),
257            self.quote_identifier(&op.name)
258        )
259    }
260}
261
262impl RustTypeMapping for DuckDbDialect {
263    fn map_type(&self, rust_type: &str) -> DataType {
264        match rust_type {
265            "bool" => DataType::Boolean,
266            "i8" | "i16" | "u8" | "u16" => DataType::Smallint,
267            "i32" | "u32" => DataType::Integer,
268            "i64" | "u64" | "i128" | "u128" | "isize" | "usize" => DataType::Bigint,
269            "f32" => DataType::Real,
270            "f64" => DataType::Double,
271            "String" => DataType::Varchar(None),
272            "Vec<u8>" => DataType::Blob,
273            s if s.contains("DateTime") => DataType::Timestamp,
274            s if s.contains("NaiveDate") => DataType::Date,
275            _ => DataType::Text,
276        }
277    }
278}
279
280#[cfg(test)]
281mod tests {
282    use super::*;
283    use crate::migrations::column_builder::{integer, varchar};
284    use crate::migrations::operation::{DropTableOp, Operation, RenameColumnOp, RenameTableOp};
285    use crate::migrations::table_builder::CreateTableBuilder;
286
287    #[test]
288    fn test_duckdb_data_types() {
289        let d = DuckDbDialect::new();
290        assert_eq!(d.map_data_type(&DataType::Integer), "INTEGER");
291        assert_eq!(d.map_data_type(&DataType::Bigint), "BIGINT");
292        assert_eq!(d.map_data_type(&DataType::Text), "TEXT");
293        assert_eq!(
294            d.map_data_type(&DataType::Varchar(Some(255))),
295            "VARCHAR(255)"
296        );
297        assert_eq!(d.map_data_type(&DataType::Blob), "BLOB");
298        assert_eq!(d.map_data_type(&DataType::Boolean), "BOOLEAN");
299        assert_eq!(d.map_data_type(&DataType::Timestamp), "TIMESTAMP");
300        assert_eq!(d.map_data_type(&DataType::Double), "DOUBLE");
301        assert_eq!(d.map_data_type(&DataType::Real), "REAL");
302        assert_eq!(d.map_data_type(&DataType::Date), "DATE");
303        assert_eq!(d.map_data_type(&DataType::Time), "TIME");
304        assert_eq!(
305            d.map_data_type(&DataType::Decimal {
306                precision: Some(10),
307                scale: Some(2)
308            }),
309            "DECIMAL(10, 2)"
310        );
311    }
312
313    #[test]
314    fn test_create_table_basic() {
315        let d = DuckDbDialect::new();
316        let op = CreateTableBuilder::new()
317            .name("users")
318            .column(varchar("username", 255).not_null().unique().build())
319            .build();
320
321        let sql = d.create_table(&op);
322        assert_eq!(
323            sql,
324            "CREATE TABLE \"users\" (\n\
325             \x20   \"username\" VARCHAR(255) NOT NULL UNIQUE\n\
326             )"
327        );
328    }
329
330    #[test]
331    fn test_create_table_if_not_exists() {
332        let d = DuckDbDialect::new();
333        let op = CreateTableBuilder::new()
334            .if_not_exists()
335            .name("users")
336            .column(varchar("username", 255).not_null().build())
337            .build();
338
339        let sql = d.create_table(&op);
340        assert!(sql.contains("CREATE TABLE IF NOT EXISTS \"users\""));
341    }
342
343    #[test]
344    fn test_autoincrement_generates_sequence() {
345        let d = DuckDbDialect::new();
346        let op = CreateTableBuilder::new()
347            .name("users")
348            .column(integer("id").primary_key().autoincrement().build())
349            .column(varchar("username", 255).not_null().unique().build())
350            .build();
351
352        let sql = d.create_table(&op);
353
354        assert!(
355            sql.contains(
356                "CREATE SEQUENCE IF NOT EXISTS \
357                 \"seq_users_id\" START 1;"
358            ),
359            "Missing sequence DDL in:\n{sql}"
360        );
361        assert!(
362            sql.contains("DEFAULT nextval('seq_users_id')"),
363            "Missing nextval default in:\n{sql}"
364        );
365        assert!(
366            !sql.contains("AUTOINCREMENT"),
367            "Should not contain AUTOINCREMENT keyword"
368        );
369    }
370
371    #[test]
372    fn test_varchar_unique_not_null() {
373        let d = DuckDbDialect::new();
374        let op = CreateTableBuilder::new()
375            .name("items")
376            .column(varchar("domain", 255).not_null().unique().build())
377            .build();
378
379        let sql = d.create_table(&op);
380        assert!(
381            sql.contains("\"domain\" VARCHAR(255) NOT NULL UNIQUE"),
382            "Expected NOT NULL UNIQUE in:\n{sql}"
383        );
384    }
385
386    #[test]
387    fn test_drop_table() {
388        let d = DuckDbDialect::new();
389
390        let op = DropTableOp {
391            name: "users".to_string(),
392            if_exists: false,
393            cascade: false,
394        };
395        assert_eq!(d.drop_table(&op), "DROP TABLE \"users\"");
396
397        let op = DropTableOp {
398            name: "users".to_string(),
399            if_exists: true,
400            cascade: true,
401        };
402        assert_eq!(d.drop_table(&op), "DROP TABLE IF EXISTS \"users\" CASCADE");
403    }
404
405    #[test]
406    fn test_rename_table() {
407        let d = DuckDbDialect::new();
408        let op = RenameTableOp {
409            old_name: "old_users".to_string(),
410            new_name: "users".to_string(),
411        };
412        assert_eq!(
413            d.rename_table(&op),
414            "ALTER TABLE \"old_users\" RENAME TO \"users\""
415        );
416    }
417
418    #[test]
419    fn test_add_column() {
420        let d = DuckDbDialect::new();
421        let op = Operation::add_column("users", varchar("email", 255).not_null().build());
422        if let Operation::AddColumn(ref add_op) = op {
423            let sql = d.add_column(add_op);
424            assert_eq!(
425                sql,
426                "ALTER TABLE \"users\" ADD COLUMN \
427                 \"email\" VARCHAR(255) NOT NULL"
428            );
429        }
430    }
431
432    #[test]
433    fn test_drop_column() {
434        let d = DuckDbDialect::new();
435        let op = Operation::drop_column("users", "email");
436        if let Operation::DropColumn(ref drop_op) = op {
437            let sql = d.drop_column(drop_op);
438            assert_eq!(sql, "ALTER TABLE \"users\" DROP COLUMN \"email\"");
439        }
440    }
441
442    #[test]
443    fn test_rename_column() {
444        let d = DuckDbDialect::new();
445        let op = RenameColumnOp {
446            table: "users".to_string(),
447            old_name: "name".to_string(),
448            new_name: "full_name".to_string(),
449        };
450        assert_eq!(
451            d.rename_column(&op),
452            "ALTER TABLE \"users\" RENAME COLUMN \
453             \"name\" TO \"full_name\""
454        );
455    }
456
457    #[test]
458    fn test_alter_column_set_data_type() {
459        let d = DuckDbDialect::new();
460        let op = AlterColumnOp {
461            table: "users".to_string(),
462            column: "age".to_string(),
463            change: AlterColumnChange::SetDataType(DataType::Bigint),
464        };
465        assert_eq!(
466            d.alter_column(&op),
467            "ALTER TABLE \"users\" ALTER COLUMN \"age\" \
468             SET DATA TYPE BIGINT"
469        );
470    }
471
472    #[test]
473    fn test_alter_column_set_not_null() {
474        let d = DuckDbDialect::new();
475        let op = AlterColumnOp {
476            table: "users".to_string(),
477            column: "email".to_string(),
478            change: AlterColumnChange::SetNullable(false),
479        };
480        assert_eq!(
481            d.alter_column(&op),
482            "ALTER TABLE \"users\" ALTER COLUMN \"email\" SET NOT NULL"
483        );
484    }
485
486    #[test]
487    fn test_alter_column_drop_not_null() {
488        let d = DuckDbDialect::new();
489        let op = AlterColumnOp {
490            table: "users".to_string(),
491            column: "email".to_string(),
492            change: AlterColumnChange::SetNullable(true),
493        };
494        assert_eq!(
495            d.alter_column(&op),
496            "ALTER TABLE \"users\" ALTER COLUMN \"email\" DROP NOT NULL"
497        );
498    }
499
500    #[test]
501    fn test_alter_column_set_default() {
502        let d = DuckDbDialect::new();
503        let op = AlterColumnOp {
504            table: "users".to_string(),
505            column: "active".to_string(),
506            change: AlterColumnChange::SetDefault(DefaultValue::Boolean(true)),
507        };
508        assert_eq!(
509            d.alter_column(&op),
510            "ALTER TABLE \"users\" ALTER COLUMN \"active\" \
511             SET DEFAULT TRUE"
512        );
513    }
514
515    #[test]
516    fn test_alter_column_drop_default() {
517        let d = DuckDbDialect::new();
518        let op = AlterColumnOp {
519            table: "users".to_string(),
520            column: "active".to_string(),
521            change: AlterColumnChange::DropDefault,
522        };
523        assert_eq!(
524            d.alter_column(&op),
525            "ALTER TABLE \"users\" ALTER COLUMN \"active\" DROP DEFAULT"
526        );
527    }
528
529    #[test]
530    fn test_create_index() {
531        let d = DuckDbDialect::new();
532        let op = crate::migrations::operation::CreateIndexOp {
533            name: "idx_users_email".to_string(),
534            table: "users".to_string(),
535            columns: vec!["email".to_string()],
536            unique: true,
537            index_type: crate::migrations::operation::IndexType::BTree,
538            if_not_exists: true,
539            condition: None,
540        };
541        assert_eq!(
542            d.create_index(&op),
543            "CREATE UNIQUE INDEX IF NOT EXISTS \"idx_users_email\" \
544             ON \"users\" (\"email\")"
545        );
546    }
547
548    #[test]
549    fn test_drop_index() {
550        let d = DuckDbDialect::new();
551
552        let op = crate::migrations::operation::DropIndexOp {
553            name: "idx_users_email".to_string(),
554            table: None,
555            if_exists: false,
556        };
557        assert_eq!(d.drop_index(&op), "DROP INDEX \"idx_users_email\"");
558
559        let op = crate::migrations::operation::DropIndexOp {
560            name: "idx_users_email".to_string(),
561            table: None,
562            if_exists: true,
563        };
564        assert_eq!(
565            d.drop_index(&op),
566            "DROP INDEX IF EXISTS \"idx_users_email\""
567        );
568    }
569
570    #[test]
571    fn test_drop_foreign_key() {
572        let d = DuckDbDialect::new();
573        let op = crate::migrations::operation::DropForeignKeyOp {
574            table: "invoices".to_string(),
575            name: "fk_invoices_user".to_string(),
576        };
577        assert_eq!(
578            d.drop_foreign_key(&op),
579            "ALTER TABLE \"invoices\" DROP CONSTRAINT \
580             \"fk_invoices_user\""
581        );
582    }
583
584    #[test]
585    fn test_consumer_scenario_two_tables_with_sequences() {
586        let d = DuckDbDialect::new();
587
588        let ops: Vec<Operation> = vec![
589            CreateTableBuilder::new()
590                .if_not_exists()
591                .name("excluded_domains")
592                .column(integer("id").primary_key().autoincrement().build())
593                .column(varchar("domain", 255).not_null().unique().build())
594                .build()
595                .into(),
596            CreateTableBuilder::new()
597                .if_not_exists()
598                .name("excluded_ips")
599                .column(integer("id").primary_key().autoincrement().build())
600                .column(varchar("cidr", 255).not_null().unique().build())
601                .build()
602                .into(),
603        ];
604
605        let sqls: Vec<String> = ops.iter().map(|op| d.generate_sql(op)).collect();
606
607        // First table
608        assert!(
609            sqls[0].contains(
610                "CREATE SEQUENCE IF NOT EXISTS \
611                 \"seq_excluded_domains_id\" START 1;"
612            ),
613            "Missing sequence for excluded_domains:\n{}",
614            sqls[0]
615        );
616        assert!(
617            sqls[0].contains("CREATE TABLE IF NOT EXISTS \"excluded_domains\""),
618            "Missing CREATE TABLE:\n{}",
619            sqls[0]
620        );
621        assert!(
622            sqls[0].contains("DEFAULT nextval('seq_excluded_domains_id')"),
623            "Missing nextval default:\n{}",
624            sqls[0]
625        );
626        assert!(
627            sqls[0].contains("\"domain\" VARCHAR(255) NOT NULL UNIQUE"),
628            "Missing domain column:\n{}",
629            sqls[0]
630        );
631
632        // Second table
633        assert!(
634            sqls[1].contains(
635                "CREATE SEQUENCE IF NOT EXISTS \
636                 \"seq_excluded_ips_id\" START 1;"
637            ),
638            "Missing sequence for excluded_ips:\n{}",
639            sqls[1]
640        );
641        assert!(
642            sqls[1].contains("DEFAULT nextval('seq_excluded_ips_id')"),
643            "Missing nextval default:\n{}",
644            sqls[1]
645        );
646        assert!(
647            sqls[1].contains("\"cidr\" VARCHAR(255) NOT NULL UNIQUE"),
648            "Missing cidr column:\n{}",
649            sqls[1]
650        );
651    }
652}
oxide_sql_core/migrations/dialect/duckdb.rs

oxide_sql_core/migrations/dialect/
duckdb.rs
