wehub-resource-sync
d718c5a372
CI / compile_and_lint (push) Failing after 0s
CI / docker_build (linux/amd64, -linux-amd64-duckdb, duckdb) (push) Failing after 0s
CI / docker_build (linux/arm64, -linux-arm64, minimal) (push) Failing after 2s
CI / docker_build (linux/arm64, -linux-arm64-duckdb, duckdb) (push) Failing after 1s
CI / docker_build (linux/amd64, minimal) (push) Failing after 1s
CI / test (, sqlite, sqlite::memory:) (push) Has been skipped
CI / test (mssql, mssql, mssql://root:Password123!@127.0.0.1/sqlpage) (push) Has been skipped
CI / test (mysql, mysql, mysql://root:Password123!@127.0.0.1/sqlpage) (push) Has been skipped
CI / test (oracle, oracle, Driver=Oracle 21 ODBC driver;Dbq=//127.0.0.1:1521/FREEPDB1;Uid=root;Pwd=Password123!) (push) Has been skipped
CI / test (postgres, odbc, Driver=PostgreSQL Unicode;Server=127.0.0.1;Port=5432;Database=sqlpage;UID=root;PWD=Password123!, true) (push) Has been skipped
CI / test (postgres, postgres, postgres://root:Password123!@127.0.0.1/sqlpage) (push) Has been skipped
CI / playwright (push) Has been skipped
CI / docker_build (linux/arm/v7, -linux-arm-v7, minimal) (push) Failing after 0s
CI / hurl_examples (push) Failing after 8s
deploy website / deploy_official_site (push) Failing after 1s
CI / hurl (${{ matrix.example }}) (push) Has been skipped
CI / docker_push (duckdb) (push) Has been cancelled
CI / docker_push (minimal) (push) Has been cancelled
CI / windows_test (push) Has been cancelled
42 lines
2.0 KiB
Markdown
42 lines
2.0 KiB
Markdown
# SQLPage migrations
|
|
|
|
SQLPage migrations are SQL scripts that you can use to create or update the database schema.
|
|
They are entirely optional: you can use SQLPage without them, and manage the database schema yourself with other tools.
|
|
|
|
If you are new to SQL migrations, please read our [**introduction to database migrations**](https://sql-page.com/your-first-sql-website/migrations.sql).
|
|
|
|
## Creating a migration
|
|
|
|
To create a migration, create a file in the `sqlpage/migrations` directory with the following name:
|
|
|
|
```
|
|
<version>_<name>.sql
|
|
```
|
|
|
|
Where `<version>` is a number that represents the version of the migration, and `<name>` is a name for the migration.
|
|
For example, `001_initial.sql` or `002_add_users.sql`.
|
|
|
|
When you need to update the database schema, always create a **new** migration file with a new version number
|
|
that is greater than the previous one.
|
|
Use commands like `ALTER TABLE` to update the schema declaratively instead of modifying the existing `CREATE TABLE`
|
|
statements.
|
|
|
|
If you try to edit an existing migration, SQLPage will not run it again, will detect
|
|
|
|
## Running migrations
|
|
|
|
Migrations that need to be applied are run automatically when SQLPage starts.
|
|
You need to restart SQLPage each time you create a new migration.
|
|
|
|
## How does it work?
|
|
|
|
SQLPage keeps track of the migrations that have been applied in a table called `_sqlx_migrations`.
|
|
This table is created automatically when SQLPage starts for the first time, if you create migration files.
|
|
If you don't create any migration files, SQLPage will never touch the database schema on its own.
|
|
|
|
When SQLPage starts, it checks the `_sqlx_migrations` table to see which migrations have been applied.
|
|
It checks the `sqlpage/migrations` directory to see which migrations are available.
|
|
If the checksum of a migration file is different from the checksum of the migration that has been applied,
|
|
SQLPage will return an error and refuse to start.
|
|
If you end up in this situation, you can remove the `_sqlx_migrations` table: all your old migrations will be reapplied, and SQLPage will start again.
|