Golang SQLite - Scaler Topics

Overview

SQLite is an open-source software library that implements a self-contained, serverless, and zero-configuration SQL database engine which is used with Golang a statically typed compiled programming language with in-built support for garbage collection and concurrent programming. It was designed at Google by Ken Thompson and their team and publicly released in Nov 2009 and used heavily at Google and many tech giants.

Introduction

SQLite is an open-source software library that implements a self-contained, serverless, and zero-configuration SQL database engine.

Self-contained: it has no external dependencies.
Serverless: it does not require a separate server process to operate.
Zero configuration: means no setup or administration is needed.

SQLite Insights:

D. Richard Hipp developed it in the year 2000.
SQLite is written in C language and is available in Windows, Linux, Mac-OS, Android, etc
SQLite size is less than 1 MB (887KB in sqlite3)
SQLite supports most of the query language features found in SQL92 standard

Comments:

There are two ways of creating comments in SQLite:
- For single-line comments, we can use -(two hyphens) or #, for eg #this line is a comment
- For multi-line comments, we use /_ and _/, for eg

SQLite naming convention:

The naming convention is a set of rules for choosing the character sequence to be used for identifiers. An identifier is a name to denote a database, table, column, trigger, etc.

Identifier rules:

Name can be any combination of letters, digits, $ and _
The name cannot start with a digit or $
Names are case insensitive
The name can be of any length (no upper limit)
Names cannot be reserved words

Note: name can be put in [] or ('') to use any name of your choice.

Install SQLite

Please follow the installation instructions in SQLite.

Add golang sqlite3 package:

SQLite3 Create Database

SQLite does not use the CREATE DATABASE command to create a database. Open the command prompt in the folder in which you want to create a database. Then type the following command:

Example:

Database names should be unique and descriptive. Also, note database names are case insensitive.

.open command works as follows: if the database already exists then open the database else create a new one.

Check Database:

We can ensure that the database got created by writing .databases

Example:

Drop Database:

SQLite does not use the DROP DATABASE command to drop a database.
There is no special syntax to drop the database in SQLite.
As SQLite stores the whole database in a single file so if we want to delete the database then manually go to the file location and delete it.

Note: deleting the database would result in the loss of complete information stored in a database.

Populating the SQLite Database

Create Table:

CREATE TABLE is used for creating a table.

The syntax is:

Here, constraints are used to enforce the integrity of the data in a table. When a constraint is defined at a table level using the CONSTRAINT keyword it is called a table-level constraint.

The table-level column can use multiple columns.
The table level constraint can be provided with some names also.

Drop Table:

DROP TABLE is used for dropping a table. All data, indexes, triggers, and constraints will also be dropped.

The syntax is:

Table Information:

.tables: it is used to check if the table got created. It will list all the tables attached to the database.
.schema: it is used to get complete information about a table.

Note: the name of the table cannot start with sqlite because they are reserved for internal use. If we do this we get:

Data Types:

It is an attribute that specifies the type of data that can be stored in the column.
INTEGER: The options supported are: TINY INT, SMALL INT, MEDIUM INT, INT, and BIG INT.
REAL: The options supported are: FLOAT and DOUBLE
TEXT: The options supported are: CHAR(20) and VARCHAR(255)
BOOLEAN: The column will store 0 for false and 1 for true.

Type Affinity:

SQLite uses a dynamic type system, the value stored in a column determines its data type, not the column's data type.
SQLite determines the data type of a value based on its data type according to the following rules:
- Integers are values with no enclosing quotes, decimal points, or exponent.
- Real are values with no enclosing quotes but with decimal points and or exponents.
Text is the values in single quotes or double quotes

Constraints:

These are the rules enforced on data columns on the table.
They are used to limit the type of data that can go into a table to ensure the accuracy and reliability of data.
Constraints can be column-level or table level

Note:

If any constraint is failed we get the:
- Error: CHECK constraint failed: stu
- Error: UNIQUE constraint failed: stu.rno
Once the table is created we cannot add or remove the constraint.

Types of constraints:

Constraint	Description
NOT NULL	To ensure that a column does not have any null value
DEFAULT	It is used for providing a null value
UNIQUE	It is used to prevent two records from having identical values.
PRIMARY KEY	It is used so that each record has unique, not null values.
CHECK	It is used to enforce a condition check on the values being entered into a record.
FOREIGN KEY	It is used to enforce the existing relationship between tables.

ALTER Table:

SQLite ALTER Table statement is used to

Add a column to a table
Rename table

Add column:

To add a column to the existing table we use the following ALTER statement: The syntax is:

Modify, drop, and rename column:

We cannot directly use the ALTER statement to modify, drop or rename a column.
The workaround is:
- Rename the existing table name to some other table name.
- Create a new table with modified definitions, and copy the data into the new table.

Golang SQLite Setup

To use sqlite3 in golang, we need a database driver. For installing the sqlite3, use the command given below:

For installing go-sqlite3 we need gcc as well as CGO_ENABLED=1

Golang SQL Repository

Database Connection Open

For opening the database connection in sqlite3 along with golang, we can use the code snippet given below:

This line initializes the db and it then handles it by using the open method. When the above line is executed, we will get an error that says:

This is because the database/sql doesn’t know and hence we first need to import it before using it.

Initialize Database and Setup Schema

Create Table:

Golang INSERT INTO Database

The INSERT INTO statement is used to add new rows of data into a table in the database.

The first syntax is:

The second syntax is:

In the first syntax, the value must be provided for each of the columns in the table else we get an error: table st1 has 2 columns but one value was supplied.

In the second syntax:

Value must be provided for each of the columns mentioned else we get the error: 1 value for 2 columns
Columns can be listed in any order, just as long as the list of columns and their values line up correctly.
Columns not listed will get their default values.
If a column is not null and does not have a default value and is not provided with any value then we get the error: NOT NULL constraint failed: st2.b
We can insert one or multiple records with each record separated by a comma. If any of the records violates any constraint then none of the records would be inserted.

UPDATE:

The UPDATE statement is used to assign new values to one or more columns of existing rows in a table.

An update statement can update 0 or more rows.

The syntax is:

DELETE:

The DELETE statement is used to delete the existing records from a table.

The syntax is:

Note:
SQLite does not support TRUNCATE. Hence to delete all rows from a table WHERE condition part should not be mentioned.

REPLACE:

REPLACE statement is used to delete and reinsert existing rows.
The idea is when any UNIQUE or PRIMARY KEY constraint is violated then the REPLACE command will delete that row that causes the constraint violation and insert a new row.
REPLACE will perform a process in two steps:
- Delete the row that causes constraint violation.
- Insert new row

Note: if any constraint violation is occurring in the 2nd step then it would not insert the row and roll back the transaction.

If the record does not exist then it would perform [INSERT] operation.

The first syntax is:

The second syntax is:

Golang Select Row.Scan

Now that a few data are inserted, let’s retrieve a few data from the table. For this, we will be using sql.DB.query for retrieving data by id.

By using this, the table returns sql. Rows is a cursor that points to many rows in the table.

The SELECT statement is used to fetch data from a database table.

Syntax of a SELECT statement is

Constraint	Description
DISTINCT	If we use a distinct keyword in our sqlite select statement it returns only distinct rows of data.
ALL	If we use the ALL keyword in the select statement it returns all the rows of data even if it is duplicated.
table-list	It is a list of tables from which you want to get data.
WHERE expr	The WHERE expr is used to define our custom conditions to get required data from tables.
GROUP BY expr-list	The GROUP BY expr-list in SQLite is used to combine one or more rows of results into a single row of output. This is especially useful when the result contains aggregate functions.
HAVING expr	The HAVING expr is similar to WHERE except that HAVING applies after grouping has occurred.
ORDER BY sort-expr-list	The ORDER BY sort-expr-list causes the output rows to be sorted.
LIMIT integer	The LIMIT integer is used to set a limit on the number of rows returned in the result. The optional OFFSET integer following LIMIT specifies how many rows to skip at the beginning of the result set.

Retrieving Many Rows with rows.Scan

For retrieving more than one row from the table, we can use rows.Scan as given in the code example.

Testing List

By using the list method that is threaded through /list we can pull out the items in the list as shown in the example given below:

Output:

After this, we can call it by using the command line as given below:

Conclusion

SQLite is an open-source software library that implements a self-contained, serverless, and zero-configuration SQL database engine.
Self-contained: it has no external dependencies.
Serverless: it does not require a separate server process to operate.
Zero configuration: means no setup or administration is needed.