sql_schema/
lib.rs

1use std::fmt;
2
3use bon::bon;
4use diff::Diff;
5use migration::Migrate;
6use sqlparser::{
7    ast::Statement,
8    dialect::{self},
9    parser::{self, Parser},
10};
11use thiserror::Error;
12
13mod diff;
14mod migration;
15pub mod path_template;
16
17#[derive(Error, Debug)]
18#[error("Oops, we couldn't parse that!")]
19pub struct ParseError(#[from] parser::ParserError);
20
21#[derive(Debug, Copy, Clone, PartialEq, Eq, PartialOrd, Ord, Default)]
22#[cfg_attr(feature = "clap", derive(clap::ValueEnum), clap(rename_all = "lower"))]
23#[non_exhaustive]
24pub enum Dialect {
25    Ansi,
26    BigQuery,
27    ClickHouse,
28    Databricks,
29    DuckDb,
30    #[default]
31    Generic,
32    Hive,
33    MsSql,
34    MySql,
35    PostgreSql,
36    RedshiftSql,
37    Snowflake,
38    SQLite,
39}
40
41impl Dialect {
42    fn to_sqlparser_dialect(self) -> Box<dyn dialect::Dialect> {
43        match self {
44            Self::Ansi => Box::new(dialect::AnsiDialect {}),
45            Self::BigQuery => Box::new(dialect::BigQueryDialect {}),
46            Self::ClickHouse => Box::new(dialect::ClickHouseDialect {}),
47            Self::Databricks => Box::new(dialect::DatabricksDialect {}),
48            Self::DuckDb => Box::new(dialect::DuckDbDialect {}),
49            Self::Generic => Box::new(dialect::GenericDialect {}),
50            Self::Hive => Box::new(dialect::HiveDialect {}),
51            Self::MsSql => Box::new(dialect::MsSqlDialect {}),
52            Self::MySql => Box::new(dialect::MySqlDialect {}),
53            Self::PostgreSql => Box::new(dialect::PostgreSqlDialect {}),
54            Self::RedshiftSql => Box::new(dialect::RedshiftSqlDialect {}),
55            Self::Snowflake => Box::new(dialect::SnowflakeDialect {}),
56            Self::SQLite => Box::new(dialect::SQLiteDialect {}),
57        }
58    }
59}
60
61impl fmt::Display for Dialect {
62    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
63        // NOTE: this must match how clap::ValueEnum displays variants
64        write!(
65            f,
66            "{}",
67            format!("{self:?}")
68                .to_ascii_lowercase()
69                .split('-')
70                .collect::<String>()
71        )
72    }
73}
74
75#[derive(Debug, Clone)]
76pub struct SyntaxTree(Vec<Statement>);
77
78#[bon]
79impl SyntaxTree {
80    #[builder]
81    pub fn new<'a>(dialect: Option<Dialect>, sql: impl Into<&'a str>) -> Result<Self, ParseError> {
82        let dialect = dialect.unwrap_or_default().to_sqlparser_dialect();
83        let ast = Parser::parse_sql(dialect.as_ref(), sql.into())?;
84        Ok(Self(ast))
85    }
86
87    pub fn empty() -> Self {
88        Self(vec![])
89    }
90}
91
92impl SyntaxTree {
93    pub fn diff(&self, other: &SyntaxTree) -> Option<Self> {
94        Diff::diff(&self.0, &other.0).map(Self)
95    }
96
97    pub fn migrate(self, other: &SyntaxTree) -> Option<Self> {
98        Migrate::migrate(self.0, &other.0).map(Self)
99    }
100}
101
102impl fmt::Display for SyntaxTree {
103    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
104        let mut iter = self.0.iter().peekable();
105        while let Some(s) = iter.next() {
106            let formatted = sqlformat::format(
107                format!("{s};").as_str(),
108                &sqlformat::QueryParams::None,
109                &sqlformat::FormatOptions::default(),
110            );
111            write!(f, "{formatted}")?;
112            if iter.peek().is_some() {
113                write!(f, "\n\n")?;
114            }
115        }
116        Ok(())
117    }
118}
119
120#[cfg(test)]
121mod tests {
122    use super::*;
123
124    #[derive(Debug)]
125    struct TestCase {
126        dialect: Dialect,
127        sql_a: &'static str,
128        sql_b: &'static str,
129        expect: &'static str,
130    }
131
132    fn run_test_case<F>(tc: &TestCase, testfn: F)
133    where
134        F: Fn(SyntaxTree, SyntaxTree) -> SyntaxTree,
135    {
136        let ast_a = SyntaxTree::builder()
137            .dialect(tc.dialect.clone())
138            .sql(tc.sql_a)
139            .build()
140            .unwrap();
141        let ast_b = SyntaxTree::builder()
142            .dialect(tc.dialect.clone())
143            .sql(tc.sql_b)
144            .build()
145            .unwrap();
146        SyntaxTree::builder()
147            .dialect(tc.dialect.clone())
148            .sql(tc.expect)
149            .build()
150            .expect(format!("invalid SQL: {:?}", tc.expect).as_str());
151        let actual = testfn(ast_a, ast_b);
152        assert_eq!(actual.to_string(), tc.expect, "{tc:?}");
153    }
154
155    fn run_test_cases<F>(test_cases: Vec<TestCase>, testfn: F)
156    where
157        F: Fn(SyntaxTree, SyntaxTree) -> SyntaxTree,
158    {
159        test_cases
160            .into_iter()
161            .for_each(|tc| run_test_case(&tc, |ast_a, ast_b| testfn(ast_a, ast_b)));
162    }
163
164    #[test]
165    fn diff_create_table() {
166        run_test_cases(
167            vec![TestCase {
168                dialect: Dialect::Generic,
169                sql_a: "CREATE TABLE foo(\
170                            id int PRIMARY KEY
171                        )",
172                sql_b: "CREATE TABLE foo(\
173                            id int PRIMARY KEY
174                        );\
175                        CREATE TABLE bar (id INT PRIMARY KEY);",
176                expect: "CREATE TABLE bar (id INT PRIMARY KEY);",
177            }],
178            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
179        );
180    }
181
182    #[test]
183    fn diff_drop_table() {
184        run_test_cases(
185            vec![TestCase {
186                dialect: Dialect::Generic,
187                sql_a: "CREATE TABLE foo(\
188                        id int PRIMARY KEY
189                    );\
190                    CREATE TABLE bar (id INT PRIMARY KEY);",
191                sql_b: "CREATE TABLE foo(\
192                        id int PRIMARY KEY
193                    )",
194                expect: "DROP TABLE bar;",
195            }],
196            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
197        );
198    }
199
200    #[test]
201    fn diff_add_column() {
202        run_test_cases(
203            vec![TestCase {
204                dialect: Dialect::Generic,
205                sql_a: "CREATE TABLE foo(\
206                        id int PRIMARY KEY
207                    )",
208                sql_b: "CREATE TABLE foo(\
209                        id int PRIMARY KEY,
210                        bar text
211                    )",
212                expect: "ALTER TABLE\n  foo\nADD\n  COLUMN bar TEXT;",
213            }],
214            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
215        );
216    }
217
218    #[test]
219    fn diff_drop_column() {
220        run_test_cases(
221            vec![TestCase {
222                dialect: Dialect::Generic,
223                sql_a: "CREATE TABLE foo(\
224                        id int PRIMARY KEY,
225                        bar text
226                    )",
227                sql_b: "CREATE TABLE foo(\
228                        id int PRIMARY KEY
229                    )",
230                expect: "ALTER TABLE\n  foo DROP COLUMN bar;",
231            }],
232            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
233        );
234    }
235
236    #[test]
237    fn diff_create_index() {
238        run_test_cases(
239            vec![
240                TestCase {
241                    dialect: Dialect::Generic,
242                    sql_a: "CREATE UNIQUE INDEX title_idx ON films (title);",
243                    sql_b: "CREATE UNIQUE INDEX title_idx ON films ((lower(title)));",
244                    expect: "DROP INDEX title_idx;\n\nCREATE UNIQUE INDEX title_idx ON films((lower(title)));",
245                },
246                TestCase {
247                    dialect: Dialect::Generic,
248                    sql_a: "CREATE UNIQUE INDEX IF NOT EXISTS title_idx ON films (title);",
249                    sql_b: "CREATE UNIQUE INDEX IF NOT EXISTS title_idx ON films ((lower(title)));",
250                    expect: "DROP INDEX IF EXISTS title_idx;\n\nCREATE UNIQUE INDEX IF NOT EXISTS title_idx ON films((lower(title)));",
251                },
252            ],
253            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
254        );
255    }
256
257    #[test]
258    fn diff_create_type() {
259        run_test_cases(
260            vec![
261                TestCase {
262                    dialect: Dialect::Generic,
263                    sql_a: "CREATE TYPE bug_status AS ENUM ('new', 'open');",
264                    sql_b: "CREATE TYPE foo AS ENUM ('bar');",
265                    expect: "DROP TYPE bug_status;\n\nCREATE TYPE foo AS ENUM ('bar');",
266                },
267                TestCase {
268                    dialect: Dialect::Generic,
269                    sql_a: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');",
270                    sql_b: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'assigned', 'closed');",
271                    expect: "ALTER TYPE bug_status\nADD\n  VALUE 'assigned'\nAFTER\n  'open';",
272                },
273                TestCase {
274                    dialect: Dialect::Generic,
275                    sql_a: "CREATE TYPE bug_status AS ENUM ('open', 'closed');",
276                    sql_b: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');",
277                    expect: "ALTER TYPE bug_status\nADD\n  VALUE 'new' BEFORE 'open';",
278                },
279                TestCase {
280                    dialect: Dialect::Generic,
281                    sql_a: "CREATE TYPE bug_status AS ENUM ('new', 'open');",
282                    sql_b: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');",
283                    expect: "ALTER TYPE bug_status\nADD\n  VALUE 'closed';",
284                },
285                TestCase {
286                    dialect: Dialect::Generic,
287                    sql_a: "CREATE TYPE bug_status AS ENUM ('new', 'open');",
288                    sql_b: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'assigned', 'closed');",
289                    expect: "ALTER TYPE bug_status\nADD\n  VALUE 'assigned';\n\nALTER TYPE bug_status\nADD\n  VALUE 'closed';",
290                },
291                TestCase {
292                    dialect: Dialect::Generic,
293                    sql_a: "CREATE TYPE bug_status AS ENUM ('open', 'critical');",
294                    sql_b: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'assigned', 'closed', 'critical');",
295                    expect: "ALTER TYPE bug_status\nADD\n  VALUE 'new' BEFORE 'open';\n\nALTER TYPE bug_status\nADD\n  VALUE 'assigned'\nAFTER\n  'open';\n\nALTER TYPE bug_status\nADD\n  VALUE 'closed'\nAFTER\n  'assigned';",
296                },
297                TestCase {
298                    dialect: Dialect::Generic,
299                    sql_a: "CREATE TYPE bug_status AS ENUM ('open');",
300                    sql_b: "CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');",
301                    expect: "ALTER TYPE bug_status\nADD\n  VALUE 'new' BEFORE 'open';\n\nALTER TYPE bug_status\nADD\n  VALUE 'closed';",
302                },
303            ],
304            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
305        );
306    }
307
308    #[test]
309    fn diff_create_extension() {
310        run_test_cases(
311            vec![TestCase {
312                dialect: Dialect::Generic,
313                sql_a: "CREATE EXTENSION hstore;",
314                sql_b: "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";",
315                expect: "DROP EXTENSION hstore;\n\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";",
316            }],
317            |ast_a, ast_b| ast_a.diff(&ast_b).unwrap(),
318        );
319    }
320
321    #[test]
322    fn apply_create_table() {
323        run_test_cases(
324            vec![TestCase {
325                dialect: Dialect::Generic,
326                sql_a: "CREATE TABLE bar (id INT PRIMARY KEY);",
327                sql_b: "CREATE TABLE foo (id INT PRIMARY KEY);",
328                expect: "CREATE TABLE bar (id INT PRIMARY KEY);\n\nCREATE TABLE foo (id INT PRIMARY KEY);",
329            }],
330            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
331        );
332    }
333
334    #[test]
335    fn apply_drop_table() {
336        run_test_cases(
337            vec![TestCase {
338                dialect: Dialect::Generic,
339                sql_a: "CREATE TABLE bar (id INT PRIMARY KEY)",
340                sql_b: "DROP TABLE bar; CREATE TABLE foo (id INT PRIMARY KEY)",
341                expect: "CREATE TABLE foo (id INT PRIMARY KEY);",
342            }],
343            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
344        );
345    }
346
347    #[test]
348    fn apply_alter_table_add_column() {
349        run_test_cases(
350            vec![TestCase {
351                dialect: Dialect::Generic,
352                sql_a: "CREATE TABLE bar (id INT PRIMARY KEY)",
353                sql_b: "ALTER TABLE bar ADD COLUMN bar TEXT",
354                expect: "CREATE TABLE bar (id INT PRIMARY KEY, bar TEXT);",
355            }],
356            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
357        );
358    }
359
360    #[test]
361    fn apply_alter_table_drop_column() {
362        run_test_cases(
363            vec![TestCase {
364                dialect: Dialect::Generic,
365                sql_a: "CREATE TABLE bar (bar TEXT, id INT PRIMARY KEY)",
366                sql_b: "ALTER TABLE bar DROP COLUMN bar",
367                expect: "CREATE TABLE bar (id INT PRIMARY KEY);",
368            }],
369            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
370        );
371    }
372
373    #[test]
374    fn apply_alter_table_alter_column() {
375        run_test_cases(
376            vec![
377                TestCase {
378                    dialect: Dialect::Generic,
379                    sql_a: "CREATE TABLE bar (bar TEXT, id INT PRIMARY KEY)",
380                    sql_b: "ALTER TABLE bar ALTER COLUMN bar SET NOT NULL",
381                    expect: "CREATE TABLE bar (bar TEXT NOT NULL, id INT PRIMARY KEY);",
382                },
383                TestCase {
384                    dialect: Dialect::Generic,
385                    sql_a: "CREATE TABLE bar (bar TEXT NOT NULL, id INT PRIMARY KEY)",
386                    sql_b: "ALTER TABLE bar ALTER COLUMN bar DROP NOT NULL",
387                    expect: "CREATE TABLE bar (bar TEXT, id INT PRIMARY KEY);",
388                },
389                TestCase {
390                    dialect: Dialect::Generic,
391                    sql_a: "CREATE TABLE bar (bar TEXT NOT NULL DEFAULT 'foo', id INT PRIMARY KEY)",
392                    sql_b: "ALTER TABLE bar ALTER COLUMN bar DROP DEFAULT",
393                    expect: "CREATE TABLE bar (bar TEXT NOT NULL, id INT PRIMARY KEY);",
394                },
395                TestCase {
396                    dialect: Dialect::Generic,
397                    sql_a: "CREATE TABLE bar (bar TEXT, id INT PRIMARY KEY)",
398                    sql_b: "ALTER TABLE bar ALTER COLUMN bar SET DATA TYPE INTEGER",
399                    expect: "CREATE TABLE bar (bar INTEGER, id INT PRIMARY KEY);",
400                },
401                TestCase {
402                    dialect: Dialect::PostgreSql,
403                    sql_a: "CREATE TABLE bar (bar TEXT, id INT PRIMARY KEY)",
404                    sql_b: "ALTER TABLE bar ALTER COLUMN bar SET DATA TYPE timestamp with time zone\n USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'",
405                    expect: "CREATE TABLE bar (bar TIMESTAMP WITH TIME ZONE, id INT PRIMARY KEY);",
406                },
407                TestCase {
408                    dialect: Dialect::Generic,
409                    sql_a: "CREATE TABLE bar (bar INTEGER, id INT PRIMARY KEY)",
410                    sql_b: "ALTER TABLE bar ALTER COLUMN bar ADD GENERATED BY DEFAULT AS IDENTITY",
411                    expect: "CREATE TABLE bar (\n  bar INTEGER GENERATED BY DEFAULT AS IDENTITY,\n  id INT PRIMARY KEY\n);",
412                },
413                TestCase {
414                    dialect: Dialect::Generic,
415                    sql_a: "CREATE TABLE bar (bar INTEGER, id INT PRIMARY KEY)",
416                    sql_b: "ALTER TABLE bar ALTER COLUMN bar ADD GENERATED ALWAYS AS IDENTITY (START WITH 10)",
417                    expect: "CREATE TABLE bar (\n  bar INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10),\n  id INT PRIMARY KEY\n);",
418                },
419            ],
420            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
421        );
422    }
423
424    #[test]
425    fn apply_create_index() {
426        run_test_cases(
427            vec![
428                TestCase {
429                    dialect: Dialect::Generic,
430                    sql_a: "CREATE UNIQUE INDEX title_idx ON films (title);",
431                    sql_b: "CREATE INDEX code_idx ON films (code);",
432                    expect: "CREATE UNIQUE INDEX title_idx ON films(title);\n\nCREATE INDEX code_idx ON films(code);",
433                },
434                TestCase {
435                    dialect: Dialect::Generic,
436                    sql_a: "CREATE UNIQUE INDEX title_idx ON films (title);",
437                    sql_b: "DROP INDEX title_idx;",
438                    expect: "",
439                },
440                TestCase {
441                    dialect: Dialect::Generic,
442                    sql_a: "CREATE UNIQUE INDEX title_idx ON films (title);",
443                    sql_b: "DROP INDEX title_idx;CREATE INDEX code_idx ON films (code);",
444                    expect: "CREATE INDEX code_idx ON films(code);",
445                },
446            ],
447            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
448        );
449    }
450
451    #[test]
452    fn apply_alter_create_type() {
453        run_test_cases(
454            vec![TestCase {
455                dialect: Dialect::Generic,
456                sql_a: "CREATE TYPE bug_status AS ENUM ('open', 'closed');",
457                sql_b: "CREATE TYPE compfoo AS (f1 int, f2 text);",
458                expect: "CREATE TYPE bug_status AS ENUM ('open', 'closed');\n\nCREATE TYPE compfoo AS (f1 INT, f2 TEXT);",
459            }],
460            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
461        );
462    }
463
464    #[test]
465    fn apply_alter_type_rename() {
466        run_test_cases(
467            vec![TestCase {
468                dialect: Dialect::Generic,
469                sql_a: "CREATE TYPE bug_status AS ENUM ('open', 'closed');",
470                sql_b: "ALTER TYPE bug_status RENAME TO issue_status",
471                expect: "CREATE TYPE issue_status AS ENUM ('open', 'closed');",
472            }],
473            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
474        );
475    }
476
477    #[test]
478    fn apply_alter_type_add_value() {
479        run_test_cases(
480            vec![
481                TestCase {
482                    dialect: Dialect::Generic,
483                    sql_a: "CREATE TYPE bug_status AS ENUM ('open');",
484                    sql_b: "ALTER TYPE bug_status ADD VALUE 'new' BEFORE 'open';",
485                    expect: "CREATE TYPE bug_status AS ENUM ('new', 'open');",
486                },
487                TestCase {
488                    dialect: Dialect::Generic,
489                    sql_a: "CREATE TYPE bug_status AS ENUM ('open');",
490                    sql_b: "ALTER TYPE bug_status ADD VALUE 'closed' AFTER 'open';",
491                    expect: "CREATE TYPE bug_status AS ENUM ('open', 'closed');",
492                },
493                TestCase {
494                    dialect: Dialect::Generic,
495                    sql_a: "CREATE TYPE bug_status AS ENUM ('open');",
496                    sql_b: "ALTER TYPE bug_status ADD VALUE 'closed';",
497                    expect: "CREATE TYPE bug_status AS ENUM ('open', 'closed');",
498                },
499            ],
500            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
501        );
502    }
503
504    #[test]
505    fn apply_alter_type_rename_value() {
506        run_test_cases(
507            vec![TestCase {
508                dialect: Dialect::Generic,
509                sql_a: "CREATE TYPE bug_status AS ENUM ('new', 'closed');",
510                sql_b: "ALTER TYPE bug_status RENAME VALUE 'new' TO 'open';",
511                expect: "CREATE TYPE bug_status AS ENUM ('open', 'closed');",
512            }],
513            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
514        );
515    }
516
517    #[test]
518    fn apply_create_extension() {
519        run_test_cases(
520            vec![TestCase {
521                dialect: Dialect::Generic,
522                sql_a: "CREATE EXTENSION hstore;",
523                sql_b: "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";",
524                expect: "CREATE EXTENSION hstore;\n\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";",
525            }],
526            |ast_a, ast_b| ast_a.migrate(&ast_b).unwrap(),
527        );
528    }
529}
sql_schema/lib.rs

sql_schema/
lib.rs
