glebarez / sqlite Goto Github PK

View Code? Open in Web Editor NEW

This project forked from go-gorm/sqlite

592.0 3.0 39.0 361 KB

The pure-Go SQLite driver for GORM

License: MIT License

Go 100.00%

gorm gorm-orm gorm-driver driver golang sqlite sqlite3

sqlite's Introduction

Pure-Go SQLite driver for GORM

Pure-go (without cgo) implementation of SQLite driver for GORM

This driver has SQLite embedded, you don't need to install one separately.

Usage

import (
  "github.com/glebarez/sqlite"
  "gorm.io/gorm"
)

db, err := gorm.Open(sqlite.Open("sqlite.db"), &gorm.Config{})

In-memory DB example

db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})

Foreign-key constraint activation

Foreign-key constraint is disabled by default in SQLite. To activate it, use connection URL parameter:

db, err := gorm.Open(sqlite.Open(":memory:?_pragma=foreign_keys(1)"), &gorm.Config{})

More info: https://www.sqlite.org/foreignkeys.html

FAQ

How is this better than standard GORM SQLite driver?

The standard GORM driver for SQLite has one major drawback: it is based on a Go-bindings of SQLite C-source (this is called cgo). This fact imposes following restrictions on Go developers:

to build and run your code, you will need a C compiler installed on a machine
SQLite has many features that need to be enabled at compile time (e.g. json support). If you plan to use those, you will have to include proper build tags for every go command to work properly (go run, go test, etc.).
Because of C-compiler requirement, you can't build your Go code inside tiny stripped containers like (golang-alpine)
Building on GCP is not possible because Google Cloud Platform does not allow gcc to be executed.

Instead, this driver is based on pure-Go implementation of SQLite (https://gitlab.com/cznic/sqlite), which is basically an original SQLite C-source AST, translated into Go! So, you may be sure you're using the original SQLite implementation under the hood.

Is this tested good ?

Yes, The CI pipeline of this driver employs whole test base of GORM, which includes more than 12k tests (see badge on the page-top). Testing is run against latest major releases of Go:

1.18
1.19

In following environments:

Linux
Windows
MacOS

Is it fast?

Well, it's slower than CGo implementation, but not terribly. See the bechmark of underlying pure-Go driver vs CGo implementation.

Included features

JSON1 (https://www.sqlite.org/json1.html)
Math functions (https://www.sqlite.org/lang_mathfunc.html)

sqlite's People

Contributors

Stargazers

Watchers

sqlite's Issues

[FEAT] Support CREATE VIRTUAL TABLE / FTS5

This package seems not support CREATE VIRTUAL TABLE / FTS5, test code
https://go.dev/play/p/PxjDDoIn1N2

It would be nice to have support for the CREATE VIRTUAL TABLE feature,
Thanks.

ref

https://www.sqlite.org/fts5.html

Performance comparison with KVDB?

Hi, how is the performance compared to some KVDB like Badger or Pebble?

Other functions from modernc.org/sqlite

Hi! First of all Thank you for this project 🌷

How i can benefit from other features with the modernc.org/sqlite driver?

For example, I can create a custom SQLite function by writing this:

import sqlite "modernc.org/sqlite"

func main() {
// register a new sqlite function! easily: (e.g SELECT new_id();)
sqlite.MustRegisterScalarFunction("new_id", 0, newID)

// ....

}

But this function (and others) doesn't appear in your driver.

Thanks 🤗

Cause panic on arm9

Question:

I had no problem running it on Cortex-A7, but I had problems running it on arm9
The CGO version works on arm9

Code:
package main

import (
"github.com/glebarez/sqlite"
"gorm.io/gorm"
)

type Product struct {
gorm.Model
Code string
Price uint
}

func main() {
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
if err != nil {
panic("failed to connect database")
}
db.AutoMigrate(&Product{})
}

Build args:
GOARCH=arm GOOS=linux GOARM=5 go build .

Panic:
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x2c pc=0x373ef4]

goroutine 1 [running]:
modernc.org/sqlite/lib.Xsqlite3WhereBegin(0x2d025a0, 0xae5008b0, 0xad50b2b8, 0x0, 0xad50b0b8, 0xad507548, 0xad50b038, 0x0, 0x140)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:106300 +0xd20
modernc.org/sqlite/lib.Xsqlite3Select(0x2d025a0, 0xae5008b0, 0xad50b038, 0xae500f00)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:92009 +0x19fc
modernc.org/sqlite/lib.yy_reduce(0x2d025a0, 0xae5009f0, 0x54, 0x1, {0xad50b466, 0x0}, 0xae5008b0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:111851 +0x10d4
modernc.org/sqlite/lib.Xsqlite3Parser(0x2d025a0, 0xae5009f0, 0x1, {0xad50b466, 0x0})
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:113411 +0xb8
modernc.org/sqlite/lib.Xsqlite3RunParser(0x2d025a0, 0xae5008b0, 0xad50b438)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:114134 +0x32c
modernc.org/sqlite/lib.sqlite3Prepare(0x2d025a0, 0xad900018, 0xad50b438, 0xffffffff, 0x80, 0x0, 0xae500890, 0xae500894)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:85921 +0x338
modernc.org/sqlite/lib.sqlite3LockAndPrepare(0x2d025a0, 0xad900018, 0xad50b438, 0xffffffff, 0x80, 0x0, 0xae500890, 0xae500894)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:86004 +0x150
modernc.org/sqlite/lib.Xsqlite3_prepare_v2(...)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:86071
modernc.org/sqlite/lib.Xsqlite3_exec(0x2d025a0, 0xad900018, 0xad50b438, 0x48747c, 0xae500858, 0x0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:80623 +0x218
modernc.org/sqlite/lib.Xsqlite3InitOne(0x2d025a0, 0xad900018, 0x0, 0xae500054, 0x0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:85545 +0x850
modernc.org/sqlite/lib.Xsqlite3Init(0x2d025a0, 0xad900018, 0xae500054)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:85622 +0x70
modernc.org/sqlite/lib.Xsqlite3ReadSchema(0x2d025a0, 0xae500050)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:85649 +0x50
modernc.org/sqlite/lib.Xsqlite3LocateTable(0x2d025a0, 0xae500050, 0x0, 0xad50b738, 0x0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:68135 +0xa8
modernc.org/sqlite/lib.Xsqlite3LocateTableItem(0x2d025a0, 0xae500050, 0x0, 0xad50b8c0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:68192 +0x60
modernc.org/sqlite/lib.selectExpander(0x2d025a0, 0xae500790, 0xad50b4b8)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:90568 +0x330
modernc.org/sqlite/lib.Xsqlite3WalkSelect(0x2d025a0, 0xae500790, 0xad50b4b8)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:54992 +0x70
modernc.org/sqlite/lib.sqlite3SelectExpand(0x2d025a0, 0xae500050, 0xad50b4b8)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:90856 +0xf8
modernc.org/sqlite/lib.Xsqlite3SelectPrep(0x2d025a0, 0xae500050, 0xad50b4b8, 0x0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:90931 +0x50
modernc.org/sqlite/lib.Xsqlite3Select(0x2d025a0, 0xae500050, 0xad50b4b8, 0xae5006a0)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:91558 +0x1d4
modernc.org/sqlite/lib.yy_reduce(0x2d025a0, 0xae500190, 0x54, 0x1, {0xadb00550, 0x0}, 0xae500050)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:111851 +0x10d4
modernc.org/sqlite/lib.Xsqlite3Parser(0x2d025a0, 0xae500190, 0x1, {0xadb00550, 0x0})
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:113411 +0xb8
modernc.org/sqlite/lib.Xsqlite3RunParser(0x2d025a0, 0xae500050, 0xadb00510)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:114134 +0x32c
modernc.org/sqlite/lib.sqlite3Prepare(0x2d025a0, 0xad900018, 0xadb00510, 0xffffffff, 0x80, 0x0, 0xae300060, 0xae300068)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:85921 +0x338
modernc.org/sqlite/lib.sqlite3LockAndPrepare(0x2d025a0, 0xad900018, 0xadb00510, 0xffffffff, 0x80, 0x0, 0xae300060, 0xae300068)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:86004 +0x150
modernc.org/sqlite/lib.Xsqlite3_prepare_v2(...)
/Users/trevan/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:86071
github.com/glebarez/go-sqlite.(*conn).prepareV2(0x2c8fde0, 0x2c7591c)
/Users/trevan/go/pkg/mod/github.com/glebarez/[email protected]/sqlite.go:1275 +0x17c
github.com/glebarez/go-sqlite.(*stmt).query(0x2d1c6b8, {0x4f24a0, 0x755d78}, {0x2d2c060, 0x1, 0x1})
/Users/trevan/go/pkg/mod/github.com/glebarez/[email protected]/sqlite.go:605 +0x134
github.com/glebarez/go-sqlite.(*conn).query(0x2c8fde0, {0x4f24a0, 0x755d78}, {0x2c39500, 0x40}, {0x2d2c060, 0x1, 0x1})
/Users/trevan/go/pkg/mod/github.com/glebarez/[email protected]/sqlite.go:1519 +0x100
github.com/glebarez/go-sqlite.(*conn).QueryContext(0x2c8fde0, {0x4f24a0, 0x755d78}, {0x2c39500, 0x40}, {0x2d2c060, 0x1, 0x1})
/Users/trevan/go/pkg/mod/github.com/glebarez/[email protected]/sqlite_go18.go:38 +0x54
database/sql.ctxDriverQuery({0x4f24a0, 0x755d78}, {0xae830d80, 0x2c8fde0}, {0x0, 0x0}, {0x2c39500, 0x40}, {0x2d2c060, 0x1, ...})
/usr/local/go/src/database/sql/ctxutil.go:48 +0x94
database/sql.(*DB).queryDC.func1()
/usr/local/go/src/database/sql/sql.go:1748 +0x178
database/sql.withLock({0x4f1fa8, 0x2d1a000}, 0x2c75b5c)
/usr/local/go/src/database/sql/sql.go:3502 +0x84
database/sql.(*DB).queryDC(0x2c76870, {0x4f24a0, 0x755d78}, {0x0, 0x0}, 0x2d1a000, 0x2d1c6b0, {0x2c39500, 0x40}, {0x2d1c678, ...})
/usr/local/go/src/database/sql/sql.go:1743 +0x174
database/sql.(*DB).query(0x2c76870, {0x4f24a0, 0x755d78}, {0x2c39500, 0x40}, {0x2d1c678, 0x1, 0x1}, 0x1)
/usr/local/go/src/database/sql/sql.go:1726 +0xe4
database/sql.(*DB).QueryContext.func1(0x1)
/usr/local/go/src/database/sql/sql.go:1704 +0x6c
database/sql.(*DB).retry(0x2c76870, 0x2c75c64)
/usr/local/go/src/database/sql/sql.go:1538 +0x78
database/sql.(*DB).QueryContext(0x2c76870, {0x4f24a0, 0x755d78}, {0x2c39500, 0x40}, {0x2d1c678, 0x1, 0x1})
/usr/local/go/src/database/sql/sql.go:1703 +0x9c
database/sql.(*DB).QueryRowContext(0x2c76870, {0x4f24a0, 0x755d78}, {0x2c39500, 0x40}, {0x2d1c678, 0x1, 0x1})
/usr/local/go/src/database/sql/sql.go:1804 +0x54
gorm.io/gorm/callbacks.RowQuery(0x2d1e9e0)
/Users/trevan/go/pkg/mod/gorm.io/[email protected]/callbacks/row.go:18 +0x208
gorm.io/gorm.(*processor).Execute(0x2ce0b40, 0x2d1e9e0)
/Users/trevan/go/pkg/mod/gorm.io/[email protected]/callbacks.go:130 +0x3ec
gorm.io/gorm.(*DB).Row(0x2d1e9e0)
/Users/trevan/go/pkg/mod/gorm.io/[email protected]/finisher_api.go:502 +0x94
github.com/glebarez/sqlite.Migrator.HasTable.func1(0x2c6ab60)
/Users/trevan/go/pkg/mod/github.com/glebarez/[email protected]/migrator.go:32 +0x94
gorm.io/gorm/migrator.Migrator.RunWithValue({{0x1, 0x2d1e9c0, {0x4f2980, 0x2d2c048}}}, {0x42e9e8, 0x2d02640}, 0x2c75e34)
/Users/trevan/go/pkg/mod/gorm.io/[email protected]/migrator/migrator.go:71 +0x144
github.com/glebarez/sqlite.Migrator.HasTable({{{0x1, 0x2d1e9c0, {0x4f2980, 0x2d2c048}}}}, {0x42e9e8, 0x2d02640})
/Users/trevan/go/pkg/mod/github.com/glebarez/[email protected]/migrator.go:31 +0x90
gorm.io/gorm/migrator.Migrator.AutoMigrate({{0x1, 0x2d1e5c0, {0x4f2980, 0x2cbdd88}}}, {0x2d1c350, 0x1, 0x1})
/Users/trevan/go/pkg/mod/gorm.io/[email protected]/migrator/migrator.go:120 +0x198
gorm.io/gorm.(*DB).AutoMigrate(0x2c8fd40, {0x2d1c350, 0x1, 0x1})
/Users/trevan/go/pkg/mod/gorm.io/[email protected]/migrator.go:24 +0x48
main.main()
/Users/trevan/Code/VSCode/demo/dnptime/main.go:28 +0x130

驱动不支持windows, 386

如图

很容易就报错数据库被锁

sqlite database is locked (5) (SQLITE_BUSY)

Generates a wrong DDL

I'm still new to GORM and have been using modernc.org/sqlite for quite a while.

When I try to rerun the migrations I get the following error for these models:

// Model is the base model for all models.
type Model struct {
	ID        uint           `gorm:"primaryKey"`
	CreatedAt time.Time      `gorm:"not null;default:CURRENT_TIMESTAMP"`
	UpdatedAt time.Time      `gorm:"not null;default:CURRENT_TIMESTAMP"`
	DeletedAt gorm.DeletedAt `gorm:"index"`
}

// PublicKey is a public key model.
type PublicKey struct {
	Model
	Key    string `gorm:"column:public_key;index:idx_public_key_key,unique;index:idx_public_key_user,unique;not null"`
	UserID uint   `gorm:"index:idx_public_key_user,unique;not null"`
	User   User   `gorm:"constraint:OnDelete:CASCADE,OnUpdate:CASCADE;"`
}

// TableName returns the table name for the public key model.
func (PublicKey) TableName() string {
	return "public_key"
}

2023/07/05 16:07:27 /Users/ayman/.go/pkg/mod/github.com/glebarez/[email protected]/migrator.go:406 SQL logic error: near "text": syntax error (1)
[0.014ms] [rows:0] CREATE TABLE `public_key__temp` text NOT NULL UNIQUE,`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`deleted_at` datetime,`public_key` ?,`user_id` integer NOT NULL,PRIMARY KEY (`id`),CONSTRAINT `fk_user_public_key` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE)

I'm assuming the error is happening because I'm using the same name for the column public_key and table name public_key

AutoMigrate causes an "invalid DDL, unbalanced brackets" error

Bug

Using db.AutoMigrate with a specific Model causes the sqlite module to return an error invalid DDL, unbalanced brackets.

This is happening in https://github.com/envelope-zero/backend/blob/2adc00f348d0be5ad0101d72fbe37f2ecbc95bbf/main.go#L47. Using git bisect, I tracked it back to the upgrade of gorm to 1.23.6, (this commit), however, I am not sure why it only started occuring there.

The bug does not occur with the upstream sqlite driver.

Details

The error specifically seems to occur on the Transactions model. Tracing it back by debugging the parseDDL function, I noticed that one call to the function is broken.

parseDDL is called with one string:

"CREATE TABLE `transactions__temp` (`id` text,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`date` datetime,`amount` ?,8),`note` text,`budget_id` text,`source_account_id` text,`destination_account_id` text,`envelope_id` text,`reconciled` numeric,PRIMARY KEY (`id`),CONSTRAINT `fk_transactions_budget` FOREIGN KEY (`budget_id`) REFERENCES `budgets`(`id`),CONSTRAINT `fk_transactions_source_account` FOREIGN KEY (`source_account_id`) REFERENCES `accounts`(`id`),CONSTRAINT `fk_transactions_destination_account` FOREIGN KEY (`destination_account_id`) REFERENCES `accounts`(`id`),CONSTRAINT `fk_transactions_envelope` FOREIGN KEY (`envelope_id`) REFERENCES `envelopes`(`id`))"

Notice the

`amount` ?,8)

part, which is clearly broken. The model specifies this as

Amount               decimal.Decimal `json:"amount" gorm:"type:DECIMAL(20,8)"`

The error is returned on ddlmod.go, line 77, with a bracketLevel of -1.

Reproduction

git clone https://github.com/envelope-zero/backend.git
go run main.go and attach a debugger with set breakpoints (In VSCode, you can use the Run API (SQLite)

Additional info

I’d like to debug this further, but so far I failed to find a good approach to find out where the string gets mangled and wasn't able to find this out. I’ll happily debug this further once I find an approach and I’m happy about any input anyone might be able to provide.

AutoMigrate has a duplicate column name problem.

go-gorm/sqlite has solved it. see go-gorm/sqlite

armhf: broken database after upgrading modernc/sqlite from v1.30.1 to v1.32.0

For evcc.io, we're using go-gorm/gorm and glebarez/sqlite for database backend. After upgrading modernc/sqlite from v1.30.1 to v1.32.0, users on linux/armhf architecture have experienced broken database: evcc-io/evcc#15686 (comment). Gorm error message is

failed to initialize database, got error unable to open database file: out of memory (14)

Reverting to an earlier commit does not cure this- it looks as if the database is terminally damaged. However, the same database can still be read and processed on darwin/arm64- so maybe there is still hope after all.

glebarez/sqlite version remained unchanged at v1.11.0. Migrated here from https://gitlab.com/cznic/sqlite/-/issues/191.

invalid operation: limit.Limit > 0 (mismatched types *int and untyped int)

`
C:\Users\xxx\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:100:22: invalid operation: limit.Limit > 0 (mismatched types *int and untyped int)

C:\Users\xxx\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:101:24: invalid operation: limit.Limit <= 0 (mismatched types *int and untyped int)

C:\Users\xxx\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:102:21: cannot use -1 (untyped int constant) as *int value in assignment

C:\Users\xxx\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:105:39: cannot use limit.Limit (variable of type *int) as int value in argument to strconv.Itoa
`

Build fail on Darwin/arm64 because of libc dependency

go version
go version go1.22.0 darwin/arm64

I'm trying to build application but get this error:

go run main.go

../../../../../.asdf/installs/golang/1.22.0/packages/pkg/mod/modernc.org/[email protected]/libc_darwin.go:484:20: undefined: unix.Getcwd```

Did you test you library on Apple Silicon?

arm5 linux SQLite

sys：Linux buildroot 4.4.207+ #86 Wed Jun 30 08:47:30 CST 2021 armv5tejl GNU/Linux
sqlite3:SQLite version 3.21.0 2017-10-24 18:55:49

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x2c pc=0x413e14]

goroutine 1 [running]:
modernc.org/sqlite/lib.Xsqlite3WhereBegin(0x36fd500, 0xae5008b0, 0xad50b6b8, 0x0, 0xad50b4b8, 0xad507548, 0xad50b438, 0x0, 0x140)
/Users/haidao/go/pkg/mod/modernc.org/[email protected]/lib/sqlite_linux_arm.go:106303 +0xd20

AutoMigrate returns error "invalid DDL"

What's the output of `go env`?

go env

$ go env
GO111MODULE="on"
GOARCH="amd64"
GOBIN=""
GOCACHE="/Users/cn/Library/Caches/go-build"
GOENV="/Users/cn/Library/Application Support/go/env"
GOEXE=""
GOEXPERIMENT=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="darwin"
GOINSECURE=""
GOMODCACHE="/Users/cn/Workspace/golang/pkg/mod"
GOOS="darwin"
GOPATH="/Users/cn/Workspace/golang"
GOPRIVATE=""
GOPROXY="https://goproxy.cn,direct"
GOROOT="/Users/cn/Workspace/gosdk/go1.17"
GOSUMDB="sum.golang.google.cn"
GOTMPDIR=""
GOTOOLDIR="/Users/cn/Workspace/gosdk/go1.17/pkg/tool/darwin_amd64"
GOVCS=""
GOVERSION="go1.17"
GCCGO="gccgo"
AR="ar"
CC="clang"
CXX="clang++"
CGO_ENABLED="1"
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -arch x86_64 -m64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=/var/folders/9d/p9d0dmmj04zfrll8cm7rndsm0000gn/T/go-build2220765180=/tmp/go-build -gno-record-gcc-switches -fno-common"

What did I do?

package main

import (
	"fmt"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

type Test struct {
	gorm.Model
}

func (*Test) TableName() string {
	return "test-a"
}

func main() {
	db, err := gorm.Open(sqlite.Open("sqlite.db"), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	if err = db.AutoMigrate(&Test{}); err != nil {
		panic(err)
	}
	fmt.Println("migrated")
}

At the first run, table test-a is migrated successfully:

% go run main.go 
migrated

Then I rerun it, and the error panicked:

% go run main.go
panic: invalid DDL

goroutine 1 [running]:
main.main()
	/Users/calvin/Workspace/golang/src/segment/main.go:24 +0x248
exit status 2

Compile for wasm

I am looking for ways to compile this library into wasm, but no luck so far. Any instructions to follow?

Is it possible to set a journal_mode?

Wrong error on Unique constraints

The library does not return the ErrDuplicatedKey as gorm documents, but just returns the go-sqlite 2067 ("constraint failed: UNIQUE constraint failed: users.username (2067)") error.

To be compliant, there should be a mapping of the errors so that one can check errors.Is(err, gorm.ErrDuplicatedKey) independently of the used database driver.

Still depends on CGO

sqlite/go.sum

Line 17 in ed824cf

    
           github.com/mattn/go-sqlite3 v1.14.10/go.mod h1:NyWgC/yNuGj7Q9rpYnZvas74GogHl5/Z4A/KQRfk6bU=

, seems that it still depends on mattn/go-sqlite3, which can't run without CGO enabled.

Out of sync with gorm.io/driver/sqlite

I have currently added some changes in migrator.go on gorm.io/driver/sqlite, which can resolve some problems in the issues. Could you rebase those changes? Thx.

速度较cgo版本差距有点大

两者对比试试看
main.go

package main

import (
	"log"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type Product struct {
	gorm.Model
	Code  string
	Price uint
}


func main() {
	db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}

	// Migrate the schema
	db.AutoMigrate(&Product{})

	s := time.Now()
	for i := 0; i < 100; i++ {
		// Create
		db.Create(&Product{Code: "D42", Price: 100})
	}
	log.Print(time.Since(s).Seconds())
	
}

对比下这个

package main

import (
	"log"
	"time"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

type Product struct {
	gorm.Model
	Code  string
	Price uint
}

func main() {

	db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
	if err != nil {
		panic(err)
	}
	// Migrate the schema
	db.AutoMigrate(&Product{})

	s := time.Now()
	for i := 0; i < 100; i++ {
		 db.Create(&Product{Code: "D42", Price: 100})
	}
	log.Print(time.Since(s).Seconds())

}

``

How to register custom functions? Can't import modernc lib

I'm trying to figure out how to add custom functions. When I register the custom functions directly to the modernc lib, then I get the following error because both the glebarez lib and the modernc call sql.Register using the same driver name.

 Register called twice for driver sqlite

import (
	"database/sql/driver"
	"fmt"
	"github.com/glebarez/sqlite"
	"github.com/tidwall/gjson"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	sqlitecore "modernc.org/sqlite"
)

func init() {
        sqlitecore.RegisterFunction("FOOBAR", &sqlitecore.FunctionImpl{
		NArgs:         2,
		Deterministic: true,
		Scalar: ...,
	})
}

func open(dsn string) (*gorm.DB, error) {
	return gorm.Open(sqlite.Open(dsn), &gorm.Config{
		Logger: logger.Discard,
	})
}

Error Occurred After Upgrading to 1.11.0

Here is the full log

package command-line-arguments
        imports github.com/pplmx/blog-go/cmd
        imports github.com/pplmx/blog-go/internal/db
        imports github.com/glebarez/sqlite
        imports github.com/glebarez/go-sqlite
        imports modernc.org/libc: C source files not allowed when not using cgo or SWIG: libucrt.c

Workgroud

Downgrade to 1.10.0

[FEAT] Support Encrypt SQLite DB

It would be nice to have an encrypted database like this lib, which not a lot of people do:

https://github.com/jackfr0st13/gorm-sqlite-cipher

Thanks,

Failed to compile Windows 32-bit on Debian.

env
go version go1.20 linux/amd64

CGO_ENABLED=0 GOOS=windows GOARCH=386 go build -o ../../build/sqlitemanager/amd64/BL-SqliteManager.exe
modernc.org/sqlite/lib
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/defs.go:13:35: undefined: sqlite3_index_constraint
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/defs.go:14:35: undefined: sqlite3_index_orderby
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/defs.go:15:35: undefined: sqlite3_index_constraint_usage
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:19:5: undefined: Xsqlite3_threadsafe
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:29:11: undefined: Xsqlite3_config
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:29:32: undefined: SQLITE_CONFIG_MUTEX
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:29:122: undefined: SQLITE_OK
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:30:8: undefined: Xsqlite3_errstr
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:40:17: undefined: Sqlite3_mutex_methods
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:239:46: undefined: SQLITE_OK
/home/guanxy/go/pkg/mod/modernc.org/[email protected]/lib/mutex.go:239:46: too many errors

[Update] SQlite 3.40

SQlite 3.40 is already out, with some cool changes: https://www.sqlite.org/changes.html
Hope to see it implemented soon :)

Thanks in adavnce!

another implementation of SQLITE with no CGO and GORM

I have been using this with success.

https://github.com/ncruces/go-sqlite3/blob/main/gormlite/go.mod

this is SQLITE with no CGO and has GORM.

Its benchmarked here: https://github.com/cvilsmeier/go-sqlite-bench

github.com/glebarez/[email protected]/sqlite.go:101:22: cannot convert 0 (untyped int constant) to *int

time.Time returns an empty location instead of UTC

Problem

Querying a time.Time field results in the location being returned as time.Location("") even though the field was previously set to UTC.

Querying the same field using the gorm driver results in the location being return as time.UTC

Example

package sqlitetimetest_test

import (
	"testing"
	"time"

	glebarezsqlite "github.com/glebarez/sqlite"
	"github.com/stretchr/testify/require"
	gormsqlite "gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

type Row struct {
	Timestamp time.Time `gorm:"column:timestamp;type:datetime" json:"timestamp"`
}

// go-gorm/sqlite

func TestGorm(t *testing.T) {
	gormConfig := &gorm.Config{NamingStrategy: schema.NamingStrategy{SingularTable: true}}
	db, err := gorm.Open(gormsqlite.Open("file::memory:"), gormConfig)
	require.NoError(t, err)
	if err := db.AutoMigrate(&Row{}); err != nil {
		t.Fatal(err)
	}

	expectedNow := time.Now().UTC()
	if err := db.Create(&Row{Timestamp: expectedNow}).Error; err != nil {
		t.Fatal(err)
	}

	r := &Row{}
	if err := db.Model(&Row{}).Find(r).Error; err != nil {
		t.Fatal(err)
	}
	require.Equal(t, expectedNow, r.Timestamp)

	sql, err := db.DB()
	if err != nil {
		t.Fatal(err)
	}

	rr := &Row{}
	if err := sql.QueryRow("SELECT timestamp FROM row").Scan(&rr.Timestamp); err != nil {
		t.Fatal(err)
	}
	require.Equal(t, expectedNow, rr.Timestamp)
}
/// Test passes with the expectation that the location is returned as UTC.

// ### glebarez/sqlite

func TestGlebarez(t *testing.T) {
	gormConfig := &gorm.Config{NamingStrategy: schema.NamingStrategy{SingularTable: true}}
	db, err := gorm.Open(glebarezsqlite.Open("file::memory:"), gormConfig)
	require.NoError(t, err)
	if err := db.AutoMigrate(&Row{}); err != nil {
		t.Fatal(err)
	}

	expectedNow := time.Now().UTC()
	if err := db.Create(&Row{Timestamp: expectedNow}).Error; err != nil {
		t.Fatal(err)
	}

	r := &Row{}
	if err := db.Model(&Row{}).Find(r).Error; err != nil {
		t.Fatal(err)
	}
	require.Equal(t, expectedNow, r.Timestamp)
	// Error:    Not equal:
	// expected: time.Date(2022, time.September, 29, 20, 59, 28, 687510000, time.UTC)
	// actual  : time.Date(2022, time.September, 29, 20, 59, 28, 687510000, time.Location(""))

	sql, err := db.DB()
	if err != nil {
		t.Fatal(err)
	}

	rr := &Row{}
	if err := sql.QueryRow("SELECT timestamp FROM row").Scan(&rr.Timestamp); err != nil {
		t.Fatal(err)
	}
	require.Equal(t, expectedNow, rr.Timestamp)
	// Error:    Not equal:
	// expected: time.Date(2022, time.September, 29, 20, 59, 28, 687510000, time.UTC)
	// actual  : time.Date(2022, time.September, 29, 20, 59, 28, 687510000, time.Location(""))
    
    // glebarez driver returns time.Location("") and not UTC.
}

Question

Wondering if this is the expected behavior? or a bug? If it is expected, how can we make sure that it will return UTC? Thank you!

Failed UNIQUE constraint does not return gorm error

Scenario:
A table which has fields with UNIQUE constraint.
Try to insert a new entry with the same value for the UNIQUE field as an already existing entry.

Expected result:
Returned error being gorm.ErrDuplicatedKey

Actual result:
Returned error is from sqlite3: constraint failed: UNIQUE constraint failed: table.field(2067) which is clumsy to react to in code

FOREIGN KEY constraint failed (1811) on AutoMigrate when set foreign_keys ON for SQLite

Related to go-gorm/gorm#7034
Please read for more info there!