Creating Data

Getting data into your database

Adding a row

Each item stored in a database
is a single row in a table.

Open the table in SQLite Studio

Double click the users table in the sidebar.

Switch to the data tab

Click the data tab at the top of the window.

Click the “Insert Row” button

The button to insert a row is the
green square with a plus sign on it.

Fill in the row data

Click a cell and then start typing.
Leave user_id and photo as NULL.

Commit your changes

Click the green tick button to save
your changes to the table data.

Adding rows from the shell

We can also script the creation of data.
This is helpful for creating dummy data.

Create a new file dummy-data.sql

Create a new file called dummy-data.sql
in the db folder and open it in your editor.

Create a transaction

BEGIN TRANSACTION;

COMMIT;

Delete all existing users

BEGIN TRANSACTION;

DELETE FROM users;

COMMIT;

Write an insert statement

BEGIN TRANSACTION;

DELETE FROM users;

INSERT INTO users VALUES(
  NULL, 
  'tanya', 
  'test', 
  'Tanya', 
  'Gray', 
  'tanya@gathergather.co.nz', 
  NULL
);

COMMIT;

Open the database in the shell

sqlite3 message-board.db

Execute the dummy data script

.read dummy-data.sql

Visually check the result

Open the table in SQLite Studio and refresh
to see the new rows you’ve added.

Challenge: Additional users

Add at least two extra fake users to
your dummy data script and execute it.

Scripting complex data

Many times we want to include data
which goes beyond just plain text.

Current date or time

date(‘now’)

time(‘now’)

datetime(‘now’)

julianday(‘now’)

Specific date or time

date(‘YYYY-MM-DD’)

time(‘HH:MM:SS’)

datetime(‘YYYY-MM-DD HH:MM:SS’)

julianday(‘123456789’)

Images

Stored as a BLOB, which needs to be Hex format.

You can convert images to Hex here

Random numbers

random() % (:high - :low) + :low

random() % (10 - 5) + 5

Inserting linked data

Most tables will have some form of linked data,
and we need to script these relationships too.

Decide correct order of creation

Rows which “own” other rows need to be created first.

For example, a user needs to be created before their posts
so that we can apply the user_id to the posts.

Write an independent insert statement

Write a top-level insert statement which doesn’t
require foreign key data, such as a user.

Write dependent insert statements

Write an insert statement for a row which
depends on the previously created row.

Use the last row ID as the foreign key

INSERT INTO messages VALUES (
  NULL,
  'Hello',
  datetime('now'),
  last_insert_rowid()
);

Execute the script in the shell

.read dummy-data.sql

Visually check the result

Open the table in SQLite Studio and refresh
to see the new rows you’ve added.

Challenge: Additional messages

Add a message for each of your users
by adding to your dummy-data script.

Thumbs Up!

Creating Data: Complete!

Take me to the next chapter!

Loading...