Description of migrations is missing

[karlhorky]

Describe the bug

On the Configuration page of the docs, the "2. A basic example of migrations folder" section is missing a description of what migrations are, both:

1. In a general sense (general definition of migrations)
2. In terms of SafeQL - what does SafeQL do with migrations and what behavior can the user expect / not expect?

To Reproduce

Steps to reproduce the behavior:

1. Navigate to the docs page above
2. Look for an explanation of migrations

Expected behavior

Migrations are explained

Screenshots

--

Desktop (please complete the following information):

• OS: n/a
• PostgreSQL version: n/a
• Version: n/a

Additional context

--

[Newbie012]

I think explaining terms such as "migrations" are a bit out of scope for SafeQL since I'm not trying to teach developers about SQL but rather give them a DX solution.

I believe the Configuration page should be as practical as possible. Although, I won't mind if there was a brief explanation (a sentence or two) about the topic with a follow-up to an FAQ page.

[karlhorky]

I think explaining terms such as "migrations" are a bit out of scope for SafeQL since I'm not trying to teach developers about SQL but rather give them a DX solution.

Ok, I just meant a single sentence about this, but I understand if you don't want to include this information.

I believe the Configuration page should be as practical as possible.

Totally agree! But the Configuration page should definitely have a description about what each thing does. Currently it is unexplained and undocumented - and as an experienced developer I still don't understand what this feature is, even after you wrote to me on Twitter about it 😬

[Newbie012]

I see. Documentation was never my strong side. I'll try to be as descriptive as possible here, and maybe we can find a way to make it clearer in the docs:

I support two ways of analyzing the queries. Either by supplying a database URL or by migrations folder.

When you supply a database URL, the plugin does the following actions:

1. It connects to the database.
2. It fetches the database columns with additional metadata.
3. It runs query descriptions on all of the queries. Doing so won't execute the query. Instead, it will fetch a description of the query, which then be used for type inference.
4. It parses the query to an AST to look for LEFT joins, so it can determine which properties are nullable (query description won't cover it). In the future, it will play a more significant role.
5. It does some computations and generates the appropriate TypeScript types.

Now the fun part - migrations:

Schema migrations are a way of managing your database changes chronologically. Like Git, it helps track down how the database was built. It's the source of truth of your database declaration.
In other words, if you run your migrations against an empty schema, you'll get a database that represents the migrations folder.

How to run migrations?

Migration can have one or more statements (ALTER, CREATE, etc.). We run migrations like how we run queries.

Why it matters when using SafeQL?

Since we can't query against migration files (since they are only a set of instructions rather than an actual database), we need to create a database (we'll call it a shadow database) based on a set of instructions written in the migrations. Once we have a database, we can do what's written in the steps above.
Every time we decide we want to make a change in the migrations, we restart the ESLint server (or re-run in the terminal). Each time, it will drop (if it exists) the shadow database to avoid conflicts and recreate it (I want to improve this part, although it's pretty fast).
In other words, if we manually create a database based on the migrations folder and supply the database URL instead, if will work the same (until you decide to make some changes in the database or the migrations).

To create a database, we have to connect to Postgres. To connect to Postgres, we have to connect to an existing database. Which database will always exist? postgres. That's why SafeQL requires supplying a connection URL. It also requires databaseName, the "shadow database" that will be recreated and dropped every time. I have an open issue (#8) that will either simplify this step or make it more confusing.

[karlhorky]

Nice, lots of detail there! I think this is definitely enough. Also, this is what I assumed that SafeQL was doing from what I could gather, so that's good too.

[Newbie012]

fixed in #48

see https://safeql.dev/api/index.html#connections-migrationsdir

[karlhorky]

Thanks!

I guess 2 things are still open for me here:

1. Maybe the new guide page can be linked to also in the 2. A basic example of migrations folder section?
2. The new documentation doesn't really describe what formats are allowed for migrations. Eg. can I use ley or another arbitrary tool for my migrations? Can I write my migration files in TypeScript? Can I write my migration files in SQL?

[Newbie012]

Hey,

About 1 - Feel free to submit a PR or open a new issue for it
About 2 - Migration strategy is currently not supported; only raw (.sql) files are. If you feel like this should be supported, open a new issue for it (as it's currently out of the scope of this issue).

[karlhorky]

Ok I'll open a PR for both of these - documenting that only .sql migrations are supported

[karlhorky]

Open at #49