github.com/adharshmk96/stk@v1.2.3/pkg/sqlMigrator/dbrepo/sqlite.go (about)

     1  package dbrepo
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"log"
     7  	"time"
     8  
     9  	sqlmigrator "github.com/adharshmk96/stk/pkg/sqlMigrator"
    10  	_ "github.com/mattn/go-sqlite3"
    11  )
    12  
    13  // sqlite implementation
    14  type sqliteDb struct {
    15  	conn *sql.DB
    16  }
    17  
    18  func NewSQLiteRepo(filePath string) sqlmigrator.DBRepo {
    19  	conn, err := sql.Open("sqlite3", filePath)
    20  	if err != nil {
    21  		log.Fatal(err)
    22  		panic(err)
    23  	}
    24  	repo := &sqliteDb{
    25  		conn: conn,
    26  	}
    27  
    28  	repo.InitMigrationTable()
    29  
    30  	return repo
    31  }
    32  
    33  // TODO: handle the result
    34  func (db *sqliteDb) Exec(query string) error {
    35  	_, err := db.conn.Exec(query)
    36  	if err != nil {
    37  		return err
    38  	}
    39  
    40  	return nil
    41  }
    42  
    43  func (db *sqliteDb) PushHistory(migration *sqlmigrator.MigrationDBEntry) error {
    44  	_, err := db.conn.Exec(`INSERT INTO `+MIGRATION_TABLE_NAME+` (number, name, direction) VALUES (?, ?, ?)`, migration.Number, migration.Name, migration.Direction)
    45  	if err != nil {
    46  		return err
    47  	}
    48  
    49  	return nil
    50  }
    51  
    52  func (db *sqliteDb) LoadHistory() ([]*sqlmigrator.MigrationDBEntry, error) {
    53  
    54  	rows, err := db.conn.Query(`SELECT * FROM (
    55  		SELECT id, number, name, direction, created FROM ` + MIGRATION_TABLE_NAME + ` ORDER BY id DESC LIMIT 20
    56  	) ORDER BY id ASC`)
    57  	if err != nil {
    58  		return nil, err
    59  	}
    60  
    61  	defer rows.Close()
    62  
    63  	var migrations []*sqlmigrator.MigrationDBEntry
    64  
    65  	for rows.Next() {
    66  		var id int
    67  		var number int
    68  		var name string
    69  		var direction string
    70  		var created time.Time
    71  
    72  		err = rows.Scan(&id, &number, &name, &direction, &created)
    73  		if err != nil {
    74  			return nil, err
    75  		}
    76  
    77  		migrations = append(migrations, &sqlmigrator.MigrationDBEntry{
    78  			Number:    number,
    79  			Name:      name,
    80  			Direction: direction,
    81  			Created:   created,
    82  		})
    83  	}
    84  
    85  	return migrations, nil
    86  }
    87  
    88  func (db *sqliteDb) InitMigrationTable() error {
    89  	// create migration table if not exists
    90  	_, err := db.conn.Exec(`CREATE TABLE IF NOT EXISTS ` + MIGRATION_TABLE_NAME + ` (
    91  		id INTEGER PRIMARY KEY AUTOINCREMENT,
    92  		number INTEGER NOT NULL,
    93  		name VARCHAR(255) NOT NULL,
    94  		direction VARCHAR(4) NOT NULL,
    95  		created DATETIME DEFAULT CURRENT_TIMESTAMP
    96  	)`)
    97  	if err != nil {
    98  		fmt.Println("error creating migration table")
    99  		return err
   100  	}
   101  
   102  	return nil
   103  }
   104  
   105  func (db *sqliteDb) DeleteMigrationTable() error {
   106  	_, err := db.conn.Exec("DROP TABLE IF EXISTS " + MIGRATION_TABLE_NAME)
   107  	if err != nil {
   108  		return err
   109  	}
   110  	return nil
   111  }