When creating a new SQLite database or connection to an existing one, with the file name additional options can be given. This is also known as a DSN string. (Data Source Name).
Options are append after the filename of the SQLite database.
The database filename and options are seperated by an ? (Question Mark).
This also applies when using an in-memory database instead of a file.
Options can be given using the following format: KEYWORD=VALUE and multiple options can be combined with the & ampersand.
This library supports dsn options of SQLite itself and provides additional options.
A boolean value can be one of:
-
0nofalseoff -
1yestrueon
- Pre-2.0.0 Conversion Table
- Busy Timeout
- Case Sensitive LIKE
- Defer Foreing Keys
- Foreign Keys
- Ignore CHECK Contraints
- Journal Mode
- Locking Mode
- Mode
- Mutex
- Query Only
- Recursive Triggers
- Secure Delete
- Shared-Cache Mode
- Synchronous
- Timezone
- Transaction Lock
- Vacuum
- Writable Schema
- User Authentication
In version 2.0.0 the names of the DSN keys changed. This table contains the conversion between pre-2.0.0 releases to version 2.0.0.
| Pre-2.0.0 | 2.x |
|---|---|
| cache | cache |
| mutex | mutex |
| _auth | N.A. |
| _auth_user | user |
| _auth_pass | pass |
| _auth_salt | salt |
| _auth_crypt | crypt |
| _auto_vacuum | auto_vacuum |
| _busy_timeout | busy_timeout |
| _case_sensitive_like | case_sensitive_like |
| _cslike | cslike |
| _defer_foreign_keys | defer_foreign_keys |
| _defer_fk | defer_fk |
| _foreign_keys | foreign_keys |
| _fk | fk |
| _ignore_check_constraints | ignore_check_constraints |
| _journal_mode | journal_mode |
| _journal | journal |
| _loc | tz, timezone |
| _locking_mode | locking_mode |
| _locking | locking |
| _query_only | query_only |
| _recursive_triggers | recursive_triggers |
| _rt | rt |
| _secure_delete | secure_delete |
| _synchronous | synchronous |
| _sync | sync |
| _timeout | timeout |
| _txlock | txlock |
| _vacuum | vacuum |
| _writable_schema | writable_schema |
Set the BUSY_TIMEOUT for SQLite3.
For more information see PRAGMA busy_timeout
Key: busy_timeout, timeout
Value: number (Milliseconds)
CaseSensitiveLike controls the behaviour of the LIKE operator.
Default or disabled the LIKE operation is case-insensitive. When enabling this options behaviour of LIKE will become case-sensitive.
Key: case_sensitive_like, cslike
Value: boolean
DeferForeignKeys when enabled will cause the enforcement of all foreign key constraints is delayed until the outermost transaction is committed.
The defer_foreign_keys pragma defaults to false so that foreign key constraints are only deferred if they are created as "DEFERRABLE INITIALLY DEFERRED". The defer_foreign_keys pragma is automatically switched off at each COMMIT or ROLLBACK. Hence, the defer_foreign_keys pragma must be separately enabled for each transaction. This pragma is only meaningful if foreign key constraints are enabled, of course.
For more information see PRAGMA defer_foreign_keys
Key: defer_foreign_keys, defer_fk
Value: boolean
For more information see PRAGMA foreign_keys.
Key: foreign_keys, fk
Value: boolean
For more information see PRAGMA ignore_check_constraints.
Key: ignore_check_constraints
Value: boolean
For more information see sqlite.org
Key: immutable
Value: boolean
Default: false
For more information see PRAGMA journal_mode.
Key: journal_mode, journal
Delete: DELETE
| Values | Description |
|---|---|
| DELETE | Journal DELETE Mode |
| TRUNCATE | Journal TRUNCATE Mode |
| PERSIST | Journal PERSIST Mode |
| MEMORY | Journal MEMORY Mode |
| WAL | Journal WAL Mode |
| OFF | Journal OFF |
Key: lock, locking, locking_mode
Default: normal
| Values | Description |
|---|---|
| normal | Normal Locking Mode |
| exclusive | Exclusive Locking Mode |
In NORMAL locking-mode (the default unless overridden at compile-time using SQLITE_DEFAULT_LOCKING_MODE), a database connection unlocks the database file at the conclusion of each read or write transaction.
When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held.
For more information see sqlite.org
Key: mode
| Values | Description |
|---|---|
| ro | Read Only Mode |
| rw | Read / Write Mode |
| rwc | Read / Write / Create Mode |
| memory | Memory Mode |
Mutex represents how the database opens connections within single or multi-threading
Key: mutex
| Values | Description |
|---|---|
| no | MutexNo will force the database connection opens in the multi-thread threading mode as long as the single-thread mode has not been set at compile-time or start-time |
| full | MutexFull will force the database connection opens in the serialized threading mode unless single-thread was previously selected at compile-time or start-time |
Set database to Query only. For more information see PRAGMA query_only.
Key: query_only
Value: boolean
Default: false
For more information see PRAGMA recursive_triggers
Key: recursive_trigger, rt
Value: boolean
Default: false
Configure data secure deleting mode. For more information see PRAGMA secure_delete
Key: secure_delete
Value: boolean|FAST
The "fast" setting for secure_delete (added circa 2017-08-01) is an intermediate setting in between "on" and "off". When secure_delete is set to "fast", SQLite will overwrite deleted content with zeros only if doing so does not increase the amount of I/O. In other words, the "fast" setting uses more CPU cycles but does not use more I/O. This has the effect of purging all old content from b-tree pages, but leaving forensic traces on freelist pages.
Set cache mode for more information see sqlite.org
Key: cache
| Values | Description |
|---|---|
| shared | Shared Cache Mode |
| private | Private Cache Mode |
For more information see PRAGMA synchronous.
Key: synchronous, sync
Default: NORMAL
| Values | Description |
|---|---|
| 0 | OFF | With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. |
| 1 | NORMAL | When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. |
| 2 | FULL | When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. |
| 3 | EXTRA | EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss. |
Key: tz, timezone
| Values | Description |
|---|---|
| auto | Use time.Local
|
| Timezone | Timezone according to IANA Time ZoneInfo. Example: Europe/Amsterdam
|
Key: txlock, transaction_lock
Default: Deferred
| Values | Description |
|---|---|
| deferred | Deferred transaction behaviour |
| immediate | Immediate transaction behaviour |
| exclusive | Exclusive transaction behaviour |
Deferred means that no locks are acquired on the database until the database is first accessed.
Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation.
The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed.
If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used.
After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database however.
TxLockExclusive exclusive transaction behaviour. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases.
After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.
When is on, the SQLITE_MASTER tables in which database can be changed using ordinary UPDATE, INSERT, and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file.
Key: writable_schema
Value: boolean
Default: OFF
These configuration keys are only working when Go_SQLite3 has been compiled with the sqlite_userauth module.
To use the User Authentication module the package has to be compiled with the tag sqlite_userauth. See Features for more information.
For more information see User Authentication.
Key: user
Value: string
Key: pass
Value: string
Key: salt
Value: string
This key will use either a pre-configured Crypt Encoder to encode username and password.
Key: crypt
Default: SHA1 Encoder
Go-SQLite3 comes default with several crypt encoders.
| Encoder | Description |
|---|---|
| sha1 | SHA1 Encoder |
| ssha1 | Salted SHA1 Encoder |
| sha256 | SHA256 Encoder |
| ssha256 | Salted SHA256 Encoder |
| sha384 | SHA384 Encoder |
| ssha384 | Salted SHA384 Encoder |
| sha512 | SHA512 Encoder |
| ssha512 | Salted SHA512 Encoder |
It is possible to define your own encoder. The string representation of the encoder must then be used as the value of the crypt key.
For more information about creating your own encoder see Crypt Encoder.