We're going to start building out some tables in SQLite. We've looked at the history, internals, and how SQLite works under the hood, which I think is very interesting and helpful as we move forward. Now, we'll get to start working with SQLite directly.

If you're coming from a MySQL or PostgreSQL background, some of these concepts will be surprising. Namely, SQLite has a different approach to data types, which they refer to as "flexible typing."

Flexible Typing in SQLite

Historically, SQLite has not been strongly typed. You could say it's "weakly typed," but the SQLite team prefers the term "flexible typing." This means that you can create a table with no data types, and SQLite will gladly accept and store any type of data in the columns.

For example, we can create a table called ex with no data types specified:

CREATE TABLE ex(a, b, c);
INSERT INTO ex VALUES (1, 'asdf', 3);
INSERT INTO ex VALUES ('asdf', 2, 'asdf');
SELECT * FROM ex;

In this case, the data types are intermingled within the columns, but SQLite will still store the data safely without any data loss.

This flexible typing can be valuable in certain situations, such as:

• Handling CSV data with "dirty" or mixed data types
• Implementing an entity-attribute-value (EAV) pattern
• Using SQLite as a key-value store where the keys and values can be of any type

However, this flexibility can also be problematic in some cases, which is where the concept of "strict typing" comes into play.

Strict Typing in SQLite

Let's create another table, ex2, with specified data types:

CREATE TABLE ex2 (
  a INTEGER,
  b TEXT
);

INSERT INTO ex2 VALUES (1, 'hello');
INSERT INTO ex2 VALUES ('hello', 2);

Even though we've declared the data types, SQLite will still allow us to insert data that doesn't match the specified types. When we query the table, we can see that SQLite tries to maintain the types as best it can:

SELECT a, typeof(a), b, typeof(b) FROM ex2;

This shows that the integer value 1 is stored as an integer, but the string 'hello' is stored as text in the a column.

The key takeaway is that in default SQLite tables, the data type is applied at the cell level, not the column level. This means you can have a mix of data types within a single column.

SQLite also has a concept of "type affinity," which we'll explore in the next video. This is how SQLite tries to manage the flexible typing and maintain data integrity as best it can.