aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorEugene Triguba <eugenetriguba@gmail.com>2024-03-11 19:35:06 -0400
committerEugene Triguba <eugenetriguba@gmail.com>2024-03-11 19:35:06 -0400
commit591e74c57cdfb9cd380e401ed9c43fd8dfe6f47d (patch)
tree50a772b399b4b77f65b81ae1844827ddc3f54641
parentFix driver name in tutorial (diff)
downloadcli-591e74c57cdfb9cd380e401ed9c43fd8dfe6f47d.zip
Add support for sqlite3
-rw-r--r--.env.sqlite32
-rw-r--r--.github/workflows/test.yml2
-rw-r--r--CHANGELOG.md1
-rw-r--r--README.md3
-rw-r--r--go.mod1
-rw-r--r--go.sum2
-rw-r--r--internal/storage/db.go5
-rw-r--r--internal/storage/db_test.go12
-rw-r--r--internal/storage/mssql_test.go10
-rw-r--r--internal/storage/mysql_test.go8
-rw-r--r--internal/storage/postgresql_test.go8
-rw-r--r--internal/storage/sqlite3.go48
-rw-r--r--internal/storage/sqlite3_test.go94
13 files changed, 177 insertions, 19 deletions
diff --git a/.env.sqlite3 b/.env.sqlite3
new file mode 100644
index 0000000..b2300d7
--- /dev/null
+++ b/.env.sqlite3
@@ -0,0 +1,2 @@
+BOLT_DB_CONN_DBNAME=/tmp/tmp.db
+BOLT_DB_CONN_DRIVER=sqlite3
diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml
index 565aa80..6b2aa5e 100644
--- a/.github/workflows/test.yml
+++ b/.github/workflows/test.yml
@@ -15,10 +15,12 @@ jobs:
- {name: postgresql}
- {name: mysql}
- {name: mssql}
+ - {name: sqlite3}
steps:
- name: Checkout Source Code
uses: actions/checkout@v4
- name: Start Database
+ if: ${{ matrix.db.name }} != 'sqlite3'
run: |
env $(cat ".env.${{ matrix.db.name }}" | xargs) docker compose -f "docker-compose.${{ matrix.db.name }}.yml" up -d
- name: Run Tests
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 964d621..f627ead 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -10,6 +10,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
### Added
- Support for Microsoft SQL Server.
+- Support for SQLite3.
## [0.5.0] - 2024-03-10
diff --git a/README.md b/README.md
index 37ad4fa..0f5bc43 100644
--- a/README.md
+++ b/README.md
@@ -248,6 +248,7 @@ In your `upgrade.sql` or `downgrade.sql`, add the following line as the first li
- PostgreSQL
- MySQL
- Microsoft SQL Server
+- SQLite3
### Configuration
@@ -288,7 +289,7 @@ password =
# The name of the database within your DBMS.
dbname =
# The name of the database driver to use to connect to
-# the database. Either "postgresql", "mysql", or "mssql".
+# the database. Either "postgresql", "mysql", "mssql", or "sqlite3".
driver =
```
diff --git a/go.mod b/go.mod
index f650e26..24282d3 100644
--- a/go.mod
+++ b/go.mod
@@ -19,6 +19,7 @@ require (
github.com/jackc/pgpassfile v1.0.0 // indirect
github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
github.com/jackc/puddle/v2 v2.2.1 // indirect
+ github.com/mattn/go-sqlite3 v1.14.22 // indirect
github.com/microsoft/go-mssqldb v1.7.0 // indirect
golang.org/x/crypto v0.20.0 // indirect
golang.org/x/sync v0.1.0 // indirect
diff --git a/go.sum b/go.sum
index ac6da7d..e51ccc6 100644
--- a/go.sum
+++ b/go.sum
@@ -35,6 +35,8 @@ github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk
github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
github.com/kelseyhightower/envconfig v1.4.0 h1:Im6hONhd3pLkfDFsbRgu68RDNkGF1r3dvMUtDTo2cv8=
github.com/kelseyhightower/envconfig v1.4.0/go.mod h1:cccZRl6mQpaq41TPp5QxidR+Sa3axMbJDNb//FQX6Gg=
+github.com/mattn/go-sqlite3 v1.14.22 h1:2gZY6PC6kBnID23Tichd1K+Z0oS6nE/XwU+Vz/5o4kU=
+github.com/mattn/go-sqlite3 v1.14.22/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y=
github.com/microsoft/go-mssqldb v1.7.0 h1:sgMPW0HA6Ihd37Yx0MzHyKD726C2kY/8KJsQtXHNaAs=
github.com/microsoft/go-mssqldb v1.7.0/go.mod h1:kOvZKUdrhhFQmxLZqbwUV0rHkNkZpthMITIb2Ko1IoA=
github.com/modocache/gover v0.0.0-20171022184752-b58185e213c5/go.mod h1:caMODM3PzxT8aQXRPkAt8xlV/e7d7w8GM5g0fa5F0D8=
diff --git a/internal/storage/db.go b/internal/storage/db.go
index 12adc4c..dc32304 100644
--- a/internal/storage/db.go
+++ b/internal/storage/db.go
@@ -8,17 +8,20 @@ import (
"github.com/eugenetriguba/bolt/internal/configloader"
_ "github.com/go-sql-driver/mysql"
_ "github.com/jackc/pgx/v5/stdlib"
+ _ "github.com/mattn/go-sqlite3"
_ "github.com/microsoft/go-mssqldb"
)
var postgresqlDriverName = "postgresql"
var mysqlDriverName = "mysql"
var mssqlDriverName = "mssql"
+var sqliteDriverName = "sqlite3"
var supportedDrivers = map[string]dbDriver{
postgresqlDriverName: {name: "pgx", adapter: PostgresqlAdapter{}},
mysqlDriverName: {name: "mysql", adapter: MySQLAdapter{}},
mssqlDriverName: {name: "sqlserver", adapter: MSSQLAdapter{}},
+ sqliteDriverName: {name: "sqlite3", adapter: SqliteAdapter{}},
}
type dbDriver struct {
@@ -33,7 +36,7 @@ var (
ErrUnableToConnect = errors.New("unable to open connection to database")
ErrUnsupportedDriver = fmt.Errorf(
"unsupported driver, supported drivers are %s",
- []string{postgresqlDriverName, mysqlDriverName, mssqlDriverName},
+ []string{postgresqlDriverName, mysqlDriverName, mssqlDriverName, sqliteDriverName},
)
)
diff --git a/internal/storage/db_test.go b/internal/storage/db_test.go
index 4b7dc93..f3386f5 100644
--- a/internal/storage/db_test.go
+++ b/internal/storage/db_test.go
@@ -3,6 +3,7 @@ package storage_test
import (
"database/sql"
"errors"
+ "os"
"testing"
"github.com/eugenetriguba/bolt/internal/bolttest"
@@ -27,10 +28,13 @@ func TestNewDB_UnsupportedDriver(t *testing.T) {
}
func TestNewDB_UnableToConnect(t *testing.T) {
- t.Setenv("BOLT_DB_CONN_HOST", "")
- t.Setenv("BOLT_DB_CONN_PORT", "")
- _, err := storage.NewDB(bolttest.NewTestConnectionConfig())
- assert.ErrorIs(t, err, storage.ErrUnableToConnect)
+ driver := os.Getenv("BOLT_DB_CONN_DRIVER")
+ if driver != "sqlite3" {
+ t.Setenv("BOLT_DB_CONN_HOST", "")
+ t.Setenv("BOLT_DB_CONN_PORT", "")
+ _, err := storage.NewDB(bolttest.NewTestConnectionConfig())
+ assert.ErrorIs(t, err, storage.ErrUnableToConnect)
+ }
}
func TestClose_IsClosed(t *testing.T) {
diff --git a/internal/storage/mssql_test.go b/internal/storage/mssql_test.go
index 400b210..4a99ffd 100644
--- a/internal/storage/mssql_test.go
+++ b/internal/storage/mssql_test.go
@@ -13,7 +13,7 @@ import (
"github.com/eugenetriguba/checkmate/assert"
)
-func TestConvertGenericPlaceholders(t *testing.T) {
+func TestMSSQL_ConvertGenericPlaceholders(t *testing.T) {
type test struct {
query string
argCount int
@@ -44,7 +44,7 @@ func TestConvertGenericPlaceholders(t *testing.T) {
}
}
-func TestTableExists(t *testing.T) {
+func TestMSSQL_TableExists(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.MSSQLAdapter{}
db, err := sql.Open("sqlserver", adapter.CreateDSN(cfg))
@@ -67,7 +67,7 @@ func TestTableExists(t *testing.T) {
assert.True(t, exists)
}
-func TestDatabaseName(t *testing.T) {
+func TestMSSQL_DatabaseName(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.MSSQLAdapter{}
db, err := sql.Open("sqlserver", adapter.CreateDSN(cfg))
@@ -82,7 +82,7 @@ func TestDatabaseName(t *testing.T) {
assert.Equal(t, name, cfg.DBName)
}
-func TestCreateDSN(t *testing.T) {
+func TestMSSQL_CreateDSN(t *testing.T) {
cfg := configloader.ConnectionConfig{
Driver: "mssql",
Host: "db1",
@@ -102,7 +102,7 @@ func TestCreateDSN(t *testing.T) {
assert.Equal(t, cs, expectedConnectionString)
}
-func TestCreateDSN_UnparsablePort(t *testing.T) {
+func TestMSSQL_CreateDSN_UnparsablePort(t *testing.T) {
cfg := configloader.ConnectionConfig{
Driver: "mssql",
Host: "db1",
diff --git a/internal/storage/mysql_test.go b/internal/storage/mysql_test.go
index 76f1098..b6f48b3 100644
--- a/internal/storage/mysql_test.go
+++ b/internal/storage/mysql_test.go
@@ -13,7 +13,7 @@ import (
"github.com/eugenetriguba/checkmate/assert"
)
-func TestConvertGenericPlaceholders(t *testing.T) {
+func TestMySQL_ConvertGenericPlaceholders(t *testing.T) {
type test struct {
query string
argCount int
@@ -44,7 +44,7 @@ func TestConvertGenericPlaceholders(t *testing.T) {
}
}
-func TestTableExists(t *testing.T) {
+func TestMySQL_TableExists(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.MySQLAdapter{}
db, err := sql.Open("mysql", adapter.CreateDSN(cfg))
@@ -67,7 +67,7 @@ func TestTableExists(t *testing.T) {
assert.True(t, exists)
}
-func TestDatabaseName(t *testing.T) {
+func TestMySQL_DatabaseName(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.MySQLAdapter{}
db, err := sql.Open("mysql", adapter.CreateDSN(cfg))
@@ -82,7 +82,7 @@ func TestDatabaseName(t *testing.T) {
assert.Equal(t, name, cfg.DBName)
}
-func TestCreateDSN(t *testing.T) {
+func TestMySQL_CreateDSN(t *testing.T) {
cfg := configloader.ConnectionConfig{
Driver: "mysql",
Host: "db1",
diff --git a/internal/storage/postgresql_test.go b/internal/storage/postgresql_test.go
index 8218cc1..ef89cff 100644
--- a/internal/storage/postgresql_test.go
+++ b/internal/storage/postgresql_test.go
@@ -13,7 +13,7 @@ import (
"github.com/eugenetriguba/checkmate/assert"
)
-func TestConvertGenericPlaceholders(t *testing.T) {
+func TestPostgresql_ConvertGenericPlaceholders(t *testing.T) {
type test struct {
query string
argCount int
@@ -44,7 +44,7 @@ func TestConvertGenericPlaceholders(t *testing.T) {
}
}
-func TestTableExists(t *testing.T) {
+func TestPostgresql_TableExists(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.PostgresqlAdapter{}
db, err := sql.Open("pgx", adapter.CreateDSN(cfg))
@@ -67,7 +67,7 @@ func TestTableExists(t *testing.T) {
assert.True(t, exists)
}
-func TestDatabaseName(t *testing.T) {
+func TestPostgresql_DatabaseName(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.PostgresqlAdapter{}
db, err := sql.Open("pgx", adapter.CreateDSN(cfg))
@@ -82,7 +82,7 @@ func TestDatabaseName(t *testing.T) {
assert.Equal(t, name, cfg.DBName)
}
-func TestCreateDSN(t *testing.T) {
+func TestPostgresql_CreateDSN(t *testing.T) {
cfg := configloader.ConnectionConfig{
Driver: "postgres",
Host: "db1",
diff --git a/internal/storage/sqlite3.go b/internal/storage/sqlite3.go
new file mode 100644
index 0000000..877dc7e
--- /dev/null
+++ b/internal/storage/sqlite3.go
@@ -0,0 +1,48 @@
+package storage
+
+import (
+ "fmt"
+
+ "github.com/eugenetriguba/bolt/internal/configloader"
+)
+
+type SqliteAdapter struct{}
+
+func (s SqliteAdapter) ConvertGenericPlaceholders(
+ query string,
+ argsCount int,
+) string {
+ // The sqlite driver uses ? as a generic placeholder,
+ // so no translation is necessary.
+ return query
+}
+
+func (s SqliteAdapter) TableExists(
+ executor sqlExecutor,
+ tableName string,
+) (bool, error) {
+ var count int
+ err := executor.QueryRow(`
+ SELECT COUNT(*)
+ FROM sqlite_master
+ WHERE type='table' AND name=?;
+ `, tableName).Scan(&count)
+ if err != nil {
+ return false, fmt.Errorf(
+ "unable to check if %s exists: %w",
+ tableName,
+ err,
+ )
+ }
+
+ return count > 0, nil
+}
+
+func (s SqliteAdapter) DatabaseName(executor sqlExecutor) (string, error) {
+ return "main", nil
+}
+
+func (s SqliteAdapter) CreateDSN(cfg configloader.ConnectionConfig) string {
+ // Note: Use the dbname as the sqlite db name/path
+ return cfg.DBName
+}
diff --git a/internal/storage/sqlite3_test.go b/internal/storage/sqlite3_test.go
new file mode 100644
index 0000000..69eea38
--- /dev/null
+++ b/internal/storage/sqlite3_test.go
@@ -0,0 +1,94 @@
+//go:build sqlite3
+
+package storage_test
+
+import (
+ "database/sql"
+ "testing"
+
+ "github.com/eugenetriguba/bolt/internal/bolttest"
+ "github.com/eugenetriguba/bolt/internal/configloader"
+ "github.com/eugenetriguba/bolt/internal/storage"
+ "github.com/eugenetriguba/checkmate/assert"
+)
+
+func TestSqlite3_ConvertGenericPlaceholders(t *testing.T) {
+ type test struct {
+ query string
+ argCount int
+ expectedQuery string
+ }
+
+ testCases := []test{
+ {
+ query: "SELECT id, name FROM some_table;",
+ argCount: 0,
+ expectedQuery: "SELECT id, name FROM some_table;",
+ },
+ {
+ query: "SELECT id, name FROM some_table WHERE id = ?;",
+ argCount: 1,
+ expectedQuery: "SELECT id, name FROM some_table WHERE id = ?;",
+ },
+ {
+ query: "SELECT id, name FROM some_table WHERE id = ? AND name = ?;",
+ argCount: 2,
+ expectedQuery: "SELECT id, name FROM some_table WHERE id = ? AND name = ?;",
+ },
+ }
+ for _, tc := range testCases {
+ adapter := storage.MySQLAdapter{}
+ newQuery := adapter.ConvertGenericPlaceholders(tc.query, tc.argCount)
+ assert.Equal(t, newQuery, tc.expectedQuery)
+ }
+}
+
+func TestSqlite3_TableExists(t *testing.T) {
+ cfg := bolttest.NewTestConnectionConfig()
+ adapter := storage.SqliteAdapter{}
+ db, err := sql.Open("sqlite3", adapter.CreateDSN(cfg))
+ assert.Nil(t, err)
+ t.Cleanup(func() {
+ _, err = db.Exec("DROP TABLE IF EXISTS tmp;")
+ assert.Nil(t, err)
+ assert.Nil(t, db.Close())
+ })
+
+ exists, err := adapter.TableExists(db, "tmp")
+ assert.Nil(t, err)
+ assert.False(t, exists)
+
+ _, err = db.Exec("CREATE TABLE tmp(id INT PRIMARY KEY);")
+ assert.Nil(t, err)
+
+ exists, err = adapter.TableExists(db, "tmp")
+ assert.Nil(t, err)
+ assert.True(t, exists)
+}
+
+func TestSqlite3_DatabaseName(t *testing.T) {
+ cfg := bolttest.NewTestConnectionConfig()
+ adapter := storage.SqliteAdapter{}
+ db, err := sql.Open("sqlite3", adapter.CreateDSN(cfg))
+ assert.Nil(t, err)
+ t.Cleanup(func() {
+ assert.Nil(t, db.Close())
+ })
+
+ name, err := adapter.DatabaseName(db)
+ assert.Nil(t, err)
+
+ assert.Equal(t, name, "main")
+}
+
+func TestSqlite3_CreateDSN(t *testing.T) {
+ cfg := configloader.ConnectionConfig{
+ Driver: "sqlite3",
+ DBName: "./tmp/test.db",
+ }
+ adapter := storage.SqliteAdapter{}
+
+ cs := adapter.CreateDSN(cfg)
+
+ assert.Equal(t, cs, cfg.DBName)
+}