Feature: Migrations

[sayore]

Just like replace is doing it for Data, we could add an utility function which looks at first if a table exists, if not create it, and if the table exists also checks if fields|columns are missing, adds them in a non-destructive fashion.

I've already made an Table Creator that i could PR(if wanted)

Ex.:

checkTableExists('session').then(...).catch(() => {
           createTable("session", [
                new BaseField('id').isUnique().setType("VARCHAR(256)"),
                new BaseField('created_at').setType('BIGNUMBER')
            ])
        })

It doesn't yet support checking for fields missing. Though creating a table would be a useful addition i think.

[rahmanfadhil]

Hi, thanks for the idea!

Currently, I'm working on a database migration feature which is very similar with this. The code lives under the migrations branch. There is a file called table.ts, it uses the Table class to construct a new table schema. However, it's super unstable and a lot of features are still missing. So, feel free to poke around and I'm open for suggestions!

Example:

const table = new Table("users", [
    { type: "increments", name: "id", primaryKey: true, autoIncrement: true },
    { type: "varchar", name: "name" },
], { dialect: "mysql" });

const query = table.toSQL();
// create table users (id int primary key auto_increment, name varchar);

I think the way we construct the schema is a little bit different. I use JS plain object instead of a class for defining each field instead of a separate class (it's still type-safe though). So, I'm curious about your progress on this feature so far.

[sayore]

I looked into it, maybe need to test some more differences(between Datatype Slang in the different SQL Languages);

So i think what would be cool if we stayed inline with the queryBuilder signature, making it in the same style:

adapter.tableBuilder(tblname)
  .addField({ type: "increments", name: "id", primaryKey: true, autoIncrement: true })
  .addField({ type: "varchar", name: "name" })
  .execute();

I made some groundwork for this, and made addField(), exists() and remove() functions, i don't know if my code is to messy for a PR though.

https://github.com/edenprojectde/cotton/blob/migrations/src/table.ts https://github.com/edenprojectde/cotton/blob/migrations/src/table_test.ts

This needs to be added to the exists function query: MySQL: table_schema = 'yourdb' Postgre: tableschema = 'yourdb'

[rahmanfadhil]

Merged #6

[rahmanfadhil]

I redesigned the migration API (migrations branch, in src/migrations folder).

It's built on top of @edenprojectde 's code (#6), thanks 👍!

Here are some examples:

const db = connect({
  // ..
})

// Initialize
const migration = new Migration(db)

// Create table
await migration.createTable("posts")
  .addColumn({
    type: "increments",
    name: "id",
    primaryKey: true,
    autoIncrement: true,
  })
  .addColumn({ type: "varchar", name: "name" })
  .execute()

// Check if table exists
const tableInfo = migration.getTableInfo("posts")
tableInfo.exists() // true

// Drop table
migration.dropTable("posts")
migration.dropTable("posts", { ifExists: true })

Planned feature:

// Update table
await migration.alterTable('posts')
  .renameColumn('title', 'newTitle')
  .dropColumn('likes')
  .alterColumn('hello', { hello, world })
  .addIndex('category') // this actually executed outside the 'ALTER TABLE' statement, but it's still possible.
  .execute()

// Drop multiple tables
await migration.dropTables(['users', 'posts'])

// Rename existing table
migration.renameTable('posts', 'articles')

I think I agree with @edenprojectde, having the same pattern in both query builder and migration is definitely a syntactic sugar.

Currently, it consists of three classes, Migration to handle anything about migrations, TableBuilder to construct a table schema using the query builder pattern, and TableInfo to get information about a table.

We're also going to have a TableUpdater class which can alter a table, add and modify columns, and add more indexes to the table. All the methods are not implemented yet, and I'm still finding a better name for this class.

I really want to hear your thoughts on this.

[sayore]

Looks really good to me!

Srsly this could become the best SQL-tooling lib i've seen up to today. 😄

Some notes:

We should take another look at some of the functions and let them throw if default case happens: https://github.com/rahmanfadhil/cotton/blob/c867a98f5ef6e7153287bd357d0d9b86a3589b70/src/table.ts#L214-L221 Why? If we later add mariadb, this will silently fail. Also default case should not happen anymore(i used it while i was working down the list of Queries)

Would've PR'd this but i don't know if you pushed your changes to the repo at this point in time. :D

Maybe later we could even make an SQL Exporter/Importer(that would be useful for the moment a project wants to branch out to a bigger type of database sqlite => mysqldb or mariadb), i think i got something like that already somewhere for JS.

[rahmanfadhil]

Awesome! I'm very glad you like it!

We should take another look at some of the functions and let them throw if default case happens:

That's a pretty easy thing to fix. And the tests will fail anyway since we have a special test case for this. So, everything is good.

https://github.com/rahmanfadhil/cotton/blob/5828520c05d5fe4edbe717cceca3e4df4f82f174/src/migrations/migration_test.ts#L5-L13

Maybe later we could even make an SQL Exporter/Importer(that would be useful for the moment a project wants to branch out to a bigger type of database sqlite => mysqldb or mariadb), i think i got something like that already somewhere for JS.

I'm not sure if anyone needs to export/import their database programmatically. Maybe we can provide a CLI for it? 🤔

Anyway, I'm really excited to have some kind of database seeder and factory to populate the database with realistic dummy data for development and testing purposes.

Lastly, I'm going to remove the old migration code in src/table.ts, because everything is already moved to src/migrations.

I will keep this issue open until this feature is released and merged into the master branch. So, if you have any ideas or suggestions, I'm more than happy to discuss about it. Thanks!

[sayore]

Dummy Data: Yes i can see that!

Export/Import: We could expose export/import it as a seperate lib maybe, it would be pretty useless space mongerer for most projects, making it a cli + seperate lib would be a good idea!

_{Sent with GitHawk}

[rahmanfadhil]

This feature is now merged with master 🎉

Checkout the docs for more info.