Description

sqlite is a thin wrapper for the SQLite library, which in turn is "a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine."

Install SQLite Dependency

On any platform (Windows, Linux, macOS), you can run:

v vlib/db/sqlite/install_thirdparty_sqlite.vsh

This downloads the SQLite amalgamation source and places it in v/thirdparty/sqlite. V will then compile it automatically during your build.

The installer pins one amalgamation version (currently 3.53.4) instead of taking the newest. SQLite 3.54.0 uses the SRWLOCK type in its Windows mutex code, which tcc's windows.h does not declare, so v -cc tcc on Windows cannot build 3.54.0. The pin moves only when someone updates it by hand, after checking that tcc builds the new amalgamation. SQLite fixes released after 3.53.4 are not picked up automatically.

If you need to install SQLite manually, use the SQLite amalgamation package. v/thirdparty/sqlite must contain sqlite3.c and sqlite3.h directly.

Do not use the autoconf/source package here, and do not rename sqlite3.c to sqlite3.cpp.

On macOS, V can also fall back to the system libsqlite3 when pkg-config is not available. It includes sqlite3.h from the macOS SDK and links the system library, so an extra SQLite install is not needed. Install Xcode Command Line Tools if the SDK headers are missing.

On Linux, you can also install the system development package instead:

• Debian/Ubuntu: sudo apt install -y libsqlite3-dev
• Fedora/RHEL: sudo dnf -y install sqlite-devel
• Arch: sudo pacman -S sqlite

Convenience Methods

The DB struct provides several helper methods for common introspection queries:

import db.sqlite

db := sqlite.connect('mydb.db') or { panic(err) }

// List all user tables
tables := db.tables()!

// Get column names for a table
cols := db.columns('users')!

// Get CREATE statements (single table or all objects)
s := db.schema('users')!

// Database file size in bytes
size := db.db_size()!

Interactive CLI

V includes a built-in SQLite CLI as a replacement for sqlite3:

v sqlite mydb.db

Features include a full readline REPL with history and tab completion, 9 output modes (table, box, markdown, csv, json, line, html, insert, quote), .dump, .import/.export, .backup, session control, and schema tools. Run .help inside the REPL for the full command list.

Performance Tips

When performing a large amount of database calls (i.e. INSERTS), significant performance increase can be obtained by controlling the synchronization and journal modes.

For instance:

import db.sqlite

db := sqlite.connect('foo.db') or { panic(err) }
db.synchronization_mode(sqlite.SyncMode.off)!
db.journal_mode(sqlite.JournalMode.memory)!

fn connect(path string) !DB

connect Opens the connection with a database.

fn connect_full(path string, mode_flags []OpenModeFlag, vfs_name string) !DB

connect_full Opens connection to sqlite database. It gives more control than open. Flags give control over readonly and create decisions. Specific VFS can be chosen. Pass '' for vfs_name, if you want to use the default VFS for the current platform, (which is equivalent to 'win32' on Windows, and 'unix' on !Windows systems).

fn get_default_vfs() !&Sqlite3_vfs

get_default_vfs Asks sqlite for default VFS instance

fn get_vfs(name string) !&Sqlite3_vfs

get_vfs Requests sqlite to return instance of VFS with given name. when such vfs is not known, none is returned

fn is_error(code int) bool

is_error checks if code is an error code.

fn new_connection_pool(config string, size int) !ConnectionPool

new_connection_pool creates a new connection pool with the given size and configuration.

fn JournalMode.from[W](input W) !JournalMode

from returns the JournalMode value of an integer or of a field name, or an error if there is none.

fn OpenModeFlag.from[W](input W) !OpenModeFlag

from returns the OpenModeFlag value of an integer or of a field name, or an error if there is none.

fn Result.from[W](input W) !Result

from returns the Result value of an integer or of a field name, or an error if there is none.

fn Sqlite3TransactionLevel.from[W](input W) !Sqlite3TransactionLevel

from returns the Sqlite3TransactionLevel value of an integer or of a field name, or an error if there is none.

fn SyncMode.from[W](input W) !SyncMode

from returns the SyncMode value of an integer or of a field name, or an error if there is none.

type Params = []string | [][]string

type Sqlite3_file = C.sqlite3_file

type Sqlite3_io_methods = C.sqlite3_io_methods

type Sqlite3_vfs = C.sqlite3_vfs

fn (mut v Sqlite3_vfs) register_as_nondefault() !

register_as_nondefault Asks sqlite to register VFS passed in receiver argument as the known VFS. more info about VFS: https://www.sqlite.org/c3ref/vfs.html 'not TODOs' to prevent corruption: https://sqlite.org/howtocorrupt.html example VFS: https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c

fn (mut v Sqlite3_vfs) unregister() !

unregister Requests sqlite to stop using VFS as passed in receiver argument

enum JournalMode {
	off
	delete
	truncate
	persist
	memory
}

enum OpenModeFlag {
	readonly     = 0x00000001
	readwrite    = 0x00000002
	create       = 0x00000004
	uri          = 0x00000040
	memory       = 0x00000080
	nomutex      = 0x00008000
	fullmutex    = 0x00010000
	sharedcache  = 0x00020000
	privatecache = 0x00040000
	exrescode    = 0x02000000
	nofollow     = 0x01000000
}

https://www.sqlite.org/c3ref/c_open_autoproxy.html

enum Result {
	ok                      = 0
	error                   = 1
	internal                = 2
	perm                    = 3
	abort                   = 4
	busy                    = 5
	locked                  = 6
	nomem                   = 7
	readonly                = 8
	interrupt               = 9
	ioerr                   = 10
	corrupt                 = 11
	notfound                = 12
	full                    = 13
	cantopen                = 14
	protocol                = 15
	empty                   = 16
	schema                  = 17
	toobig                  = 18
	constraint              = 19
	mismatch                = 20
	misuse                  = 21
	nolfs                   = 22
	auth                    = 23
	format                  = 24
	range                   = 25
	notadb                  = 26
	notice                  = 27
	warning                 = 28
	row                     = 100
	done                    = 101
	ok_load_permanently     = 256
	error_missing_collseq   = 257
	busy_recovery           = 261
	locked_sharedcache      = 262
	readonly_recovery       = 264
	ioerr_read              = 266
	corrupt_vtab            = 267
	cantopen_notempdir      = 270
	constraint_check        = 275
	notice_recover_wal      = 283
	warning_autoindex       = 284
	error_retry             = 513
	abort_rollback          = 516
	busy_snapshot           = 517
	locked_vtab             = 518
	readonly_cantlock       = 520
	ioerr_short_read        = 522
	corrupt_sequence        = 523
	cantopen_isdir          = 526
	constraint_commithook   = 531
	notice_recover_rollback = 539
	error_snapshot          = 769
	busy_timeout            = 773
	readonly_rollback       = 776
	ioerr_write             = 778
	corrupt_index           = 779
	cantopen_fullpath       = 782
	constraint_foreignkey   = 787
	readonly_dbmoved        = 1032
	ioerr_fsync             = 1034
	cantopen_convpath       = 1038
	constraint_function     = 1043
	readonly_cantinit       = 1288
	ioerr_dir_fsync         = 1290
	cantopen_dirtywal       = 1294
	constraint_notnull      = 1299
	readonly_directory      = 1544
	ioerr_truncate          = 1546
	cantopen_symlink        = 1550
	constraint_primarykey   = 1555
	ioerr_fstat             = 1802
	constraint_trigger      = 1811
	ioerr_unlock            = 2058
	constraint_unique       = 2067
	ioerr_rdlock            = 2314
	constraint_vtab         = 2323
	ioerr_delete            = 2570
	constraint_rowid        = 2579
	ioerr_blocked           = 2826
	constraint_pinned       = 2835
	ioerr_nomem             = 3082
	ioerr_access            = 3338
	ioerr_checkreservedlock = 3594
	ioerr_lock              = 3850
	ioerr_close             = 4106
	ioerr_dir_close         = 4362
	ioerr_shmopen           = 4618
	ioerr_shmsize           = 4874
	ioerr_shmlock           = 5130
	ioerr_shmmap            = 5386
	ioerr_seek              = 5642
	ioerr_delete_noent      = 5898
	ioerr_mmap              = 6154
	ioerr_gettemppath       = 6410
	ioerr_convpath          = 6666
	ioerr_vnode             = 6922
	ioerr_auth              = 7178
	ioerr_begin_atomic      = 7434
	ioerr_commit_atomic     = 7690
	ioerr_rollback_atomic   = 7946
	ioerr_data              = 8202
}

Result represents Sqlite Result and Error Codes see https://www.sqlite.org/rescode.html

fn (r Result) is_error() bool

is_error checks if it is an error code.

enum Sqlite3TransactionLevel {
	deferred
	immediate
	exclusive
}

enum SyncMode {
	off
	normal
	full
}

struct C.sqlite3 {
}

struct C.sqlite3_file {
pub mut:
	pMethods &C.sqlite3_io_methods // Methods for an open file
}

https://www.sqlite.org/c3ref/file.html

struct C.sqlite3_io_methods {
mut:
	// version 1 and later fields
	iVersion i32

	xClose                 Sqlite3_file_op_fn
	xRead                  Sqlite3_file_rw_fn
	xWrite                 Sqlite3_file_rw_fn
	xTruncate              Sqlite3_file_truncate_fn
	xSync                  Sqlite3_file_flag_fn
	xFileSize              Sig1
	xLock                  Sqlite3_file_flag_fn
	xUnlock                Sqlite3_file_flag_fn
	xCheckReservedLock     Sig2
	xFileControl           Sqlite3_file_control_fn
	xSectorSize            Sqlite3_file_op_fn
	xDeviceCharacteristics Sqlite3_file_op_fn
	// version 2 and later fields
	xShmMap     Sqlite3_file_shm_map_fn
	xShmLock    Sqlite3_file_shm_lock_fn
	xShmBarrier Sqlite3_file_shm_barrier_fn
	xShmUnmap   Sqlite3_file_flag_fn
	// version 3 and later fields
	xFetch   Sqlite3_file_fetch_fn
	xUnfetch Sqlite3_file_unfetch_fn
}

https://www.sqlite.org/c3ref/io_methods.html

struct C.sqlite3_stmt {
}

struct C.sqlite3_vfs {
pub mut:
	// version 1 and later fields
	iVersion   i32          // Structure version number (currently 3)
	szOsFile   i32          // Size of subclassed sqlite3_file
	mxPathname i32          // Maximum file pathname length
	pNext      &Sqlite3_vfs // Next registered VFS
	zName      &char        // Name of this virtual file system
	pAppData   voidptr      // Pointer to application-specific data

	xOpen   Sqlite3_vfs_open_fn
	xDelete Sqlite3_vfs_delete_fn

	xAccess       Sqlite3_vfs_access_fn
	xFullPathname Sqlite3_vfs_fullpathname_fn
	xDlOpen       Sqlite3_vfs_dlopen_fn
	xDlError      Sqlite3_vfs_dlerror_fn
	xDlSym        Sqlite3_vfs_dlsym_fn // to fn accepting void and returning
	xDlClose      Sqlite3_vfs_dlclose_fn
	xRandomness   Sqlite3_vfs_randomness_fn
	xSleep        Sqlite3_vfs_sleep_fn
	xCurrentTime  Sqlite3_vfs_current_time_fn
	xGetLastError Sqlite3_vfs_get_last_error_fn
	// version two and later only fields
	xCurrentTimeInt64 Sqlite3_vfs_current_time_i64_fn
	// version three and later only fields
	xSetSystemCall  Sqlite3_vfs_set_system_call_fn
	xGetSystemCall  Sqlite3_vfs_get_system_call_fn
	xNextSystemCall Sqlite3_vfs_next_system_call_fn
}

struct ConnectionPool {
mut:
	connections chan DB
	config      string
}

fn (mut pool ConnectionPool) acquire() !DB

acquire gets a connection from the pool

fn (mut pool ConnectionPool) release(conn DB)

release returns a connection back to the pool.

fn (mut pool ConnectionPool) close()

close closes all connections in the pool.

struct DB {
pub mut:
	is_open bool
mut:
	conn &C.sqlite3 = unsafe { nil }
}

fn (db DB) select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ![][]orm.Primitive

select is used internally by V's ORM for processing SELECT queries

fn (db DB) insert(table orm.Table, data orm.QueryData) !

insert is used internally by V's ORM for processing INSERT queries

fn (db DB) update(table orm.Table, data orm.QueryData, where orm.QueryData) !

update is used internally by V's ORM for processing UPDATE queries

fn (db DB) delete(table orm.Table, where orm.QueryData) !

delete is used internally by V's ORM for processing DELETE queries

fn (db DB) last_id() int

last_id is used internally by V's ORM for post-processing INSERT queries

fn (db DB) create(table orm.Table, fields []orm.TableField) !

create is used internally by V's ORM for processing table creation queries (DDL)

fn (db DB) drop(table orm.Table) !

drop is used internally by V's ORM for processing table destroying queries (DDL)

fn (db DB) execute(query string) ![]orm.Row

execute runs a raw SQL query and returns result rows as driver-agnostic orm.Row values.

fn (mut db DB) orm_begin() !

orm_begin starts a transaction for ORM helpers.

fn (mut db DB) orm_commit() !

orm_commit commits a transaction for ORM helpers.

fn (mut db DB) orm_rollback() !

orm_rollback rolls back a transaction for ORM helpers.

fn (mut db DB) orm_savepoint(name string) !

orm_savepoint creates a savepoint for ORM helpers.

fn (mut db DB) orm_rollback_to(name string) !

orm_rollback_to rolls back to a savepoint for ORM helpers.

fn (mut db DB) orm_release_savepoint(name string) !

orm_release_savepoint releases a savepoint for ORM helpers.

fn (db &DB) str() string

str returns a text representation of the DB

fn (mut db DB) close() !

close Closes the DB.

Todo: For all functions, determine whether the connection is closed first, and determine what to do if it is

fn (db &DB) last_insert_rowid() i64

last_insert_rowid returns last inserted rowid https://www.sqlite.org/c3ref/last_insert_rowid.html

fn (db &DB) get_affected_rows_count() int

get_affected_rows_count returns sqlite changes() meaning amount of rows affected by most recent sql query

fn (db &DB) q_int(query string) !int

q_int returns a single integer value, from the first column of the result of executing query, or an error on failure

fn (db &DB) q_string(query string) !string

q_string returns a single string value, from the first column of the result of executing query, or an error on failure

fn (db &DB) exec_map(query string) ![]map[string]string

exec_map executes the query on the given db, and returns an array of maps of strings, or an error on failure

fn (db &DB) exec(query string) ![]Row

exec executes the query on the given db, and returns an array of all the results, or an error on failure

fn (db &DB) exec_one(query string) !Row

exec_one executes a query on the given db. It returns either the first row from the result, if the query was successful, or an error.

fn (db &DB) error_message(code int, query string) IError

error_message returns a proper V error, given an integer error code received from SQLite, and a query string

fn (db &DB) exec_none(query string) int

exec_none executes a query, and returns the integer SQLite result code. Use it, in case you don't expect any row results, but still want a result code. e.g. for queries like these: INSERT INTO ... VALUES (...)

fn (db &DB) exec_param_many(query string, params Params) ![]Row

exec_param_many executes a query with parameters provided as ?, and returns either an error on failure, or the full result set on success

fn (db &DB) exec_param(query string, param string) ![]Row

exec_param executes a query with one parameter provided as a ?, and returns either an error on failure, or the full result set on success

fn (db &DB) exec_param2(query string, param string, param2 string) ![]Row

exec_param2 executes a query with two parameters provided as ? placeholders.

fn (mut db DB) create_table(table_name string, columns []string) !

create_table issues a "create table if not exists" command to the db. It creates the table named 'table_name', with columns generated from 'columns' array. The default columns type will be TEXT.

fn (db &DB) busy_timeout(ms int) int

busy_timeout sets a busy timeout in milliseconds. Sleeps for a specified amount of time when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated. (see https://www.sqlite.org/c3ref/busy_timeout.html)

fn (db &DB) synchronization_mode(sync_mode SyncMode) !

synchronization_mode sets disk synchronization mode, which controls how aggressively SQLite will write data to physical storage. If the command fails to execute an error is returned .off: No syncs at all. (fastest) .normal: Sync after each sequence of critical disk operations. .full: Sync after each critical disk operation (slowest).

fn (db &DB) journal_mode(journal_mode JournalMode) !

journal_mode controls how the journal file is stored and processed. If the command fails to execute an error is returned .off: No journal record is kept. (fastest) .memory: Journal record is held in memory, rather than on disk. .delete: At the conclusion of a transaction, journal file is deleted. .truncate: Journal file is truncated to a length of zero bytes. .persist: Journal file is left in place, but the header is overwritten to indicate journal is no longer valid.

fn (mut db DB) begin(param Sqlite3TransactionParam) !

begin begins a new transaction.

fn (mut db DB) savepoint(savepoint string) !

savepoint create a new savepoint.

fn (mut db DB) commit() !

commit commits the current transaction.

fn (mut db DB) rollback() !

rollback rollbacks the current transaction.

fn (mut db DB) rollback_to(savepoint string) !

rollback_to rollbacks to a specified savepoint.

fn (mut db DB) release_savepoint(savepoint string) !

release_savepoint releases a specified savepoint.

fn (db &DB) tables() ![]string

tables returns the names of all user tables in the database.

fn (db &DB) columns(table string) ![]string

columns returns the column names for the given table.

fn (db &DB) schema(table string) !string

schema returns the CREATE statement(s) for the given table, or for all objects if table is empty.

fn (db &DB) db_size() !i64

db_size returns the database file size in bytes, computed from page_count and page_size.

fn (mut db DB) reset() !

reset returns the connection to initial state for reuse

fn (mut db DB) validate() !bool

validate checks if the connection is still usable

struct Row {
pub mut:
	vals  []string
	names []string
}

fn (row Row) val(index int) string

val returns the value at index.

fn (row Row) values() []string

values returns all row values.

fn (r &Row) get_string(col_name string) string

get_string returns the value for the given column name, or '' if the column is not found or if the corresponding value index is out of range.

fn (r &Row) get_int(col_name string) int

get_int returns the integer value for the given column name, or 0 if the column is not found.

struct Sqlite3TransactionParam {
	transaction_level Sqlite3TransactionLevel = .deferred
}

struct Stmt {
	stmt &C.sqlite3_stmt = unsafe { nil }
	db   &DB             = unsafe { nil }
}