Repository: ariga/atlas
Pull request #2908 (2024-06-30 11:32:16)

sql/sqlite: support jsonb


Patch:
diff --git a/doc/md/guides/docker.mdx b/doc/md/guides/docker.mdx
new file mode 100644
index 00000000..50e05af7
--- /dev/null
+++ b/doc/md/guides/docker.mdx
@@ -0,0 +1,109 @@
+---
+title: Running Atlas in Docker
+id: atlas-in-docker
+slug: atlas-in-docker
+---
+Atlas ships as a set of official [Docker Images](https://hub.docker.com/r/arigaio/atlas) for you to use.
+
+To run Atlas in Docker, execute:
+
+```shell
+docker run --rm -it arigaio/atlas:latest-alpine
+```
+
+Depending on your use case, you may want to use a different image type:
+
+| Base Image | Image Tags                    | Purpose                                        |
+|------------|-------------------------------|------------------------------------------------|
+| Distroless | `latest`, `latest-distroless` | Bare bone image containing only Atlas          |
+| Alpine     | `latest-alpine`               | Alpine based image, with basic shell (/bin/sh) |
+
+## Common Issues
+
+### `Use 'atlas login' to access this feature`
+
+Atlas is an open-core project, with some features available only to signed-in users.  To use these features, you
+must sign in to Atlas.  To sign in:
+
+1. Run:
+
+  ```shell
+  docker run --rm -it \
+ //highlight-next-line
+    -v ~/.atlas:/root/.atlas \
+    arigaio/atlas:latest login
+  ```
+
+2. Atlas will provide you with a URL to visit in your browser:
+
+  ```
+  Please visit:
+
+  https://auth.atlasgo.cloud/login?cli=ade66529-e6c0-4c56-8311-e23d0efe9ee9&port=33281
+
+  Follow the instructions on screen. (Hit <ENTER> to manually provide the code.)
+  ```
+
+3. Visit the URL in your browser and follow the on-screen instructions.
+
+4. Copy the code provided by Atlas Cloud:
+   ![](https://atlasgo.io/uploads/docker-guide/copy-this-code.png)
+
+5. Paste the code back into the terminal where you ran `atlas login` and hit `<ENTER>`:
+   ```
+   Please enter the auth code:
+   ```
+
+6. Atlas will verify your code and provide you with a success message:
+   ```
+   You are now connected to acme-corp-1337-ltd on Atlas Cloud.
+   ```
+
+7. You can now use Atlas features that require authentication. Use the `-v ~/.atlas:/root/.atlas` flag to persist your
+   login credentials across Docker runs. For example:
+
+   ```shell
+    docker run --rm -it \
+   //highlight-next-line
+      -v ~/.atlas:/root/.atlas \
+      arigaio/atlas:latest-alpine schema inspect --url "<url>"
+    ```
+
+### `"docker": executable file not found in $PATH`
+
+Atlas heavily relies on the presence of a [Dev Database](/concepts/dev-database) for various calculations
+and schema normalization.  To use a Dev Database, users provide Atlas with the URL to connect to an empty
+database of the type they wish to operate on.
+
+To streamline work with Dev Databases, Atlas provides a convenience driver named `docker://`, in which Atlas
+depends on the Docker CLI command `docker` to be present in the runtime environment.  Running Docker-in-Docker
+is a notoriously nuanced topic and so we do not ship `docker` in the distributed Atlas images.
+
+For this reason, Atlas users who wish to run Atlas in Docker, cannot, by default use the `docker://` driver.
+
+#### Workaround: Spin up a local database container and use it
+
+A common workaround is to spin up a local, empty database container and connect to it.
+
+1. Create a Docker Network to establish network connectivity between your local DB and Atlas:
+  ```
+  docker network create db-network
+  ```
+2. Run the database:
+  ```
+  docker run --name pg-dev-db --network db-network -e POSTGRES_PASSWORD=mysecretpassword -d postgres:16
+  ```
+3. Use the new dev db:
+  ```
+  docker run --rm --network db-network \
+    -v $PWD:/data \
+    arigaio/atlas migrate diff \
+    --to file:///data/schema.hcl \
+    --dir file:///data/migrations \
+    --dev-url "postgres://postgres:mysecretpassword@pg-dev:5432/postgres?sslmode=disable"
+  ```
+  Note a few things about this command:
+  * We use the `--network` flag to use the network we created for our dev database on step 1.
+  * We mount our local dir as `/data`
+  * We use the URL for our dev database as the `--dev-url` flag, note that the hostname `pg-dev` was specified in step
+    2 as the container name.
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index 92b093be..2f867b84 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -255,6 +255,10 @@ module.exports = {
                 },
             ]
         },
+        {
+            type: 'doc',
+            id: 'guides/atlas-in-docker',
+        },
         {
             type: 'category',
             label: 'Testing',
diff --git a/sql/sqlite/convert.go b/sql/sqlite/convert.go
index f5c18604..55ef8cb0 100644
--- a/sql/sqlite/convert.go
+++ b/sql/sqlite/convert.go
@@ -95,7 +95,7 @@ func ParseType(c string) (schema.Type, error) {
 			ct.Size = int(p)
 		}
 		return ct, nil
-	case "json":
+	case "json", "jsonb":
 		return &schema.JSONType{T: t}, nil
 	case "date", "datetime", "time", "timestamp":
 		return &schema.TimeType{T: t}, nil
diff --git a/sql/sqlite/sqlspec.go b/sql/sqlite/sqlspec.go
index 7581128e..5c469f58 100644
--- a/sql/sqlite/sqlspec.go
+++ b/sql/sqlite/sqlspec.go
@@ -280,6 +280,7 @@ var TypeRegistry = schemahcl.NewRegistry(
 		schemahcl.NewTypeSpec("datetime"),
 		schemahcl.NewTypeSpec("json"),
 		schemahcl.NewTypeSpec("uuid"),
+		schemahcl.NewTypeSpec("jsonb"),
 	),
 )
 


Test patch:
diff --git a/sql/sqlite/inspect_test.go b/sql/sqlite/inspect_test.go
index f9f021eb..7d1db781 100644
--- a/sql/sqlite/inspect_test.go
+++ b/sql/sqlite/inspect_test.go
@@ -30,6 +30,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					WillReturnRows(sqltest.Rows(`
  name |   type       | nullable | dflt_value  | primary  | hidden
 ------+--------------+----------+ ------------+----------+----------
+ id   | integer       |  0      |     0x1     |  1       |  0
  c1   | int           |  1      |     a       |  0       |  0
  c2   | integer       |  0      |     97      |  0       |  0
  c3   | varchar(100)  |  1      |    'A'      |  0       |  0
@@ -44,7 +45,7 @@ func TestDriver_InspectTable(t *testing.T) {
  w    | int           |  0      |             |  0       |  2
  x    | text          |  0      |             |  0       |  3
  y    | text          |  0      |             |  0       |  2
- id   | integer       |  0      |     0x1     |  1       |  0
+ j    | jsonb         |  0      |             |  0       |  0
 `))
 				m.noIndexes("users")
 				m.noFKs("users")
@@ -52,6 +53,7 @@ func TestDriver_InspectTable(t *testing.T) {
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
 				columns := []*schema.Column{
+					{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}, Attrs: []schema.Attr{&AutoIncrement{}}, Default: &schema.Literal{V: "0x1"}},
 					{Name: "c1", Type: &schema.ColumnType{Null: true, Type: &schema.IntegerType{T: "int"}, Raw: "int"}, Default: &schema.RawExpr{X: "a"}},
 					{Name: "c2", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}, Default: &schema.Literal{V: "97"}},
 					{Name: "c3", Type: &schema.ColumnType{Null: true, Type: &schema.StringType{T: "varchar", Size: 100}, Raw: "varchar(100)"}, Default: &schema.Literal{V: "'A'"}},
@@ -66,14 +68,14 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "w", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}, Raw: "int"}, Attrs: []schema.Attr{&schema.GeneratedExpr{Type: "VIRTUAL", Expr: "(a*10)"}}},
 					{Name: "x", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Raw: "text"}, Attrs: []schema.Attr{&schema.GeneratedExpr{Type: "STORED", Expr: "(typeof(c))"}}},
 					{Name: "y", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Raw: "text"}, Attrs: []schema.Attr{&schema.GeneratedExpr{Type: "VIRTUAL", Expr: "(substr(b,a,a+2))"}}},
-					{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}, Attrs: []schema.Attr{&AutoIncrement{}}, Default: &schema.Literal{V: "0x1"}},
+					{Name: "j", Type: &schema.ColumnType{Type: &schema.JSONType{T: "jsonb"}, Raw: "jsonb"}},
 				}
 				require.Equal(t.Columns, columns)
 				require.EqualValues(&schema.Index{
 					Name:   "PRIMARY",
 					Unique: true,
 					Table:  t,
-					Parts:  []*schema.IndexPart{{SeqNo: 1, C: columns[len(columns)-1]}},
+					Parts:  []*schema.IndexPart{{SeqNo: 1, C: columns[0]}},
 					Attrs:  []schema.Attr{&AutoIncrement{}},
 				}, t.PrimaryKey)
 			},
diff --git a/sql/sqlite/sqlspec_test.go b/sql/sqlite/sqlspec_test.go
index 4b214552..8f83dfb5 100644
--- a/sql/sqlite/sqlspec_test.go
+++ b/sql/sqlite/sqlspec_test.go
@@ -458,6 +458,10 @@ func TestTypes(t *testing.T) {
 			typeExpr: "uuid",
 			expected: &schema.UUIDType{T: "uuid"},
 		},
+		{
+			typeExpr: "jsonb",
+			expected: &schema.JSONType{T: "jsonb"},
+		},
 	} {
 		t.Run(tt.typeExpr, func(t *testing.T) {
 			var test schema.Schema